JDBC @Query & Templates

## Background **Spring Data JDBC** is a persistence module that maps *aggregates* (an aggregate root plus the entities it owns) to relational tables using a lightweight mapping layer, deliberately *without* JPA, Hibernate, lazy loading, a persistence context, or dirty checking. Because there is no JPA behind it, there is also **no JPQL** (Java Persistence Query Language, the entity-field-based query language JPA compiles into SQL). ## What `@Query` expects The annotation is `org.springframework.data.jdbc.repository.query.Query`. Its `value` attribute is a **plain SQL string** sent to the database essentially as written. You reference **table names and column names**, not Java field names. Example: ```java @Query("SELECT * FROM customer WHERE last_name = :lastName") List<Customer> findByLastName(@Param("lastName") String lastName); ``` Contrast with Spring Data JPA, where `@Query("select c from Customer c where c.lastName = :lastName")` is **JPQL** referencing the *entity* `Customer` and its *field* `lastName`; JPA then translates it to SQL. In JPA you opt into raw SQL with `@Query(value = "...", nativeQuery = true)`. In Spring Data JDBC there is no such flag because **everything is native SQL already**. ## When you need `@Query` Spring Data JDBC still supports **derived query methods** (`findByLastName(String)`), where the method name is parsed and SQL is generated for you. You reach for `@Query` when the derived approach cannot express what you need: joins, aggregations, GROUP BY, window functions, CTEs, vendor-specific syntax, or hand-tuned SQL. ## Parameter binding - **Named parameters** `:name` are the norm; bind each with a `@Param("name")` method argument (the `@Param` name may be omitted if you compile with `-parameters`). - Positional `?` placeholders are generally avoided; named binding is idiomatic and safe. - Binding is done through `NamedParameterJdbcTemplate` under the hood, so values are passed as JDBC bind parameters, which prevents SQL injection. **Never** string-concatenate user input into the SQL. ## Modifying queries A `@Query` that performs INSERT/UPDATE/DELETE must also be annotated `@Modifying` (`org.springframework.data.jdbc.repository.query.Modifying` / the Spring Data `@Modifying`). The method should return `void`, `int`/`long` (rows affected), or `boolean` (any row affected). ```java @Modifying @Query("UPDATE customer SET active = false WHERE last_login < :cutoff") int deactivateStaleAccounts(@Param("cutoff") LocalDate cutoff); ``` ## Result mapping For SELECTs, the returned `ResultSet` is mapped back to the return type. By default Spring Data JDBC uses its own entity mapper (matching columns to the aggregate's properties). You can override mapping with a custom `RowMapper` via `@Query(rowMapperClass = ...)` or `rowMapperRef = ...`. Return types can be a single entity, `Optional`, `List`, `Stream`, a scalar (e.g. `long` for a COUNT), or a DTO/projection when you supply a RowMapper. ## Gotchas - **No JPQL / no HQL**: writing `SELECT c FROM Customer c` will fail, it is not SQL. - **Column vs field names**: use `last_name` (the column), not `lastName`, unless your naming strategy maps them. - **Portability**: SQL is dialect-bound; moving from H2 to Postgres can break vendor-specific queries. - **`@Modifying` required** for writes; forgetting it causes the query to be treated as a read and fail or behave unexpectedly. - **No automatic loading of referenced entities** for a custom `@Query`: if you hand-write SQL, you are responsible for what columns come back; the default aggregate mapping only fully reconstructs the aggregate when the SELECT returns the expected columns.