support for native sql?

[mvysny]

Exposed looks great! Yet at some point I fear that some complex queries will not be representable by Kotlin magic. And, frankly,

(Users innerJoin Cities).slice(Users.name, Cities.name).
            select {(Users.id.eq("andrey") or Users.name.eq("Sergey")) and
                    Users.id.eq("sergey") and Users.cityId.eq(Cities.id)}.forEach {
            println("${it[Users.name]} lives in ${it[Cities.name]}")
        }

Is too high-level magic for my tired brain :-)

I would prefer something like this for complex queries (you know one of those queries hand-tuned for perfection, which runs only 5 hours instead of 10 weeks):

"select u.name, c.name from user u inner join city c where blah blah".map(Users.name, Cities.name).forEach { ... }

Anyway, thanks for a fresh wind in the stale waters of JPA ;) It's good to have a framework where I can represent tables with objects, but without the 1st level cache/LazyInitializationException/other goodies from the JPA world. Yet, I want to be in charge of creating/migrating tables (of the DDL), and of queries more complex than "gimme all users in this city".

[Tapac]

Hi @mvysny , thank you for warm words.

About "native sql", you might try something like this:

TransactionManager.current().exec("select u.name, c.name from user u inner join city c where blah blah") { rs ->
     val result = arrayListOf<Pair<String, String>>()
     while (rs.next()) {
          result += rs.getString("u.name") to rs.getString("c.name") 
     }
     result
}.forEeach { ... }

or more generic

fun <T:Any> String.execAndMap(transform : (ResultSet) -> T) : List<T> {
     val result = arrayListOf<T>()
     TransactionManager.current().exec("") { 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") 
}

[sobvan]

Hi @Tapac,

thanks for the example. I think, however, there is a small error in the more generic one. The line

TransactionManager.current().exec("") { rs ->

should be

TransactionManager.current().exec(this) { rs ->

in my opinion.

[Tapac]

@istvanszoboszlai you are right! To be honest I don't ran this code, so it not surprise that it contains bug:D Glad you find it.

[jsonbrooks]

How can you prevent SQL injection if you use this approach, just passing in a raw string?

[Tapac]

@jsonbrooks , do not use user-provided data in such queries?

[jsonbrooks]

It just seems like a very common use case to me if you do need custom native queries.

Would it be possible to expose an exec method with a PreparedStatement as input instead?

[Tapac]

@jsonbrooks
First of all, we already struggle from what Exposed is tightly bound to a jdbc implementation, what prevents us from making it cross-platform or use async-drivers, so we don't want to make it even more depended.

Also, it's not so hard to make such function and place it locally:

fun Transaction.exec(sql: String, body: PreparedStatement.() -> Unit) : ResultSet? {
    return connection.prepareStatement(sql).apply(body).run {
        if (sql.toLowerCase().startsWith("select "))
            executeQuery()
        else {
            executeUpdate()
            resultSet
        }
    }
}

[Aditya94A]

Would it be possible to define triggers in this way?

[Tapac]

@AdityaAnand1 , yes if you provide raw SQL with complete trigger creation text

[sobvan]

First of all, we already struggle from what Exposed is tightly bound to a jdbc implementation, what prevents us from making it cross-platform or use async-drivers...

Wow, @Tapac, does it mean that you already work on adding support for async drivers?
I am so amazed by the fact that in the µ-services world there is still no proper async RDBMS support.

[Tapac]

@istvanszoboszlai , yes, I have a plan to try support async drivers, but not sure how it should be implemented without making every function suspend. So I can't estimate when this functionality will be released, but this feature is one of the topmost in the priority list.

[Linuxea]

Hi @mvysny , thank you for warm words.

About "native sql", you might try something like this:

TransactionManager.current().exec("select u.name, c.name from user u inner join city c where blah blah") { rs ->
     val result = arrayListOf<Pair<String, String>>()
     while (rs.next()) {
          result += rs.getString("u.name") to rs.getString("c.name") 
     }
     result
}.forEeach { ... }

or more generic

fun <T:Any> String.execAndMap(transform : (ResultSet) -> T) : List<T> {
     val result = arrayListOf<T>()
     TransactionManager.current().exec("") { 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") 
}

Excuse me. I don't know why show me the code as follows:

My exposed version:

<jetbrains.exposed.version>0.16.1</jetbrains.exposed.version>

[Linuxea]

Hi @mvysny , thank you for warm words.
About "native sql", you might try something like this:

TransactionManager.current().exec("select u.name, c.name from user u inner join city c where blah blah") { rs ->
     val result = arrayListOf<Pair<String, String>>()
     while (rs.next()) {
          result += rs.getString("u.name") to rs.getString("c.name") 
     }
     result
}.forEeach { ... }

or more generic

fun <T:Any> String.execAndMap(transform : (ResultSet) -> T) : List<T> {
     val result = arrayListOf<T>()
     TransactionManager.current().exec("") { 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") 
}

Excuse me. I don't know why show me the code as follows:

My exposed version:

<jetbrains.exposed.version>0.16.1</jetbrains.exposed.version>

My solution:

[Tapac]

Just remove unneeded parenteses:

[Linuxea]

Just remove unneeded parenteses:

Haaa. Thank you very much!
I am fresh in kotlin, now I am more understand them.

[akifb]

If you want to use ResultRow, (with the help of given code samples) we can perform transformation like below.

fun FieldSet.nativeSelect(query: String): List<ResultRow> {
    val fieldsIndex = realFields.toSet().mapIndexed { index, expression -> expression to index }.toMap()
    val resultRows = mutableListOf<ResultRow>()
    TransactionManager.current().exec(query) { resultSet ->
        while (resultSet.next()) {
            resultRows.add(ResultRow.create(resultSet, fieldsIndex))
        }
    }

    return resultRows
}

private fun getResultRows(): List<ResultRow> {
        val query = "..."
        return SampleTable.nativeSelect(query)
}

The only possible problem is fieldsIndex creation is repeated whenever nativeQueryMethod is called although it just depends on table structure. To overcome it, we can define it as a singleton object and use native select as utility function as below.

// another alternative

// global object
val sampleTableFieldsIndex = SampleTable.realFields.toSet().mapIndexed { index, expression -> expression to index }.toMap()

fun nativeSelect(query: String, fieldsIndex: Map<Expression<*>, Int>): List<ResultRow> {
    val resultRows = mutableListOf<ResultRow>()
    TransactionManager.current().exec(query) { resultSet ->
        while (resultSet.next()) {
            resultRows.add(ResultRow.create(resultSet, fieldsIndex))
        }
    }

    return resultRows
}

private fun getResultRows(): List<ResultRow> {
        val query = "..."
        return nativeSelect(query, sampleTableFieldsIndex)
}

[imbananko]

@akifb big thanks for ResultRow snippets above

after the some investigation I figured out that this code works only if you keep the order of columns in select query the same as in indexed query structure (that you use for mapping)

based on this topic: https://stackoverflow.com/questions/62786434/how-to-convert-kotline-exposed-resultset-to-entity
I had to add the following:

val IntIdTable.allFields get() = fields.joinToString(", ") { "$tableName.${(it as Column<*>).name}" }

So my final code looks like that:

val sql = "" +
          "select ${MyTable.allFields} " +
          "from my_table;" 

val result = MyTable.nativeSelect(sql)

[ChrisBucchere-RL]

It just seems like a very common use case to me if you do need custom native queries.

Would it be possible to expose an exec method with a PreparedStatement as input instead?

@jsonbrooks Did you ever find a way to get exec working with a prepared statement?