KotliQuery

Build Status

A handy RDB client library in Kotlin. Highly inspired from ScalikeJDBC. This library focuses on providing handy and Kotlin-ish API to issue a query and extract values from its JDBC ResultSet iterator.

Getting Started

You can try this library with Gradle right now. See the sample project:

https://github.com/seratch/kotliquery/tree/master/sample

build.gradle

apply plugin: 'kotlin'

buildscript {
    ext.kotlin_version = '1.1.3-2'
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath "org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlin_version"
    }
}
repositories {
    mavenCentral()
}
dependencies {
    compile "org.jetbrains.kotlin:kotlin-stdlib:$kotlin_version"
    compile 'com.github.seratch:kotliquery:1.1.4'
    compile 'com.h2database:h2:1.4.196'
    compile 'com.zaxxer:HikariCP:2.6.3'
}

Example

KotliQuery is very easy-to-use. After reading this short documentation, you will have learnt enough.

Creating DB Session

Session object, thin wrapper of java.sql.Connection instance, runs queries.

import kotliquery.*

val session = sessionOf("jdbc:h2:mem:hello", "user", "pass")

HikariCP

Using connection pool would be better for serious programming.

HikariCP is blazingly fast and so handy.

HikariCP.default("jdbc:h2:mem:hello", "user", "pass")

using(sessionOf(HikariCP.dataSource())) { session ->
   // working with the session
}

DDL Execution

session.run(queryOf("""
  create table members (
    id serial not null primary key,
    name varchar(64),
    created_at timestamp not null
  )
""").asExecute) // returns Boolean

Update Operations

val insertQuery: String = "insert into members (name,  created_at) values (?, ?)"

session.run(queryOf(insertQuery, "Alice", Date()).asUpdate) // returns effected row count
session.run(queryOf(insertQuery, "Bob", Date()).asUpdate)

Select Queries

Prepare select query execution in the following steps:

  • Create Query object by using queryOf factory
  • Bind extractor function ((Row) -> A) to the Query object via #map method
  • Specify response type (asList/asSingle) at the end
val allIdsQuery = queryOf("select id from members").map { row -> row.int("id") }.asList
val ids: List<Int> = session.run(allIdsQuery)

Extractor function can return any type of result from ResultSet.

data class Member(
  val id: Int,
  val name: String?,
  val createdAt: java.time.ZonedDateTime)

val toMember: (Row) -> Member = { row -> 
  Member(
    row.int("id"), 
    row.stringOrNull("name"), 
    row.zonedDateTime("created_at")
  )
}

val allMembersQuery = queryOf("select id, name, created_at from members").map(toMember).asList
val members: List<Member> = session.run(allMembersQuery)
val aliceQuery = queryOf("select id, name, created_at from members where name = ?", "Alice").map(toMember).asSingle
val alice: Member? = session.run(aliceQuery)

Named query parameters

Alternative syntax is supported to allow named parameters in all queries.

queryOf("""select id, name, created_at 
	from members 
	where (:name is not null or name = :name)
	  and (:age is not null or age = :age)""", 
	mapOf("name" to "Alice"))

In the query above, the param age is not supplied on purpose.

Performance-wise this syntax is slightly slower to prepare the statement and a tiny bit more memory-consuming, due to param mapping. Use it if readability is a priority.

Importantly, this method is not based on “artificial” string replacement. In fact, the statement is prepared just as if it was the default syntax.

Working with Large Dataset

#forEach allows you to make some side-effect in iterations. This API is useful for handling large ResultSet.

session.forEach(queryOf("select id from members")) { row ->
  // working with large data set
})

Transaction

Session object provides transaction block.

session.transaction { tx ->
  // begin
  tx.run(queryOf("insert into members (name,  created_at) values (?, ?)", "Alice", Date()).asUpdate)
}
// commit

session.transaction { tx ->
  // begin
  tx.run(queryOf("update members set name = ? where id = ?", "Chris", 1).asUpdate)
  throw RuntimeException() // rollback
}

License

(The MIT License)

Copyright (c) 2015 - Kazuhiro Sera

compile "com.github.seratch:kotliquery:1.1.4"

Related Libraries

kuery

Strongly typed SQL in Kotlin

Last updated 3 mins ago

sqldelight

Generates Java models from CREATE TABLE statements.

Last updated 3 mins ago

kotliquery

A handy Database access library in Kotlin

Last updated 3 mins ago

pultusorm

PultusORM is a sqlite ORM library for kotlin / Java / Android.

Last updated 3 mins ago

requery

requery - modern SQL based query & persistence for Java / Kotlin / Android

Last updated 3 mins ago