JDBC wrapper in a functional manner

Question

I implemented a simple Java JDBC wrapper in Kotlin with Arrow functional library.

/**
 * Executes simple SQL statement that takes no arguments.
 * E.g. "CREATE TABLE" statement.
 *
 * @return IO with either an exception or boolean result
 */
fun String.executeStatement(dataSource: DataSource): IO<Either<Throwable, Boolean>> {
    val connection = dataSource.connection
    return IO { connection.createStatement().execute(this) }.attemptAndCloseConnection(connection)
}

/**
 * Executes prepared SQL statement (a statement that takes arguments).
 * E.g. "INSERT, UPDATE, DELETE..." statements.
 *
 * @return IO with either an exception or with result of execution as an integer
 */
fun String.executePreparedStatement(dataSource: DataSource, vararg args: Any): IO<Either<Throwable, Int>> {
    val connection = dataSource.connection
    return IO {
        val preparedStatement = connection.prepareStatement(this)
        args.forEachIndexed() { idx, arg -> preparedStatement.setObject(idx + 1, arg) }
        preparedStatement.executeUpdate()
    }.attemptAndCloseConnection(connection)
}

/**
 * Executes SQL query
 */
fun <E> String.executeQuery(dataSource: DataSource, resultSetMapper: (ResultSet) -> E, vararg args: Any): IO<Either<Throwable, List<E>>> {
    val connection = dataSource.connection
    return IO {
        val list = mutableListOf<E>()
        val preparedStatement = connection.prepareStatement(this)
        args.forEachIndexed() { idx, arg -> preparedStatement.setObject(idx + 1, arg) }
        val resultSet = preparedStatement.executeQuery()
        while (resultSet.next()) {
            list.add(resultSetMapper(resultSet))
        }
        list.toList()
    }.attemptAndCloseConnection(connection)
}

private fun <A> IO<A>.attemptAndCloseConnection(connection: Connection?) =
        this.attempt().map { connection?.close(); it }

Every result of execution is wrapped in an instance of IO to represent side-effects.

Functions are invoked directly on strings which produces an instance of Either<Throwable, T> when run:

val dataSource = HikariDataSource()
// add config for data source...

"""CREATE TABLE cities (
            id INTEGER PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL);""".executeStatement(dataSource)
                .unsafeRunSync()

"INSERT INTO cities (name) VALUES (?)".executePreparedStatement(dataSource, "New York").unsafeRunSync()

My major concerns are:

• Passing an instance of DataSource upon invocation of every function. E.g. with Scala, I could pass data source implicitly which would declutter invocations.
• Mutable list in executeQuery function. Is it even ok?
• Closing connection (attemptAndCloseConnection function) - is there some better way to do this?
• Other proposals?

Answer 1

You can pass context by defining extenstions inside the class + by using "Builder API". But I recommend you to avoid extensions like String.smt().

object ConnectionPool{
    fun useConnection(body: Connection.() -> Unit){
        val connection = Connection()
        connection.body()
    }
}

class Connection {
    val field = 42
    fun String.execute() {
        println(field)
    }
    fun execute2(query: String){
        println(field)
    }
}

fun sample() {
    ConnectionPool.useConnection {
        "CREATE TABLE".execute()
        execute2("CREATE TABLE")
    }
}

Also you can avoid mutablelist if you use generateSequence{} and map{}

generateSequence { if (resultSet.next()) resultSet else null }
   .map{ resultSetMapper(it) }.toList()

And I would reccomend to put close connection to useConnection().