Using bind variable names in SQL with JOOQ

SqlJooqBindVariableOrganizer.kt

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

}