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

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:

Table 1. 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`

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

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.

Using `@Query`

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

Declare a query method by using @Query

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.

Properties one-to-one relationships must have there name prefixed by the name of the relationship plus _. For example if the User from the example above has an address with the property city the column for that city must be labeled address_city.

Note that String-based queries do not support pagination nor accept Sort, PageRequest, and Limit as a query parameter as for these queries the query would be required to be rewritten. If you want to apply limiting, please express this intent using SQL and bind the appropriate parameters to the query yourself.

Queries may contain SpEL expressions. There are two variants that are evaluated differently.

In the first variant a SpEL expression is prefixed with : and used like a bind variable. Such a SpEL expression will get replaced with a bind variable and the variable gets bound to the result of the SpEL expression.

Use a SpEL in a query

@Query("SELECT * FROM person WHERE id = :#{#person.id}")
Person findWithSpEL(PersonRef person);

This can be used to access members of a parameter, as demonstrated in the example above. For more involved use cases an EvaluationContextExtension can be made available in the application context, which in turn can make any object available in to the SpEL.

The other variant can be used anywhere in the query and the result of evaluating the query will replace the expression in the query string.

Use a SpEL in a query

@Query("SELECT * FROM #{tableName} WHERE id = :id")
Person findWithSpEL(PersonRef person);

It is evaluated once before the first execution and uses a StandardEvaluationContext with the two variables tableName and qualifiedTableName added. This use is most useful when table names are dynamic themselves, because they use SpEL expressions as well.

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.

Spring Data JDBC supports only named parameters.

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.

Named queries are handled in the same way as queries provided by annotation.

Customizing Query Methods

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.

Custom `RowMapper` or `ResultSetExtractor`

The @Query annotation allows you to specify a custom RowMapper or ResultSetExtractor to use. The attributes rowMapperClass and resultSetExtractorClass allow you to specify classes to use, which will get instantiated using a default constructor. Alternatively you may set rowMapperClassRef or resultSetExtractorClassRef to a bean name from your Spring application context.

If you want to use a certain RowMapper not just for a single method but for all methods with custom queries returning a certain type, you may register a RowMapperMap bean and registering a RowMapper per method return type. The following example shows how to register DefaultQueryMappingConfiguration:

@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.

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.

Modifying Query

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

@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.