How to execute IN() SQL queries with Spring's JDBCTemplate effectively?

sql

named-parameter-jdbc-template

row-mapper

data-source

byAnton Shumikhin·Feb 17, 2025

To execute an IN() clause with dynamic parameters using Spring, the NamedParameterJdbcTemplate is a reliable choice. See this succinct example:

// You're calling in the major player now, NamedParameterJdbcTemplate
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate);

// Query with a dynamite IN clause. Spoiler Alert: It's about to go "Boom!" 
String sql = "SELECT * FROM table WHERE column IN (:values)";

// A list of values, much like a list of favourite pizzas or your friends 
List<Integer> values = Arrays.asList(1, 2, 3); 

// Here's our MapSqlParameterSource, adding a bit of magic!
MapSqlParameterSource parameters = new MapSqlParameterSource("values", values);

// Now, we execute the SQL query and get the results. Just like ordering a pizza...
List<ResultType> result = template.query(sql, parameters, new BeanPropertyRowMapper<>(ResultType.class));

This approach conveniently expands the list for the query, keeping manual query building and SQL injection risks at bay.

More than just aesthetics: NamedParameterJdbcTemplate

NamedParameterJdbcTemplate brings about major enhancements in code maintainability. It's like hiring a cleaning service for your code – making it readable and efficient without the trouble of spraying disinfectant all by yourself!

Collections: Your new best friends

The Set and List collections come super handy for multiple values, without the need for string concatenation or manual iteration:

// These are cool lists. Not like laundry lists. Much cooler!
Set<String> ids = new HashSet<>(Arrays.asList("id1", "id2", "id3"));
MapSqlParameterSource parameters = new MapSqlParameterSource("ids", ids);

Your code gains seep cleanliness and eliminates duplicate values (if using Set). In other words, it's like decluttering the closet.

Ordered for all: Parameter Substitution

MapSqlParameterSource enables you to add parameters in a proper sequence, ensuring an orderly arrangement of parameters in your IN() clause:

// An organized tool chest in the midst of coding chaos.
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("id1", value1);
parameters.addValue("id2", value2); // More value. But wait, there's more...

Say goodbye to manual string construction, and say hello to structured parameter substitution.

Conversion made easy: Arrays -> List

Arrays.asList is your saviour when it comes to converting arrays to List easily. It's like turning water into wine:

// Quick change artist at work, ladies, and gentlemen.
String[] idArray = {"id1", "id2", "id3"};
List<String> idList = Arrays.asList(idArray);
MapSqlParameterSource parameters = new MapSqlParameterSource("ids", idList);

This elegant conversion method keeps the IN() clause dynamic and flexible for arrays of different sizes.

Ensuring DataSource is served right

An incorrect DataSource setup can cook up a storm of unexpected issues. Ensure your NamedParameterJdbcTemplate is prepared with the right DataSource:

// NamedParameterJdbcTemplate, now serving: Correct DataSource.
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

The right connection information in your NamedParameterJdbcTemplate results in smooth and effective database queries.

Putting RowMapper to good use

Custom RowMapper implementations, like MyRowMapper, gives you more power than an espresso shot when it comes to fine-grained control over how result sets map to objects:

// A RowMapper that stands out from the crowd. 
public class MyRowMapper implements RowMapper<ResultType> {
    @Override
    public ResultType mapRow(ResultSet rs, int rowNum) throws SQLException {
        ResultType result = new ResultType();
        result.setColumnValue(rs.getString("column"));
        // Additional processing - because perfection is made of details!
        return result;
    }
}