How to Run Raw SQL with Kotlin's Exposed Library

Spring Boot Kotlin Exposed Stored Procedure

Of course. You can use a transaction and run your own sql statement in it (calling the procedure) and create a mapping function for that object, where you map the fields of the result set to the ones of the object.

e: you can have a look in here, how to call simple sql statement and map the resultset

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

Write query with HAVING clause in Kotlin with Exposed

sorry for the delay! You are the man! Saved my development!

I made some adaptations to your suggestion.

My solution was as follows:

fun <T : Any> wrapAsExpressionWithColumnType(query: Query, columnType: IColumnType) =
    object : ExpressionWithColumnType<T?>() {
        private val expression = wrapAsExpression<T>(query)
        override fun toSQL(queryBuilder: QueryBuilder) = expression.toSQL(queryBuilder)
        override val columnType: IColumnType = columnType
    }

And the final code for the query:

transaction {
    val contestacaoEventTable = ContestacaoEventTable.alias("det")
    val pedidoContestacaoTable = PedidoContestacaoTable.alias("tdt")

    val contestacaoEventTransacaoId = contestacaoEventTable[ContestacaoEventTable.contestacaoTransacaoId]
    val contestacaoEventCreatedAt = contestacaoEventTable[ContestacaoEventTable.createdAt]
    val contestacaoEventStatus = contestacaoEventTable[ContestacaoEventTable.status]

    val pedidoContestacaoId = pedidoContestacaoTable[PedidoContestacaoTable.id]
    val pedidoContestacaoTransacaoId = pedidoContestacaoTable[PedidoContestacaoTable.transacaoId]

    val subQuery = contestacaoEventTable
        .slice(contestacaoEventTable[ContestacaoEventTable.status])
        .selectAll()
        .andWhere { contestacaoEventTransacaoId eq pedidoContestacaoId }
        .orderBy(contestacaoEventCreatedAt to SortOrder.DESC)
        .limit(1)
        .alias("subQuery")
    PedidoTable
        .innerJoin(pedidoContestacaoTable, { PedidoTable.id }, { pedidoContestacaoTransacaoId })
        .slice(pedidoContestacaoId)
        .selectAll()
        .groupBy(pedidoContestacaoId)
        .andWhere { PedidoTable.number eq '1234' }
        .having {
            wrapAsExpressionWithColumnType<String>(subQuery.query, contestacaoEventStatus.columnType)
                .inList(listOf(PedidoStatus.CAPTURED.name, PedidoStatus.EXPIRED.name))
        }.toList().size
}

Thanks a lot for the help . Hope I can help other people.

How to call functions/procedures from Oracle Database using Kotlin Exposed?

You always can execute any "raw" sqls with Transaction.exec function (faq), but also it's possible to define your own functions to use it later in Exposed DSL (wiki)

Kotlin exposed - Entity for table with no identity column

Exposed DAO requires some identity to fetch and store entities from a table. It doesn't require to be a primary or auto-increment column but in that case, you have to ensure that the values in that column are unique.

For example, you may map Entity to a Table with String id:

object FooTable : IdTable<String>() {
    val myIdColumn = varchar("my_id_column", 50).uniqueIndex()
    override val id: Column<EntityID<String>> = myIdColumn.entityId()
}

class FooEntity(id: String) : Entity<String>(id) {
    companion object : EntityClass<String, FooEntity>(FooTable)
}

---

Related Topics