Android中SQLite使用示例

我们首先定义一下数据库的表名和列名：

public class CrimeDbSchema {    public static final class CrimeTable {        public static final String NAME = "crimes";        public static final class Cols {            public static final String UUID = "uuid";            public static final String TITLE = "title";            public static final String DATE = "date";            public static final String SOLVED = "solved";        }    }}

Android中数据库的创建，一般依赖于继承SQLiteOpenHelper的子类，例如：

public class CrimeBaseHelper extends SQLiteOpenHelper{    private static final int VERSION = 1;    private static final String DATABASE_NAME = "crimeBase.db";    public CrimeBaseHelper(Context context) {        super(context, DATABASE_NAME, null, VERSION);    }    //重写onCreate函数，完成数据库的创建    @Override    public void onCreate(SQLiteDatabase sqLiteDatabase) {        //调用execSQL创建数据库        //指定表的名称及其中的列        sqLiteDatabase.execSQL("create table " + CrimeTable.NAME + "(" +                "_id integer primary key autoincrement, " +                CrimeTable.Cols.UUID + ", " +                CrimeTable.Cols.TITLE + ", " +                CrimeTable.Cols.DATE + ", " +                CrimeTable.Cols.SOLVED + ")");    }    @Override    //数据库升级时才会调用，通过VERSION来判断    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {    }}

定义了SQLiteOpenHelper后，代码中通过SQLiteOpenHelper的接口就可以获得数据库，类似的代码如下：

//在调用SQLiteOpenHelper的getWritableDatabase时，SQLiteOpenHelper会判断数据是否创建过数据库//没有创建过，则回调子类的onCreate函数进行创建; 否则，根据版本的变化情况，决定是否调用onUpgrademDataBase = new CrimeBaseHelper(context.getApplicationContext()).getWritableDatabase();

向数据库写入信息时，一般会用到ContentValues，例如：

private static ContentValues getContentValues(Crime crime) {    //ContentValues以键值对的方式写入信息    //其中键就是数据库中的列    ContentValues values = new ContentValues();    values.put(CrimeTable.Cols.UUID, crime.getId().toString());    values.put(CrimeTable.Cols.TITLE, crime.getTitle());    values.put(CrimeTable.Cols.DATE, crime.getDate().getTime());    values.put(CrimeTable.Cols.SOLVED, crime.isSolved() ? 1 : 0);    return values;}

有了ContentValues后，就可对数据库进行增、删、改、查操作，例如：

public void addCrime(Crime c) {    ContentValues values = getContentValues(c);    //数据库的插入操作    mDataBase.insert(CrimeTable.NAME, null, values);}public void removeCrime(Crime c) {    //数据库删除操作    mDataBase.delete(CrimeTable.NAME,            CrimeTable.Cols.UUID + " = ?",            new String[] {c.getId().toString()});}public void updateCrime(Crime c) {    String uuidString = c.getId().toString();    ContentValues values = getContentValues(c);    //数据库更新的操作    mDataBase.update(CrimeTable.NAME, values,            CrimeTable.Cols.UUID + " = ?",            new String[] {uuidString});}private CrimeCursorWrapper queryCrimes(String whereClause, String[] whereArgs) {    //query接口将返回一个Cursor对象    Cursor cursor = mDataBase.query(            CrimeTable.NAME,            null,            whereClause,            whereArgs,            null,            null,            null);    //从Cursor对象中，再进一步解析出数据    //由于Cursor解析数据的过程比较繁琐，因此一般定义一个CursorWrapper来封装对应的操作    return new CrimeCursorWrapper(cursor);}

以上API参数的具体含义，参考SQLiteDatabase中的注释即可，此处不做进一步描述。

CursorWrapper实现的例子，如下：

public class CrimeCursorWrapper extends CursorWrapper{    public CrimeCursorWrapper(Cursor cursor) {        super(cursor);    }    public Crime getCrime() {        //CursorWrapper继承Cursor的全部方法        String uuidString = getString(getColumnIndex(CrimeTable.Cols.UUID));        String title = getString(getColumnIndex(CrimeTable.Cols.TITLE));        long date = getLong(getColumnIndex(CrimeTable.Cols.DATE));        int isSolved = getInt(getColumnIndex(CrimeTable.Cols.SOLVED));        Crime crime = new Crime(UUID.fromString(uuidString));        crime.setTitle(title);        crime.setDate(new Date(date));        crime.setSolved(isSolved != 0);        return crime;    }}

当然，在获取到Cursor后，需要主动关闭它，例如：

public List<Crime> getCrimes() {    List<Crime> crimes = new ArrayList<>();    CrimeCursorWrapper cursor = queryCrimes(null, null);    //前面已经提到过，CursorWrapper继承了Cursor的所有方法    try {        cursor.moveToFirst();        while(!cursor.isAfterLast()) {            crimes.add(cursor.getCrime());            cursor.moveToNext();        }    } finally {        //使用后关闭        cursor.close();    }    return crimes;}