JdbcTemplate IN Clause with List in Spring Boot: The Real Way

If you've been writing Java for more than a week, you've probably needed to query a database with a dynamic list of values. The classic approach—building a query string by concatenating values—is a disaster waiting to happen. I've seen production outages caused by SQL injection through concatenated IN clauses, not to mention the subtle bugs when your list contains special characters or is empty.

Spring's JdbcTemplate provides a cleaner way, but the official documentation glosses over the pitfalls. In this guide, I'll show you the only pattern I trust in production: using NamedParameterJdbcTemplate with a SqlParameterSource. We'll cover the gotchas that will bite you in the real world, from empty lists to performance with thousands of parameters.

I'll also share a war story from a fintech app I worked on where a naive IN clause implementation brought down the payment processing pipeline. By the end, you'll know exactly how to handle IN clauses safely and efficiently.

Why String Concatenation Is a Bad Idea

Let's get this out of the way: building an IN clause by concatenating values is dangerous and fragile. I've seen code like this in production:

``java String ids = "1,2,3"; String sql = "SELECT * FROM users WHERE id IN (" + ids + ")"; jdbcTemplate.query(sql, ...); ``

This is a SQL injection waiting to happen. If ids comes from user input, an attacker can inject arbitrary SQL. Even if you think it's safe because the values are numbers, what about an empty list? You get IN (), which is invalid in most databases. Or what about a list with a million elements? You'll blow up the query size limit.

There's also the performance angle. When you concatenate, the database can't cache the query plan because each query is different. With parameterized queries, the database can reuse the plan, improving performance.

The fix: Use NamedParameterJdbcTemplate with a SqlParameterSource. This is the only production-safe approach.

// BAD: String concatenation
List<Integer> ids = Arrays.asList(1, 2, 3);
String sql = "SELECT * FROM users WHERE id IN (" +
    ids.stream().map(String::valueOf).collect(Collectors.joining(",")) + ")";
jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));

// GOOD: Named parameters
NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
String sql = "SELECT * FROM users WHERE id IN (:ids)";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("ids", ids);
List<User> users = namedJdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(User.class));

Using NamedParameterJdbcTemplate for IN Clauses

Spring's NamedParameterJdbcTemplate is the standard way to handle dynamic lists in SQL. It expands the list into the appropriate number of placeholders automatically.

Here's the pattern:

1. Create a NamedParameterJdbcTemplate instance (wrapping a regular JdbcTemplate).
2. Write your SQL with a named parameter, e.g., :ids.
3. Create a MapSqlParameterSource or BeanPropertySqlParameterSource and add your list as the value.
4. Execute the query.

Spring will generate the correct number of ? placeholders based on the list size. This works with both query() and update() methods.

Pro tip: For complex queries with multiple lists, you can use MapSqlParameterSource and add all parameters.

Edge case: empty list. If your list is empty, the generated SQL will be IN () which is invalid. You must guard against this:

``java if (ids.isEmpty()) { return Collections.emptyList(); } ``

Or use a conditional WHERE clause that skips the IN entirely.

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import java.util.List;

public List<User> findUsersByIds(List<Long> ids) {
    if (ids == null || ids.isEmpty()) {
        return List.of();
    }
    NamedParameterJdbcTemplate namedJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
    String sql = "SELECT * FROM users WHERE id IN (:ids)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("ids", ids);
    return namedJdbc.query(sql, params, new BeanPropertyRowMapper<>(User.class));
}

Handling Large Lists: Batching and Temporary Tables

When your list size exceeds the database's limit (e.g., 1000 for Oracle, 2100 for SQL Server parameter count), you need a different strategy. Here are two production-proven approaches:

1. Batch the list into chunks. Split the list into sublists of, say, 1000 elements, and execute multiple queries, then combine the results. This is simple but may be slower.

2. Use a temporary table. Insert the IDs into a temporary table, then join with it. This is more efficient for very large lists and avoids query size limits.

I prefer the temporary table approach for lists over 10,000 elements. It also allows the database to optimize the join.

Performance tip: For lists under 1000, the chunking approach is fine. For larger, use temporary tables.

Important: When using temporary tables, ensure you clean up after yourself (drop the table or use transaction-scoped temporary tables).

// Chunking approach
public List<User> findUsersByIds(List<Long> ids) {
    if (ids.isEmpty()) return List.of();
    List<User> allUsers = new ArrayList<>();
    List<List<Long>> partitions = Lists.partition(ids, 1000); // Guava
    NamedParameterJdbcTemplate namedJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
    String sql = "SELECT * FROM users WHERE id IN (:ids)";
    for (List<Long> chunk : partitions) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("ids", chunk);
        allUsers.addAll(namedJdbc.query(sql, params, new BeanPropertyRowMapper<>(User.class)));
    }
    return allUsers;
}

What the Official Docs Won't Tell You

The Spring documentation shows you how to use NamedParameterJdbcTemplate, but it doesn't warn you about these real-world pitfalls:

1. Empty list handling: The docs assume you'll never pass an empty list. But in production, you will. The generated SQL IN () is invalid in most databases. Always guard.

2. Null values in the list: If your list contains null, the generated SQL will have IN (?, ?, null) which may not match any rows. Worse, some databases treat NULL IN (1,2,3) as unknown, not false. Filter out nulls before binding.

3. Oracle's 1000-element limit: This is a hard limit. The docs don't mention it. You must chunk or use temporary tables.

4. SQL Server parameter count limit: SQL Server has a limit of 2100 parameters per query. If your list has 2100 elements, each becomes a parameter, and you'll hit the limit. Chunk at 2000 to be safe.

5. Type mismatch: If your list contains integers but the column is a different type (e.g., VARCHAR), you might get implicit conversion issues. Ensure types match.

6. Batch update with IN clause: If you're using batchUpdate with a list of IN clauses, be aware that each batch item creates a separate query. This can be slow. Consider using a single query with multiple parameters if possible.

// Proper empty list guard
public List<User> findUsersByIds(List<Long> ids) {
    if (ids == null || ids.isEmpty()) {
        return Collections.emptyList();
    }
    // Filter out nulls
    List<Long> nonNullIds = ids.stream()
        .filter(Objects::nonNull)
        .collect(Collectors.toList());
    if (nonNullIds.isEmpty()) {
        return Collections.emptyList();
    }
    // Proceed with query
    NamedParameterJdbcTemplate namedJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
    String sql = "SELECT * FROM users WHERE id IN (:ids)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("ids", nonNullIds);
    return namedJdbc.query(sql, params, new BeanPropertyRowMapper<>(User.class));
}

Alternative Approaches: IN Clause with JPA and Hibernate

If you're using JPA or Hibernate, you might be tempted to use JPQL or Criteria API. Hibernate supports IN clauses with collections, but it has its own quirks.

JPQL: SELECT u FROM User u WHERE u.id IN :ids works with a List parameter. However, Hibernate also has a limit (often 1000 or 500 depending on the dialect).

Criteria API: cb.in(u.get("id")).value(ids) works similarly.

The problem: Hibernate's IN clause handling may generate multiple SQL statements or use a temporary table internally, which can be less efficient than JdbcTemplate for large lists.

My recommendation: For simple IN clauses, JPA is fine. For complex queries or performance-critical paths, use JdbcTemplate directly. You have more control.

Spring Data JPA: If you're using Spring Data JPA, you can use @Query with a List parameter. But again, beware of limits.

// JPQL example
@Query("SELECT u FROM User u WHERE u.id IN :ids")
List<User> findUsersByIds(@Param("ids") List<Long> ids);

// Criteria API example
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);
cq.select(root).where(root.get("id").in(ids));
List<User> users = entityManager.createQuery(cq).getResultList();

Performance Considerations and Best Practices

When using IN clauses with large lists, performance can degrade. Here are some tips from the trenches:

1. Index the column: Ensure the column used in the IN clause is indexed. Otherwise, the query will do a full table scan.

2. Use bind variables: Named parameters are bind variables, which allow the database to cache the query plan.

3. Avoid distinct in subqueries: If you're using a subquery to generate the list, avoid DISTINCT if not needed.

4. Consider using EXISTS instead: For very large lists, an EXISTS clause with a join to a temporary table can be faster.

5. Monitor the database: Use slow query logs to identify problematic IN clauses.

6. Test with realistic data sizes: Don't just test with 10 elements. Test with 1000, 10000, etc.

7. Use connection pooling: Ensure your connection pool is configured correctly to handle the load.

Benchmark: I've seen a query with 5000 IDs take 5 seconds with IN clause, but 0.5 seconds with a temporary table join. Always measure.

// Example of using temporary table for large lists
// Assume we have a global temporary table temp_ids (id NUMBER)
public List<User> findUsersByIdsTempTable(List<Long> ids) {
    // Insert IDs into temp table
    String insertSql = "INSERT INTO temp_ids (id) VALUES (?)";
    jdbcTemplate.batchUpdate(insertSql, ids, ids.size(), (ps, id) -> ps.setLong(1, id));
    // Query using join
    String sql = "SELECT u.* FROM users u JOIN temp_ids t ON u.id = t.id";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}