Android SqlDelight详解和Demo例子: 点我
Android SqlBrite使用介绍和官方demo详解: 点我
Android AutoValue使用和扩展库: 点我

在说到SqlBrite的时候有介绍到它的官方的例子，官方的栗子是没有用到SqlDelight的。这里我在这个例子的基础上添加SqlDelight，实现SqlBrite和SqlDelight的无缝连接。

这里我偷个懒，fork了SqlBrite的源码，然后添加了Sqldelight的栗子的module，然后把Sqldelight和SqlBrite的代码都加了中文注释； 然后再写了了一个Module，在SqlBrite的sample的基础上无缝结合SqlDelight。

sqlbrite_sqldelight_sample的Github地址: https://github.com/tpnet/sqlbrite

结合前和结合后的对比

这里以sqlbrite_sqldelight_sample 和 sqlbrite_sample作为例子：

1. bean类对比

SqlBrite没有结合SqlDelight:

@AutoValue
public abstract class TodoList implements Parcelable {


    public static final String TABLE = "todo_list";

    public static final String ID = "_id";
    public static final String NAME = "name";
    public static final String ARCHIVED = "archived";

    public abstract long id();

    public abstract String name();

    public abstract boolean archived();


    //利用RXjava进行数据的获取，返回bean列表
    public static Func1<Cursor, List<TodoList>> MAP = new Func1<Cursor, List<TodoList>>() {
        @Override
        public List<TodoList> call(final Cursor cursor) {
            try {
                List<TodoList> values = new ArrayList<>(cursor.getCount());

                while (cursor.moveToNext()) {
                    long id = Db.getLong(cursor, ID);
                    String name = Db.getString(cursor, NAME);
                    boolean archived = Db.getBoolean(cursor, ARCHIVED);
                    values.add(new AutoValue_TodoList(id, name, archived));
                }
                return values;
            } finally {
                cursor.close();
            }
        }
    };


    //构建器，用来生成ContentValues
    public static final class Builder {
        private final ContentValues values = new ContentValues();

        public Builder id(long id) {
            values.put(ID, id);
            return this;
        }

        public Builder name(String name) {
            values.put(NAME, name);
            return this;
        }

        public Builder archived(boolean archived) {
            values.put(ARCHIVED, archived);
            return this;
        }

        public ContentValues build() {
            return values; // TODO defensive copy?
        }
    }
}

SqlBrite结合了SqlDelight之后:

@AutoValue
public abstract class TodoList implements TodoListModel,Parcelable {

public abstract class TodoList implements TodoListModel,Parcelable {


    public final static Factory<TodoList> FACTORY = new Factory<>(new  TodoListModel.Creator<TodoList>() {
        @Override
        public TodoList create(long _id, @NonNull String name, @Nullable String archived) {
            return new AutoValue_TodoList(_id, name, archived);

        }

    });


    @AutoValue
    public abstract static class ListsItem implements Parcelable, List_queryModel {

    }

    public static Func1<Cursor, ListsItem> LIST_ITEM_FUNC = new Func1<Cursor, ListsItem>() {
        @Override
        public ListsItem call(Cursor cursor) {
            return LIST_ITEM_MAPPER.map(cursor);
        }
    };

    //查询title映射
    public final static RowMapper<String> ROW_TITLE_MAPPER = FACTORY.title_queryMapper();


    public final static RowMapper<ListsItem> LIST_ITEM_MAPPER = FACTORY.list_queryMapper(new List_queryCreator<ListsItem>() {
        @Override
        public ListsItem create(long _id, @NonNull String name, long item_count) {
            return new AutoValue_TodoList_ListsItem(_id, name, item_count);
        }
    });

}


}

可以看到: 结合后的bean我们写的代码量少了，组合ContentValues这些事情不用我们手动做了，解析cursor不用我们手动做了，各种操作对象可以放在这里。

2. SQLiteOpenHelper类对比

结合前:

final class DbOpenHelper extends SQLiteOpenHelper {
    private static final int VERSION = 1;

    //创建todo_list表的语句
    private static final String CREATE_LIST = ""
            + "CREATE TABLE " + TodoList.TABLE + "("
            + TodoList.ID + " INTEGER NOT NULL PRIMARY KEY,"
            + TodoList.NAME + " TEXT NOT NULL,"
            + TodoList.ARCHIVED + " INTEGER NOT NULL DEFAULT 0"
            + ")";

    //创建todo_item表的语句
    private static final String CREATE_ITEM = ""
            + "CREATE TABLE " + TodoItem.TABLE + "("
            + TodoItem.ID + " INTEGER NOT NULL PRIMARY KEY,"
            + TodoItem.LIST_ID + " INTEGER NOT NULL REFERENCES " + TodoList.TABLE + "(" + TodoList.ID + "),"
            + TodoItem.DESCRIPTION + " TEXT NOT NULL,"
            + TodoItem.COMPLETE + " INTEGER NOT NULL DEFAULT 0"
            + ")";


    //创建 单列索引，加速查询
    private static final String CREATE_ITEM_LIST_ID_INDEX =
            "CREATE INDEX item_list_id ON " + TodoItem.TABLE + " (" + TodoItem.LIST_ID + ")";

    //构造器，创建数据库
    public DbOpenHelper(Context context) {
        super(context, "todo.db", null /* factory */, VERSION);
    }


    //创建数据库的时候回调
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建对应的表和索引
        db.execSQL(CREATE_LIST);
        db.execSQL(CREATE_ITEM);
        db.execSQL(CREATE_ITEM_LIST_ID_INDEX);

        //插入数据到todo_list表,返回id
        long groceryListId = db.insert(TodoList.TABLE, null, new TodoList.Builder()
                .name("Grocery List")
                .build());

        //根据todo_list表的id，插入数据到todo_item表，
        db.insert(TodoItem.TABLE, null, new TodoItem.Builder()
                .listId(groceryListId)
                .description("Beer")
                .build());
        db.insert(TodoItem.TABLE, null, new TodoItem.Builder()
                .listId(groceryListId)
                .description("Point Break on DVD")
                .build());
        db.insert(TodoItem.TABLE, null, new TodoItem.Builder()
                .listId(groceryListId)
                .description("Bad Boys 2 on DVD")
                .build());

        ....(和上面的套路一样这里省略了)

    }

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

结合后:

final class DbOpenHelper extends SQLiteOpenHelper {
    private static final int VERSION = 1;


    //构造器，创建数据库
    public DbOpenHelper(Context context) {
        super(context, "todo.db", null /* factory */, VERSION);
    }


    //创建数据库的时候回调
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建对应的表和索引
        db.execSQL(TodoList.CREATE_TABLE);
        db.execSQL(TodoItem.CREATE_TABLE);
        db.execSQL(CREATE_ITEM_LIST_ID_INDEX);

        //初始化插入实体
        TodoList.Insert_list insertList = new TodoListModel.Insert_list(db);
        TodoItem.Insert_item insertItem = new TodoItemModel.Insert_item(db);


        //插入数据到todo_list表,返回id
        insertList.bind("Grocery List", "0");
        long groceryListId = insertList.program.executeInsert();


        //根据todo_list表的id，插入数据到todo_item
        insertItem.bind(groceryListId, "Beer", false);
        insertItem.program.executeInsert();

        insertItem.bind(groceryListId, "Point Break on DVD", false);
        insertItem.program.executeInsert();

        insertItem.bind(groceryListId, "Bad Boys 2 on DVD", false);
        insertItem.program.executeInsert();


        ...(和上面的套路一样,这里省略)


    }

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

可以看到： 结合后的SQLiteOpenHelper sql语句不用组合放在这里，清晰多了； 创表语句放在sq文件中,在自动生成的model，也可以得到创表的语句； 插增删改查利用bind来实现，更方便了，不用自己组合ContentValue，逻辑清晰，代码简介。

3. 查询对比

结合前:

subscription = db.createQuery(ListsItem.TABLES, ListsItem.QUERY)
                .mapToList(ListsItem.MAPPER)   //映射到ListItem的MAPPER
                .observeOn(AndroidSchedulers.mainThread()) //设置订阅者在主线程进行
                .subscribe(adapter);


static Func1<Cursor, ListsItem> MAPPER = new Func1<Cursor, ListsItem>() {
        @Override
        public ListsItem call(Cursor cursor) {
            long id = Db.getLong(cursor, TodoList.ID);
            String name = Db.getString(cursor, TodoList.NAME);
            int itemCount = Db.getInt(cursor, ITEM_COUNT);
            return new AutoValue_ListsItem(id, name, itemCount);
        }
    };

结合后:

//创建查询
SqlDelightStatement sqLiteStatement = TodoList.FACTORY.list_query();

subscription = db.createQuery(TodoList.TABLE_NAME, sqLiteStatement.statement, sqLiteStatement.args)
        .mapToList(TodoList.LIST_ITEM_FUNC)   //映射到ListItem的MAPPER
        .observeOn(AndroidSchedulers.mainThread()) //设置订阅者在主线程进行
        .subscribe(adapter);


public static Func1<Cursor, ListsItem> MAPPER = new Func1<Cursor, ListsItem>() {
        @Override
        public ListsItem call(Cursor cursor) {
            return LIST_ITEM_MAPPER.map(cursor);
        }
    };

可以看到结合后的Curor解析不用我们手动解析，对应的查询语句和参数可以在SqlDelightStatement获取。

4. 更新添加对比

结合前：

Observable.combineLatest(createClicked, RxTextView.textChanges(name),
                new Func2<String, CharSequence, String>() {
                    @Override
                    public String call(String ignored, CharSequence text) {
                        return text.toString();
                    }
                }) //
                .observeOn(Schedulers.io())
                .subscribe(new Action1<String>() {
                    @Override
                    public void call(String description) {
                        db.insert(TodoItem.TABLE,
                                new TodoItem.Builder().listId(getListId()).description(description).build());
                    }
                });

结合后:

Observable.combineLatest(createClicked, RxTextView.textChanges(name),
                new Func2<String, CharSequence, String>() {
                    @Override
                    public String call(String ignored, CharSequence text) {
                        return text.toString();
                    }
                }) //
                .observeOn(Schedulers.io())
                .subscribe(new Action1<String>() {
                    @Override
                    public void call(String description) {

                        //初始化插入对象
                        TodoItemModel.Insert_item insertItem = new TodoItemModel.Insert_item(db.getWritableDatabase());

                        //填入数据
                        insertItem.bind(getListId(), description, false);
                        //开始插入，这里会Rxjava会异步回调，进行更新列表
                        db.executeInsert(insertItem.table, insertItem.program);

                        //注意这里不要使用下面的插入代码，会导致Rxjava不能异步回调发送数据
                        //insertItem.program.executeInsert();

                    }
                });

可以看到结合后：利用了SqlDelight生成的对应的Model类进行更新插入，不需要自己编写ContentValue，更方便的插入。

当然还有其他的变化。大家可以看看源码。