How to use named parameter in plain sql with jooq

Question

I'm using JOOQ with plain/raw SQL, so that means i'm not using any code generation or the fluid DSL thingy.

The following code works:

Connection connection = ...;
DSLContext context = DSL.using(connection, ...);
String sql = "select * from mytable t where (t.id = ?)"; 
String id = ...; //
Result<Record> result = context.fetch(sql, id);

Now let's say i have a query with multiple parameters like this:

String sql = "select * from mytable t where (t.id = ?) " + 
             "and (t.is_active = ?) and (t.total > ?)"; 

How do i use a named parameter with these types of queries? I'm thinking something like :

String sql = "select * from mytable t where (t.id = :id) " + 
             "and (t.is_active = :is_active) and (t.total > :total)"; 

ResultQuery<Record> rq = context.resultQuery(sql);
rq.getParam("id").setValue(...); 
rq.getParam("is_active").setValue(...);
rq.getParam("total").setValue(...);
Result<Record> result = rq.fetch();

But the above code doesn't work (for obvious reasons). Thanks in advance.

Answer 1

jOOQ currently doesn't support executing SQL with named parameters. You can use jOOQ to render named parameters if you're executing the query with another API, such as Spring JDBC. For more information, consider the manual:

http://www.jooq.org/doc/latest/manual/sql-building/bind-values/named-parameters

But the plain SQL templating API allows for re-using templates, e.g.

String sql = "select * "
           + "from mytable t "
           + "where t.id = {0} or (t.id != {0} and t.name = {1})";
ResultQuery<Record> q = ctx.resultQuery(sql, val(1), val("A"));

This way, you can at least re-use values several times.

Answer 2

Because Lukas said that this feature is not available, I thought I'll code a 'good enough' solution.

The idea is that the variable name like :name has to be replaced with the {0} at all places and the rest is done by JOOQ. I thought this is the easiest way of doing it. (Replacing variables with their proper form, like handling data types is definitely a lot of work.)

I merited some ideas from this other StackOverflow answer and then created this gist in Kotlin (it would have been too long in Java otherwise).

The current gist looks like this now:

import org.jooq.DSLContext
import org.jooq.Record
import org.jooq.ResultQuery
import org.jooq.impl.DSL

object SqlJooqBindVariableOrganizer {

    data class Processed(
        val statement: String,
        val originalStatement: String,
        val variables: List<Pair<String, Any>>,
    ) {

        fun toResultQuery(context: DSLContext): ResultQuery<Record> {
            return context.resultQuery(
                statement,
                *variables.map { DSL.`val`(it.second) }.toTypedArray(),
            )
        }

    }

    private fun extractBindVariableLocations(
        statement: String,
    ): Map<String, List<IntRange>> {
        // https://stackoverflow.com/a/20644736/4420543
        // https://gist.github.com/ruseel/e10bd3fee3c2b165044317f5378c7446
        // not sure about this regex, I haven't used colon inside string to test it out
        return Regex("(?<!')(:[\\w]*)(?!')")
            .findAll(statement)
            .map { result ->
                val variableName = result.value.substringAfter(":")
                val range = result.range
                variableName to range
            }
            .groupBy(
                { it.first },
                { it.second }
            )
    }

    fun createStatement(
        statement: String,
        vararg variables: Pair<String, Any>,
    ): Processed {
        return createStatement(statement, variables.toList())
    }

    fun createStatement(
        statement: String,
        variables: List<Pair<String, Any>>,
    ): Processed {
        val locations = extractBindVariableLocations(statement)

        val notProvidedKeys = locations.keys.subtract(variables.map { it.first })
        if (notProvidedKeys.isNotEmpty()) {
            throw RuntimeException("Some variables are not provided:\n"
                    + notProvidedKeys.joinToString()
            )
        }

        val relevantVariables = variables
            // there may be more variables provided, so filter this
            .filter { it.first in locations.keys }

        // these locations should have the same order as the variables
        // so it is important to know the proper order of the indices
        val variableNameToIndex = relevantVariables
            .mapIndexed { index, variable -> variable.first to index }
            .associateBy({ it.first }, { it.second })


        val variableNameReplacements = locations
            .flatMap { (variableName, ranges) ->
                ranges.map { range -> variableName to range }
            }
            // the replacements have to be done in a reversed order,
            // as the replaced string is not equal length
            .sortedByDescending { it.second.first }

        // replace :name with {0}
        val processedStatement = variableNameReplacements
            .fold(statement) { statementSoFar, (variableName, range) ->
                // index has to exist, we just checked it
                val index = variableNameToIndex[variableName]!!

                statementSoFar.replaceRange(range, "{$index}")
            }

        return Processed(
            statement = processedStatement,
            originalStatement = statement,
            variables = relevantVariables,
        )
    }

}