Android原生SQL接口

简单示例

定义Schema

通过定义Scheme来声明表的表名，表头等信息。

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";
        }
    }
}

实现SQLiteOpenHelper

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);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + CrimeTable.NAME + "(" +
                " _id integer primary key autoincrement, " +
                CrimeTable.Cols.UUID + ", " +
                CrimeTable.Cols.TITLE + ", " +
                CrimeTable.Cols.DATE + ", " +
                CrimeTable.Cols.SOLVED +
                ")"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }
}

使用SQLiteOpenHelper打开数据库

• 数据库的生命周期与应用一样，所以应该getApplicationContext()来作为数据库的Context。
• 调用getWritableDatabase()时的流程：
  1. 确认目标数据库存在。
  2. 如果不存在就创建数据库，然后初始化数据（调用onCreate()）。
  3. 如果存在就打开并确认CrimeDbSchema是否是最新版本。
  4. 如果是旧版就升级到新版（调用onUpgrade()）。

// ...
private final Context mContext;
private final SQLiteDatabase mDatabase;
// ...
mContext = context.getApplicationContext();
mDatabase = new CrimeBaseHelper(mContext).getWritableDatabase();
// ...

创建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);

增

insert()第二的参数是String类型，名为nullColumnHack，当values是一个空ContentValues类型对象时，若不给出第二个参数，可能会SQLite会说无法插入，如果给出一个String作为uuid，可以使SQLite能插入空ContentValues类型对象。

// ...
private final SQLiteDatabase mDatabase;
// ...
ContentValues values = getContentValues(c);
mDatabase.insert(CrimeTable.NAME, null, values);

改

// ...
private final SQLiteDatabase mDatabase;
// ...
String uuidString = crime.getId().toString();
ContentValues values = getContentValues(crime);
mDatabase.update(CrimeTable.NAME, values, CrimeTable.Cols.UUID + " = ?", new String[]{uuidString});

查

使用query()查询，返回类似迭代器的Cursor类对象。

// ...
private final SQLiteDatabase mDatabase;
// ...
Cursor cursor = mDatabase.query(CrimeTable.NAME, null, whereClause, whereArgs, null, null, null);

删

// ...
private final SQLiteDatabase mDatabase;
// ...
String uuidString = crime.getId().toString();
ContentValues values = getContentValues(crime);
mDatabase.delete(CrimeTable.NAME, whereClause, whereArgs);

CursorWrapper类

创建

用CursorWrapper类包装Cursor，使之变得易用。

public class CrimeCursorWrapper extends CursorWrapper {
    public CrimeCursorWrapper(Cursor cursor) {
        super(cursor);
    }

    public Crime getCrime() {
        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;
    }
}

使用示例1

CrimeCursorWrapper cursor = /* 获取cursor */;
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
    crimes.add(cursor.getCrime());
    cursor.moveToNext();
}
cursor.close();

使用示例2

CrimeCursorWrapper cursor = /* 获取cursor */;
try {
    if (cursor.getCount() == 0) {
        return null;
    }
    cursor.moveToFirst();
    return cursor.getCrime();
}
finally {
    cursor.close();
}

注意事项

• Cursor类对象或CursorWrapper类对象用完要close()。