Spring Data JPA Query examples

In Spring Data JPA, there are several ways to query a database. But that can lead to long and hard to read method names. To overcome that, Spring Data JPA allows you to use custom queries using the @Query annotation.

When you use pre-defined method names, it is important to identify your domain object"s property naming conventions. The below examples assume that your domain properties follow the camel case. For example, the first name variable is declared as String firstName; This is important when writing the query as well.

You can conveniently derive a query by the method name.

public interface Users extends CrudRepository(Users, Long){
	public List<Users> findByCountryId(Long countryId);
}

The above method can be written using the following query.

@Query(value="SELECT user FROM Users user where user.countryId = ?1")
public List<Users> findUserByCountryId(Long countryId);

To find users by first and last name:

public interface Users extends CrudRepository(Users, Long){
	public List<Users> findByFirstnameAndLastName(String firstName, String lastName);
}

@Query(value="SELECT user FROM Users user where user.firstName = ?1 and user.lastName = ?2")
public List<Users> findByFullName(String firstName, String lastName);

Likewise, the below queries reside in the Spring Data CrudRepository interface. We ignore the implementation code for clarity.

To find users by like:

public interface Users extends CrudRepository(Users, Long){
	public List<Users> findByCountryNameLike(String countryName);
}

@Query(value="SELECT user FROM Users user where user.countryName like :countryName")
public List<Users> findByCountryNameLike(String countryName);

Find users in:

@Query(value="SELECT user FROM Users user where user.userId in :userIds")
public List<Users> findUsersIn(List userIds);

Distinct

public List<Users> findDistinctByFirstNameAndLastName(String firstName, String lastName)
//Using @Query annotation
@Query(value="SELECT distinct u FROM Users u where u.firstName = ?1 and u.lastName = ?2")
public List<Users> findDistinctByFirstAndLastName(String lastName, String firstName);

And

public List<Users> findByFirstNameAndLastName(String firstName, String lastName);
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName = ?1 and u.lastName = ?2")
public List<Users> findByFirstNameAndLastName(String firstName, String lastName);

Or

public List<Users> findByLastNameOrFirstName(String firstName, String lastName);
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName = ?1 or u.lastName = ?2")
public List<Users> findByLastOrFirstName(String firstName, String lastName);

Between

public List<Users> findByAgeBetween(int from, int to)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.age between 1? and 2?")
public List<Users> findByAgeBetween(int from, int to);

LessThan

public List<Users> findByAgeLessThan(int age)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.age < 1?")
public List<Users> findByAgeLessThan(int from, int to);

For dates, use Before:

public List<Users> findByRegisteredDateBefore(Date date)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.registeredDate < 1?")
public List<Users> findByRegisteredDateBefore(Date date);

LessThanEqual

public List<Users> findByAgeLessThanEqual(int age)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.age <= 1?")
public List<Users> findByAgeLessThanEqual(int age);

GreaterThan

public List<Users> findByAgeGreaterThan(int age)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.age > 1?")
public List<Users> findByAgeGreaterThan(int age);

For dates, use After:

public List<Users> findByRegisteredDateAfter(Date date)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.registeredDate > 1?")
public List<Users> findByRegisteredDateAfter(Date date);

GreaterThanEqual

public List<Users> findByAgeGreaterThanEqual(int age)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.age >= 1?")
public List<Users> findByAgeGreaterThanEqual(int age);

IsNull, Null

public List<Users> findByLocationIsNull()
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.location is null")
public List<Users> findByLocationIsNull();

IsNotNull, NotNull

public List<Users> findByLocationIsNotNull()
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.location is not null")
public List<Users> findByLocationIsNotNull();

Like

public List<Users> findByFirstNameLike(String firstName)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName like ?1")
public List<Users> findByFirstNameLike(String firstName);

To reuse parameters with like:

@Query(value="SELECT u FROM Users u where u.firstName like :text or u.lastName like :text")
public List<Users> findByFirstNameLike(@Param("text") String firstName);

NotLike

public List<Users> findByFirstNameNotLike(String firstName)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName not like ?1")
public List<Users> findByFirstNameNotLike(String firstName);

StartingWith

public List<Users> findByFirstNameStartingWith(String firstName)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName like ?1")
public List<Users> findByFirstNameStartingWith(String firstName);
//Append % to first name firstName = "Lance%"

EndingWith

public List<Users> findByFirstNameEndingWith(String firstName)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName like ?1")
public List<Users> findByFirstNameEndingWith(String firstName);
//Prepend % to first name firstName = "%Lance"

Containing

public List<Users> findByFirstNameContaining(String firstName)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName like ?1")
public List<Users> findByFirstNameEndingWith(String firstName);
//Wrap parameter in % firstName = "%Lance%"

OrderBy

public List<Users> findByLocationOrderByAgeDesc(String location)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.location = ?1 order by u.age desc")
public List<Users> findByLocationOrderByAgeDesc(String location);

Not

public List<Users> findByLocationNot(String location)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.location <> ?1")
public List<Users> findByLocationNot(String location);

In

public List<Users> findByLocationIn(Collection<String> locations)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.location in ?1")
public List<Users> findByLocationIn(Collection<String> locations);

NotIn

public List<Users> findByLocationNotIn(Collection<String> locations)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.location not in ?1")
public List<Users> findByLocationNotIn(Collection<String> locations);

True

public List<Users> findByVarifiedTrue()
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.varified = true")
public List<Users> findByVarifiedTrue();

False

public List<Users> findByVarifiedFalse()
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.varified = false")
public List<Users> findByVarifiedFalse();

IgnoreCase

public List<Users> findByFirstNameIgnoreCase(String firstName)
//Using @Query annotation
@Query(value="SELECT u FROM Users u where u.firstName = UPPER(?1)")
public List<Users> findByFirstNameIgnoreCase(String firstName);