As part of migrating our service to Kotlin, we wanted to keep our Postgres DB in pure sql and decided to use Ktor(as Native SQL) along with HikariCP as the DataSource for connection pooling. (For a few reasons, e.g - we're creating our own DOMAINS, TRIGGER FUNCTIONS and using JSON/JSONB, which is not all compatible with ORMs, and we also don't want to be framework-dependant cause we might switch to different ones in the future)
I'm using :
- HikariCP for connection pool
- PreparedStatements for all the queries (via the Ktorm library)
I've made these functions :
fun <T : Any> String.select(db: Database, vararg values: Any, transform: (ResultSet) -> T): List<T> {
val result = arrayListOf<T>()
db.useConnection { conn ->
conn.prepareStatement(this).use { st ->
values.forEachIndexed { idx, value ->
st.bind(idx + 1, value)
}
st.executeQuery().asIterable().map { rs ->
result += transform(rs)
}
}
}
return result
}
private fun PreparedStatement.bind(index: Int, value: Any) {
when (value) {
is Int -> setInt(index, value)
is Long -> setLong(index, value)
is Float -> setFloat(index, value)
is String -> setString(index, value)
}
}
The usage is then like this :
val preparedStatement = "Select * from users where name = ?"
val users: List<User> = preparedStatement.select(dbClient.db, "John") { row ->
User(
id = row.getInt(1),
name = row.getString(2),
lastName = row.getString(3)
)
}
But when I run this I noticed this message in my logs :
com.zaxxer.hikari.pool.ProxyConnection - HikariPool-1 - Executed rollback on connection org.postgresql.jdbc.PgConnection@32eebfca due to dirty commit state on close().
When I tried to use autoCommit = true , I started getting then :
HikariPool-0 - Reset (autoCommit) on connection
When I tried simply using conn.commit() at the end of the statement execution, it seemed there were no suspicious logs. But should I really COMMIT in SELECT queries ?
