Skip to content

TEX.md

Latest commit

 

History

History
130 lines (87 loc) · 3.02 KB

TEX.md

File metadata and controls

130 lines (87 loc) · 3.02 KB

TEX: klite-jdbc

Given a Kotlin project

How to talk to the DB?

  • Hibernate is big, slow and complex

  • Sql2o - verbose code, JVM crashes

  • Spring Data - generates code that you can't debug

  • Exposed for Kotlin - too big a DSL - I still like plain SQL

  • Frameworks should not hide the real stuff from the dev; also GWT/React/etc

  • They should simplify common use cases, letting to go deeper for full-power (e.g. plain SQL)

JDBC is ok

You just need small helpers to reduce boilerplate. Kotlin's extension functions are perfect for that

jdbc:

val rs = conn.prepareStatement("select * from users where email = ?").run {
  setString(1, "hello@example.com")
  executeQuery()
}

while (rs.next()) {
  User(rs.getString("name"), rs.getString("email"))
}

// BUT: don't forget to close() things

klite:

db.query("select * from users", mapOf("email" to "hello@example.com"))

db.select("users", mapOf("email" to "hello@example.com")) { // this == ResultSet
  User(getString("name"), getString("email"))
}

// More convenience extensions, e.g.:
ResultSet.getLocalDate()
ResultSet.getId()

ORMs usually simplify mapping of classes

It's good to keep domain classes close to DB tables, with same column names

val user = db.select("users", "email" to "hello@example.com") { create<User>() }.first()

What if I need to change how some fields are read?

val user = db.select("users", "email" to "hello@example.com") { create(User::role to Role.BACKOFFICE) }

Why not type-safe where?

val user = db.select<User>("users", User::email to Email("hello@example.com"))

Operators

User::birthDay to null
User::birthDay to SqlOp("<", someDate)

User::age to listOf(1, 2, 3)

User::birthDay to notNull
User::birthDay lte someDate

User::name like "Anton%"
User::name to SqlExpr("(select ... where x = ?)", x)

Crud operations?

db.insert("users", mapOf("name" to "Anton", "email" to Email("hello@example.com")))
// will generate the trivial: insert into users (name, email) values (?, ?)

// with "ORM"
db.insert("users", user.toValues())
db.insert("users", user.toValuesSkipping(User::role) + mapOf("anything" to "else"))

db.update("users", mapOf("id" to user.id), user.toValues())
db.delete("users", mapOf("id" to user.id))

db.upsert("users", user.toValues()) // "id" by default checked for uniqueness

Repositories

  • BaseRepository
  • CrudRepository

POKOs -> data classes

Autogenerated IDs:

data class User(val name: String, val email: Email, override val id: UUID = randomUUID()): Entity

Auto-validation of required fields, proper types for more complex checks (e.g. Email)

DB Generated fields

val generatedId = GeneratedKey<Int>()
db.insert(table, mapOf("id" to generatedId, "hello" to "Hello"))

Migrations

  • DBMigrator vs Liquibase, SQL format
  • Treat DB objects as maintainable code

Transactions

db is DataSource

Caller is responsible for starting/closing transactions, e.g. klite request handler or JobRunner