[SPRING] How to pass a list of tuples to a IN clause using NamedParameterJdbcTemplate

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!