SQLDelight 使用笔记
Code Overlook
以JVM环境下的纯kotlin项目为实例记录的使用笔记
import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.sqlite.JdbcSqliteDriver
import cn.xqher.AppDatabase
import cn.xqher.User
import cn.xqher.UserQueries
fun main(args: Array<String>) {
val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:test.db")
AppDatabase.Schema.create(driver)
val database = AppDatabase(
driver = driver
)
val userQueries: UserQueries = database.userQueries
userQueries.selectByNames(listOf("admin", "田所")).executeAsList().forEach {
println("search->$it")
}
// 会把初始插入的记录删掉
userQueries.rm_rf()
val user = User(
user_id = 2,
user_name = "田所浩二"
)
userQueries.insertUser(user)
userQueries.insert(3, "田所浩二")
val users = mutableListOf<User>(
User(4, "李田所"),
User(5, "李天梭"),
User(114514, "田所浩二"),
)
userQueries.transaction {
users.forEach {user ->
userQueries.insertNotRepeatName(
user.user_id, user.user_name
)
}
}
userQueries.selectAll().executeAsList().forEach {
println(it)
}
userQueries.transaction {
with(userQueries) {
deleteByName("李天梭")
updateNameById("野兽先辈", 2)
}
}
println("====================")
userQueries.selectAll().executeAsList().forEach {
println(it)
}
}
Gradle setup
设置plugin，gradle中引入依赖
// build.gradle.kts
plugins {
id("app.cash.sqldelight") version "2.0.0"
}
dependencies {
implementation("app.cash.sqldelight:sqlite-driver:2.0.0")
}
// 创建数据库容器`Database`,且指定报名为`cn.xqher`,代码中通过包名引入库
sqldelight {
databases {
create("AppDatabase") {
packageName.set("cn.xqher")
}
}
}
File setup
在项目main文件夹下新建sqldelight/{{packageName}}文件夹
在sqldelight/{{packageName}}/处新建User.sq文件存储sql语句，注意该sq文件名决定通过数据库引入的查询集queries名
在sq文件中编写数据库创建代码，此处顺便插入一条数据
注意：不写上判断是否存在就直接添加，二次运行时可能会造成错误
CREATE TABLE IF NOT EXISTS User (
user_id INTEGER PRIMARY KEY NOT NULL,
user_name TEXT NOT NULL
);
INSERT OR IGNORE INTO User (user_id, user_name)
VALUES (1, 'admin');
selectAll:
SELECT * FROM User;
-- 传入列表
selectByNames:
SELECT *
FROM User
WHERE user_name IN ?;
insert:
INSERT OR IGNORE INTO User(user_id, user_name)
VALUES (?, ?);
-- 插入生成的表class 对象
insertUser:
INSERT OR IGNORE INTO User(user_id, user_name)
VALUES ?;
-- 很哈人
rm_rf:
DELETE FROM User;
-- 分组语句
insertNotRepeatName {
INSERT INTO User (user_id, user_name)
SELECT ?, :name
WHERE NOT EXISTS (
SELECT * FROM User WHERE user_name = :name
);
}
deleteByName:
DELETE FROM User
WHERE user_name = ?;
-- ?自行推断参数名，使用`:arugment_name`可自定义参数名
updateNameById:
UPDATE User SET user_name = :name
WHERE user_id = ?;
以标签:sql语句编写的形式将使SQLDelight能为此生产类型安全的函数(通过文件名Queries调用)
默认的数据库类型对应如下
| INTERGER | Long |
| REAL | Double |
| TEXT | String |
| BLOB | ByteArray |
Transactions
使用transaction同时执行多个语句
userQueries.transaction {
with(userQueries) {
deleteByName("李天梭")
updateNameById("野兽先辈", 2)
}
}
使用transactionWithResult获取返回值
userQueries.transactionWithResult {
users.forEach {user ->
userQueries.insertNotRepeatName(
user.user_id, user.user_name
)
}
userQueries.selectAll().executeAsList()
}.forEach{
println(it)
}
