Keyword Sample JPQL snippet

… where [Link]
JPA Query Methods :: Spring Data JPA And findByLastnameAndFirstname = ?1 and
[Link] = ?2
… where [Link]
This section describes the various ways to create a query with Spring Data JPA. Or findByLastnameOrFirstname = ?1 or
[Link] = ?2
… where
Query Lookup Strategies [Link] = ?1
(or … where
The JPA module supports defining a query manually as a String or having it being derived from Is, Equals findByFirstname,findByFirstnameIs,findByFirstnameEquals
[Link] IS
the method name. NULL if the
argument is null)
Derived queries with the predicates IsStartingWith, StartingWith, StartsWith, IsEndingWith, … where
EndingWith, EndsWith, IsNotContaining, NotContaining, NotContains, IsContaining, Containing, Between findByStartDateBetween [Link]
Contains the respective arguments for these queries will get sanitized. This means if the between ?1 and ?2
arguments actually contain characters recognized by LIKE as wildcards these will get escaped so LessThan findByAgeLessThan … where [Link] < ?1
they match only as literals. The escape character used can be configured by setting the LessThanEqual findByAgeLessThanEqual
… where [Link] <= ?
escapeCharacter of the @EnableJpaRepositories annotation. Compare with Using Value 1
Expressions. GreaterThan findByAgeGreaterThan … where [Link] > ?1
… where [Link] >= ?
GreaterThanEqual findByAgeGreaterThanEqual
1
Declared Queries
… where
After findByStartDateAfter
[Link] > ?1
Although getting a query derived from the method name is quite convenient, one might face the
… where
situation in which either the method name parser does not support the keyword one wants to Before findByStartDateBefore
[Link] < ?1
use or the method name would get unnecessarily ugly. So you can either use JPA named queries … where [Link] is
through a naming convention (see Using JPA Named Queries for more information) or rather IsNull, Null findByAge(Is)Null
null
annotate your query method with @Query (see Using @Query for details). IsNotNull, … where [Link] is
findByAge(Is)NotNull
NotNull not null
Query Creation … where
Like findByFirstnameLike [Link] like ?
1
Generally, the query creation mechanism for JPA works as described in Query Methods. The
… where
following example shows what a JPA query method translates into: NotLike findByFirstnameNotLike [Link] not
like ?1
Example 1. Query creation from method names … where
[Link] like ?
public interface UserRepository extends Repository<User, Long> { StartingWith findByFirstnameStartingWith
1 (parameter bound
List<User> findByEmailAddressAndLastname(String emailAddress, String lastname); with appended %)
} … where
[Link] like ?
EndingWith findByFirstnameEndingWith
We create a query using the JPA criteria API from this, but, essentially, this translates into the 1 (parameter bound
following query: select u from User u where [Link] = ?1 and [Link] = ?2. with prepended %)
Spring Data JPA does a property check and traverses nested properties, as described in Property … where
Expressions. [Link] like ?
Containing findByFirstnameContaining
1 (parameter bound
The following table describes the keywords supported for JPA and what a method containing wrapped in %)
that keyword translates to: … where [Link] = ?1
OrderBy findByAgeOrderByLastnameDesc order by
Table 1. Supported keywords inside method names [Link] desc
Keyword Sample JPQL snippet … where [Link]
Not findByLastnameNot
<> ?1
select distinct …
where [Link] = … where [Link] in ?
Distinct findDistinctByLastnameAndFirstname In findByAgeIn(Collection<Age> ages)
?1 and [Link] 1
= ?2
 Keyword Sample JPQL snippet XML Named Query Definition
… where [Link] not
NotIn findByAgeNotIn(Collection<Age> ages)
in ?1 To use XML configuration, add the necessary <named-query /> element to the [Link] JPA
True findByActiveTrue()
… where [Link] = configuration file located in the META-INF folder of your classpath. Automatic invocation of
true named queries is enabled by using some defined naming convention. For more details, see
… where [Link] = below.
False findByActiveFalse()
false
… where Example 3. XML named query configuration
IgnoreCase findByFirstnameIgnoreCase UPPER([Link])
= UPPER(?1) <named-query name="[Link]">
In and NotIn also take any subclass of Collection as a parameter as well as arrays or varargs. <query>select u from User u where [Link] = ?1</query>
</named-query>
For other syntactical versions of the same logical operator, check Repository query keywords.
DISTINCT can be tricky and not always producing the results you expect. For example, select The query has a special name that is used to resolve it at runtime.
distinct u from User u will produce a complete different result than select distinct
[Link] from User u. In the first case, since you are including [Link], nothing will Declaring Interfaces
duplicated, hence you’ll get the whole table, and it would be of User objects.
To allow these named queries, specify the UserRepository as follows:
However, that latter query would narrow the focus to just [Link] and find all unique
last names for that table. This would also yield a List<String> result set instead of a List<User> Example 4. Query method declaration in UserRepository
result set.
public interface UserRepository extends JpaRepository<User, Long> {
countDistinctByLastname(String lastname) can also produce unexpected results. Spring Data
JPA will derive select count(distinct [Link]) from User u where [Link] = ?1. Again, List<User> findByLastname(String lastname);
since [Link] won’t hit any duplicates, this query will count up all the users that had the binding User findByEmailAddress(String emailAddress);
last name. Which would the same as countByLastname(String lastname)! }

What is the point of this query anyway? To find the number of people with a given last name? Spring Data tries to resolve a call to these methods to a named query, starting with the simple
To find the number of distinct people with that binding last name? To find the number of name of the configured domain class, followed by the method name separated by a dot. So the
distinct last names? (That last one is an entirely different query!) Using distinct sometimes preceding example would use the named queries defined earlier instead of trying to create a
requires writing the query by hand and using @Query to best capture the information you seek, query from the method name.
since you also may be needing a projection to capture the result set.
Using @Query
Annotation-based Configuration
Using named queries to declare queries for entities is a valid approach and works fine for a small
Annotation-based configuration has the advantage of not needing another configuration file to
number of queries. As the queries themselves are tied to the Java method that runs them, you
be edited, lowering maintenance effort. You pay for that benefit by the need to recompile your
can actually bind them directly by using the Spring Data JPA @Query annotation rather than
domain class for every new query declaration.
annotating them to the domain class. This frees the domain class from persistence specific
Example 2. Annotation-based named query configuration information and co-locates the query to the repository interface.

@Entity Queries annotated to the query method take precedence over queries defined using @NamedQuery
@NamedQuery(name = "[Link]", or named queries declared in [Link].
query = "select u from User u where [Link] = ?1")
public class User { The following example shows a query created with the @Query annotation:
} Example 5. Declare query at the query method using @Query

Using JPA Named Queries public interface UserRepository extends JpaRepository<User, Long> {

@Query("select u from User u where [Link] = ?1")

The examples use the <named-query /> element and @NamedQuery annotation. The queries for User findByEmailAddress(String emailAddress);
}
these configuration elements have to be defined in the JPA query language. Of course, you can
use <named-native-query /> or @NamedNativeQuery too. These elements let you define the query
in native SQL by losing the database platform independence. Applying a QueryRewriter
Sometimes, no matter how many features you try to apply, it seems impossible to get Spring Depending on what you’re doing with your QueryRewriter, it may be advisable to have more than
Data JPA to apply every thing you’d like to a query before it is sent to the EntityManager. one, each registered with the application context.

You have the ability to get your hands on the query, right before it’s sent to the EntityManager In a CDI-based environment, Spring Data JPA will search the BeanManager for instances of your
and "rewrite" it. That is, you can make any alterations at the last moment. implementation of QueryRewriter.

Example 6. Declare a QueryRewriter using @Query

Using Advanced LIKE Expressions
public interface MyRepository extends JpaRepository<User, Long> {
The query running mechanism for manually defined queries created with @Query allows the
@NativeQuery(value = "select original_user_alias.* from SD_USER original_user_alias" definition of advanced LIKE expressions inside the query definition, as shown in the following
queryRewriter = [Link])
example:
List<User> findByNativeQuery(String param);

@Query(value = "select original_user_alias from User original_user_alias" Example 9. Advanced like expressions in @Query
queryRewriter = [Link])
List<User> findByNonNativeQuery(String param); public interface UserRepository extends JpaRepository<User, Long> {
}
@Query("select u from User u where [Link] like %?1")
List<User> findByFirstnameEndsWith(String firstname);
This example shows both a native (pure SQL) rewriter as well as a JPQL query, both leveraging
}
the same QueryRewriter. In this scenario, Spring Data JPA will look for a bean registered in the
application context of the corresponding type. In the preceding example, the LIKE delimiter character (%) is recognized, and the query is
transformed into a valid JPQL query (removing the %). Upon running the query, the parameter
You can write a query rewriter like this:
passed to the method call gets augmented with the previously recognized LIKE pattern.
Example 7. Example QueryRewriter
Native Queries
public class MyQueryRewriter implements QueryRewriter {
Using the @NativeQuery annotation allows running native queries, as shown in the following
@Override example:
public String rewrite(String query, Sort sort) {
return [Link]("original_user_alias", "rewritten_user_alias");
} Example 10. Declare a native query at the query method using @Query
}
public interface UserRepository extends JpaRepository<User, Long> {
You have to ensure your QueryRewriter is registered in the application context, whether it’s by @NativeQuery(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1")
applying one of Spring Framework’s @Component-based annotations, or having it as part of a User findByEmailAddress(String emailAddress);
@Bean method inside an @Configuration class. }

Another option is to have the repository itself implement the interface. The @NativeQuery annotation is mostly a composed annotation for @Query(nativeQuery=true)
but it also provides additional attributes such as sqlResultSetMapping to leverage JPA’s
Example 8. Repository that provides the QueryRewriter @SqlResultSetMapping(…).

public interface MyRepository extends JpaRepository<User, Long>, QueryRewriter { Spring Data can rewrite simple queries for pagination and sorting. More complex queries
require either JSqlParser to be on the class path or a countQuery declared in your code. See the
@Query(value = "select original_user_alias.* from SD_USER original_user_alias" example below for more details.
nativeQuery = true,
queryRewriter = [Link])
List<User> findByNativeQuery(String param);
Example 11. Declare native count queries for pagination at the query method by using
@NativeQuery
@Query(value = "select original_user_alias from User original_user_alias"
queryRewriter = [Link]) public interface UserRepository extends JpaRepository<User, Long> {
List<User> findByNonNativeQuery(String param);
@NativeQuery(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
@Override countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1")
default String rewrite(String query, Sort sort) { Page<User> findByLastname(String lastname, Pageable pageable);
return [Link]("original_user_alias", "rewritten_user_alias" }
}
} It is possible to disable usage of JSqlParser for parsing native queries although it is available
on the classpath by setting [Link]=regex via the
 [Link] file or a system property.
@Query("select [Link], LENGTH([Link]) as fn_len from User u where [Link] like ?1%"
Valid values are (case-insensitive): List<Object[]> findByAsArrayAndSort(String lastname, Sort sort);
}
• auto (default, automatic selection) [Link]("lannister", [Link]("firstname")); (1)
[Link]("stark", [Link]("LENGTH(firstname)")); (2)
• regex (Use the builtin regex-based Query Enhancer) [Link]("targaryen", [Link]("LENGTH(firstname)")); (3)
[Link]("bolton", [Link]("fn_len")); (4)
• jsqlparser (Use JSqlParser)
1 Valid Sort expression pointing to property in domain model.
2 Invalid Sort containing function call. Throws Exception.
A similar approach also works with named native queries, by adding the .count suffix to a copy
3 Valid Sort containing explicitly unsafe Order.
of your query. You probably need to register a result set mapping for your count query, though.
4 Valid Sort expression pointing to aliased function.
Next to obtaining mapped results, native queries allow you to read the raw Tuple from the
database by choosing a Map container as the method’s return type. The resulting map contains When working with large data sets, scrolling can help to process those results efficiently without
key/value pairs representing the actual database column name and the value. loading all results into memory.

Example 12. Native query retuning raw column name/value pairs You have multiple options to consume large query results:

interface UserRepository extends JpaRepository<User, Long> { 1. Paging. You have learned in the previous chapter about Pageable and PageRequest.
@NativeQuery("SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1") 2. Offset-based scrolling. This is a lighter variant than paging because it does not require the
Map<String, Object> findRawMapByEmail(String emailAddress); (1)
total result count.
@NativeQuery("SELECT * FROM USERS WHERE LASTNAME = ?1")
List<Map<String, Object>> findRawMapByLastname(String lastname); (2) 3. Keyset-baset scrolling. This method avoids the shortcomings of offset-based result
} retrieval by leveraging database indexes.

1 Single Map result backed by a Tuple. Read more on which method to use best for your particular arrangement.
2 Multiple Map results backed by Tuples.
Scrolling with String-based query methods is not yet supported. Scrolling is also not supported
String-based Tuple Queries are only supported by Hibernate. Eclipselink supports only
using stored @Procedure query methods.
Criteria-based Tuple Queries.

Using Sort Using Named Parameters

By default, Spring Data JPA uses position-based parameter binding, as described in all the
Sorting can be done by either providing a PageRequest or by using Sort directly. The properties
preceding examples. This makes query methods a little error-prone when refactoring regarding
actually used within the Order instances of Sort need to match your domain model, which means
the parameter position. To solve this issue, you can use @Param annotation to give a method
they need to resolve to either a property or an alias used within the query. The JPQL defines this
parameter a concrete name and bind the name in the query, as shown in the following example:
as a state field path expression.
Example 14. Using named parameters
Using any non-referenceable path expression leads to an Exception.
public interface UserRepository extends JpaRepository<User, Long> {
However, using Sort together with @Query lets you sneak in non-path-checked Order instances
@Query("select u from User u where [Link] = :firstname or [Link] = :lastname"
containing functions within the ORDER BY clause. This is possible because the Order is appended
User findByLastnameOrFirstname(@Param("lastname") String lastname,
to the given query string. By default, Spring Data JPA rejects any Order instance containing @Param("firstname") String firstname);
function calls, but you can use [Link] to add potentially unsafe ordering. }

The following example uses Sort and JpaSort, including an unsafe option on JpaSort: The method parameters are switched according to their order in the defined query.
As of version 4, Spring fully supports Java 8’s parameter name discovery based on the -
Example 13. Using Sort and JpaSort
parameters compiler flag. By using this flag in your build as an alternative to debug
public interface UserRepository extends JpaRepository<User, Long> { information, you can omit the @Param annotation for named parameters.

@Query("select u from User u where [Link] like ?1%")

List<User> findByAndSort(String lastname, Sort sort); Using Expressions
We support the usage of restricted expressions in manually defined queries that are defined with …
@Query. Upon the query being run, these expressions are evaluated against a predefined set of String attribute;
}
variables.
@Entity
If you are not familiar with Value Expressions, please refer to Value Expressions Fundamentals public class ConcreteType extends AbstractMappedType { … }
to learn about SpEL Expressions and Property Placeholders.
@NoRepositoryBean
public interface MappedTypeRepository<T extends AbstractMappedType>
Spring Data JPA supports a variable called entityName. Its usage is select x from extends Repository<T, Long> {
#{#entityName} x. It inserts the entityName of the domain type associated with the given
repository. The entityName is resolved as follows: * If the domain type has set the name property @Query("select t from #{#entityName} t where [Link] = ?1")
on the @Entity annotation, it is used. * Otherwise, the simple class-name of the domain type is List<T> findAllByAttribute(String attribute);
}
used.
public interface ConcreteRepository
The following example demonstrates one use case for the #{#entityName} expression in a query extends MappedTypeRepository<ConcreteType> { … }
string where you want to define a repository interface with a query method and a manually
defined query: In the preceding example, the MappedTypeRepository interface is the common parent interface
for a few domain types extending AbstractMappedType. It also defines the generic
Example 15. Using SpEL expressions in repository query methods: entityName findAllByAttribute(…) method, which can be used on instances of the specialized repository

@Entity
interfaces. If you now invoke findByAllAttribute(…) on ConcreteRepository, the query becomes
public class User { select t from ConcreteType t where [Link] = ?1.

@Id You can also use Expressions to control arguments may also be used to control method
@GeneratedValue arguments. In these expressions the entity name is not available, but the arguments are. They
Long id; can be accessed by name or index as demonstrated in the following example.
String lastname;
}
Example 17. Using Value Expressions in Repository Query Methods: Accessing Arguments

@Query("select u from User u where [Link] = ?1 and [Link]=?#{[0]} and [Link]

public interface UserRepository extends JpaRepository<User,Long> {
List<User> findByFirstnameAndCurrentUserWithCustomQuery(String firstname);
@Query("select u from #{#entityName} u where [Link] = ?1")
List<User> findByLastname(String lastname); For like-conditions one often wants to append % to the beginning or the end of a String valued
} parameter. This can be done by appending or prefixing a bind parameter marker or a SpEL
expression with %. Again the following example demonstrates this.
To avoid stating the actual entity name in the query string of a @Query annotation, you can use
the #{#entityName} variable. Example 18. Using Value Expressions in Repository Query Methods: Wildcard shortcut

The entityName can be customized by using the @Entity annotation. Customizations in [Link] @Query("select u from User u where [Link] like %:#{[0]}% and [Link] like %:lastname%"
List<User> findByLastnameWithSpelExpression(@Param("lastname") String lastname);
are not supported for the SpEL expressions.
When using like-conditions with values that are coming from a not secure source the values
Of course, you could have just used User in the query declaration directly, but that would require should be sanitized so they can’t contain any wildcards and thereby allow attackers to select
you to change the query as well. The reference to #entityName picks up potential future more data than they should be able to. For this purpose the escape(String) method is made
remappings of the User class to a different entity name (for example, by using @Entity(name = available in the SpEL context. It prefixes all instances of _ and % in the first argument with the
"MyUser").
single character from the second argument. In combination with the escape clause of the like
expression available in JPQL and standard SQL this allows easy cleaning of bind parameters.
Another use case for the #{#entityName} expression in a query string is if you want to define a
generic repository interface with specialized repository interfaces for a concrete domain type. To Example 19. Using Value Expressions in Repository Query Methods: Sanitizing Input Values
not repeat the definition of custom query methods on the concrete interfaces, you can use the
entity name expression in the query string of the @Query annotation in the generic repository @Query("select u from User u where [Link] like %?#{escape([0])}% escape ?#{escapeCharacter
interface, as shown in the following example: List<User> findContainingEscaped(String namePart);

Example 16. Using SpEL expressions in Repository Query Methods: entityName with Given this method declaration in a repository interface findContainingEscaped("Peter_") will
Inheritance find Peter_Parker but not Peter Parker. The escape character used can be configured by setting
the escapeCharacter of the @EnableJpaRepositories annotation. Note that the method
@MappedSuperclass escape(String) available in the SpEL context will only escape the SQL and JPQL standard
public abstract class AbstractMappedType {
wildcards _ and %. If the underlying database or the JPA implementation supports additional wish the EntityManager to be cleared automatically, you can set the @Modifying annotation’s
wildcards these will not get escaped. clearAutomatically attribute to true.

Example 20. Using Value Expressions in Repository Query Methods: Configuration Properties The @Modifying annotation is only relevant in combination with the @Query annotation. Derived
query methods or custom methods do not require this annotation.
@Query("select u from User u where [Link] = ?${[Link]:unknown}"
List<User> findContainingEscaped(String namePart);
Derived Delete Queries
You can refer in your query methods also to configuration property names including fallbacks if
you wish to resolve a property from Environment during runtime. The property is being evaluated Spring Data JPA also supports derived delete queries that let you avoid having to declare the
upon query execution. Typically, property placeholders resolve to String-like values. JPQL query explicitly, as shown in the following example:

Example 22. Using a derived delete query

Other Methods
interface UserRepository extends Repository<User, Long> {
Spring Data JPA offers many ways to build queries. But sometimes, your query may simply be
void deleteByRoleId(long roleId);
too complicated for the techniques offered. In that situation, consider:
@Modifying
• If you haven’t already, simply write the query yourself using @Query. @Query("delete from User u where [Link] = ?1")
void deleteInBulkByRoleId(long roleId);
• If that doesn’t fit your needs, consider implementing a custom implementation. This lets }
you register a method in your repository while leaving the implementation completely up
to you. This gives you the ability to: Although the deleteByRoleId(…) method looks like it basically produces the same result as the
deleteInBulkByRoleId(…), there is an important difference between the two method declarations
◦ Talk directly to the EntityManager (writing pure HQL/JPQL/EQL/native SQL or in terms of the way they are run. As the name suggests, the latter method issues a single JPQL
using the Criteria API) query (the one defined in the annotation) against the database. This means even currently
loaded instances of User do not see lifecycle callbacks invoked.
◦ Leverage Spring Framework’s JdbcTemplate (native SQL)
To make sure lifecycle queries are actually invoked, an invocation of deleteByRoleId(…) runs a
◦ Use another 3rd-party database toolkit. query and then deletes the returned instances one by one, so that the persistence provider can
actually invoke @PreRemove callbacks on those entities.
• Another option is putting your query inside the database and then using either Spring Data
JPA’s @StoredProcedure annotation or if it’s a database function using the @Query In fact, a derived delete query is a shortcut for running the query and then calling
annotation and invoking it with a CALL. [Link](Iterable<User> users) on the result and keeping behavior in sync with
the implementations of other delete(…) methods in CrudRepository.
These tactics may be most effective when you need maximum control of your query, while still
letting Spring Data JPA provide resource management. When deleting a lot of objects you will need to consider the performance implications to ensure
sufficient memory availability. All resulting objects are loaded into memory before being
Modifying Queries deleted and are held in the session until flushing or completing the transaction.

All the previous sections describe how to declare queries to access a given entity or collection of Applying Query Hints
entities. You can add custom modifying behavior by using the custom method facilities
described in Custom Implementations for Spring Data Repositories. As this approach is feasible To apply JPA query hints to the queries declared in your repository interface, you can use the
for comprehensive custom functionality, you can modify queries that only need parameter @QueryHints annotation. It takes an array of JPA @QueryHint annotations plus a boolean flag to
binding by annotating the query method with @Modifying, as shown in the following example: potentially disable the hints applied to the additional count query triggered when applying
pagination, as shown in the following example:
Example 21. Declaring manipulating queries

@Modifying Example 23. Using QueryHints with a repository method

@Query("update User u set [Link] = ?1 where [Link] = ?2")
int setFixedFirstnameFor(String firstname, String lastname); public interface UserRepository extends Repository<User, Long> {

@QueryHints(value = { @QueryHint(name = "name", value = "value")},

Doing so triggers the query annotated to the method as an updating query instead of a selecting forCounting = false)
one. As the EntityManager might contain outdated entities after the execution of the modifying Page<User> findByLastname(String lastname, Pageable pageable);
query, we do not automatically clear it (see the JavaDoc of [Link]() for details), }
since this effectively drops all non-flushed changes still pending in the EntityManager. If you
The preceding declaration would apply the configured @QueryHint for that actually query but If you have a [Link] file, you can apply it there:
omit applying it to the count query triggered to calculate the total number of pages.
Example 26. [Link]-based configuration
Sometimes, you need to debug a query based upon database performance. The query your
database administrator shows you may look VERY different than what you wrote using @Query, <persistence-unit name="my-persistence-unit">
or it may look nothing like what you presume Spring Data JPA has generated regarding a custom ...registered classes...
finder or if you used query by example.
<properties>
To make this process easier, you can insert custom comments into almost any JPA operation, <property name="hibernate.use_sql_comments" value="true" />
whether its a query or other operation by applying the @Meta annotation. </properties>
</persistence-unit>
Example 24. Apply @Meta annotation to repository operations
Finally, if you are using Spring Boot, then you can set it up inside your [Link]
public interface RoleRepository extends JpaRepository<Role, Integer> { file:

@Meta(comment = "find roles by name") Example 27. Spring Boot property-based configuration
List<Role> findByName(String name);
[Link].use_sql_comments=true
@Override
@Meta(comment = "find roles using QBE") To activate query comments in EclipseLink, you must set [Link] to
<S extends Role> List<S> findAll(Example<S> example);
FINE.
@Meta(comment = "count roles for a given name")
long countByName(String name); If you are using Java-based configuration settings, this can be done like this:

@Override Example 28. Java-based JPA configuration

@Meta(comment = "exists based on QBE")
<S extends Role> boolean exists(Example<S> example); @Bean
} public Properties jpaProperties() {

This sample repository has a mixture of custom finders as well as overriding the inherited Properties properties = new Properties();
operations from JpaRepository. Either way, the @Meta annotation lets you add a comment that will [Link]("[Link]", "FINE");
return properties;
be inserted into queries before they are sent to the database. }

It’s also important to note that this feature isn’t confined solely to queries. It extends to the If you have a [Link] file, you can apply it there:
count and exists operations. And while not shown, it also extends to certain delete operations.
Example 29. [Link]-based configuration
While we have attempted to apply this feature everywhere possible, some operations of the
underlying EntityManager don’t support comments. For example, <persistence-unit name="my-persistence-unit">
[Link]() is clearly documented as supporting comments, but
...registered classes...
[Link]() operations do not.
<properties>
Neither JPQL logging nor SQL logging is a standard in JPA, so each provider requires custom <property name="[Link]" value="FINE" />
configuration, as shown the sections below. </properties>
</persistence-unit>
To activate query comments in Hibernate, you must set hibernate.use_sql_comments to true.
Finally, if you are using Spring Boot, then you can set it up inside your [Link]
If you are using Java-based configuration settings, this can be done like this: file:

Example 25. Java-based JPA configuration Example 30. Spring Boot property-based configuration

@Bean [Link]=FINE
public Properties jpaProperties() {

Properties properties = new Properties(); Configuring Fetch- and LoadGraphs

[Link]("hibernate.use_sql_comments", "true");
return properties; The JPA 2.1 specification introduced support for specifying Fetch- and LoadGraphs that we also
} support with the @EntityGraph annotation, which lets you reference a @NamedEntityGraph
definition. You can use that annotation on an entity to configure the fetch plan of the resulting for (User u : users) {
query. The type (Fetch or Load) of the fetching can be configured by using the type attribute on // consume the user
}
the @EntityGraph annotation. See the JPA 2.1 Spec 3.7.4 for further reference.
// obtain the next Scroll
The following example shows how to define a named entity graph on an entity: users = repository.findFirst10ByLastnameOrderByFirstname("Doe", [Link]([Link]()
} while (![Link]() && [Link]());
Example 31. Defining a named entity graph on an entity.
The ScrollPosition identifies the exact position of an element with the entire query result.
@Entity Query execution treats the position parameter exclusive, results will start after the given
@NamedEntityGraph(name = "[Link]",
attributeNodes = @NamedAttributeNode("members"))
position. ScrollPosition#offset() and ScrollPosition#keyset() as special incarnations of a
public class GroupInfo { ScrollPosition indicating the start of a scroll operation.
The above example shows static sorting and limiting. You can define query methods
// default fetch mode is lazy.
@ManyToMany
alternatively that accept a Sort object define a more complex sorting order or sorting on a per-
List<GroupMember> members = new ArrayList<GroupMember>(); request basis. In a similar way, providing a Limit object allows you to define a dynamic limit on
a per-request basis instead of applying a static limitation. Read more on dynamic sorting and
… limiting in the Query Methods Details.
}
WindowIterator provides a utility to simplify scrolling across Windows by removing the need to
The following example shows how to reference a named entity graph on a repository query
check for the presence of a next Window and applying the ScrollPosition.
method:
WindowIterator<User> users = [Link](position -> repository.findFirst10ByLastnameOrde
Example 32. Referencing a named entity graph definition on a repository query method. .startingAt([Link]());

public interface GroupRepository extends CrudRepository<GroupInfo, String> { while ([Link]()) {

User u = [Link]();
@EntityGraph(value = "[Link]", type = [Link]) // consume the user
GroupInfo getByGroupName(String name); }

}
Scrolling using Offset
It is also possible to define ad hoc entity graphs by using @EntityGraph. The provided
attributePaths are translated into the according EntityGraph without needing to explicitly add Offset scrolling uses similar to pagination, an Offset counter to skip a number of results and let
@NamedEntityGraph to your domain types, as shown in the following example: the data source only return results beginning at the given Offset. This simple mechanism avoids
large results being sent to the client application. However, most databases require materializing
Example 33. Using ad-hoc entity graph definitions on a repository query method the full query result before your server can return the results.

public interface GroupRepository extends CrudRepository<GroupInfo, String> { Example 34. Using OffsetScrollPosition with Repository Query Methods
@EntityGraph(attributePaths = { "members" }) interface UserRepository extends Repository<User, Long> {
GroupInfo getByGroupName(String name);
Window<User> findFirst10ByLastnameOrderByFirstname(String lastname, OffsetScrollPosition posi
} }

Scrolling is a more fine-grained approach to iterate through larger results set chunks. Scrolling WindowIterator<User> users = [Link](position -> repository.findFirst10ByLastnameOrde
consists of a stable sort, a scroll type (Offset- or Keyset-based scrolling) and result limiting. You .startingAt([Link]()); (1)
can define simple sorting expressions by using property names and define static result limiting
1 Start with no offset to include the element at position 0.
using the Top or First keyword through query derivation. You can concatenate expressions to
collect multiple criteria into one expression. There is a difference between [Link]() and [Link](0L). The
former indicates the start of scroll operation, pointing to no specific offset whereas the latter
Scroll queries return a Window<T> that allows obtaining the element’s scroll position to fetch the identifies the first element (at position 0) of the result. Given the exclusive nature of scrolling,
next Window<T> until your application has consumed the entire query result. Similar to using [Link](0) skips the first element and translate to an offset of 1.
consuming a Java Iterator<List<…>> by obtaining the next batch of results, query result
scrolling lets you access the a ScrollPosition through [Link](…). Scrolling using Keyset-Filtering
Window<User> users = repository.findFirst10ByLastnameOrderByFirstname("Doe", [Link]());
do { Offset-based requires most databases require materializing the entire result before your server
can return the results. So while the client only sees the portion of the requested results, your
server needs to build the full result, which causes additional load.

Keyset-Filtering approaches result subset retrieval by leveraging built-in capabilities of your

database aiming to reduce the computation and I/O requirements for individual queries. This
approach maintains a set of keys to resume scrolling by passing keys into the query, effectively
amending your filter criteria.

The core idea of Keyset-Filtering is to start retrieving results using a stable sorting order. Once
you want to scroll to the next chunk, you obtain a ScrollPosition that is used to reconstruct the
position within the sorted result. The ScrollPosition captures the keyset of the last entity within
the current Window. To run the query, reconstruction rewrites the criteria clause to include all
sort fields and the primary key so that the database can leverage potential indexes to run the
query. The database needs only constructing a much smaller result from the given keyset
position without the need to fully materialize a large result and then skipping results until
reaching a particular offset.

Keyset-Filtering requires the keyset properties (those used for sorting) to be non-nullable. This
limitation applies due to the store specific null value handling of comparison operators as well
as the need to run queries against an indexed source. Keyset-Filtering on nullable properties
will lead to unexpected results.

Using KeysetScrollPosition with Repository Query Methods

interface UserRepository extends Repository<User, Long> {

Window<User> findFirst10ByLastnameOrderByFirstname(String lastname, KeysetScrollPosition position)

}

WindowIterator<User> users = [Link](position -> repository.findFirst10ByLastnameOrderByFirstna

.startingAt([Link]()); (1)

1 Start at the very beginning and do not apply additional filtering.

Keyset-Filtering works best when your database contains an index that matches the sort fields,
hence a static sort works well. Scroll queries applying Keyset-Filtering require to the properties
used in the sort order to be returned by the query, and these must be mapped in the returned
entity.

You can use interface and DTO projections, however make sure to include all properties that
you’ve sorted by to avoid keyset extraction failures.

When specifying your Sort order, it is sufficient to include sort properties relevant to your query;
You do not need to ensure unique query results if you do not want to. The keyset query
mechanism amends your sort order by including the primary key (or any remainder of
composite primary keys) to ensure each query result is unique.