Here's a walkthrough of the pattern I described yesterday. The first block is the file motivating and using the pattern. The second block is the implementations of the various ideas.
It's not the best possible solution or anything, but it is simple enough to understand, extend, and use.
My favorite part is that you can switch a few components to interfaces and then apply them to other non-JDBC environments either for testing or, if careful, for APIs that take SQL, like the Databricks API.
package com.christopherphiliphebert.app
import com.christopherphiliphebert.utils.*
import java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
fun main() {
val c: Connection = DriverManager.getConnection("jdbc:duckdb:")
c.createStatement().execute("CREATE TABLE test (a INTEGER, b VARCHAR)")
c.createStatement().execute("INSERT INTO test VALUES (1, 'one')")
c.createStatement().execute("INSERT INTO test VALUES (2, 'two')")
// This is a typical use of PreparedStatement
c.prepareStatement("SELECT * FROM test WHERE a = ?").use { s: PreparedStatement ->
s.setInt(1, 1)
s.executeQuery().apply {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}")
}
}
}
// The issue, is that if you conditionally assemble the query, it gets complicated.
val condition = true
// These conditionals may be determined in other functions and only assembled here.
val sql = "SELECT * FROM test" + if (condition) " WHERE a = ?" else " WHERE b = ?"
c.prepareStatement(sql).use { s: PreparedStatement ->
// And these conditionals need to correspond with the conditionals above,
// which may have been determined in another context with other information.
if (condition) {
s.setInt(1, 1)
} else {
s.setString(1, "one")
}
s.executeQuery().apply {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}")
}
}
}
// What we want is to associate the parameter setting with the SQL fragment it would correspond to.
// An incomplete version may be like:
val condition2 = true
val sql2Part0 = PreparedFragmentV0("SELECT * FROM test")
val sql2Part1 = if (condition2) {
PreparedFragmentV0(" WHERE a = ?") {
setInt(/* parameterIndex = */ 1, 1) // Requires us to know this is the first parameter
}
} else {
PreparedFragmentV0(" WHERE b = ?") {
setString(/* parameterIndex = */ 1, "one") // Requires us to know this is the first parameter
}
}
val sql2 = sql2Part0.sql + sql2Part1.sql
c.prepareStatement(sql2).use { s: PreparedStatement ->
sql2Part0.parameters(s)
sql2Part1.parameters(s)
s.executeQuery().apply {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}")
}
}
}
// But that requires knowing the order of the fragments and the parameters.
// If the number of fragments before the current one is conditional, we're toast.
// We must abstract away the absolute parameterIndex.
val condition3 = true
val sql3Part0 = PreparedFragmentV1("SELECT * FROM test WHERE ")
val sql3Part1 = if (condition3) {
PreparedFragmentV1(" a = ?") {
addInt(1)
}
} else {
PreparedFragmentV1(" TRUE ")
}
val sql3Part2 = if (condition2) {
PreparedFragmentV1(" AND a = ?") {
addInt(1)
}
} else {
PreparedFragmentV1(" AND b = ?") {
addString("one")
}
}
println("SQL 3")
c.executeV0(buildList {
add(sql3Part0)
add(sql3Part1)
add(sql3Part2)
}) {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}")
}
}
// We also want to more easily "add" fragments into one.
// Sometimes we may have nested conditional fragment logic,
// and flattening into a list may be challenging.
println("SQL 4")
c.executeV1(sql3Part0 + sql3Part1 + sql3Part2) {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}")
}/
}
// One thing I want to make better is lists.
// java.sql.PreparedStatement doesn't support list "?" parameters the way some named parameter libraries do.
// So it requires looping over the list twice: once to build the string, and once to add the parameters.
// The interpolated string loop is messy to read and write.
val someThings = listOf("one", "two")
val sql5 = PreparedFragmentV1("SELECT * FROM test WHERE b IN (${someThings.joinToString { "?" }})") {
someThings.forEach(::addString)
}
println("SQL 5")
c.executeV1(sql5) {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}")
}
}
// New approach: We add the special fragment constructor specialized in formatting lists.
// We do this rather than introduce some new SQL DSL entity e.g. `:?` or `:list` because we don't want to parse.
// There are innumerable SQL dialects and we cannot correctly navigate them all.
val someThings2 = listOf("one", "two")
val sql6 = PreparedFragmentV2("SELECT * FROM test WHERE b IN ") + PreparedFragmentV2(someThings2)
println("SQL 6")
c.executeV2(sql6) {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}")
}
}
// Now we can get pretty fancy!
val stuff = listOf(
"a" to listOf(1, 2),
"b" to listOf("one", "two"),
)
val sql7 = PreparedFragmentV2("SELECT *, ? AS constant FROM test WHERE ") {
addInt(42)
} + stuff.map { (column, values) ->
PreparedFragmentV2("$column IN ") + PreparedFragmentV2(values)
}.reduce { acc, fragment -> acc + PreparedFragmentV2(" AND ") + fragment }
println("SQL 7")
c.executeV2(sql7) {
while (next()) {
println("a = ${getInt("a")}, b = ${getString("b")}, constant = ${getInt("constant")}")
}
}
// I think that's the idea for now.
// Cleaning up the names and adding more utility functions will make it easier to read and write.
// You can add all the other setStuff() types supported by PreparedStatement.
}
package com.christopherphiliphebert.utils
import org.intellij.lang.annotations.Language
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
data class PreparedFragmentV0(
@Language("SQL") val sql: String,
val parameters: PreparedStatement.() -> Unit = {},
)
interface FragmentPreparerV0 {
fun addInt(value: Int)
fun addString(value: String)
}
data class PreparedFragmentV1(
@Language("SQL") val sql: String,
val parameters: FragmentPreparerV0.() -> Unit = {},
) {
operator fun plus(other: PreparedFragmentV1): PreparedFragmentV1 {
return PreparedFragmentV1(sql + other.sql) { parameters(); other.parameters(this) }
}
}
fun <T : Any> Connection.executeV0(fragments: List<PreparedFragmentV1>, block: ResultSet.() -> T): T =
prepareStatement(fragments.joinToString(separator = "") { it.sql }).use { s: PreparedStatement ->
val preparer = object : FragmentPreparerV0 {
var index = 0
override fun addInt(value: Int) {
s.setInt(++index, value)
}
override fun addString(value: String) {
s.setString(++index, value)
}
}
fragments.forEach { it.parameters(preparer) }
block(s.executeQuery())
}
fun <T : Any> Connection.executeV1(fragment: PreparedFragmentV1, block: ResultSet.() -> T): T =
prepareStatement(fragment.sql).use { s: PreparedStatement ->
val preparer = object : FragmentPreparerV0 {
var index = 0
override fun addInt(value: Int) {
s.setInt(++index, value)
}
override fun addString(value: String) {
s.setString(++index, value)
}
}
fragment.parameters(preparer)
block(s.executeQuery())
}
interface FragmentPreparerV2 {
fun addInt(value: Int)
fun addString(value: String)
}
class PreparedFragmentV2(
@Language("SQL")
val sql: String,
val parameters: FragmentPreparerV2.() -> Unit = {},
) {
constructor(list: List<*>) : this(
sql = list.joinToString(prefix = "(", postfix = ")") { "?" },
parameters = {
list.forEach {
when (it) {
is Int -> addInt(it)
is String -> addString(it)
else -> throw IllegalArgumentException("Unsupported type")
}
}
}
)
operator fun plus(other: PreparedFragmentV2): PreparedFragmentV2 =
PreparedFragmentV2(sql + other.sql) { parameters(); other.parameters(this) }
}
fun <T : Any> Connection.executeV2(fragment: PreparedFragmentV2, block: ResultSet.() -> T): T =
prepareStatement(fragment.sql).use { s: PreparedStatement ->
val preparer = object : FragmentPreparerV2 {
var index = 0
override fun addInt(value: Int) {
s.setInt(++index, value)
}
override fun addString(value: String) {
s.setString(++index, value)
}
}
fragment.parameters(preparer)
block(s.executeQuery())
}