本文已参与「新人创作礼」活动，一起开启掘金创作之路。

SQL

投影

默认请问下检索会返回投影的数据类, 但是可以通过类型安全的Mapper来覆盖这个行为.

val selectAllNames = playerQueries.selectAll(
  mapper = { player_number, full_name -> full_name.toUppercase() }
)
println(selectAllNames.executeAsList())
// Prints ["RYAN GETZLAF", "COREY PERRY"]

通常情况下, 无论任何时候都应该充分利用SQL来自定义投影.

selectNames:
SELECT upper(full_name)
FROM hockeyPlayer;

val selectAllNames = playerQueries.selectNames()
println(selectAllNames.executeAsList())
// Prints ["RYAN GETZLAF", "COREY PERRY"]

参数

绑定参数

.sq文件使用了与SQLite完全相同的语法, 包括SQLite绑定参数. 如果语句包含绑定参数, 相应的方法将需要对应的实参.

类型推断

SQLDelight能够推断运行时参数的正确类型和可空性, 包含自定义列类型.

selectByNumber:
SELECT *
FROM hockeyPlayer
WHERE player_number = ?;

val selectNumber10 = playerQueries.selectByNumber(player_number = 10)
println(selectNumber10.executeAsOne())
// Prints "Corey Perry"

命名参数

命名参数或者有序参数都可用.

firstOrLastName:
SELECT *
FROM hockeyPlayer
WHERE full_name LIKE ('% ' || :name)
OR full_name LIKE (:name || ' %');

playerQueries.firstOrLastName(name = "Ryan")

可变参数

值集也可当作参数传递.

selectByNames:
SELECT *
FROM hockeyPlayer
WHERE full_name IN ?;

playerQueries.selectByNames(listOf("Alec", "Jake", "Matt"))

插入

INSERT VALUES参数能够跟表的数据类绑定.

insertPlayer:
INSERT INTO hockeyPlayer
VALUES ?;

val rickardRakell = HockeyPlayer(
  full_name = "Rickard Rakell",
  number = 67
)
playerQueries.insertPlayer(rickardRakell)

类型

SQLite类型

SQLDelight列定义完全相同于常规SQLite列定义, 但支持额外的列约束, 列约束在生成的接口中指定了列的Kotlin类型. SQLDelight天然支持Long, Double, String, ByteArray, Int, Short, Float和Boolean.

CREATE TABLE some_types (
  some_long INTEGER,           -- Stored as INTEGER in db, retrieved as Long
  some_double REAL,            -- Stored as REAL in db, retrieved as Double
  some_string TEXT,            -- Stored as TEXT in db, retrieved as String
  some_blob BLOB,              -- Stored as BLOB in db, retrieved as ByteArray
  some_int INTEGER AS Int,     -- Stored as INTEGER in db, retrieved as Int
  some_short INTEGER AS Short, -- Stored as INTEGER in db, retrieved as Short
  some_float REAL AS Float     -- Stored as REAL in db, retrieved as Float
);

Boolean列在数据库中保存为INTEGER, 所以并没有给定INTEGER列约束. 比如, 使用DEFAULT 0将值默认为false.

CREATE TABLE hockey_player (
  injured INTEGER AS Boolean DEFAULT 0
)

自定义列类型

如果想要将列检索为自定义类型, 需要指定Kotlin类型:

import kotlin.collections.List;

CREATE TABLE hockeyPlayer (
  cup_wins TEXT AS List<String> NOT NULL
);

然而, 创建Database需要提供ColumnAdapter, 它知道如果在数据库类型和自定义类型之间映射:

val listOfStringsAdapter = object : ColumnAdapter<List<String>, String> {
  override fun decode(databaseValue: String) =
    if (databaseValue.isEmpty()) {
      listOf()
    } else {
      databaseValue.split(",")
    }
  override fun encode(value: List<String>) = value.joinToString(separator = ",")
}

val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = hockeyPlayer.Adapter(
    cup_winsAdapter = listOfStringsAdapter
  )
)

枚举

为了方便起见, SQLDelight运行时引入了ColumnAdapter将枚举保存为String数据.

import com.example.hockey.HockeyPlayer;

CREATE TABLE hockeyPlayer (
  position TEXT AS HockeyPlayer.Position
)

val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = HockeyPlayer.Adapter(
    positionAdapter = EnumColumnAdapter()
  )
)

事务

API

如果要在一条事务中执行多条语句, 就要用transaction函数.

val players = listOf<Player>()
database.playerQueries.transaction {
  players.forEach { player ->
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}

要从事务中返回值, 就要用transactionWithResult函数.

val players: List<Player> = database.playerQueries.transactionWithResult {
  database.playerQueries.selectAll().executeAsList()
}

回滚

如果任何时候事务中出现了异常, 事务都将回滚. 只要在事务内部, 就可以手动回滚事务, 但是如果事务已经返回值了, 就需要指定事务的返回值了.

database.playerQueries.transaction {
  players.forEach { player ->
    if (player.number == 0) rollback()
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}

val numberInserted: Int = database.playerQueries.transactionWithResult {
  players.forEach { player ->
    if (player.number == 0) rollback(0)
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
  players.size
}

回调

可以在事务完成或者回滚之后注册回调:

database.playerQueries.transaction {
  afterRollback { log("No players were inserted.") }
  afterCommit { log("${players.size} players were inserted.") }

  players.forEach { player ->
    database.playerQueries.insert(
      player_number = player.number,
      full_name = player.fullName
    )
  }
}

分组语句

可以将多个SQL语句一起分组, 一次执行:

upsert {
  UPDATE myTable
  SET column1 = :column1,
      column2 = :column2
  WHERE id = :id;

  INSERT OR IGNORE INTO myTable (id, column1, column2)
  VALUES (:id, :column1, :column2);
}

SQLDelight for Android - 从SQL语句中生成Kotlin代码 - 3

SQL

投影

参数