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<T> 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](https://docs.spring.io/spring-data/jpa/docs/1.4.2.RELEASE/reference/html/jpa.repositories.html) * [Spring Docs - JPA](http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-creation) * [REST (Restful Web Services) ](http://bloomlab.blogspot.com.ee/2015/07/rest-restfull-webservices.html) * [Themeleaf - Offsite](http://www.thymeleaf.org/) * [Web Resource Optimizer for Java](http://alexo.github.io/wro4j/) * [Wro4j - Github](https://github.com/wro4j/wro4j) * [Keynote: Top JS frmeworks 2015](http://bloomlab.blogspot.com.ee/2015/03/top-javascript-frameworks-2015.html)