I’d like to share something I recently came across that I think is little known (or at least I didn’t find it in the documentation 😅)
I was working on a jdbc-related @Component
and needed a IN
clause with multiple tuples. A IN
with multiple tuples is something like this:
SELECT * FROM MY_TABLE
WHERE (COL_1, COL_2, COL_3)
IN (('AAA', 111, 'X'), ('BBB', 222, 'Y'))
While I was coding I had a doubt: is the NamedParameterJdbcTemplate
able to handle it? Fortunately, yes, it does! How? The answer is more trivial than expected!
NamedParameterJdbcTemplate
expects a tuple to be provided as Object[]
so if you have multiple tuples you simply need a List<Object[]>
. You cannot pass the list directly to the NamedParameterJdbcTemplate
but you need to add it to a SqlParameterSource
. Here is an example:
@Autowired
NamedParameterJdbcTemplate jdbcTemplate;
...
var tuples = List.of( // Java 11
new Object[] {"AAA", 111, 'X'},
new Object[] {"BBB", 222, 'Y'}
);
var sql = "SELECT * FROM MY_TABLE "
+ "WHERE (COL_1, COL_2, COL_3) "
+ "IN (:values)"; // <-- the placeholder
var list = jdbcTemplate.queryForList(
sql,
new MapSqlParameterSource()
// bind the list to the placeholder
.addValue("values", tuples)
);
...
That’s all!