sqldelight是一個輕量級的sqlite操作代碼生成框架腾窝，直接編寫sql語句生成代碼。

環(huán)境

buildscript {
  repositories {
    mavenCentral()
  }
  dependencies {
    classpath 'com.squareup.sqldelight:gradle-plugin:0.6.1'
  }
}

apply plugin: 'com.squareup.sqldelight'

安裝Android Studio插件虹脯，可以對編寫的sql語句進行校驗
Android Studio -> Preferences -> Plugins -> Browse repositories -> 搜索SQLDelight安裝

基本使用

1、在項目main/java同級目錄下創(chuàng)建sqldelight存放sql語句的目錄main/sqldelight/you/path
2归形、創(chuàng)建一個sq文件Department.sq
（1）創(chuàng)建表

CREATE TABLE department(
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
    shortname TEXT ,
    fullname TEXT ,
    code TEXT ,
    createtime TEXT ,
    pid INTEGER
);

（2）添加基本的搜索、插入暇榴、刪除的sql語句

selectAll:
SELECT * FROM department;

deleteAll:
DELETE FROM department;

insertDepartment:
INSERT INTO department(_id,shortname,fullname,code,createtime)
VALUES (?,?,?,?,?);

selectDepartment:
SELECT * FROM department
WHERE _id = ?;

3、使用Build->Make project生成Model文件

image.png

在build相應目錄下可以找到生成的Model文件DepartmentModel

使用google的AutoValue 快速的創(chuàng)建一個實現(xiàn)Department.java蕉世。

@AutoValue
public abstract class Department implements DepartmentModel {
    public static final Factory<Department>FACTORY = new Factory<>(AutoValue_Department::new);
}

4蔼紧、對生成的DepartmentModel進行使用。（結(jié)合sqlbrite可以實現(xiàn)響應式數(shù)據(jù)查詢）
創(chuàng)建一個數(shù)據(jù)源接口DepartmentDataSource.java

public interface DepartmentDataSource {
    void insertDepartment(Department department);
    void insertDepartments(List<Department> departments) ;
    List<Department> selectAllDepartment();
    Observable<List<Department>>listenDepartments();
}

對數(shù)據(jù)源做實現(xiàn)DepartmentLocalDataSource.java

public class DepartmentLocalDataSource implements DepartmentDataSource {

    private static DepartmentDataSource INSTANCE;
    protected final BriteDatabase mDatabaseHelper;


    public DepartmentLocalDataSource(Context context){
        SqlitedelightDbHelper departmentDbHelper = new SqlitedelightDbHelper(context);
        SqlBrite sqlBrite = new SqlBrite.Builder().build();
        mDatabaseHelper = sqlBrite.wrapDatabaseHelper(departmentDbHelper, Schedulers.io());
    }
    public static DepartmentDataSource getInstance(@NonNull Context context){
        if(INSTANCE == null){
            INSTANCE = new DepartmentLocalDataSource(context);
        }
        return INSTANCE;
    }
    public static void destroyInstance(){
        if(INSTANCE!=null) {
            INSTANCE.close();
        }
        INSTANCE = null;
    }


    @Override
    public void insertDepartment(Department department) {
        Department.InsertDepartment insertDepartment = new DepartmentModel.InsertDepartment(mDatabaseHelper.getWritableDatabase());
        insertDepartment.bind(department._id(),department.shortname(),department.fullname(),department.code(),department.createtime());
        mDatabaseHelper.executeInsert(insertDepartment.table,insertDepartment.program);
    }

    @Override
    public void insertDepartments(List<Department> departments) {
        BriteDatabase.Transaction transaction = mDatabaseHelper.newTransaction();
        Department.InsertDepartment insertDepartment = new DepartmentModel.InsertDepartment(mDatabaseHelper.getWritableDatabase());

        for(Department department : departments){
            insertDepartment.bind(department._id(),department.shortname(),department.fullname(),department.code(),department.createtime());
            mDatabaseHelper.executeInsert(insertDepartment.table,insertDepartment.program);
        }
        transaction.markSuccessful();
        transaction.end();
    }

    @Override
    public List<Department> selectAllDepartment() {
        SqlDelightStatement sqlDelightStatement = Department.FACTORY.selectAll();
        Cursor cursor = mDatabaseHelper.query(sqlDelightStatement.statement,sqlDelightStatement.args);
        List<Department>departments = new ArrayList<>();
        while (cursor.moveToNext()){
            departments.add(Department.FACTORY.selectAllMapper().map(cursor));
        }
        return departments;
    }
@Override
    public Observable<List<Department>> listenDepartments() {
        SqlDelightStatement sqlDelightStatement = Department.FACTORY.selectAll();

        return mDatabaseHelper.createQuery(sqlDelightStatement.tables,sqlDelightStatement.statement,sqlDelightStatement.args).mapToList(new Function<Cursor, Department>() {
            @Override
            public Department apply(@io.reactivex.annotations.NonNull Cursor cursor) throws Exception {
                return Department.FACTORY.selectAllMapper().map(cursor);
            }
        });
    }
}

兩表聯(lián)查

創(chuàng)建一個員工表Employee.sq狠轻，添加一個聯(lián)查sql語句奸例。

CREATE TABLE employee(
    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
    name TEXT ,
    code TEXT ,
    createtime TEXT ,
    departmentid INTEGER ,
    deleted INTEGER,
    idcard TEXT ,
    headPath TEXT
);     
...

selectEmployeeFullInfo:
SELECT * FROM employee
JOIN department ON employee.departmentid = department._id
WHERE employee._id = ?;

生成EmployeeModel文件，并創(chuàng)建實現(xiàn)Employee.java向楼。

@AutoValue
public abstract class Employee implements EmployeeModel{
    public static final Factory<Employee> FACTORY = new Factory<>(AutoValue_Employee::new);

    public static final RowMapper<EmployeeFullInfo>EMPLOYEE_FULL_INFO_ROW_MAPPER = FACTORY.selectEmployeeFullInfoMapper(AutoValue_Employee_EmployeeFullInfo::new,Department.FACTORY);

    @AutoValue
    public abstract static class EmployeeFullInfo implements SelectEmployeeFullInfoModel<Employee,Department>{
    }
}

在DepartmentDataSource.java數(shù)據(jù)源添加一個聯(lián)查方法查吊，使用id查詢員工信息并將其單位信息同時返回。

public interface DepartmentDataSource { 
    
    ...
    
    Employee.EmployeeFullInfo selectEmployeeFullInfo(int id);
}

在DepartmentLocalDataSource.java完成實現(xiàn)湖蜕。

public class DepartmentLocalDataSource implements DepartmentDataSource {

    ...

    @Override
    public Employee.EmployeeFullInfo selectEmployeeFullInfo(int id) {
        SqlDelightStatement sqlDelightStatement = Employee.FACTORY.selectEmployeeFullInfo(id);
        Cursor cursor = mDatabaseHelper.query(sqlDelightStatement.statement,sqlDelightStatement.args);

        Employee.EmployeeFullInfo fullInfo = null;
        if(cursor.moveToNext()){
            fullInfo =  Employee.EMPLOYEE_FULL_INFO_ROW_MAPPER.map(cursor);
        }
        return fullInfo;
    }
}