Writing JPQL Queries with Spring Data
The JPA, Java Persistence API, gives us a query language, Java Persistence Query Language, JPQL. Since JPQL queries use entity classes and attributes, JPA gives us a Query interface that uses EntityManager to create queries that interact with the database.
There are three methods available to implement JPQL queries that fetch instances based on non primary key attributes; Query based on method name, using the ‘@Query’ annotation, using the ‘@NamedQuery’ annotation. This post will show the use of the method name creation method.
This will not be a post on setting up the entire Spring application but will focus on the implementation of the repository interface/class only.
To use method name creation method Spring Data allows the use of certain criteria in order to generate queries. The methods using this technique of query creation must use phrases already recognized and established by Spring and JPQL such as findBy, getBy etc. Additionally, it is possible to provide other keywords inside of these to make queries more specific. Example:
Assuming there is an entity class, Athlete, mapped to a table, with a name, sport, and birthday, and passed to a CrudRepository one can write:
List<Athlete> findByBirthday(LocalDate birthday);
We can use multiple parameters to search by either or like:
Athlete findByEmailOrSport(String email, String sport);
Where the number of parameters has to equal the number of search conditions and be in the same order.
Using this method it is possible to incorporate certain sorting techniques in the query such as Between, Equals, Before, After, Null and NotNull and Like:
List<Athlete> findByBirthdayBetween(LocalDate from, LocalDate to);
Athlete findBySportEquals(String sport);
List<Athlete> findByEmailNotNull();
List<Athlete> findByNameLike(String pattern);
