一、简介
SQLDelight 和 SqlBrite 是 Square 公司推出的一个 Android 平台数据库解决方案。
在了解这个两个东西前，必须先得有Andorid的Sqlite的知识(SqliteDatabase and SqliteDataHelper)， AutoValue
要分清他们两个的功能:
二、SqlDelight
SqlDelight是从SQL语句来生成JAVA对象。这样的好处是，所有SQL语句都位于同一个位置，通过查看SQL语句可以清楚的了解需要实现的功能和数据库的结构。
SqlDelight添加了对SQL语句的编译时验证、表名字和列名字的代码自动完成功能。让编写SQL语句更加快捷。在编译的时候，根据SQL语句生成Java模型接口和builder来把数据行和Java对象实现转换。
2.1 导包
- 在项目build.gradle的dependencies添加：
classpath 'com.squareup.sqldelight:gradle-plugin:0.6.0'
- 在module的build.gralde头部添加apply
apply plugin: 'com.squareup.sqldelight'
重新Sync即可。
2.2 使用
SqlDelight 可以根据建表的 SQL 语句自动生成 Java model interface，interface 的每个方法就是这张表的每一列。
1. 编写sq语句
在main目录创建sqldelight目录，然后按照包名创建目录， main/sqldelight/com/xxx/xxx/sq文件(一定得是正确的目录，不然构建失败)
这里编写创建表和查询的语句;
create table user( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL);select_by_name: select * from user;
2. 生成接口Module
编写完毕sql语句之后，make一下moduel，即可在build/generated/source/sqldelight/包名/看到xxxModule接口文件 (xxx就是你的sq文件的名称)。
可以看到接口大概是这样的。
public interface UserModel { String TABLE_NAME = "user"; String _ID = "_id"; String NAME = "name"; String AGE = "age"; String CREATE_TABLE = "" + "create table user(\r\n" + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\r\n" + " name TEXT NOT NULL,\r\n" + " age INTEGER NOT NULL\r\n" + ")"; long _id(); @NonNull String name(); long age(); interface Creator<T extends UserModel> { T create(long _id, @NonNull String name, long age); } 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.getLong(2) ); } } 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.age(copy.age()); } } 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 age(long age) { contentValues.put("age", age); 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 select_by_name() { return new SqlDelightStatement("" + "select * from user", new String[0], Collections.<String>singleton("user")); } public Mapper<T> select_by_nameMapper() { return new Mapper<T>(this); } }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
从这个接口可以看到重要的有下面四个类和接口：
Mapper
把 Cursor 映射为 Java 对象
Marshal
把 Java 对象转换为 ContentValues，好方便插入数据库
接口Creator
里面定义了一个方法create用来创建我们的Model类型(这里用的是泛型T)
- Factory
里面包含一个实现了Creator对象的creator，如果有列值需要转换的话还要包括相应的ColumnAdapter，它的构造函数传入Creator和ColumnAdapter这些参数，marshal方法返回Mode的Marshal(通过它的asContentValues获取对应的ContentValues)
3. 定义Bean类
这里利用AtuoValue重写接口，因为SqlDelight生成的接口适合AutoValue无缝接入的，AutoValue的作用的是,自动生成bean代码，AutoValue请点击我。
使用需要导包
apt 'com.google.auto.value:auto-value:1.4-rc3'provided 'com.google.auto.value:auto-value:1.4-rc3'apt 'com.ryanharter.auto.value:auto-value-gson:0.4.6'provided 'com.ryanharter.auto.value:auto-value-gson:0.4.6'apt 'com.ryanharter.auto.value:auto-value-parcel:0.2.5'compile 'com.ryanharter.auto.value:auto-value-parcel-adapter:0.2.5'
这时候User的bean为
@AutoValuepublic abstract class User implements UserModel { public static final Factory<User> FACTORY = new Factory<>(new Creator<User>() { @Override public User create(long _id, @NonNull String name, long age) { return new AutoValue_User(_id,name,age); } }); public static final RowMapper<User> SELECT_ALL_MAPPER = FACTORY.select_by_nameMapper();}
4. 使用
然后写一个Activity来测试看看(Sqlite相关知识请百度，点我看教程)：
public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); SQLiteDatabase sqLiteDatabase = new StuDBHelp(this,"tp",null,1).getWritableDatabase(); sqLiteDatabase.insert("user",null,createContentValues(0,"天平",21)); sqLiteDatabase.insert("user",null,createContentValues(1,"逼辉",23)); Log.e("@@", "数据数量:: "+getAllUsers(sqLiteDatabase).size()); } public List<User> getAllUsers(SQLiteDatabase db) { List<User> result = new ArrayList<>(); try (Cursor cursor = db.rawQuery(User.FACTORY.select_by_name().statement, new String[0])) { while (cursor.moveToNext()) { result.add(User.SELECT_ALL_MAPPER.map(cursor)); } } return result; } public ContentValues createContentValues(int id,String name,int age){ ContentValues contentValues = new ContentValues(); contentValues.put("_id",id); contentValues.put("name",name); contentValues.put("age",age); return contentValues; } class StuDBHelp extends SQLiteOpenHelper{ public StuDBHelp(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(User.CREATE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
这时候你会看到输出:
数据数量:: 2
2.3 sq语句参数
.sq文件使用与SQLite完全相同的语法，包括SQLite绑定args(官方文档点我)。 如果语句包含绑定args，将在Factory上生成类型安全方法，该方法返回包含查询字符串，查询args和要查询的表的字段的SqlDelightStatement。
上面的例子中，我把sq文件里面的查询语句添加where条件，变成:
select_by_name: select * from user where name='天平';
然后重新make一下module，然后修改查询的getAllUsers方法:
public List<User> getAllUsers(SQLiteDatabase db) { List<User> result = new ArrayList<>() //创建 SqlDelightStatement对象，里面有查询字符串和参数 SqlDelightStatement query = User.FACTORY.select_by_name("天平") //try_with_resources写法，括号里面的资源需要继承AutoCloseable，作用是可以自动关闭对象 try (Cursor cursor = db.rawQuery(User.FACTORY.select_by_name("天平").statement, query.args)) { while (cursor.moveToNext()) { result.add(User.SELECT_ALL_MAPPER.map(cursor)) } } return result }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
此时可以看到输出:
数据数量:: 1
如果是数组就用IN：
select_by_names:select * from user where name in ?;
public List<User> getAllUsers(SQLiteDatabase db) { List<User> result = new ArrayList<>() SqlDelightStatement query = User.FACTORY.select_by_name(new String[]{"天平","逼辉"}) try (Cursor cursor = db.rawQuery(query.statement, query.args)) { while (cursor.moveToNext()) { result.add(User.SELECT_ALL_MAPPER.map(cursor)) } } return result }
还能用运算符，例如查询user表的name字段是天开头的或结尾的:
select_by_name: select * from user where name like '%' || ?1or name like ?1 ||'%';
SqlDelightStatement query = User.FACTORY.select_by_name("天")Cursor cursor = db.rawQuery(query.statement, query.args)
2.4 插入、更新、删除
执行多次的插入，更新和删除 都需要make一下module，SQLDelight会为为它们生成一个类型安全类。
例如在User.sq添加一句:
update_age:update user set age = ? where name = ?;
重新make一下module会看到UserModel有一个Update_age类:
final class Update_age extends SqlDelightCompiledStatement.Update { public Update_age(SQLiteDatabase database) { super("user", database.compileStatement("" + "update user set age = ? where name = ?")); } public void bind(long age, @NonNull String name) { program.bindLong(1, age); program.bindString(2, name); } }
更新的时候(在上面的栗子的MainActivity的onCreate修改):
@Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); SQLiteDatabase sqLiteDatabase = new StuDBHelp(this,"tp",null,1).getWritableDatabase(); sqLiteDatabase.insert("user",null,createContentValues(0,"天平",21)); sqLiteDatabase.insert("user",null,createContentValues(1,"逼辉",23)); User.Update_age updateAge; updateAge = new User.Update_age(sqLiteDatabase); updateAge.bind(66,"天平"); updateAge.bind(99,"逼辉"); updateAge.program.execute(); for(User user:getAllUsers(sqLiteDatabase)){ Log.e("@@", "user: "+user.toString()); } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
这时候会看到输出:
user: User{_id=0, name=天平, age=66}user: User{_id=1, name=逼辉, age=99}
ps:注意： 如果是bind了多个，executeUpdateDelete只能修改最后那个。 如果需要同时修改需要使用execute，如上面的代码
2.5 多个结果
选择返回多个结果列的话，要为查询生成的结果自定义模型、映射程序和工厂方法。例如:
在User.sq底部添加语句:
names_for_age:select age, group_concat(name)from usergroup by age;
在User bean里面添加一个类和静态常量:
@AutoValuepublic abstract class User implements UserModel { public static final Factory<User> FACTORY = new Factory<>(new Creator<User>() { @Override public User create(long _id, @NonNull String name, long age) { return new AutoValue_User(_id,name,age); } }); public static final RowMapper<User> SELECT_ALL_MAPPER = FACTORY.select_by_nameMapper(); @AutoValue public abstract static class NamesForNumber implements Names_for_ageModel { public String[] names() { return group_concat_name().split(","); } } public static final RowMapper<NamesForNumber> NAMES_FOR_NUMBER_MAPPER = FACTORY.names_for_ageMapper(new Names_for_ageCreator<NamesForNumber>() { @Override public NamesForNumber create(long age, @NonNull String group_concat_name) { return new AutoValue_User_NamesForNumber(age,group_concat_name); } });}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
在MainActivity中使用看看:
public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); SQLiteDatabase sqLiteDatabase = new StuDBHelp(this,"tp",null,1).getWritableDatabase(); sqLiteDatabase.insert("user",null,createContentValues(0,"天平",21)); sqLiteDatabase.insert("user",null,createContentValues(1,"逼辉",23)); Map<Long,String[]> map = namesForNumber(sqLiteDatabase); for(Long age:map.keySet()){ String[] str = map.get(age); String nameAll = ""; for(String s:str){ nameAll += s ; } Log.e("@@", "数据: "+age+"-"+ nameAll); } } /** * 从数据库查询，存放到map中， * @parm db 数据库 */ public Map<Long, String[]> namesForNumber(SQLiteDatabase db) { Map<Long, String[]> namesForNumberMap = new LinkedHashMap<>(); SqlDelightStatement sqlDelightStatement = User.FACTORY.names_for_age(); try (Cursor cursor = db.rawQuery(sqlDelightStatement.statement, new String[0])) { while (cursor.moveToNext()) { User.NamesForNumber namesForNumber = NAMES_FOR_NUMBER_MAPPER.map(cursor); namesForNumberMap.put(namesForNumber.age(), namesForNumber.names()); } } return namesForNumberMap; } public ContentValues createContentValues(int id,String name,int age){ ContentValues contentValues = new ContentValues(); contentValues.put("_id",id); contentValues.put("name",name); contentValues.put("age",age); return contentValues; } class StuDBHelp extends SQLiteOpenHelper{ public StuDBHelp(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(User.CREATE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
然后可以看到输出:
数据: 66-天平数据: 99-逼辉
2.6 类型转换
SQLDelight列定义与常规SQLite列定义相同，但支持转换类型(约束)，会在生成的接口中指定列的类型，例如：
CREATE TABLE some_types ( some_long INTEGER, -- 在数据库中存储为INTEGER，获取的时候为Long some_double REAL, -- 在数据库中存储为REAL，获取的时候为Double some_string TEXT, -- 在数据库中存储为TEXT，获取的时候为String some_blob BLOB, -- 在数据库中存储为BLOB，获取的时候为byte[] some_int INTEGER AS Integer, -- 在数据库中存储为INTEGER，获取的时候为Integer some_short INTEGER AS Short, -- 在数据库中存储为INTEGER，获取的时候为Short some_float REAL AS Float -- 在数据库中存储为REAL，获取的时候为Float);
SQLDelight支持布尔列，可以将它们作为int存储在db中然后实现为int:
CREATE TABLE hockey_player ( injured INTEGER AS Boolean DEFAULT 0)
2.7 其他类型保存
如果上面的基本类型不满足要求还可以自定义类型,还可以指定java类型。但是，需要提供一个ColumnAdapter，存储取出的时候进行转换。
自定义类
又拿上面的User进行开刀(把上面的更新和分组查询删掉了)，添加一个表类型，添加一个插入数据的语句。
import java.util.Calendar; create table user( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL, -- 储存类型为INTEGER，取出来时候转换为Calendar birth_date INTEGER AS Calendar NOT NULL);select_by_name: select * from user;insert_time:insert into user (_id,name,age,birth_date) values (?,?,?,?);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
User bean类里面添加一个ColumnAdapter,这时候User是这样的
@AutoValuepublic abstract class User implements UserModel { private static final ColumnAdapter<Calendar,Long> CALENDAR_ADAPTER = new ColumnAdapter<Calendar, Long>() { @NonNull @Override public Calendar decode(Long databaseValue) { Calendar calendar = Calendar.getInstance(); calendar.setTimeInMillis(databaseValue); return calendar; } @Override public Long encode(@NonNull Calendar value) { return value.getTimeInMillis(); } }; public static final Factory<User> FACTORY = new Factory<>(new Creator<User>() { @Override public User create(long _id, @NonNull String name, long age, @NonNull Calendar birth_date) { return new AutoValue_User(_id,name,age,birth_date); } },CALENDAR_ADAPTER); public static final RowMapper<User> SELECT_ALL_MAPPER = FACTORY.select_by_nameMapper();}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
因为要重新建立表，先把app卸载了。现在的MainActivity代码为：
public class MainActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); SQLiteDatabase sqLiteDatabase = new StuDBHelp(this,"tp",null,2).getWritableDatabase(); User.Insert_time delgiht= new User.Insert_time(sqLiteDatabase,User.FACTORY); delgiht.bind(0,"天平",21,Calendar.getInstance()); delgiht.program.executeInsert(); delgiht.bind(1,"傻逼",21,Calendar.getInstance()); delgiht.program.executeInsert(); delgiht.bind(2,"屌爆天",21,Calendar.getInstance()); delgiht.program.executeInsert(); delgiht.bind(3,"智障",21,Calendar.getInstance()); delgiht.program.executeInsert(); delgiht.program.clearBindings(); for(User user:getAllUsers(sqLiteDatabase)){ Log.e("@@", "user: "+user._id() + user.name() + user.age() + user.birth_date().getTime()); } sqLiteDatabase.close(); } @RequiresApi(api = Build.VERSION_CODES.KITKAT) public List<User> getAllUsers(SQLiteDatabase db) { List<User> result = new ArrayList<>(); SqlDelightStatement query = User.FACTORY.select_by_name(); try (Cursor cursor = db.rawQuery(query.statement, query.args)) { while (cursor.moveToNext()) { result.add(User.SELECT_ALL_MAPPER.map(cursor)); } } return result; } public ContentValues createContentValues(int id,String name,int age){ ContentValues contentValues = new ContentValues(); contentValues.put("_id",id); contentValues.put("name",name); contentValues.put("age",age); return contentValues; } class StuDBHelp extends SQLiteOpenHelper{ public StuDBHelp(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(User.CREATE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
这时候看到输出为:
0天平21Tue Mar 14 17:04:13 GMT+08:00 20171傻逼21Tue Mar 14 17:04:14 GMT+08:00 20172屌爆天21Tue Mar 14 17:04:14 GMT+08:00 20173智障21Tue Mar 14 17:04:14 GMT+08:00 2017
ps注意: 在插入的时候 executeInsert只能插入bind的最后一行，execute也是，和select不一样。 还有sq里面的自定义类型需要导包或者填写全路径
枚举
枚举可以转换为TEXT类型保存到数据库,sqlDelight有提供一个EnumColumnAdapter类:
继续拿User来开刀
首先在User bean加一个枚举:
public enum Position { CENTER, LEFT_WING, RIGHT_WING, DEFENSE, GOALIE }
sq语句改为:
create table user( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL, -- 储存类型为INTEGER，取出来时候转换为Calendar birth_date INTEGER AS Calendar NOT NULL, -- 全路径的方法 position TEXT as com.minstone.testsqldelight.User.Position NOT NULL);...insert_time:insert into user (_id,name,age,birth_date,position) values (?,?,?,?,?);
User bean类添加一个EnumColumnAdapter适配器:
@AutoValuepublic abstract class User implements UserModel { public enum Position { CENTER, LEFT_WING, RIGHT_WING, DEFENSE, GOALIE } private static final ColumnAdapter<Calendar,Long> CALENDAR_ADAPTER = new ColumnAdapter<Calendar, Long>() { @NonNull @Override public Calendar decode(Long databaseValue) { Calendar calendar = Calendar.getInstance(); calendar.setTimeInMillis(databaseValue); return calendar; } @Override public Long encode(@NonNull Calendar value) { return value.getTimeInMillis(); } }; private static final ColumnAdapter<Position,String> POSITION_ADAPTER = EnumColumnAdapter.create(Position.class); public static final Factory<User> FACTORY = new Factory<>(new Creator<User>() { @Override public User create(long _id, @NonNull String name, long age, @NonNull Calendar birth_date, @NonNull Position position) { return new AutoValue_User(_id,name,age,birth_date,position); } },CALENDAR_ADAPTER,POSITION_ADAPTER); public static final RowMapper<User> SELECT_ALL_MAPPER = FACTORY.select_by_nameMapper();}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
MainActivty如上面那样添加属性,输出log加user.position()，卸载app，运行看到结果:
0天平21Tue Mar 14 17:34:38 GMT+08:00 2017CENTER1傻逼21Tue Mar 14 17:34:38 GMT+08:00 2017LEFT_WING2屌爆天21Tue Mar 14 17:34:38 GMT+08:00 2017RIGHT_WING3智障21Tue Mar 14 17:34:38 GMT+08:00 2017GOALIE
View
Sqlite中有视图View这个概念，sqlDelight也是支持的。
继续拿User开刀,添加两个语句：
.....names_view:CREATE VIEW names ASSELECT substr(name, 0, 2) AS first_name, substr(name, 2, length(name)) AS last_name, _idFROM user;select_names:SELECT *FROM names;
编辑之后，会在UserModle里面添加了NamesModel、NamesCreator、NamesMapper:
所以我们使用的时候和User差不多，在User bean类里面,引用映射器需要实现视图模型:
...... @AutoValue public static abstract class Names implements NamesModel { } public static final RowMapper<Names> NAMES_VIEW_MAPPER = FACTORY.select_namesMapper(new UserModel.NamesCreator<User.Names>() { @Override public User.Names create(@NonNull final String first_name, @NonNull final String last_name, final long _id) { return new AutoValue_User_Names(first_name,last_name,_id); } });
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
在MainActivity中来一发:
protected void onCreate(Bundle savedInstanceState) { ...... sqLiteDatabase.execSQL(User.NAMES_VIEW); for(User.Names names:getView(sqLiteDatabase)){ Log.e("@@", "user: " + names.first_name() + "-" +names.last_name()+ "-" + names._id()); } sqLiteDatabase.close(); } @RequiresApi(api = Build.VERSION_CODES.KITKAT) public List<User.Names> getView(SQLiteDatabase db) { List<User.Names> result = new ArrayList<>(); SqlDelightStatement query = User.FACTORY.select_names(); try (Cursor cursor = db.rawQuery(query.statement, query.args)) { while (cursor.moveToNext()) { result.add(User.NAMES_VIEW_MAPPER.map(cursor)); } } return result; }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
可以看到输出:
user: 天-平-0傻-逼-1屌-爆天-2智-障-3
2.8 Join
链接多个表或者view视图。
接着上面来撸,在sq添加一个select_all_info:
select_all_info:SELECT *FROM userJOIN names USING (_id);
在User的bean里面添加:
@AutoValue public static abstract class AllInfo implements Select_all_infoModel<User, Names> { } public static final RowMapper<AllInfo> SELECT_ALL_INFO_MAPPER = FACTORY.select_all_infoMapper(new Select_all_infoCreator<User, Names, AllInfo>() { @Override public AllInfo create(@NonNull User user, @NonNull Names names) { return new AutoValue_User_AllInfo(user,names); } }, new NamesCreator<Names>() { @Override public Names create(@NonNull String first_name, @NonNull String last_name, long _id) { return new AutoValue_User_Names(first_name, last_name, _id); } });
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
MainActivit里面使用:
protected void onCreate(Bundle savedInstanceState) { ...... // 创建View sqLiteDatabase.execSQL(User.NAMES_VIEW) // allInfo for(User.Names names:getView(sqLiteDatabase)){ Log.e("@@", "names: " + names.toString()) } // allInfo for(User.AllInfo allInfo:getAllView(sqLiteDatabase)){ Log.e("@@", "allInfo: " + allInfo.toString()) } sqLiteDatabase.close() } public List<User.AllInfo> getAllView(SQLiteDatabase db) { List<User.AllInfo> result = new ArrayList<>() //这是查询all info SqlDelightStatement query = User.FACTORY.select_all_info() //try_with_resources写法，括号里面的资源需要继承AutoCloseable，作用是可以自动关闭对象 try (Cursor cursor = db.rawQuery(query.statement, query.args)) { while (cursor.moveToNext()) { result.add(User.SELECT_ALL_INFO_MAPPER.map(cursor)) } } return result }}文章出处：http://blog.csdn.net/niubitianping/article/details/62051286
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
