机がある：
CREATE TABLE person
(
id uuid primary key,
name text,
birth_date date
)
および対応するデータクラス：
data class Person(
val id: UUID,
val name: String,
val birthDate: LocalDate,
)
基本的なCRUD操作を実行する場合はどうなりますか？
リストセーブ
Person
秒
テーブルからすべてを引く
テーブル内のすべてのレコードを削除します
IDで検索
名前で削除
インターフェイスを作成するだけで十分です。
@SqliteRepository
interface PersonRepository : Repository<People> {
fun saveAll(people: List<Person>)
fun selectAll(): List<Person>
fun deleteAll()
fun selectBy(id: UUID): Person?
fun deleteBy(name: String)
}
実装は自動的に生成されます。
春のデータを思い出しますか？しかし、これはSpringでも、Hibernateでも、JPAでもありません。
TL; DR
Kotlin中心のライブラリ（フレームワークではありません）
ORMなし（JPAなし）
SQL JDBC (Kotlin Annotation Precessing)
, , ,
DSL
2 : Postgres Sqlite
2 : Postgresql Sqlite. Sqlite.
Gradle ( Maven):
build.gradle.kts
plugins {
kotlin("kapt") version "1.4.31" //(1)
kotlin("plugin.serialization") version "1.4.31"
}
dependencies {
implementation("com.github.mfarsikov:kotlite-core:0.5.0") //(2)
implementation("org.jetbrains.kotlinx:kotlinx-serialization-json:1.0.0") //(3)
implementation("org.xerial:sqlite-jdbc:3.34.0") //(4)
kapt("com.github.mfarsikov:kotlite-kapt:0.5.0") //(5)
}
kapt {
arguments {
arg("kotlite.db.qualifiedName", "my.pkg.DB") //(6)
}
}
build.gradle.kts
(`kapt`).
core
- . , .
/ JSON .
Sqlite .
kapt , `kapt`- . SQL JDBC.
( ), ( ).
import kotlite.annotations.SqliteRepository
@SqliteRepository
interface PersonRepository
, Kotlite .
./gradlew kaptKotlin
:
build/generated/source/kapt/PersonRepositoryImpl.kt
@Generated
internal class PersonRepositoryImpl(
private val connection: Connection
) : PersonRepository
import kotlite.annotations.Query
import kotlite.annotations.SqliteRepository
@SqliteRepository
interface PersonRepository {
@Query("SELECT id, name, birth_date FROM person")
fun findPeople(): List<Person>
}
Kotlite
, :
List
, 0 N
Person
, :id
,name
birth_date
.
,
birthDate
birth_date
:
build/generated/source/kapt/PersonRepositoryImpl.kt
public override fun findPeople(): List<Person> {
val query = "SELECT id, name, birth_date FROM person"
return connection.prepareStatement(query).use {
it.executeQuery().use {
val acc = mutableListOf<Person>()
while (it.next()) {
acc +=
Person(
birthDate = it.getObject("birth_date", LocalDate::class.java),
id = it.getObject("id", java.util.UUID::class.java),
name = it.getString("name"),
)
}
acc
}
}
}
?
(build.gradle.kts
) , my.pkg.DB
. , . DataSource
. :
main.kt
import my.pkg.DB
import org.sqlite.SQLiteDataSource
fun main() {
val datasource = SQLiteDataSource().apply {
url = "jdbc:sqlite:path/to/my/test.db"
}
val db = DB(datasource)
val people: List<Person> = db.transaction {
personRepository.findPeople()
}
println(people)
}
@Query("SELECT id, name, birth_date FROM person WHERE name = :firstName")
fun findPeopleBy(firstName: String): List<Person>
. .
public override fun findPeopleBy(firstName: String): List<Person> {
val query = "SELECT id, name, birth_date FROM person WHERE name = ?"
return connection.prepareStatement(query).use {
it.setString(1, firstName)
it.executeQuery().use {
val acc = mutableListOf<Person>()
while (it.next()) {
acc +=
Person(
birthDate = LocalDate.parse(it.getString("birth_date")),
id = UUID.fromString(it.getString("id")),
name = it.getString("name"),
)
}
acc
}
}
}
Kotlite
.
(List)
C . , 0 N . .
(Entity)
, :
. LIMIT 2
.
@Query("SELECT id, name, birth_date FROM person WHERE name = :name")
fun findPersonBy(name: String): Person
public override fun findPersonBy(name: String): Person {
val query = """
|SELECT id, name, birth_date FROM person WHERE name = ?
|LIMIT 2
""".trimMargin()
return connection.prepareStatement(query).use {
it.setString(1, name)
it.executeQuery().use {
if (it.next()) {
val result =
Person(
birthDate = LocalDate.parse(it.getString("birth_date")),
id = UUID.fromString(it.getString("id")),
name = it.getString("name"),
)
if (it.next()) {
throw IllegalStateException("Query has returned more than one element")
}
result
} else {
throw NoSuchElementException()
}
}
}
}
kotlite.annotations.First
, ("") . : Int
, String
, UUID
LocalDate
..
@Query("SELECT name FROM person WHERE id = :id")
fun findPersonNameBy(id: UUID): String
, , - .
public override fun findPersonNameBy(id: UUID): String {
val query = """
|SELECT name FROM person WHERE id = ?
|LIMIT 2
""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, id)
it.executeQuery().use {
if (it.next()) {
val result =
it.getString(1)
if (it.next()) {
throw IllegalStateException("Query has returned more than one element")
}
result
} else {
throw NoSuchElementException()
}
}
}
}
kotlite.annotations.First
Nullable
Nullable
. null
.
@Query("SELECT name FROM person WHERE id = :id")
fun findPersonNameBy(id: UUID): String?
public override fun findPersonNameBy(id: UUID): String? {
val query = """
|SELECT name FROM person WHERE id = ?
|LIMIT 2
""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, id)
it.executeQuery().use {
if (it.next()) {
val result =
it.getString(1)
if (it.next()) {
throw IllegalStateException("Query has returned more than one element")
}
result
} else {
null
}
}
}
}
(Pagination)
Pageable
,
import kotlite.aux.page.Page
import kotlite.aux.page.Pageable
@SqliteRepository
interface PersonRepository : Repository<Person> {
@Query("SELECT name FROM person")
fun selectAll(pageable: Pageable): Page<String>
}
public override fun selectAll(pageable: Pageable): Page<String> {
val query = """
|SELECT name FROM person
|LIMIT ? OFFSET ?
""".trimMargin()
return connection.prepareStatement(query).use {
it.setInt(1, pageable.pageSize)
it.setInt(2, pageable.offset)
it.executeQuery().use {
val acc = mutableListOf<String>()
while (it.next()) {
acc +=
it.getString(1)
}
Page(pageable, acc)
}
}
}
SQL
– JDBC . SQL . , .
, . , kotlite.aux.Repository
import kotlite.annotations.SqliteRepository
import kotlite.aux.Repository
@SqliteRepository
interface PersonRepository : Repository<Person>
, SQL .
. a, UpperCamelCase
snake_case
. kotlite.annotations.Table
.
, . camelCase
snake_case
, kotlite.annotations.Column
?
, save
( kotlite.annotations.Save
) INSERT
. , . Unit
fun save(person: Person)
public override fun save(person: Person): Unit {
val query = """
|INSERT INTO person
|("birth_date", "id", "name")
|VALUES (?, ?, ?)
""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, person.birthDate)
it.setObject(2, person.id)
it.setString(3, person.name)
it.executeUpdate()
}
}
( kotlite.annotations.ID
) INSERT/UPDATE
public override fun save(person: Person): Unit {
val query = """
|INSERT INTO person
|("birth_date", "id", "name")
|VALUES (?, ?, ?)
|ON CONFLICT (id) DO
|UPDATE SET "birth_date" = EXCLUDED."birth_date", "id" = EXCLUDED."id", "name" = EXCLUDED."name"
|""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, person.birthDate)
it.setObject(2, person.id)
it.setString(3, person.name)
it.executeUpdate()
}
}
:
import kotlite.annotations.OnConflictFail
@OnConflictFail
fun save(person: Person)
kotlite.annotations.Version
public override fun save(person: Person): Unit {
val query = """
|INSERT INTO person
|("birth_date", "id", "name", "version")
|VALUES (?, ?, ?, ? + 1)
|ON CONFLICT (id) DO
|UPDATE SET "birth_date" = EXCLUDED."birth_date", "id" = EXCLUDED."id", "name" = EXCLUDED."name", "version" = EXCLUDED."version"
|WHERE person.version = EXCLUDED.version - 1
""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, person.birthDate)
it.setObject(2, person.id)
it.setString(3, person.name)
it.setInt(4, person.version)
val rows = it.executeUpdate()
if (rows != 1) {
throw OptimisticLockFailException()
}
}
}
public override fun delete(person: Person): Unit {
val query = """
|DELETE
|FROM person
|WHERE "id" = ? AND "version" = ?
""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, person.id)
it.setInt(2, person.version)
val rows = it.executeUpdate()
if (rows != 1) {
throw OptimisticLockFailException()
}
}
}
, delete ( kotlite.annotations.Delete
) DELETE
fun deleteAll()
public override fun deleteAll(): Unit {
val query = """
|DELETE
|FROM person
""".trimMargin()
return connection.prepareStatement(query).use {
it.executeUpdate()
}
}
:
fun delete(person: Person)
public override fun delete(person: Person): Unit {
val query = """
|DELETE
|FROM person
|WHERE "birth_date" = ? AND "id" = ? AND "name" = ?
""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, person.birthDate)
it.setObject(2, person.id)
it.setString(3, person.name)
it.executeUpdate()
}
}
( kotlite.annotations.Id
) – :
public override fun delete(person: Person): Unit {
val query = """
|DELETE
|FROM person
|WHERE "id" = ?
""".trimMargin()
return connection.prepareStatement(query).use {
it.setObject(1, person.id)
it.executeUpdate()
}
}
- , . " " " " .
, , SELECT
( , save
delete
).
fun selectAll(): List<Person>
public override fun selectAll(): List<Person> {
val query = """
|SELECT "birth_date", "id", "name"
|FROM person
""".trimMargin()
return connection.prepareStatement(query).use {
it.executeQuery().use {
val acc = mutableListOf<Person>()
while (it.next()) {
acc +=
Person(
birthDate = LocalDate.parse(it.getString("birth_date")),
id = UUID.fromString(it.getString("id")),
name = it.getString("name"),
)
}
acc
}
}
}
fun selectAll(): List<Person>
fun blaBlaBla(): List<Person>
.
. WHERE
AND
.
fun selectBy(name: String, birthDate: LocalDate): Person?
public override fun selectBy(name: String, birthDate: LocalDate): Person? {
val query = """
|SELECT "birth_date", "id", "name"
|FROM person
|WHERE "name" = ? AND "birth_date" = ?
|LIMIT 2
""".trimMargin()
return connection.prepareStatement(query).use {
it.setString(1, name)
it.setObject(2, birthDate)
it.executeQuery().use {
if (it.next()) {
val result =
Person(
birthDate = java.time.LocalDate.parse(it.getString("birth_date")),
id = UUID.fromString(it.getString("id")),
name = it.getString("name"),
)
if (it.next()) {
throw IllegalStateException("Query has returned more than one element")
}
result
} else {
null
}
}
}
}
>
, <=
, !=
.., OR
, kotlite.annotations.Where
:
@Where("name = :name OR birth_date < :birthDate")
fun selectBy(name: String, birthDate: LocalDate): Person?
.
public override fun selectBy(name: String, birthDate: LocalDate): Person? {
val query = """
|SELECT "birth_date", "id", "name"
|FROM person
|WHERE name = ? OR birth_date < ?
|LIMIT 2
""".trimMargin()
return connection.prepareStatement(query).use {
it.setString(1, name)
it.setObject(2, birthDate)
it.executeQuery().use {
if (it.next()) {
val result =
Person(
birthDate = java.time.LocalDate.parse(it.getString("birth_date")),
id = UUID.fromString(it.getString("id")),
name = it.getString("name"),
)
if (it.next()) {
throw IllegalStateException("Query has returned more than one element")
}
result
} else {
null
}
}
}
}
:
@OrderBy("name DESC, birth_date")
fun selectAll(): List<Person>
public override fun selectAll(): List<Person> {
val query = """
|SELECT "birth_date", "id", "name"
|FROM person
|ORDER BY name DESC, birth_date
""".trimMargin()
return connection.prepareStatement(query).use {
it.executeQuery().use {
val acc = mutableListOf<Person>()
while (it.next()) {
acc +=
Person(
birthDate = LocalDate.parse(it.getString("birth_date")),
id = UUID.fromString(it.getString("id")),
name = it.getString("name"),
)
}
acc
}
}
}
--
. . .. @Embeddable
JPA.
data class Person(
val name: Name,
)
data class Name(
val firstName: String,
val lastName: String,
)
CREATE TABLE person(
first_name text,
last_name text
)
JSON. .
--
. JSON.
data class Person(
val habits: List<String>
)
@SqliteRepository
interface PersonRepository: Repository<Person> {
fun save(person: Person)
fun select(): List<Person>
}
public override fun select(): List<Person> {
val query = """
|SELECT "habits"
|FROM person
""".trimMargin()
return connection.prepareStatement(query).use {
it.executeQuery().use {
val acc = mutableListOf<Person>()
while (it.next()) {
acc +=
Person(
habits = Json.decodeFromString(it.getString("habits")),
)
}
acc
}
}
}
public override fun save(person: Person): Unit {
val query = """
|INSERT INTO person
|("habits")
|VALUES (?)
""".trimMargin()
return connection.prepareStatement(query).use {
it.setString(1, Json.encodeToString(person.habits))
it.executeUpdate()
}
}
( JPA/Hibernate)
- SQL, (, ) , .
単純さが最も重要であるため、1対1、1対多の関係を作成する方法はありません（N + 1の問題はありません）。
遅延読み込みはありません（そして `SessionClosedException`はありません）。
タイプコンバーターの組み込みメカニズムはありません（APIはそれほど複雑ではなく、ライブラリーは1つの問題のみを解決します）。
継承階層を保持する方法はありません（主に、作成者が個人的に継承を嫌うためです。おそらく、将来追加される予定です）。
別のデータベースへの簡単な移行についての幻想はありません。
これが私たちのすべての力です
ご清聴ありがとうございました。