如何为NamedJdbcTemplate传入嵌套列表参数实现多条件IN查询

Got it, let's tackle this problem—you're trying to use a multi-column IN clause like (t.a, t.b) IN ((1,2), (3,4)) with Spring's NamedJdbcTemplate, and the usual parameter binding approaches aren't working. Here's how to fix this cleanly, without SQL injection risks:

The Right Way: Bind a List of Object Arrays (or Data Classes) #

Spring's NamedParameterJdbcTemplate (which NamedJdbcTemplate builds on) supports binding lists of multi-value elements for this exact scenario. Instead of a nested List<List<Int>>, use a List<Object[]> where each array represents one pair of values.

Step 1: Adjust Your SQL Query #

Keep the query clean with a named parameter for the pairs:

SELECT * FROM someTable t WHERE (t.a, t.b) IN (:pairs)

Step 2: Bind the Parameter in Kotlin #

Create a list of Object[] (or use Kotlin data classes for better readability) and pass it via MapSqlParameterSource:

Option 1: Using Object Arrays

val valuePairs = listOf(
    arrayOf(1, 2),
    arrayOf(3, 4)
)

val params = MapSqlParameterSource().addValue("pairs", valuePairs)
val results = namedJdbcTemplate.query(sql, params, yourRowMapper)

Option 2: Using Data Classes (More Kotlin-idiomatic)

Define a simple data class to hold your pairs, then pass a list of instances:

data class AB(val a: Int, val b: Int)

val valuePairs = listOf(
    AB(1, 2),
    AB(3, 4)
)

val params = MapSqlParameterSource().addValue("pairs", valuePairs)
val results = namedJdbcTemplate.query(sql, params, yourRowMapper)

Spring will automatically resolve each array/data class instance into the (?, ?) syntax needed for the multi-column IN clause.

Why Your Previous Approaches Failed #

• Nested List<List<Int>>: Spring doesn't automatically map nested lists to multi-column tuples. It expects each element in the list to be a single "record" (array or object) that maps to the tuple columns.
• Manual String Concatenation: When you pass a string like "(1,2),(3,4)", Spring treats it as a single literal value. This turns your query into WHERE (t.a, t.b) IN ('(1,2),(3,4)')—which is invalid, since you're comparing a tuple to a single string, not multiple tuples. Plus, this opens you up to SQL injection attacks, so it's never a good practice.

Notes #

• This works with Spring 5.x and later (which is standard for most modern projects).
• If you need to handle more than two columns, just extend the array/data class to include all required columns—Spring will adjust the (?, ?, ...) syntax automatically.

内容的提问来源于stack exchange，提问作者samanime