There is a table:
CREATE TABLE person
(
id uuid primary key,
name text,
birth_date date
)
and the corresponding data-class:
data class Person(
val id: UUID,
val name: String,
val birthDate: LocalDate,
)
What if in order to perform basic CRUD operations:
save the list of
Person
s
subtract everything from the table
delete all records in the table
find by ID
delete by name
it will be enough to create an 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)
}
and the implementation will be generated automatically.
Reminds me of Spring Data? But this is not Spring, Hibernate, or even JPA.
TL; DR
Kotlin-centric library (not a framework)
No ORM (no JPA)
Generates SQL and JDBC before compilation (Kotlin Annotation Precessing)
No magic at runtime
, , ,
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, (, ) , .
Because simplicity is paramount, there is no way to create one-to-one, one-to-many relationships (and no N + 1 problem).
No lazy loads (and no `SessionClosedException`).
There is no built-in mechanism for type converters (the API is not overcomplicated, the library solves only one problem).
There is no way to preserve inheritance hierarchies (mainly due to the author's personal dislike of inheritance. Perhaps it will be added in the future).
Under no illusion about easy migration to another database.
This is all our powers
Thank you for your attention.