《Android进阶》Sqlite的使用
之前认为Sqlite只能一次性创建多个表,其实不是
关键是对Sqlite的操作需要一些技巧:
package com.example.mydemo; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; import android.widget.Toast; public class MyDatabaseHelper extends SQLiteOpenHelper{ public static final String CREATE_BOOK = "create table book (" + "id integer primary key autoincrement, " + "author text, " + "price real, " + "pages integer, " + "name text)"; public static final String CREATE_CATEGORY = "create table Category (" + "id integer primary key autoincrement, " + "category_name text, " + "category_code integer)"; private Context mContext; public MyDatabaseHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); mContext = context; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_BOOK); // db.execSQL(CREATE_CATEGORY); /* db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)", new String[] { "The Da Vinci Code", "Dan Brown", "454", "16.96" });*/ Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).show(); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub switch(oldVersion){ case 3: db.execSQL(CREATE_CATEGORY); case 4: case 5: case 6: db.execSQL("alter table Book add column category_idd integer"); default: } } }
目前很好奇,怎么得到数据库的版本(当然db.getVersion())肯定可以
但如果仅仅是一个.db文件,没发现哪个可视化工具可以直接查看
package com.example.mydemo; import android.app.Activity; import android.app.Fragment; import android.content.ContentValues; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.view.LayoutInflater; import android.view.View; import android.view.View.OnClickListener; import android.view.ViewGroup; import android.widget.Button; import android.widget.TextView; /** * A placeholder fragment containing a simple view. */ public class PlaceholderFragment extends Fragment { OnDtaReceivedListener mCallback; private MyDatabaseHelper dbHelper; // Container Activity must implement this interface public interface OnDtaReceivedListener { public void onTextSelected(View view); } public PlaceholderFragment() { } @Override public void onAttach(Activity activity) { // TODO Auto-generated method stub super.onAttach(activity); try { mCallback = (OnDtaReceivedListener) activity; } catch (ClassCastException e) { throw new ClassCastException(activity.toString() + " must implement OnDtaReceivedListener"); } } @Override public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) { View rootView = inflater.inflate(R.layout.fragment_main, container, false); dbHelper = new MyDatabaseHelper(getActivity(), "BookStore.db", null, 7); Button createDatabase = (Button) rootView .findViewById(R.id.create_database); createDatabase.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { dbHelper.getWritableDatabase(); } }); Button addData = (Button) rootView.findViewById(R.id.add_data); addData.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { SQLiteDatabase db = dbHelper.getWritableDatabase(); ContentValues values = new ContentValues(); // 开始组装第一条数据 values.put("name", "The DaVinci Code"); values.put("author", "Dan Brown"); values.put("pages", 454); values.put("price", 16.96); db.insert("Book", null, values); // 插入第一条数据 values.clear(); // 开始组装第二条数据 values.put("name", "The Lost Symbol"); values.put("author", "Dan Brown"); values.put("pages", 510); values.put("price", 19.95); db.insert("Book", null, values); // 插入第二条数据 values.clear(); // 开始组装第三条数据 可以允许某条数据为空 values.put("name", "The Lost1"); values.put("pages", 510); values.put("price", 19.95); db.insert("Book", null, values); // 插入第二条数据 } }); Button updateData = (Button) rootView.findViewById(R.id.update_data); updateData.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { SQLiteDatabase db = dbHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("price", 10.99); db.update("Book", values, "name = ?", new String[] { "The DaVinci Code" }); //上述代码想表达的意图就是,将名字是 The Da Vinci Code的这本书的价格改成 10.99 } }); Button deleteButton = (Button) rootView.findViewById(R.id.delete_data); deleteButton.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.beginTransaction(); // 开启事务 db.delete("book", "name = ?", new String[] { "The Da Vinci Code" }); db.delete("book", "id = ?", new String[] { "4" }); } }); return rootView; } }
布局文件:
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" > <Button android:id="@+id/create_database" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Create database" /> <Button android:id="@+id/add_data" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Add data" /> <Button android:id="@+id/update_data" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Update data" /> <Button android:id="@+id/delete_data" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Delete data" /> </LinearLayout>