A handy Database access library in Kotlin
Kotlin
Latest commit d2c857f Jul 17, 2016 @seratch committed on GitHub Update README.md
Permalink
Failed to load latest commit information.
gradle/wrapper version 1.0.1 Feb 28, 2016
sample version 1.1.0 Apr 9, 2016
src version 1.1.0 Apr 9, 2016
.gitignore Kotlin 1.0.0-beta-4589 Jan 20, 2016
.travis.yml Add QueryAction Nov 28, 2015
LICENSE Update docs Feb 28, 2016
README.md Update README.md Jul 17, 2016
build.gradle version 1.1.1 Jul 17, 2016
gradlew Initial version Nov 27, 2015
gradlew.bat Initial version Nov 27, 2015

README.md

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.0.3'
    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.1'
    compile 'com.h2database:h2:1.4.192'
    compile 'com.zaxxer:HikariCP:2.4.6'
}

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)

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