Is there a way to run raw sql with Kotlin's Exposed library

Question

I'm trying to run some sql specific to postgres and would like to reuse the transaction management within Exposed.

Slapjack

Answer 1

Exposed has the Transaction.exec(String) method which probably does what you want. See https://github.com/JetBrains/Exposed/blob/master/exposed-tests/src/test/kotlin/org/jetbrains/exposed/sql/tests/mysql/MysqlTests.kt

Wheaton

Answer 2

With help from Kiskae's answer, I was able to run the raw sql with:

transaction {
     val conn = TransactionManager.current().connection
     val statement = conn.createStatement()
     val query = "REFRESH MATERIALIZED VIEW someview"
     statement.execute(query)
}

Slapjack

Kiskae's answer is dead. The link returns 404 – Hayton

Should we close resources on me? like that kotlin executeQuery.close() statement.close() conn.close() – Kenaz

Outdated answer. createStatement() doesn't exist on exposed 0.31.1. – Kailyard

@Kailyard the reason for this is because the connection field does not expose the JDBC connection directly, as a workaround (for anyone that needs the JDBC connection itself) you can do this: val conn = (TransactionManager.current().connection as JdbcConnectionImpl).connection – Sallet

Answer 3

Here an example with parameters:

transaction(database) {
   val conn = TransactionManager.current().connection
   val query = "update user set name = ? where id = ?";
   val statement = conn.prepareStatement(query, false)
   statement.fillParameters(listOf(Pair(VarCharColumnType(), "Laura"),
      Pair(IntegerColumnType(), 3)));
   statement.executeUpdate()
}

Oneirocritic

Answer 4

import java.sql.*
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.*

fun getAccId(db : Database, acc_domain: String) {
  transaction(db) {
    addLogger(StdOutSqlLogger)
    var acc_id: Long = 0
    exec("select id from accounts where domain = '${acc_domain}'") { rs ->
        while(rs.next()) {
          acc_id = rs.getLong("id")
        }
    }
    println("Acc Id is ${acc_id}")
  }
}

val database = Database.connect(datasource)
getAccId(database,"mytest.test.io")

Anecdotage

This method is prone to SQL injection and one should use prepared statement instead. See @Martin Pabst answer – Dellinger

Answer 5

You can create a simple helper function like:

fun <T : Any> String.execAndMap(transform: (ResultSet) -> T): List<T> {
    val result = arrayListOf<T>()
    TransactionManager.current().exec(this) { rs ->
        while (rs.next()) {
            result += transform(rs)
        }
    }
    return result
}
"select u.name, c.name from user u inner join city c where blah blah".execAndMap { rs ->
    rs.getString("u.name") to rs.getString("c.name")
}

Taken from: https://github.com/JetBrains/Exposed/wiki/FAQ#q-is-it-possible-to-use-native-sql--sql-as-a-string

Leavis