Keynotes: Derive JPA query from method name

JPA lookup

The JPA module supports defining a query or native query manually as String (using @NamedQuery/@NativeNamedQuery/@Query annotations) or have it being derived from the method name.

Default lookup strategy is CREATE_IF_NOT_FOUND, which combines combines CREATE and USE_DECLARED_QUERY ones. It looks up a declared query first, and if no declared query is found, it creates a custom method name-based query.

Spring Data repository query building mechanism allows quick query definition by method names & custom-tuning of it by introducing declared queries as needed.

---

##Method name-based Query

The mechanism strips the prefixes find…By, read…By, query…By, count…By, and get…By from the method and starts parsing the rest of it.
The first By acts as delimiter to indicate the start of the actual criteria.
It can contain further expressions such as a Distinct to set a distinct flag on the query to be created. At a very basic level you can define conditions on entity properties and concatenate them with And and Or.

Note: Below basically is snippets & quotes retrieved & combined from official Spring Docs

---

And

Java

List<User> findByEmailAndLastname(EmailAddress emailAddress, String lastname);

query: select u from User u where u.emailAddress = ?1 and u.lastname = ?2

---

Or

Java

List<User> findByEmailOrLastname(EmailAddress emailAddress, String lastname);

query: select u from User u where u.emailAddress = ?1 or u.lastname = ?2

---

IgnoreCase, AllIgnoreCase

Enabling ignoring case for an individual property or for all ones:

Java

List<User> findByLastnameIgnoreCase(String lastname);
List<User> findByLastnameAndFirstnameAllIgnoreCase(String lname, String fname);

---

OrderBy (Asc or Desc)

Enabling static ORDER BY for a query:

Java

List<User> findByLastnameOrderByFirstnameAsc(String lastname);
List<User> findByLastnameOrderByFirstnameDesc(String lastname);

query: select u from User u where u.lastname = ?1 order by u.firstname.asc or desc

---

IsNull, IsNotNull or NotNull

Java

List<User> findByAgeIsNull(Integer age);
List<User> findByAge(Is)NotNull(Integer age);

query: ...where u.age is null or ...not null

---

LessThan, GreaterThan

Java

List<User> findByAgeLessThan(Integer age);
List<User> findByAgeGreaterThan(Integer age);

query: … where u.age < ?1 or ...u.age > ?1

---

After, Before, Between

Java

List<Deal> findByStartDateAfter(Date after)
List<Deal> findByStartDateBefore(Date before)
List<Deal> findByStartDateBetween(@Param("from") Date from, @Param("to") Date to)

queries:

• … where x.startDate > ?1 or … where x.startDate < ?1
• … where x.startDate between :from and :to

---

Like, NotLike

Java

List<User> findByFirstnameLike(String firstname);
List<User> findByFirstnameNotLike(String firstname);

query: … where u.firstname like ?1 or not like ?1

---

StartingWith, EndingWith, Containing

Java

List<User> findByFirstnameLike(String firstname);
List<User> findByFirstnameNotLike(String firstname);
List<User> findByFirstnameContaining(String firstname);

queries:

• … where u.firstname like ?1 (parameter bound with appended %)
• … where u.firstname like ?1 (parameter bound with prepended %)
• … where u.firstname like ?1 (parameter bound wrapped in %)

---

Not

Java

List<User> findByFirstnameNot(String firstname);

query: … where x.lastname <> ?1

---

In, NotIn

Java

List<User> findByAgeIn(Collection<Age> ages);
List<User> findByAgeNotIn(Collection<Age> ages);

query: … where x.age in ?1 or not in ?1

---

True, False

Java

List<User> findByActiveTrue();
List<User> findByActiveFalse();

query: … where x.active = true or false

---

Special Parameters in Query

Besides your defined parameters, the infrastructure will recognize the following specific types:

• Sort - if you only need sorting;

    List<User> findByLastname(String lastname, Sort sort);
  

• Peageble - to apply pagination; A Page knows about the total number of elements and pages available by the infrastructure triggering a count query to calculate the overall number.

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

• Slice - less expensive, than Pageable and could be used instead; A Slice only knows about whether there’s a next Slice available which might be just sufficient when walking thought a larger result set.

---

Limit query results

It's possible to specify the maximum result size to be returned via Top and First keywords:

Java

User findFirstByOrderByLastnameAsc();

User findTopByOrderByAgeDesc();

Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);

Slice<User> findTop3ByLastname(String lastname, Pageable pageable);

List<User> findFirst10ByLastname(String lastname, Sort sort);

List<User> findTop10ByLastname(String lastname, Pageable pageable);

It's also appropriate to use Distinct keyword to limit result:

Java

List<User> findDistinctUsersByLastnameOrFirstname(String lname, String fname);
List<User> findUsersDistinctByLastnameOrFirstname(String lname, String fname);

---

Streaming query results

It's possible to processed query results incrementally by using a Java 8 Stream as return type:

Java

@Query("select u from User u")
Stream<User> findAllByCustomQueryAndStream();

Stream<User> readAllByFirstnameNotNull();

@Query("select u from User u")
Stream<User> streamAllPaged(Pageable pageable);

try (Stream<User> stream = repository.findAllByCustomQueryAndStream()) {
    stream.forEach(…);
}

Stream should be closed after usage. You can either manually close the Stream using the close() method or by using a Java 7 try-with-resources block.

---

Async query

Repository queries can be executed asynchronously, which might be quite useful, cause method will return immediately upon invocation and query task will be executed by Spring TaskExecutor:

Java

@Async
Future<User> findByFirstname(String firstname);

@Async
CompletableFuture<User> findOneByFirstname(String firstname);

@Async
ListenableFuture<User> findOneByLastname(String lastname);

---

see Also

• Spring IO - JPA Repositories
• Spring Docs - JPA
• REST (Restful Web Services)
• Themeleaf - Offsite
• Web Resource Optimizer for Java
• Wro4j - Github
• Keynote: Top JS frmeworks 2015