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.

score 14 · Answer 1 · edited May 06 '20 at 15:32

14

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

edited May 06 '20 at 15:32

Greg Dan

6,198
3
33
53

answered Dec 06 '16 at 00:19

Kiskae

24,655
2
77
74

score 11 · Answer 2 · answered Dec 11 '16 at 23:39

11

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)
}

answered Dec 11 '16 at 23:39

pondermatic

6,453
10
48
63

Kiskae's answer is dead. The link returns 404 – dustytrash Feb 17 '20 at 15:55
1

Should we close resources on me? like that ```kotlin executeQuery.close() statement.close() conn.close() ``` – Linuxea Mar 05 '20 at 09:03
4

Outdated answer. `createStatement()` doesn't exist on `exposed 0.31.1`. – Hykilpikonna Jul 09 '21 at 08:34
@Hykilpikonna 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` – MrPowerGamerBR Oct 29 '22 at 23:58

score 5 · Answer 3 · answered Aug 17 '20 at 13:14

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

score 3 · Answer 4 · answered Jun 16 '20 at 10:49

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")

This method is prone to SQL injection and one should use prepared statement instead. See @Martin Pabst answer — Kavin Eswaramoorthy, Sep 02 '21 at 20:41

score 1 · Answer 5 · edited Dec 14 '22 at 15:31

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

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

5 Answers5

Linked