Existe uma mesa:
CREATE TABLE person
(
id uuid primary key,
name text,
birth_date date
)
e a classe de dados correspondente:
data class Person(
val id: UUID,
val name: String,
val birthDate: LocalDate,
)
E se, para realizar operações CRUD básicas:
salve a lista de
Person
s
subtraia tudo da mesa
deletar todos os registros da tabela
encontrar por ID
deletar pelo nome
bastará criar uma interface:
@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)
}
e a implementação será gerada automaticamente.
Lembra-me do Spring Data? Mas isso não é Spring, Hibernate ou mesmo JPA.
TL; DR
Biblioteca centrada em Kotlin (não é uma estrutura)
Sem ORM (sem 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, (, ) , .
Como a simplicidade é fundamental, não há como criar relacionamentos um para um, um para muitos (e nenhum problema N + 1).
Sem cargas lentas (e sem `SessionClosedException`).
Não há mecanismo integrado para conversores de tipo (a API não é complicada demais, a biblioteca resolve apenas um problema).
Não há como preservar as hierarquias de herança (principalmente devido à antipatia pessoal do autor por herança. Talvez ela seja adicionada no futuro).
Sem ilusão de migração fácil para outro banco de dados.
Estes são todos os nossos poderes
Obrigado pela sua atenção.