Hay una mesa:
CREATE TABLE person
(
id uuid primary key,
name text,
birth_date date
)
y la clase de datos correspondiente:
data class Person(
val id: UUID,
val name: String,
val birthDate: LocalDate,
)
¿Y si para realizar operaciones CRUD básicas:
guardar la lista de
Person
s
restar todo de la mesa
eliminar todos los registros de la tabla
buscar por ID
eliminar por nombre
bastará con crear una interfaz:
@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)
}
y la implementación se generará automáticamente.
¿Me recuerda a Spring Data? Pero esto no es Spring, ni Hibernate, ni siquiera JPA.
TL; DR
Biblioteca centrada en Kotlin (no un marco)
Sin ORM (sin 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, (, ) , .
Debido a que la simplicidad es primordial, no hay forma de crear relaciones uno a uno, uno a muchos (y ningún problema N + 1).
Sin cargas diferidas (y sin `SessionClosedException`).
No hay un mecanismo incorporado para los convertidores de tipos (la API no es demasiado complicada, la biblioteca resuelve solo un problema).
No hay forma de preservar las jerarquías de herencia (principalmente debido a la aversión personal del autor por la herencia. Quizás se agregue en el futuro).
No se haga ilusiones sobre la migración fácil a otra base de datos.
Estos son todos nuestros poderes
Gracias por su atención.