SQLDelight是Square提供的开源库，主要的作用结合AuToValue生成Java数据和数据库表创建的SQL语句。下边是sqldelight的官方介绍
SQLDelight generates Java models from your SQL CREATE TABLE statements. These models give you a typesafe API to read & write the rows of your tables. It helps you to keep your SQL statements together, organized, and easy to access from Java.
使用SQLDelight需要把SQL语句写到.sq的文件中，一般第一条语句为创建表结构。
1.引入工程中
buildscript {
repositories {
mavenCentral()
}
dependencies {
classpath 'com.squareup.sqldelight:gradle-plugin:0.6.1'
}
}
apply plugin: 'com.squareup.sqldelight'
2.如下边的SQL语句，创建一个User表结构
--user表
create table user(
--id自增长
_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
--名字
name TEXT NOT NULL,
--头像地址
portrait TEXT ,
--手机号
phone TEXT
);
-- 查询使用到的语句，将会生成对应的方法.
selectAll:
SELECT *
FROM user;
insertRow:
INSERT INTO user(account, password,name,portrait,token,origin)
VALUES (?, ?, ?, ?,?,?);
3.通过这个sq文件将会生成一个User接口和一个内部类，进行数据库的操作。生成的文件存在app/build/generated/source/sqldelight中。
public interface UserModel {
String TABLE_NAME = "user";
String _ID = "_id";
String NAME = "name";
String PORTRAIT = "portrait";
String PHONE = "phone";
String CREATE_TABLE = ""
+ "create table user(\n"
+ " --id自增长\n"
+ " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
+ " --名字\n"
+ " name TEXT NOT NULL,\n"
+ " --头像地址\n"
+ " portrait TEXT ,\n"
+ " --手机号\n"
+ " phone TEXT\n"
+ ")";
long _id();
@NonNull
String name();
@Nullable
String portrait();
@Nullable
String phone();
interface Creator<T extends UserModel> {
T create(long _id, @NonNull String name, @Nullable String portrait, @Nullable String phone);
}
final class Mapper<T extends UserModel> implements RowMapper<T> {
private final Factory<T> userModelFactory;
public Mapper(Factory<T> userModelFactory) {
this.userModelFactory = userModelFactory;
}
@Override
public T map(@NonNull Cursor cursor) {
return userModelFactory.creator.create(
cursor.getLong(0),
cursor.getString(1),
cursor.isNull(2) ? null : cursor.getString(2),
cursor.isNull(3) ? null : cursor.getString(3)
);
}
}
final class Marshal {
protected final ContentValues contentValues = new ContentValues();
Marshal(@Nullable UserModel copy) {
if (copy != null) {
this._id(copy._id());
this.name(copy.name());
this.portrait(copy.portrait());
this.phone(copy.phone());
}
}
public ContentValues asContentValues() {
return contentValues;
}
public Marshal _id(long _id) {
contentValues.put("_id", _id);
return this;
}
public Marshal name(String name) {
contentValues.put("name", name);
return this;
}
public Marshal portrait(String portrait) {
contentValues.put("portrait", portrait);
return this;
}
public Marshal phone(String phone) {
contentValues.put("phone", phone);
return this;
}
}
final class Factory<T extends UserModel> {
public final Creator<T> creator;
public Factory(Creator<T> creator) {
this.creator = creator;
}
/**
* @deprecated Use compiled statements (https://github.com/square/sqldelight#compiled-statements)
*/
@Deprecated
public Marshal marshal() {
return new Marshal(null);
}
/**
* @deprecated Use compiled statements (https://github.com/square/sqldelight#compiled-statements)
*/
@Deprecated
public Marshal marshal(UserModel copy) {
return new Marshal(copy);
}
public SqlDelightStatement selectUser() {
return new SqlDelightStatement(""
+ "select * from user",
new String[0], Collections.<String>singleton("user"));
}
/**
* @deprecated Use {@link InsertUser}
*/
@Deprecated
public SqlDelightStatement insertUser(@NonNull String name, @Nullable String portrait,
@Nullable String phone) {
List<String> args = new ArrayList<String>();
int currentIndex = 1;
StringBuilder query = new StringBuilder();
query.append("INSERT OR IGNORE INTO user (name,portrait,phone) values (");
query.append('?').append(currentIndex++);
args.add(name);
query.append(",");
if (portrait == null) {
query.append("null");
} else {
query.append('?').append(currentIndex++);
args.add(portrait);
}
query.append(",");
if (phone == null) {
query.append("null");
} else {
query.append('?').append(currentIndex++);
args.add(phone);
}
query.append(")");
return new SqlDelightStatement(query.toString(), args.toArray(new String[args.size()]), Collections.<String>singleton("user"));
}
public Mapper<T> selectUserMapper() {
return new Mapper<T>(this);
}
}
final class InsertUser extends SqlDelightCompiledStatement.Insert {
public InsertUser(SQLiteDatabase database) {
super("user", database.compileStatement(""
+ "INSERT OR IGNORE INTO user (name,portrait,phone) values (?,?,?)"));
}
public void bind(@NonNull String name, @Nullable String portrait, @Nullable String phone) {
program.bindString(1, name);
if (portrait == null) {
program.bindNull(2);
} else {
program.bindString(2, portrait);
}
if (phone == null) {
program.bindNull(3);
} else {
program.bindString(3, phone);
}
}
}
}
- Mapper主要用于把Cursor转为Java类型。
- Marshal的作用把Java类型转化为ContentValues后续插入数据库。
- Factory里面存在一个实现了Creator对象的属性，marshal方法返回Model的Marshal对象，通过asContentValues可以返回对应的contentValues。
- Creator接口定义了一个用于创建我们的数据类型。
4.使用AutoValue生成一个对应名为AutoValue_User的Java类型，i实现生成的UserModel接口，创建自己的FACTORY创建AutoValue_User类型实例。
@AutoValue
public abstract class User implements UserModel{
//创建Factory类型
public static final Factory<User> FACTORY=new Factory<>(new Creator<User>(){
@Override
public User create(long _id, @NonNull String name, @Nullable String portrait, @Nullable String phone) {
//传入自动化生成的User实例
return new AutoValue_User(_id,name,portrait,phone);
}
});
//把Cursor对象转化为相应的实例对象
public static final RowMapper<User> SELECT_ALL_MAPPER=FACTORY.selectUserMapper();
}
5.创建数据库管理类,使用自动生成的创建表结构的语句。
public class SQLiteHelp extends SQLiteOpenHelper{
//数据库名
private static final String DBNAME = "test.db";
//数据库版本号
private static final int CURRENTVERSION = 1;
//构造器，创建数据库
public SQLiteHelp(Context context) {
this(context, DBNAME, null, CURRENTVERSION);
}
public SQLiteHelp(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, DBNAME, factory, CURRENTVERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//创建相应的数据库表
sqLiteDatabase.execSQL(User.CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
6.进行数据操作
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//创建数据库对象
SQLiteDatabase sqLiteHelp=new SQLiteHelp(this).getWritableDatabase();
//使用InsertUser Factory类的insertUser已废弃
User.InsertUser insertUser=new UserModel.InsertUser(sqLiteHelp);
insertUser.bind("zyc","","1850*******");
insertUser.program.executeInsert();
getAllUsers(sqLiteHelp);
}
public List<User> getAllUsers(SQLiteDatabase db) {
List<User> result = new ArrayList<>();
//使用到对应的SQL语句
Cursor cursor=db.rawQuery(User.FACTORY.selectUser().statement,User.FACTORY.selectUser().args);
while (cursor.moveToNext()){
//把Cursor转为Java类型
result.add(User.SELECT_ALL_MAPPER.map(cursor));
}
return result;
}
}
