[[jdbc.query-methods]]
= Query Methods

This section offers some specific information about the implementation and use of Spring Data JDBC.

Most of the data access operations you usually trigger on a repository result in a query being run against the databases.
Defining such a query is a matter of declaring a method on the repository interface, as the following example shows:

.PersonRepository with query methods
[source,java]
----
interface PersonRepository extends PagingAndSortingRepository<Person, String> {

  List<Person> findByFirstname(String firstname);                                   <1>

  List<Person> findByFirstnameOrderByLastname(String firstname, Pageable pageable); <2>

  Slice<Person> findByLastname(String lastname, Pageable pageable);                 <3>

  Page<Person> findByLastname(String lastname, Pageable pageable);                  <4>

  Person findByFirstnameAndLastname(String firstname, String lastname);             <5>

  Person findFirstByLastname(String lastname);                                      <6>

  @Query("SELECT * FROM person WHERE lastname = :lastname")
  List<Person> findByLastname(String lastname);                                     <7>
  @Query("SELECT * FROM person WHERE lastname = :lastname")
  Stream<Person> streamByLastname(String lastname);                                     <8>

  @Query("SELECT * FROM person WHERE username = :#{ principal?.username }")
  Person findActiveUser();															<9>
}
----
<1> The method shows a query for all people with the given `firstname`.
The query is derived by parsing the method name for constraints that can be concatenated with `And` and `Or`.
Thus, the method name results in a query expression of `SELECT … FROM person WHERE firstname = :firstname`.
<2> Use `Pageable` to pass offset and sorting parameters to the database.
<3> Return a `Slice<Person>`.Selects `LIMIT+1` rows to determine whether there's more data to consume. `ResultSetExtractor` customization is not supported.
<4> Run a paginated query returning `Page<Person>`.Selects only data within the given page bounds and potentially a count query to determine the total count. `ResultSetExtractor` customization is not supported.
<5> Find a single entity for the given criteria.
It completes with `IncorrectResultSizeDataAccessException` on non-unique results.
<6> In contrast to <3>, the first entity is always emitted even if the query yields more result documents.
<7> The `findByLastname` method shows a query for all people with the given `lastname`.
<8> The `streamByLastname` method returns a `Stream`, which makes values possible as soon as they are returned from the database.
<9> You can use the Spring Expression Language to dynamically resolve parameters.
In the sample, Spring Security is used to resolve the username of the current user.

The following table shows the keywords that are supported for query methods:

[cols="1,2,3",options="header",subs="quotes"]
.Supported keywords for query methods
|===
| Keyword
| Sample
| Logical result

| `After`
| `findByBirthdateAfter(Date date)`
| `birthdate > date`

| `GreaterThan`
| `findByAgeGreaterThan(int age)`
| `age > age`

| `GreaterThanEqual`
| `findByAgeGreaterThanEqual(int age)`
| `age >= age`

| `Before`
| `findByBirthdateBefore(Date date)`
| `birthdate < date`

| `LessThan`
| `findByAgeLessThan(int age)`
| `age < age`

| `LessThanEqual`
| `findByAgeLessThanEqual(int age)`
| `age \<= age`

| `Between`
| `findByAgeBetween(int from, int to)`
| `age BETWEEN from AND to`

| `NotBetween`
| `findByAgeNotBetween(int from, int to)`
| `age NOT BETWEEN from AND to`

| `In`
| `findByAgeIn(Collection<Integer> ages)`
| `age IN (age1, age2, ageN)`

| `NotIn`
| `findByAgeNotIn(Collection ages)`
| `age NOT IN (age1, age2, ageN)`

| `IsNotNull`, `NotNull`
| `findByFirstnameNotNull()`
| `firstname IS NOT NULL`

| `IsNull`, `Null`
| `findByFirstnameNull()`
| `firstname IS NULL`

| `Like`, `StartingWith`, `EndingWith`
| `findByFirstnameLike(String name)`
| `firstname LIKE name`

| `NotLike`, `IsNotLike`
| `findByFirstnameNotLike(String name)`
| `firstname NOT LIKE name`

| `Containing` on String
| `findByFirstnameContaining(String name)`
| `firstname LIKE '%' + name + '%'`

| `NotContaining` on String
| `findByFirstnameNotContaining(String name)`
| `firstname NOT LIKE '%' + name + '%'`

| `(No keyword)`
| `findByFirstname(String name)`
| `firstname = name`

| `Not`
| `findByFirstnameNot(String name)`
| `firstname != name`

| `IsTrue`, `True`
| `findByActiveIsTrue()`
| `active IS TRUE`

| `IsFalse`,  `False`
| `findByActiveIsFalse()`
| `active IS FALSE`
|===

NOTE: Query derivation is limited to properties that can be used in a `WHERE` clause without using joins.

[[jdbc.query-methods.strategies]]
== Query Lookup Strategies

The JDBC module supports defining a query manually as a String in a `@Query` annotation or as named query in a property file.

Deriving a query from the name of the method is is currently limited to simple properties, that means properties present in the aggregate root directly.
Also, only select queries are supported by this approach.

[[jdbc.query-methods.at-query]]
== Using `@Query`

The following example shows how to use `@Query` to declare a query method:

.Declare a query method by using @Query
[source,java]
----
interface UserRepository extends CrudRepository<User, Long> {

  @Query("select firstName, lastName from User u where u.emailAddress = :email")
  User findByEmailAddress(@Param("email") String email);
}
----

For converting the query result into entities the same `RowMapper` is used by default as for the queries Spring Data JDBC generates itself.
The query you provide must match the format the `RowMapper` expects.
Columns for all properties that are used in the constructor of an entity must be provided.
Columns for properties that get set via setter, wither or field access are optional.
Properties that don't have a matching column in the result will not be set.
The query is used for populating the aggregate root, embedded entities and one-to-one relationships including arrays of primitive types which get stored and loaded as SQL-array-types.
Separate queries are generated for maps, lists, sets and arrays of entities.

NOTE: Spring fully supports Java 8’s parameter name discovery based on the `-parameters` compiler flag.
By using this flag in your build as an alternative to debug information, you can omit the `@Param` annotation for named parameters.

NOTE: Spring Data JDBC supports only named parameters.

[[jdbc.query-methods.named-query]]
== Named Queries

If no query is given in an annotation as described in the previous section Spring Data JDBC will try to locate a named query.
There are two ways how the name of the query can be determined.
The default is to take the _domain class_ of the query, i.e. the aggregate root of the repository, take its simple name and append the name of the method separated by a `.`.
Alternatively the `@Query` annotation has a `name` attribute which can be used to specify the name of a query to be looked up.

Named queries are expected to be provided in the property file `META-INF/jdbc-named-queries.properties` on the classpath.

The location of that file may be changed by setting a value to `@EnableJdbcRepositories.namedQueriesLocation`.

[[jdbc.query-methods.at-query.streaming-results]]
=== Streaming Results

When you specify Stream as the return type of a query method, Spring Data JDBC returns elements as soon as they become available.
When dealing with large amounts of data this is suitable for reducing latency and memory requirements.

The stream contains an open connection to the database.
To avoid memory leaks, that connection needs to be closed eventually, by closing the stream.
The recommended way to do that is a `try-with-resource clause`.
It also means that, once the connection to the database is closed, the stream cannot obtain further elements and likely throws an exception.

[[jdbc.query-methods.at-query.custom-rowmapper]]
=== Custom `RowMapper`

You can configure which `RowMapper` to use, either by using the `@Query(rowMapperClass = ....)` or by registering a `RowMapperMap` bean and registering a `RowMapper` per method return type.
The following example shows how to register `DefaultQueryMappingConfiguration`:

[source,java]
----
@Bean
QueryMappingConfiguration rowMappers() {
  return new DefaultQueryMappingConfiguration()
    .register(Person.class, new PersonRowMapper())
    .register(Address.class, new AddressRowMapper());
}
----

When determining which `RowMapper` to use for a method, the following steps are followed, based on the return type of the method:

. If the type is a simple type, no `RowMapper` is used.
+
Instead, the query is expected to return a single row with a single column, and a conversion to the return type is applied to that value.
. The entity classes in the `QueryMappingConfiguration` are iterated until one is found that is a superclass or interface of the return type in question.
The `RowMapper` registered for that class is used.
+
Iterating happens in the order of registration, so make sure to register more general types after specific ones.

If applicable, wrapper types such as collections or `Optional` are unwrapped.
Thus, a return type of `Optional<Person>` uses the `Person` type in the preceding process.

NOTE: Using a custom `RowMapper` through `QueryMappingConfiguration`, `@Query(rowMapperClass=…)`, or a custom `ResultSetExtractor` disables Entity Callbacks and Lifecycle Events as the result mapping can issue its own events/callbacks if needed.

[[jdbc.query-methods.at-query.modifying]]
=== Modifying Query

You can mark a query as being a modifying query by using the `@Modifying` on query method, as the following example shows:

[source,java]
----
@Modifying
@Query("UPDATE DUMMYENTITY SET name = :name WHERE id = :id")
boolean updateName(@Param("id") Long id, @Param("name") String name);
----

You can specify the following return types:

* `void`
* `int` (updated record count)
* `boolean`(whether a record was updated)

Modifying queries are executed directly against the database.
No events or callbacks get called.
Therefore also fields with auditing annotations do not get updated if they don't get updated in the annotated query.
