Spring NamedParameterJdbcTemplate: expand an ID list without dropping tenant scope

Keep the ownership predicate in SQL

The fixture asks for three receipt IDs, but one belongs to another tenant. The query binds both tenantId and receiptIds; the result contains only the two rows owned by TENANT-A. Filtering rows after a broader query would allow unauthorized records to leave the database boundary and can waste I/O.

Collection expansion is for values, not arbitrary SQL identifiers. Never splice an untrusted comma-separated string into the SQL grammar. Tenant predicates explains why the ownership condition must appear in the database statement, and prepared values covers the lower-level boundary.

Cap the list before building SQL

Each ID becomes a bound value. A huge list increases SQL size, parameter count and planning work; databases also differ in parameter limits. Define a maximum batch size and decide what an empty list means before executing, since an empty IN clause has no useful portable SQL shape.

Checked code

var parameters = new MapSqlParameterSource()
        .addValue("tenantId", "TENANT-A")
        .addValue("receiptIds", List.of(47L, 82L, 91L));
var matches = named.queryForList(
        "select receipt_id from receipt_route where tenant_id = :tenantId " +
        "and receipt_id in (:receiptIds) order by receipt_id",
        parameters, Long.class);

Verification boundary

The Maven source kit passes JdbcParametersAndKeysContractTest.namedCollectionParameterKeepsTenantPredicateInTheQuery on its local Spring Boot 4 and Java 21 fixture.

Cost and ownership

The fixture scans a tiny H2 table. Real cost depends on tenant cardinality, ID count, indexes and the database planner. The query returns O(k) IDs for k matches and holds the supplied list and result in memory; benchmark the target database before choosing a batch cap.

Common Mistakes

• Do not remove tenant_id from the SQL and filter the returned rows in Java.
• Do not interpolate IDs or a tenant string directly into SQL.
• Do not pass an unbounded or empty ID collection without a defined policy.

Read next

Spring JdbcTemplate: bound values and visible database constraints, Spring JdbcTemplate tenant predicates: put ownership in the SQL query, Java JDBC PreparedStatement: values stay outside SQL grammar, Spring Boot JDBC pool capacity: size connections across replicas.