Android中的SQLite

示例代码：

        MessageDatabaseHelper databaseHelper = new MessageDatabaseHelper(this);
        databaseHelper.getWritableDatabase();

public class MessageDatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "message.db";
    private static final int DB_VERSION = 1;

    private static class MessageTable {
        private static final String TABLE_NAME = "message";
        private static final String COLUMN_NAME_ID = "_id";
        private static final String COLUMN_NAME_FROM_NAME = "from_name";
        private static final String COLUMN_NAME_TO_NAME = "to_name";
        private static final String COLUMN_NAME_TIME = "time";
        private static final String COLUMN_NAME_CONTENT = "content";
        private static final String COLUMN_NAME_STATE = "state";
    }

    public MessageDatabaseHelper(Context context) {
        //数据库文件名，数据库版本号
        super(context, DB_NAME, null, DB_VERSION);
    }

    //创建数据库，调用建表语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + MessageTable.TABLE_NAME + " ("
                + MessageTable.COLUMN_NAME_ID + " INTEGER PRIMARY KEY,"
                + MessageTable.COLUMN_NAME_FROM_NAME + " TEXT,"
                + MessageTable.COLUMN_NAME_TO_NAME + " TEXT,"
                + MessageTable.COLUMN_NAME_TIME + " INTEGER,"
                + MessageTable.COLUMN_NAME_CONTENT + " TEXT,"
                + MessageTable.COLUMN_NAME_STATE + " INTEGER"
                + ");");
    }
    //升级数据库，第一个数据库版本不需要写升级语句
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
    //获取只读数据库
    @Override
    public SQLiteDatabase getReadableDatabase() {
        return super.getReadableDatabase();
    }
    //获取读写数据库
    @Override
    public SQLiteDatabase getWritableDatabase() {
        return super.getWritableDatabase();
    }
}

插入数据

    //增加数据
    public void insertMessage(Message message) {
        SQLiteDatabase database = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(MessageTable.COLUMN_NAME_ID, message.getId());
        contentValues.put(MessageTable.COLUMN_NAME_FROM_NAME, message.getFromName());
        contentValues.put(MessageTable.COLUMN_NAME_TO_NAME, message.getToName());
        contentValues.put(MessageTable.COLUMN_NAME_TIME, message.getTime());
        contentValues.put(MessageTable.COLUMN_NAME_CONTENT, message.getContent());
        contentValues.put(MessageTable.COLUMN_NAME_STATE, message.getState());

        database.insertWithOnConflict(
                MessageTable.TABLE_NAME,
                null,
                contentValues,
                SQLiteDatabase.CONFLICT_REPLACE);
    }

删除数据

    public void deleteMessage(long id) {
        SQLiteDatabase database = getWritableDatabase();
        database.delete(
                MessageTable.TABLE_NAME,
                MessageTable.COLUMN_NAME_ID + " = ?",
                new String[] {String.valueOf(id)});
    }

修改数据

    public void updateMessageState(long id, int state) {
        SQLiteDatabase database = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(MessageTable.COLUMN_NAME_STATE, state);

        database.update(
                MessageTable.TABLE_NAME,
                contentValues,
                MessageTable.COLUMN_NAME_ID + " = ?",
                new String[] {String.valueOf(id)});
    }

查询数据

    public List<Message> queryAllMessages() {
        SQLiteDatabase database = getReadableDatabase();

        List<Message> messageList = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = database.query(
                    MessageTable.TABLE_NAME,
                    projection,
                    null,
                    null,
                    null,
                    null,
                    sortOrder);

            if (cursor != null && cursor.moveToFirst()) {
                do {
                    long id = cursor.getLong(cursor.getColumnIndex(MessageTable.COLUMN_NAME_ID));
                    String fromName = cursor.getString(cursor.getColumnIndex(MessageTable.COLUMN_NAME_FROM_NAME));
                    String toName = cursor.getString(cursor.getColumnIndex(MessageTable.COLUMN_NAME_TO_NAME));
                    long time = cursor.getLong(cursor.getColumnIndex(MessageTable.COLUMN_NAME_TIME));
                    String content = cursor.getString(cursor.getColumnIndex(MessageTable.COLUMN_NAME_CONTENT));
                    int state = cursor.getInt(cursor.getColumnIndex(MessageTable.COLUMN_NAME_STATE));

                    Message message = new Message(id, fromName, toName, time, content, state);
                    messageList.add(message);
                } while (cursor.moveToNext());
            }
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return messageList;
    }

批量插入（事务插入）

    public void insertMessage(List<Message> messageList) {
        SQLiteDatabase database = getWritableDatabase();

        try {
            database.beginTransaction();

            for (Message message : messageList) {
                ContentValues contentValues = new ContentValues();
                contentValues.put(MessageTable.COLUMN_NAME_ID, message.getId());
                contentValues.put(MessageTable.COLUMN_NAME_FROM_NAME, message.getFromName());
                contentValues.put(MessageTable.COLUMN_NAME_TO_NAME, message.getToName());
                contentValues.put(MessageTable.COLUMN_NAME_TIME, message.getTime());
                contentValues.put(MessageTable.COLUMN_NAME_CONTENT, message.getContent());
                contentValues.put(MessageTable.COLUMN_NAME_STATE, message.getState());

                database.insertWithOnConflict(
                        MessageTable.TABLE_NAME,
                        null,
                        contentValues,
                        SQLiteDatabase.CONFLICT_REPLACE);
            }

            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    }

数据库升级

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion == 1 && newVersion == 2) {
            updateToVersion2(db);
        }
    }

    private void updateToVersion2(SQLiteDatabase db) {
        db.execSQL("ALTER TABLE " + MessageTable.TABLE_NAME + " ADD "
                + MessageTable.COLUMN_NAME_TYPE + " INTEGER DEFAULT 1;");
    }

数据库的并发访问
WAL Mode 通过同一个openHelper并发访问
Content Provider