Spring Data JPARepository and QueryDsl

In a recent project, we made use of the CrudRepository (The hibernate JPA implementation of Spring Data JPA), along with QueryDsl.  We found it very easy to setup, and enjoyed the simplicity of our data access.  These are the steps for setting it up, and how we made use of it.

Making use of CrudRepository

1. Include the dependency in your pom.xml


2. Spring setup for creating proxy instances.

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns:beans="http://www.springframework.org/schema/beans"

  <repositories base-package="ca.project.repositories" />


Spring needs to know which package(s) to look for repositories in.  It will then find all the repositories and create proxies for them.

3. Create an interface with extends CrudRepository

public interface DogRepository extends CrudRepository<Dog, Long> {

Now you have the standard crud methods available to you:

  • save
  • findById
  • findAll
  • count
  • delete
  • exists

Customizing your repository

To your interface class, you can also add custom query methods.  The queries are generated based on the method name.  At runtime, Spring Data will generate implementations for these.

//Find by breedname property, with the case being ignored.
List<Dog> findByBreednameIgnoreCase(String breedname);
//Find by nested property. Using the Address from the Owner Class
List<Dog> findByOwnerAddress(String address);
List<Dog> findByOwner_Address(String address); //Same as above
//Too complicated but works
List<Dog> findByDognameOrOwnerAddressAndYearOfBirthGreaterThanOrderByYearOfBirthDesc(String dogname, String address, int minYearOfBirth);

These are extremely quick to write, and are great for simple lookups.  But can get difficult to read when using multiple properties.  Any errors in the name, are not reported, until runtime (usually during startup).  Limiting the number of these could be a good idea.  See the section below on QueryDsl for Simple Queries for a nice alternative.

Sorting & Paging

Extending the PagingAndSortingRepository instead of the CrudRepository provides a simple way of limiting the number of results, and ordering them.

public interface OwnerRepository extends PagingAndSortingRepository<Owner, Long> {
  List<Owner> findByAddress(String address, Sort sort);
  List<Owner> findByLastname(String lastname, Pageable pageable);

The findAll method will also accept the pageable & sort parameters.


There are many ways of handling more complex queries.  We started by using the @Query annotation from Spring Data JPA.  It works like

@Query("from Dog d where d.dogname is not in(?) order by d.birthday")
List<Dog> findOtherDogs(List<String> dognames);

Writing the code isn’t as fast, as having the query be based off the method name.  And there was still no compile time checking.  For these reasons, we switched to QueryDsl, for any queries that couldn’t be trivially expressed by method name.  This gave us compile time checking, for our queries.  And allowed us to programmatically generate our predicates.  And kept them elegant.

What you need for QueryDsl:

1. The dependencies


2. A lovely eclipse plugin for keeping the generated sources up to date


QueryDsl for Simple Queries

Now that you have QueryDsl available to you.  You can use it to produce some nice and simple queries.  Due to it’s flexibility, I prefer this to defining methods for query generation by name (above).  QueryDsl has a great benefit that the generated Q classes, will ensure compile time error checking of your queries.  It also makes junit testing a breeze, as you just need to verify that your passed in predicate meets your requirements.

Have your DogRepository extend the QueryDslPredicateExecutor.

public interface DogRepository extends CrudRepository<Dog, Long>, QueryDslPredicateExecutor<Dog>{

The following methods now become available to you:

Dog findOne(Predicate predicate);
 Iterable<Dog> findAll(Predicate predicate);
 Iterable<Dog> findAll(Predicate predicate, OrderSpecifier<?>... orders);
 Page<Dog> findAll(Predicate predicate, Pageable pageable);
 long count(Predicate predicate);

Here is an example of their use

QDog q = QDog.dog;
Predicate predicate = q.dogname.eq(dogname).or(q.dogYearOfBirth.gt(2012));
repository.findAll(predicate, new PageRequest(3, 10)));

Now you can start defining reusable predicates and clean the above up to be

repository.findAll(getNamePredicate(dogname).or(getYearOfBirthGtPredicate(2012), new PageRequest(3,10));

QueryDsl with Custom Repositories for complex queries

It would be lovely if the world kept all our queries simple.  However, sometimes it becomes necessary to do multiple joins etc.  For these cases, we generated custom repositories.  Here is an example

public interface DogRepository extends PagingAndSortingRepository<Dog, Long>, DogRepositoryCustom {
public interface DogRepositoryCustom {
 List<DogIdentifiesDTO> findDogIdentifiers(List<String> dognames);
@Transactional(readOnly = true)
public class DogRepositoryImpl implements DogRepositoryCustom {

 private EntityManager entityManager;

 public List<DogIdentifiesDTO> findDogIdentifiers(List<String> dognames) {
    JPAQuery query = new JPAQuery(entityManager);
    return query.list(new DogIdentifiesDTO(QDog.dog.id, QDog.dog.refCode));
It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn