android中sqlite的使用

创建类DatabaseHelper继承系统api中的SQLiteOpenHelper，并重写其中的onCreate和onUpgrade来进行数据库及表的创建和升级。

通过SQLiteOpenHelper中的getWritableDatabase()方法取得SQLiteDatabase类型的实例db，

通过db进行增删改查操作。

代码如下：

package com.android.db.test; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class DBAdapter { public static final String KEY_ROWID = "_id"; public static final String KEY_ISBN = "isbn"; public static final String KEY_TITLE = "title"; public static final String KEY_PUBLISHER = "publisher"; private static final String TAG = "@dbtest@"; private static final String DATABASE_NAME = "books"; private static final String DATABASE_TABLE = "titles"; private static final int DATABASE_VERSION = 1; private static final String DATABASE_CREATE = "create table titles (_id integer primary key autoincrement, " + "isbn text not null, title text not null, " + "publisher text not null);"; private final Context context; private DatabaseHelper DBHelper; private SQLiteDatabase db; public DBAdapter(Context ctx) { this.context = ctx; this.DBHelper = new DatabaseHelper(this.context); } private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(DATABASE_CREATE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS titles"); onCreate(db); } } public DBAdapter open() throws SQLException { this.db = this.DBHelper.getWritableDatabase(); return this; } public void close() { this.DBHelper.close(); } public long insertTitle(String isbn, String title, String publisher) { ContentValues initialValues = new ContentValues(); initialValues.put(KEY_ISBN, isbn); initialValues.put(KEY_TITLE, title); initialValues.put(KEY_PUBLISHER, publisher); return db.insert(DATABASE_TABLE, null, initialValues); } public boolean deleteTitle(long rowId) { return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0; } public Cursor getAllTitles() { return db.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_ISBN, KEY_TITLE, KEY_PUBLISHER }, null, null, null, null, null); } public Cursor getTitle(long rowId) throws SQLException { Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] { KEY_ROWID, KEY_ISBN, KEY_TITLE, KEY_PUBLISHER }, KEY_ROWID + "=" + rowId, null, null, null, null, null); if (mCursor != null) { mCursor.moveToFirst(); } return mCursor; } public boolean updateTitle(long rowId, String isbn, String title, String publisher) { ContentValues args = new ContentValues(); args.put(KEY_ISBN, isbn); args.put(KEY_TITLE, title); args.put(KEY_PUBLISHER, publisher); return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0; } }

 

创建一个Activity进行测试：

package com.android.db.test; import java.util.ArrayList; import java.util.List; import android.app.ListActivity; import android.database.Cursor; import android.os.Bundle; import android.util.Log; import android.widget.ArrayAdapter; public class DBTestActivity extends ListActivity { private static final String TAG = "@dbtest@"; private DBAdapter dbAdapter = null; List<String> mList = new ArrayList<String>(); /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); // setContentView(R.layout.main); /* * create and open DB */ this.dbAdapter = new DBAdapter(this); this.dbAdapter.open(); /* *         * insert books to DB         */ insertBook(); /* get all book list */ loadAllList(); /* *         * display all book as a list         */ setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, mList)); //setListAdapter(new ArrayAdapter<String>(this, R.layout.file_row, mList)); getListView().setTextFilterEnabled(true); } /* *     * insert titls to DB     */ public void insertBook() { long id; id = dbAdapter.insertTitle("0470285818", "C# 2008 Programmer's Reference", "Wrox"); id = dbAdapter.insertTitle("047017661X", "Professional Windows Vista", "Wrox"); id = dbAdapter.insertTitle("0470285818", "C Programming", "Wrox"); Log.i(TAG, "id = " + id); } @Override protected void onDestroy() { super.onDestroy(); dbAdapter.close(); } /* *     * load all book list from DB     */ public void loadAllList() { mList.add("ALL BOOK LIST"); Cursor c = dbAdapter.getAllTitles(); if (c.moveToFirst()) { do { int nameColumn = c.getColumnIndex(DBAdapter.KEY_TITLE); mList.add(c.getString(nameColumn)); } while (c.moveToNext()); } } }