DBアクセスラッパー（JDBC + Kotlin）

こんにちは。
「KotlinでJDBCをちょっといい感じに使える気がするライブラリを作った」を興味を持ったので、手を加えてさらに少し楽になるようにしてみました。
GitHub: https://github.com/kkdd/devneko-kotlin-jdbc

select 機能（helper.query()）などに手を加え、":" で始まる変数名は、 AS() の指定で別名扱いに置換されます。
例： select(":abc") {AS("abc", "xyz")} ==> SELECT xyz AS abc

下記の応用実行例では近傍地点検索（MySQL (InnoDB) で空間検索 (st_within)）と同様の検索を sqlite/spatialite で行いました。

基本機能使用例

select.kt

rs = helper.query(tblname) {
  where("id > :id1") {
    AS("id1", 1)
  }
  select("id, name, :lon, :lat") {
    AS("lon", "X(geom)")
    AS("lat", "Y(geom)")
  }
  orderBy("lat")
}
while (rs.next()) {
/*  rs.get("id")
    rs.getString("id") */
}

insert_update_delete.kt

val geom = "GeomFromText('POINT(%f %f)', ${SRID})"
helper.insert(tblname) {
    set("id", 1)
    set("name", "Uber")
    setAs("geom", geom.format(135.0, 40.0))
}

helper.update(tblname) {
    where("id = 2")
    set("name", "Amazon")
    setAs("geom", geom.format(135.0, 40.0))
}

helper.delete(tblname) {
    where("id = 3")
}

応用実行例

$ brew cask install java
$ brew install sqlite libspatialite kotlin
$ wget https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.16.1.jar
$ kotlinc -classpath ".:sqlite-jdbc-3.16.1.jar" ./src/main/kotlin/net/devneko/kjdbc/*.kt
$ kotlinc -classpath ".:sqlite-jdbc-3.16.1.jar:net/devneko/kjdbc" spatialite.kt
$ kotlin  -classpath ".:sqlite-jdbc-3.16.1.jar:net/devneko/kjdbc" SpatialiteKt

 | id | name    | geom             | dist     |
 +----+---------+------------------+----------+
 | 1  | Google  | POINT(135 40)    | null     |
 | 4  | Amazon  | POINT(135 39.99) | 1110.345 |
 | 3  | Twitter | POINT(135 39.98) | 2220.689 |
 | 2  | Apple   | POINT(135 40.02) | 2220.696 |

spatialite.kt

Class.forName("org.sqlite.JDBC")
var conn: Connection? = null
var stmt: Statement? = null
var rs: ResultSet? = null
val dbname = "spatialite-test.sqlite"
val url = "jdbc:sqlite:" + dbname
val config = SQLiteConfig().apply {
  enableLoadExtension(true)
}

conn = DriverManager.getConnection(url, config.toProperties())
stmt = conn?.createStatement()
stmt?.executeQuery("SELECT load_extension('mod_spatialite')")

rs = stmt?.executeQuery("select count(name) from sqlite_master where name='spatial_ref_sys'")
if (rs!!.getInt(1)==0) {
    stmt?.executeQuery("SELECT InitSpatialMetadata(1)")
}

val SRID = 4326
val RE = 6378137.0
val DEG = 0.017453292519943295
val tblname = "company"
val helper = SqlHelper(conn)

val dist = 3000.0
val rad = dist/RE/Math.cos(pnt[1]*DEG)/DEG
val pnt = arrayListOf(135.0, 40.0)
val mbr = "BuildCircleMbr(${pnt[0]}, ${pnt[1]}, ${rad}, ${SRID})"
val pointCenter = "GeomFromText('POINT(${pnt[0]} ${pnt[1]})', ${SRID})"
rs = helper.query(tblname) {
  where("MBRContains(${mbr}, geom) and dist <= ${dist} or dist IS NULL")
  select("id, name, :geom, :dist") {
    AS("geom", "AsText(geom)")
    AS("dist", "Round(Distance(geom, ${pointCenter}, 1), 3)")
  }
  orderBy("dist")
}
while (rs.next()) {
/*  rs.get("id")
    rs.getString("id") */
}