Android应用开发笔记(3)：SQLite数据库的插入删除查询操作

本文讲如何使用Android中内置的SQLite轻量数据库，Android SDK中已经对其进行了封装，使用起来相当简单。创建类继承SQLiteOpenHelper就可以将数据库的创建和应用版本更新后数据库的重建纳入自动管理中。本文实现一个简单的Sqlite数据库，存储人名和电话号码。效果如下截图：

 

主Activity 类SqliteSample.java 代码：

package jtapp.sqlitesamples; import android.app.Activity; import android.database.Cursor; import android.os.Bundle; import android.view.View; import android.view.View.OnClickListener; import android.widget.Button; import android.widget.TextView; public class SqliteSample extends Activity { private DBHelper mDBHelper = null; private Button btCreateDb = null; private Button btInsertData = null; private Button btViewData = null; private Button btDelOne = null; private Button btClearAll = null; /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); btCreateDb = (Button)findViewById(R.id.Button01); btCreateDb.setOnClickListener(new ClickViewHandler()); btInsertData = (Button)findViewById(R.id.Button02); btInsertData.setOnClickListener(new ClickViewHandler()); btInsertData.setEnabled(false); btViewData = (Button)findViewById(R.id.Button03); btViewData.setOnClickListener(new ClickViewHandler()); btViewData.setEnabled(false); btDelOne = (Button)findViewById(R.id.Button04); btDelOne.setOnClickListener(new ClickViewHandler()); btDelOne.setEnabled(false); btClearAll = (Button)findViewById(R.id.Button05); btClearAll.setOnClickListener(new ClickViewHandler()); btClearAll.setEnabled(false); } public class ClickViewHandler implements OnClickListener { @Override public void onClick(View v) { if (v == btCreateDb) { createDB(); } else if (v == btInsertData) { insertSomeRecords(); } else if (v == btViewData) { ViewRecords(); } else if (v == btDelOne) { DelOne(); } else if (v == btClearAll) { mDBHelper.delAllPeople(); } } } private void createDB() { String DB_NAME = "sqlitedb1"; mDBHelper = new DBHelper( SqliteSample.this, DB_NAME, null, 1); assert(mDBHelper != null); btCreateDb.setEnabled(false); btInsertData.setEnabled(true); btViewData.setEnabled(true); btDelOne.setEnabled(true); btClearAll.setEnabled(true); } private void insertSomeRecords() { mDBHelper.addPeople("一休", "18602155856"); mDBHelper.addPeople("巴巴", "13368565525"); mDBHelper.addPeople("项羽", "057156856225"); } private void ViewRecords() { // Make the query Cursor c = mDBHelper.getWritableDatabase().query( DBHelper.TB_NAME,null,null,null,null,null, DBHelper.NAME + " ASC"); StringBuilder sbRecords = new StringBuilder(""); if (c.moveToFirst()) { int idxID = c.getColumnIndex(DBHelper.ID); int idxName = c.getColumnIndex(DBHelper.NAME); int idxNumber = c.getColumnIndex(DBHelper.NUMBER1); // Iterator the records do { sbRecords.append(c.getInt(idxID)); sbRecords.append(". "); sbRecords.append(c.getString(idxName)); sbRecords.append(", "); sbRecords.append(c.getString(idxNumber)); sbRecords.append("/n"); } while (c.moveToNext()); } c.close(); // Refresh the content of TextView ((TextView)(findViewById( R.id.TextView01))).setText(sbRecords); } private void DelOne() { int id; Cursor c = mDBHelper.getWritableDatabase().query( DBHelper.TB_NAME,null,null,null,null,null, DBHelper.NAME + " ASC"); if (c.moveToFirst()) { int idxID = c.getColumnIndex(DBHelper.ID); id = c.getInt(idxID); mDBHelper.delPeople(id); } } }

ui界面 main.xml 代码，主要是几个bt的放置：

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent"> <TextView android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/hello" /> <TableRow android:id="@+id/TableRow01" android:layout_width="wrap_content" android:layout_height="wrap_content"> <Button android:text="create db" android:id="@+id/Button01" android:height="30px" android:layout_width="wrap_content" android:layout_height="wrap_content" /> <Button android:text="insert some records" android:id="@+id/Button02" android:height="30px" android:layout_width="wrap_content" android:layout_height="wrap_content" /> </TableRow> <TableRow android:id="@+id/TableRow01" android:layout_width="wrap_content" android:layout_height="wrap_content"> <Button android:text="delete one" android:id="@+id/Button04" android:height="30px" android:layout_width="wrap_content" android:layout_height="wrap_content" /> <Button android:text="clear all" android:id="@+id/Button05" android:height="30px" android:layout_width="wrap_content" android:layout_height="wrap_content" /> </TableRow> <Button android:text="view records" android:id="@+id/Button03" android:height="30px" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_gravity="center"/> <TextView android:text="..." android:id="@+id/TextView01" android:layout_width="wrap_content" android:layout_height="wrap_content" /> </LinearLayout>

 

数据库操作类 DBHelper.java 代码：

package jtapp.sqlitesamples; import android.content.ContentValues; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; public class DBHelper extends SQLiteOpenHelper { public static final String TB_NAME = "people"; public static final String ID = "_id"; public static final String NAME = "name"; public static final String NUMBER1 = "number1"; public DBHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); this.getWritableDatabase(); } /** * should be invoke when you never use DBhelper * To release the database and etc. */ public void Close() { this.getWritableDatabase().close(); } public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE IF NOT EXISTS " + TB_NAME + " (" + ID + " INTEGER PRIMARY KEY," + NAME + " VARCHAR," + NUMBER1 + " VARCHAR)"); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS "+TB_NAME); onCreate(db); } public void addPeople(String name, String number) { ContentValues values = new ContentValues(); values.put(DBHelper.NAME, name); values.put(DBHelper.NUMBER1, number); this.getWritableDatabase().insert( DBHelper.TB_NAME, DBHelper.ID, values); } public void delPeople(int id) { this.getWritableDatabase().delete( DBHelper.TB_NAME, this.ID + " = " + id, null); } public void delAllPeople() { this.getWritableDatabase().delete( DBHelper.TB_NAME, null, null); } }

数据库操作主要实现了创建数据库，升级数据库的具体操作，封装了插入单条数据，删除单条数据，和删除全部数据的操作。