skip to content

How do parameter binding and @Modifying work for @Query methods in Spring Data JDBC, including collection parameters and return types?

level: middleimportance: should knowfreq 45%

answer

  1. :name + @Param, or -parameters to drop @Param
  2. NamedParameterJdbcTemplate = safe bind params
  3. Collection -> IN (:ids) auto-expands; empty = invalid SQL
  4. @Modifying returns void/int/long/boolean
  5. single vs List vs scalar return types

basics

~10 s

Bind arguments with named parameters :name plus @Param. A collection bound to an IN (:ids) clause expands to multiple placeholders. Write queries need @Modifying and return void, int/long (rows affected), or boolean.

solid answer

~40 s

Spring Data JDBC @Query methods use named parameters :name in the SQL, each matched to a method argument annotated @Param("name") (the annotation's value can be omitted when the code is compiled with -parameters). Binding goes through NamedParameterJdbcTemplate, so values are true JDBC bind parameters and are injection-safe; a Collection argument bound to an IN (:ids) clause is automatically expanded into the right number of placeholders. For reads, the return type can be a single entity, Optional, List, Stream, or a scalar such as long for a COUNT. For writes (INSERT/UPDATE/DELETE) you must add @Modifying; those methods return void, int or long (number of affected rows), or boolean (whether any row was affected). Modifying queries should run inside a transaction. Never concatenate user input into the SQL string.

code

java · 12 lines
java
public interface CustomerRepository extends CrudRepository<Customer, Long> {

    @Query("SELECT * FROM customer WHERE id IN (:ids)")
    List<Customer> findByIds(@Param("ids") Collection<Long> ids);

    @Query("SELECT count(*) FROM customer WHERE active = true")
    long countActive();

    @Modifying
    @Query("UPDATE customer SET active = :active WHERE id = :id")
    boolean setActive(@Param("id") Long id, @Param("active") boolean active);
}

go deeper

for a junior

Bind with :name and @Param; add @Modifying for writes.

for a middle

Know collection IN-expansion, valid modifying return types, and read cardinality rules.

for a senior

Discuss injection safety via bind parameters, empty-IN pitfalls, and the absence of a persistence context vs JPA.

for a principal

Reason about transactional boundaries for bulk writes and the maintainability of hand SQL and DTO projections at scale.

## Named-parameter binding Spring Data JDBC binds `@Query` arguments by **name**. In the SQL you write `:paramName`; on the method you provide `@Param("paramName") SomeType arg`. If you compile with the `-parameters` javac flag (Spring Boot's Gradle/Maven plugins enable this), the `@Param` value can be omitted because the parameter name is retained in bytecode. Binding is performed by `NamedParameterJdbcTemplate`, which sends values as **prepared-statement bind parameters** — this is what makes them safe against SQL injection. Do **not** string-concatenate values into the query. ```java @Query("SELECT * FROM customer WHERE last_name = :lastName AND active = :active") List<Customer> find(@Param("lastName") String lastName, @Param("active") boolean active); ``` ## Collections and IN clauses When a parameter is a `Collection` (e.g. `List<Long> ids`) and is used in an `IN (:ids)` clause, `NamedParameterJdbcTemplate` **expands** it into the correct number of `?` placeholders at execution time: ```java @Query("SELECT * FROM customer WHERE id IN (:ids)") List<Customer> findByIds(@Param("ids") Collection<Long> ids); ``` An **empty** collection produces invalid SQL like `IN ()` on many databases — guard against empty input in your service code. ## Read return types A SELECT method may return: - a **single** entity or `Optional<Entity>` (0/1 rows; more than one row throws `IncorrectResultSizeDataAccessException`), - a **`List`/`Set`/`Stream`** of entities, - a **scalar** (e.g. `long count`, `boolean`, `String`), and - a **DTO/projection** when paired with a custom `RowMapper`. ## Modifying queries A `@Query` that changes data must be annotated `@Modifying` (`org.springframework.data.jdbc.repository.query.Modifying`, or the shared Spring Data `@Modifying`). Allowed return types: - `void`, - `int` / `long` — the **number of affected rows**, - `boolean` — whether **at least one** row was affected. ```java @Modifying @Query("DELETE FROM customer WHERE active = false") int purgeInactive(); ``` Unlike JPA, Spring Data JDBC has no persistence context to flush or clear, so there is no stale-context concern after a bulk update — but you still want these running inside a transaction (`@Transactional`) so they commit atomically with surrounding work. ## Gotchas - **Forgetting `@Modifying`** on a write causes the method to be handled as a query — error or no-op. - **Empty `IN` collections** yield invalid SQL; filter them out first. - **`-parameters`**: without it (and without `@Param` names) binding fails at startup with a parameter-name error. - **Wrong cardinality**: returning a single entity from a query that yields multiple rows throws at runtime. - **SpEL**: Spring Data supports `:#{...}` SpEL expressions in queries; still bind values, never concatenate.

  • What happens if you pass an empty collection to a method using IN (:ids)?
    NamedParameterJdbcTemplate expands to IN () which is invalid SQL on most databases, causing a runtime error. Guard against empty collections in the calling code (return early with an empty result).
  • What return types are valid on a @Modifying @Query method?
    void, int or long (the affected-row count), or boolean (whether any row was affected).

saying these in an interview costs you the question

  • Concatenating user input into the SQL instead of :named binding
  • Expecting IN (:ids) with an empty list to work
  • Omitting @Modifying on an UPDATE/DELETE query
  • Thinking you must flush/clear a persistence context after a bulk update (there is none in Spring Data JDBC)

context

resolving links…