db9010, A Kotlin Wrapper over JDBC

db9010 is a module that provides a wrapper over JDBC that makes it more Kotlin-friendly. It's inspired by JetBrains/Exposed, but it's vastly simpler. Indeed, it was designed with the 90/10 rule in mind: Often, you can get 90% of the benefit for 10% of the work (and complexity!).

db9010 lets you access JDBC from Kotlin with code that looks like the following example. Essentially, it gives you a way to define tables and query columns, and run SQL statements using type-safe, named accessors. It's a thin layer over JDBC with a regular syntax, so hopefully it's easy to understand.

import com.jovial.db9010.*
import java.util.logging.Handler
import java.util.logging.Level
import java.util.logging.LogRecord
import java.util.logging.Logger

object People : Table("People") {

    val id = TableColumn(this, "id", "INT AUTO_INCREMENT", Types.sqlInt)
    val name = TableColumn(this, "name", "VARCHAR(50) NOT NULL", Types.sqlString)
    val worried = TableColumn(this, "worried", "BOOLEAN NOT NULL", Types.sqlBoolean)

    override val primaryKeys = listOf(id)
}   

fun main() {

    Database.withConnection("jdbc:h2:mem:test", "root", "") { db ->

        db.createTable(People)

        db.statement().qualifyColumnNames(false).doNotCache() +
            "CREATE INDEX " + People + "_name on " + 
            People + "(" + People.name + ")" run {}

        People.apply {
            db.insertInto(this) run { row ->
                row[name] = "Alfred E. Neuman"
                row[worried] = false
            }   
        }   

        val param = Parameter(Types.sqlString)
        db.select(People.columns).from(People) + "WHERE " + People.name + " <> " + param run { query ->
            query[param] = "Bob Dobbs"      // Hide Bob, if he's there
            while (query.next()) {
                val idValue = query[People.id];  // It's type-safe
                println("id:  ${idValue}")
                println("name:  ${query[People.name]}")
                println("worried?  ${query[People.worried]}")
            }   
        }   
        db.dropTable(People)
    }   
}