This version is still in development and is not considered stable yet. For the latest stable version, please use Spring Data Relational 3.3.2! |
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:
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:
Keyword | Sample | Logical result |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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:
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 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 where bind variables are allowed. Such a SpEL expression will get replaced with a bind variable and the variable gets bound to the result of the SpEL expression.
@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.
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.
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. TheRowMapper
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.