Ada meja:
CREATE TABLE person
(
id uuid primary key,
name text,
birth_date date
)
dan kelas data yang sesuai:
data class Person(
val id: UUID,
val name: String,
val birthDate: LocalDate,
)
Bagaimana jika untuk melakukan operasi CRUD dasar:
simpan daftar
Person
s
kurangi semuanya dari tabel
hapus semua catatan di tabel
temukan dengan ID
hapus dengan nama
itu akan cukup untuk membuat antarmuka:
@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)
}
dan implementasinya akan dibuat secara otomatis.
Mengingatkan saya pada Data Musim Semi? Tapi ini bukan Spring, bukan Hibernate, atau bahkan JPA.
TL; DR
Library yang berpusat pada Kotlin (bukan framework)
Tanpa ORM (tanpa 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, (, ) , .
Karena kesederhanaan adalah yang terpenting, tidak ada cara untuk membuat hubungan satu-ke-satu, satu-ke-banyak (dan tidak ada masalah N + 1).
Tidak ada pemuatan lambat (dan tidak ada `SessionClosedException`).
Tidak ada mekanisme bawaan untuk pengonversi tipe (API tidak terlalu rumit, pustaka hanya memecahkan satu masalah).
Tidak ada cara untuk mempertahankan hierarki warisan (terutama karena ketidaksukaan pribadi penulis terhadap warisan. Mungkin akan ditambahkan di masa mendatang).
Tidak ada ilusi tentang migrasi mudah ke database lain.
Ini semua adalah kekuatan kita
Terima kasih atas perhatian Anda.