You are currently viewing Feedback requested on tiny JDBC layer in Javalin web API.

Feedback requested on tiny JDBC layer in Javalin web API.

Hello, I would appreciate feedback on my tiny JDBC layer. I have found large libraries and frameworks intimidating so I decided to write something I’d understand and something that would help me be more productive. I have included a sample usage in Javalin web API. This is the first iteration of what has worked for me. I am inexperienced with Kotlin, so that is my primary concern.

https://gist.github.com/asmarcz/acce6afeca510013deca6b6022c5b38b


import com.zaxxer.hikari.HikariDataSource import io.javalin.Javalin import org.slf4j.LoggerFactory import java.sql.PreparedStatement import java.sql.ResultSet import java.sql.SQLException val ds = HikariDataSource().apply { jdbcUrl = "jdbc:pgsql://localhost/test" username = "api" password = "dummy123" } fun ResultSet.toList(): List<Map<String, Any>> { val metadata = this.metaData val rows = mutableListOf<Map<String, Any>>() while (this.next()) { val row = mutableMapOf<String, Any>() for (i in 1..metadata.columnCount) { row[metadata.getColumnLabel(i)] = this.getObject(i) } rows.add(row) } return rows } class SQL(private val query: String) { private val generated = mutableListOf<String>() interface UpdateResult { val affected: Int val generatedKeys: List<Map<String, Any>> } fun asList(vararg params: Any): List<Map<String, Any>> { return prepare(params) { stmt -> val rows: List<Map<String, Any>> stmt.executeQuery().use { rs -> rows = rs.toList() } rows } } fun generatedKeys(vararg keys: String): SQL { generated.addAll(keys) return this } fun update(vararg params: Any): UpdateResult { return prepare(params, generated.toTypedArray()) { stmt -> val affected = stmt.executeUpdate() object : UpdateResult { override val affected = affected override val generatedKeys = stmt.generatedKeys.toList() } } } private fun <T> prepare( params: Array<out Any>, generatedKeys: Array<String>? = null, callback: (stmt: PreparedStatement) -> T ): T { ds.connection.use { con -> when (generatedKeys) { null -> con.prepareStatement(query) else -> con.prepareStatement(query, generatedKeys) }.use { stmt -> var idx = 1 for (param in params) { stmt.setObject(idx++, param) } return callback(stmt) } } } } fun main() { val app = Javalin.create().start(8100) val logger = LoggerFactory.getLogger("test-api") app.exception(SQLException::class.java) { e, ctx -> logger.error("An SQLException occurred.", e) ctx.result("Sorry, it's on us.") ctx.status(500) } app.get("/") { ctx -> ctx.json( SQL("SELECT name FROM people").asList() ) } app.get("/:length") { ctx -> ctx.json( SQL("SELECT name FROM people WHERE LENGTH(name) = ?") .asList(ctx.pathParam<Int>("length").get()) ) } app.post("/add") { ctx -> val result = SQL("INSERT INTO people (name) VALUES (?)") .generatedKeys("id") .update(ctx.formParam<String>("name").get()) ctx.result( "Affected ${result.affected} row with id=${result.generatedKeys[0]["id"]} and name=${ctx.formParam("name")}." ) } }

submitted by /u/asmarCZ
[link] [comments]