Generally, the query creation mechanism for JPA works as described in Query Methods. The following example shows what a JPA query method translates into:
public interface UserRepository extends Repository<User, Long> {
List<User> findByEmailAddressAndLastname(String emailAddress, String lastname);
}
We create a query using the JPA criteria API from this, but, essentially, this translates into the following query: select u from User u where u.emailAddress = ?1 and u.lastname = ?2. Spring Data JPA does a property check and traverses nested properties, as described in Property Expressions.
The following table describes the keywords supported for JPA and what a method containing that keyword translates to:
| Keyword | Sample | JPQL snippet |
|---|---|---|
Distinct |
findDistinctByLastnameAndFirstname |
select distinct … where x.lastname = ?1 and x.firstname = ?2 |
And |
findByLastnameAndFirstname |
… where x.lastname = ?1 and x.firstname = ?2 |
Or |
findByLastnameOrFirstname |
… where x.lastname = ?1 or x.firstname = ?2 |
Is, Equals |
findByFirstname,findByFirstnameIs,findByFirstnameEquals |
… where x.firstname = ?1 |
Between |
findByStartDateBetween |
… where x.startDate between ?1 and ?2 |
LessThan |
findByAgeLessThan |
… where x.age < ?1 |
LessThanEqual |
findByAgeLessThanEqual |
… where x.age <= ?1 |
GreaterThan |
findByAgeGreaterThan |
… where x.age > ?1 |
GreaterThanEqual |
findByAgeGreaterThanEqual |
… where x.age >= ?1 |
After |
findByStartDateAfter |
… where x.startDate > ?1 |
Before |
findByStartDateBefore |
… where x.startDate < ?1 |
IsNull, Null |
findByAge(Is)Null |
… where x.age is null |
IsNotNull, NotNull |
findByAge(Is)NotNull |
… where x.age is not null |
Like |
findByFirstnameLike |
… where x.firstname like ?1 |
NotLike |
findByFirstnameNotLike |
… where x.firstname not like ?1 |
StartingWith |
findByFirstnameStartingWith |
… where x.firstname like ?1 (parameter bound with appended %) |
EndingWith |
findByFirstnameEndingWith |
… where x.firstname like ?1 (parameter bound with prepended %) |
Containing |
findByFirstnameContaining |
… where x.firstname like ?1 (parameter bound wrapped in %) |
OrderBy |
findByAgeOrderByLastnameDesc |
… where x.age = ?1 order by x.lastname desc |
Not |
findByLastnameNot |
… where x.lastname <> ?1 |
In |
findByAgeIn(Collection<Age> ages) |
… where x.age in ?1 |
NotIn |
findByAgeNotIn(Collection<Age> ages) |
… where x.age not in ?1 |
True |
findByActiveTrue() |
… where x.active = true |
False |
findByActiveFalse() |
… where x.active = false |
IgnoreCase |
findByFirstnameIgnoreCase |
… where UPPER(x.firstname) = UPPER(?1) |
<aside>
ℹ️ Note : In and NotIn also take any subclass of Collection as a parameter as well as arrays or varargs. For other syntactical versions of the same logical operator, check Repository query keywords.
</aside>
<aside>
⚠️ Warning : DISTINCT can be tricky and not always producing the results you expect. For example, select distinct u from User u will produce a complete different result than select distinct u.lastname from User u. In the first case, since you are including User.id, nothing will duplicated, hence you’ll get the whole table, and it would be of User objects.
However, that latter query would narrow the focus to just User.lastname and find all unique last names for that table. This would also yield a List<String> result set instead of a List<User> result set.
countDistinctByLastname(String lastname) can also produce unexpected results. Spring Data JPA will derive select count(distinct u.id) from User u where u.lastname = ?1. Again, since u.id won’t hit any duplicates, this query will count up all the users that had the binding 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? To find the number of distinct people with that binding last name? To find the number of distinct last names? (That last one is an entirely different query!) Using distinct sometimes requires writing the query by hand and using @Query to best capture the information you seek, since you also may be needing a projection to capture the result set.
</aside>