Mapping named parameters to indices for PreparedStatement

Question

I needed something to map named SQL parameters into indices for JDBC queries so I wrote the ParameterMap class that takes an SQL query, searchers for parameters prefixed with a colon : and while replacing each instance it counts their indices adding them to the map as a list associated with each name. There is also an extension function forEach that passes one index at a time to the lambda where the actual parameters are set.

---

See also the follow-up.

---

class ParameterMap(sql: String) {
    private val parameterRegex = Regex("(:([a-z0-9_]+))", RegexOption.IGNORE_CASE)
    private val map = mutableMapOf<String, List<Int>>()
    val sqlWithQuestionMarks: String

    init {
        var index = 1 // <-- JDBC starts counting with 1
        sqlWithQuestionMarks = parameterRegex.replace(sql) { match ->
            val key = match.groups[2]!!.value.lowercase()
            map.computeIfPresent(key) { _, indexes -> indexes.plus(index++) }
            map.computeIfAbsent(key) { mutableListOf(index++) }
            return@replace "?"
        }
    }

    operator fun get(name: String) = map.getValue(name.lowercase())
    override fun toString() = sqlWithQuestionMarks

}

fun ParameterMap.forEach(name: String, block: (Int) -> Unit) {
    for (index in this[name]) block(index)
}

Example:

select *
from person
where
    (nullif(:first_name, '') is null or first_name = :first_name) and
    (nullif(:last_name, '') is null or last_name = :last_name)
limit 100;

val parameterMap = ParameterMap(File("sql/select.sql").readText())
val selectStatement = sqliteConnection.prepareStatement(parameterMap.sqlWithQuestionMarks)
parameterMap.forEach("first_name") {
    selectStatement.setString(it, "John")
}
parameterMap.forEach("last_name") {
    selectStatement.setString(it, "Doe")
}

---

What do you think of this helper? Can it be made more Kotlin-ish?

Answer 1

Name of the class doesn't indicate its purpose.

The class is called "ParameterMap" but its use-case (filling in values in a query) doesn't need to know about it having a map somewhere inside - it's an implementation detail.

Overriding toString is misleading for a similar reason: if I want to print a "ParameterMap" I expect to see some sort of a map, not a query.

---

But what really is the use-case here?

We have an sql statement with placeholders that need to be filled with actual values. Let's write down the use-case in a concise manner:

File("sql/select.sql").readText().fill(
    "first_name" to "John",
    "last_name" to "Doe",
)

So we really only need a function with an interface like this:

String.fill(vararg args: Pair<String, String>): PreparedStatement

There is no need to expose sqlWithQuestionMarks and the get operator, since they are irrelevant to the end-user, and therefore no need for a class either.

Now that we look at the problem this way, we can find a one-line solution:

String.executeWith(connection: Connection, vararg args: Pair<String, String>): ResultSet =
    connection.createStatement().executeQuery(this.also { args.forEach { this.replace(":${it.first}", it.second) } })

---

Nitpicks:

forEach doesn't need to be an extension function since it's defined in the same file as the class it's extending.

indexes.plus(index++) uses an operator: indexes + index++

Plural of "index" is "indices".

val key = match.groups[2]!!.value.lowercase() - this line could use a comment to explain what .groups[2]!! gives us.

Answer 2

commenting

        var index = 1 // <-- JDBC starts counting with 1

Thank you for the one-origin reminder, that's a helpful comment.

            val key = match.groups[2]!!.value.lowercase()

Hmmm, today I learned about kotlin's !! double bang type conversion idiom, cool.

EDIT

@Tenfour04's comment advises that in production code the !! anti-pattern is frowned upon.

    ... parameterRegex = Regex("(:([a-z0-9_]+))", ...)

I found that expression needlessly complex. Why are we capturing the : colon? Wouldn't ":([a-z0-9_]+)" suffice? Please add a comment explaining why two capturing groups is useful here.

BTW this regex isn't exactly bullet proof, but I feel it is Good Enough. For example WHERE text LIKE "foo:bar%" would also match, whatever.

---

typical call

parameterMap.forEach("first_name") {
    selectStatement.setString(it, "John")
}
parameterMap.forEach("last_name") {
    selectStatement.setString(it, "Doe")
}

Thank you for offering an instructive example. The first two lines look good. But these two stanzas feel clunky.

If that's the functionality that callers will usually want, maybe delete or make private the forEach function, and instead offer a compact "set these names using these values" method.

---

This codebase achieves its design goals.

I would be willing to delegate or accept maintenance tasks on it.

Answer 3

This:

map.computeIfPresent(key) { _, indexes -> indexes.plus(index++) }
map.computeIfAbsent(key) { mutableListOf(index++) }

can be simplified to:

map[key] = map.getOrElse(key, ::emptyList) + index++

The "compute" functions come from the Java standard library and are not inlined, so they have to allocate the lambdas. If you want to make your code "more Kotlinish", you probably want to avoid JVM-exclusive functions like these.

I also noticed you are inconsistent about whether you require the caller to ensure the key has only lowercase letters. get() takes care of it, but forEach will silently fail.