Android SQLite应用

安卓提供了SQLiteDatabase类来管理SQLIte，并提供了帮助类SQLiteOpenHelper，使用这两个类可以轻松地完成数据库的操作。

1、SQLiteOpenHelper类

在构造函数里面指定数据库的名称和版本，并调用getReadableDatabase或getWritableDatabase来获取数据库。
在获取数据库时，如果与数据库版本不同，会调用onCreate、onUpgrade等方法。

final int version = db.getVersion();if (version != mNewVersion) {if (db.isReadOnly()) {throw new SQLiteException("Can't upgrade read-only database from version " +db.getVersion() + " to " + mNewVersion + ": " + mName);}db.beginTransaction();try {if (version == 0) {onCreate(db);} else {if (version > mNewVersion) {onDowngrade(db, version, mNewVersion);} else {onUpgrade(db, version, mNewVersion);}}db.setVersion(mNewVersion);db.setTransactionSuccessful();} finally {db.endTransaction();}}

2、自定义PersonSQLiteOpenHelper

首先定义版本号为1，调用onCreate方法。随后修改为2，调用onUpgrade方法。

public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {public final static String LOGTAG       = "PersonSQLiteOpenHelper";public final static String DB_NAME      = "person.db";public final static String TABLE_NAME   = "contract";public final static int VERSION         = 1;public final static String COL_NAME     = "name";public final static String COL_ADDR     = "addr";public final static String TABLE_CREATE = "create table if not exists " + TABLE_NAME + "("+ "id integer primary key autoincrement not null,"+ COL_NAME + " text not null, "+ COL_ADDR + " text not null"+ ")";public PersonSQLiteOpenHelper(Context context) {this(context, DB_NAME, null, VERSION);}public PersonSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {super(context, name, factory, version);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(TABLE_CREATE);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {Log.i(LOGTAG, "onUpgrade");}}

3、SQLiteDatabase类

(1) 打开数据库
调用SQLiteOpenHelper的getWritableDatabase或getReadableDatabse方法。
也可以使用SQLiteDatabase的openDatabase或者openOrCreateDatabse方法。

(2) 数据库操作

• 查询，query或rawQuery。
• 增加，insert。
• 删除，delete。
• 修改，update。

public List<Person> query() {List<Person> list = new ArrayList<Person>();Cursor cursor = getReadableDatabase().query(TABLE_NAME,new String[]{COL_NAME, COL_ADDR}, null, null, null, null, null);while (cursor.moveToNext()) {Person person = new Person();person.name = cursor.getString(cursor.getColumnIndex(COL_NAME));person.addr = cursor.getString(cursor.getColumnIndex(COL_ADDR));list.add(person);}cursor.close();return list;}public void add(String name, String addr) {ContentValues values = new ContentValues();values.put(COL_NAME, name);values.put(COL_ADDR, addr);getWritableDatabase().insert(TABLE_NAME, null, values);}public void delete(String name) {getWritableDatabase().delete(TABLE_NAME, COL_NAME + "=?", new String[]{name});}public void modify(String name, String addr) {ContentValues values = new ContentValues();values.put(COL_NAME, name);values.put(COL_ADDR, addr);getWritableDatabase().update(TABLE_NAME, values, COL_NAME + "=?", new String[]{name});}

4、SQLIte工具SQLite Expert

下载地址http://www.sqliteexpert.com/download.html