how to convert kotlin Exposed ResultSet to Entity?

user_1982282 0
Viewed 3362

I use kotlin Exposed with DAO style, and have a code like below. here, Nodes is Table of Exposed and Node is Entity which is created from Nodes table, and has Long Id.

    transaction {
        TransactionManager.current().exec(
            "select * from nodes " + 
            "where date_part('microseconds', updated_at - last_deployed) > 1000 " + 
            "order by last_change_status asc " + 
            "limit " + max + " offset " + offset
        ) { 
            rs -> while (rs.next()) {
                // Question: how I can convert rs to Node object correctly???
                nodes.add(Node.wrap(EntityID(rs.getLong("id"), Nodes), ResultRow.create(rs, Nodes.columns)))
            }
        }

because Kotlin does not seem to be able to handle microseconds timestamp assignment correctly (it seems to be rounded in milli-seconds precision), I have to use raw SQL to ignore sub-milli-seconds difference.

I can get ResultSet correctly, but need to convert ResultSet to target Entity to use the result in different part of code.

nodes.add(Node.wrap(EntityID(rs.getLong("id"), Nodes), ResultRow.create(rs, Nodes.columns)))

passes compile, but when I actually access property (eg. podName) of Node object, it raises following exception. note that if I use regular way to select Entity like nodes = Node.find{ Nodes.lastDeployed less Nodes.updateAt }.limit(max, offset).asSequence().toList(), the error has gone.

Exception in thread "main" java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
    at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)
Caused by: java.lang.IllegalStateException: No transaction in context.
    at org.jetbrains.exposed.sql.transactions.TransactionManager$Companion.current(TransactionApi.kt:98)
    at org.jetbrains.exposed.sql.Query.getTransaction(Query.kt:16)
    at org.jetbrains.exposed.sql.Query.iterator(Query.kt:214)
    at kotlin.collections.CollectionsKt___CollectionsKt.firstOrNull(_Collections.kt:231)
    at org.jetbrains.exposed.dao.Entity.getReadValues(Entity.kt:25)
    at org.jetbrains.exposed.dao.Entity.lookup(Entity.kt:107)
    at org.jetbrains.exposed.dao.Entity.getValue(Entity.kt:88)
    at com.mydomain.models.Node.getPodName(Node.kt)

Node object created by Node.wrap(EntityID(rs.getLong("id"), Nodes), ResultRow.create(rs, Nodes.columns)) seems to be in inconsistent state and treated as not fresh (I guess that is the reason Exposed try to access database again)...

anyone have an same experience (convert raw SQL result to list of Exposed Entity)?

regards,

kotlin-exposed kotlin
1 Answers

I had the same problem and solved it thanks to the hint of JCamacho. The key is to use wrapRow together with ResultRow.create to map plain sql to a Entity.

Here's the code I'm using now (given I have the IntEntity Task and the IntIdTable TaskEntity:

("SELECT ${TaskEntity.allFields} " +
        "FROM ${TaskEntity.tableName} " +
        "WHERE ..."
).execAndMap {
    Task.wrapRow(ResultRow.create(it, TaskEntity.fields))
}

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

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
}

And the following extension to get the select fields (you can also use *, but then you need to make sure the fields are defined in exactly the same order as in your database)

val IntIdTable.allFields: String get() {
    return fields.map { "$tableName.`${(it as Column<*>).name}`" }.joinToString(", ")
}
user_25498280
Related