SQLDelight是Square提供的開源庫，主要的作用結(jié)合AuToValue生成Java數(shù)據(jù)和數(shù)據(jù)庫表創(chuàng)建的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的文件中，一般第一條語句為創(chuàng)建表結(jié)構(gòu)织中。
1.引入工程中
buildscript {
repositories {
mavenCentral()
}
dependencies {
classpath 'com.squareup.sqldelight:gradle-plugin:0.6.1'
}
}
apply plugin: 'com.squareup.sqldelight'
2.如下邊的SQL語句箍鼓，創(chuàng)建一個User表結(jié)構(gòu)
--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接口和一個內(nèi)部類整慎，進行數(shù)據(jù)庫的操作脏款。生成的文件存在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轉(zhuǎn)為Java類型裤园。
- Marshal的作用把Java類型轉(zhuǎn)化為ContentValues后續(xù)插入數(shù)據(jù)庫撤师。
- Factory里面存在一個實現(xiàn)了Creator對象的屬性，marshal方法返回Model的Marshal對象拧揽，通過asContentValues可以返回對應的contentValues剃盾。
- Creator接口定義了一個用于創(chuàng)建我們的數(shù)據(jù)類型。
4.使用AutoValue生成一個對應名為AutoValue_User的Java類型淤袜，i實現(xiàn)生成的UserModel接口痒谴，創(chuàng)建自己的FACTORY創(chuàng)建AutoValue_User類型實例。
@AutoValue
public abstract class User implements UserModel{
//創(chuàng)建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對象轉(zhuǎn)化為相應的實例對象
public static final RowMapper<User> SELECT_ALL_MAPPER=FACTORY.selectUserMapper();
}
5.創(chuàng)建數(shù)據(jù)庫管理類,使用自動生成的創(chuàng)建表結(jié)構(gòu)的語句铡羡。
public class SQLiteHelp extends SQLiteOpenHelper{
//數(shù)據(jù)庫名
private static final String DBNAME = "test.db";
//數(shù)據(jù)庫版本號
private static final int CURRENTVERSION = 1;
//構(gòu)造器积蔚，創(chuàng)建數(shù)據(jù)庫
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) {
//創(chuàng)建相應的數(shù)據(jù)庫表
sqLiteDatabase.execSQL(User.CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
6.進行數(shù)據(jù)操作
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//創(chuàng)建數(shù)據(jù)庫對象
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轉(zhuǎn)為Java類型
result.add(User.SELECT_ALL_MAPPER.map(cursor));
}
return result;
}
}
