0
\$\begingroup\$

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?
\$\endgroup\$
1
\$\begingroup\$

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().

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.