sqldelight是一个轻量级的sqlite操作代码生成框架,直接编写sql语句生成代码。
环境
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同级目录下创建sqldelight存放sql语句的目录main/sqldelight/you/path
2、创建一个sq文件Department.sq
(1)创建表
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文件
在build相应目录下可以找到生成的Model文件DepartmentModel
使用google的AutoValue 快速的创建一个实现Department.java。
@AutoValue
public abstract class Department implements DepartmentModel {
public static final Factory<Department>FACTORY = new Factory<>(AutoValue_Department::new);
}
4、对生成的DepartmentModel进行使用。(结合sqlbrite可以实现响应式数据查询)
创建一个数据源接口DepartmentDataSource.java
public interface DepartmentDataSource {
void insertDepartment(Department department);
void insertDepartments(List<Department> departments) ;
List<Department> selectAllDepartment();
Observable<List<Department>>listenDepartments();
}
对数据源做实现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);
}
});
}
}
两表联查
创建一个员工表Employee.sq,添加一个联查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文件,并创建实现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数据源添加一个联查方法,使用id查询员工信息并将其单位信息同时返回。
public interface DepartmentDataSource {
...
Employee.EmployeeFullInfo selectEmployeeFullInfo(int id);
}
在DepartmentLocalDataSource.java完成实现。
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;
}
}