Close

Using Querydsl in Spring Data JPA

[Updated: Jun 18, 2018, Created: Jun 17, 2018]

What is Querydsl?

Querydsl is a framework that supports statically typed queries. The framework uses an annotation processor to generate Java types based on JPA entities. (check out these tutorialsto understand Java annotation processor concepts).

QuerydslPredicateExecutor

Our Spring Data repository declaration needs to implement QuerydslPredicateExecutor which is Spring Data specific interface. This interface defines various query methods which allow execution of Querydsl for the provided com.querydsl.core.types.Predicate instances. Following is it's snippet:

package org.springframework.data.querydsl;
.....
public interface QuerydslPredicateExecutor<T> {
	Optional<T> findOne(Predicate predicate);
	Iterable<T> findAll(Predicate predicate);
	Iterable<T> findAll(Predicate predicate, Sort sort);
	Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders);
	Iterable<T> findAll(OrderSpecifier<?>... orders);
	Page<T> findAll(Predicate predicate, Pageable pageable);
	long count(Predicate predicate);
	boolean exists(Predicate predicate);
}

Example

Additional dependencies

pom.xml

<dependency>
   <groupId>com.querydsl</groupId>
   <artifactId>querydsl-apt</artifactId>
   <version>4.2.1</version>
</dependency>
<dependency>
   <groupId>com.querydsl</groupId>
   <artifactId>querydsl-jpa</artifactId>
   <version>4.2.1</version>
</dependency>

We also need to add following plugin to enable Querydsl annotation processor:

 <plugin>
    <groupId>com.mysema.maven</groupId>
    <artifactId>apt-maven-plugin</artifactId>
    <version>1.1.3</version>
    <executions>
        <execution>
            <goals>
                <goal>process</goal>
            </goals>
            <configuration>
                <outputDirectory>target/generated-sources/java</outputDirectory>
                <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
        </execution>
    </executions>
</plugin>

Entity

@Entity
public class Employee {
  private @Id
  @GeneratedValue
  Long id;
  private String name;
  private String dept;
  private int salary;
    .............
}

Repository

public interface EmployeeRepository extends CrudRepository<Employee, Long>,
      QuerydslPredicateExecutor<Employee> {
}

Compiling

Before we can use our Querydsl related repository methods, we need to compile our entity which will generate code (QEmployee will be generated for Employee entity).

mvn compile

We can also compile from IDE, I compiled it from Intellij:

QEmployee will be generated at following location:

Following is the generated class:

package com.logicbig.example;
.....
@Generated("com.querydsl.codegen.EntitySerializer")
public class QEmployee extends EntityPathBase<Employee> {

    private static final long serialVersionUID = 1792630833L;

    public static final QEmployee employee = new QEmployee("employee");

    public final StringPath dept = createString("dept");

    public final NumberPath<Long> id = createNumber("id", Long.class);

    public final StringPath name = createString("name");

    public final NumberPath<Integer> salary = createNumber("salary", Integer.class);

    public QEmployee(String variable) {
        super(Employee.class, forVariable(variable));
    }

    public QEmployee(Path<? extends Employee> path) {
        super(path.getType(), path.getMetadata());
    }

    public QEmployee(PathMetadata metadata) {
        super(Employee.class, metadata);
    }
}

Example Client

@Component
public class ExampleClient {

  @Autowired
  private EmployeeRepository repo;

  public void run() {
      List<Employee> employees = createEmployees();
      repo.saveAll(employees);
      System.out.println("-- employees persisted --");
      employees.forEach(System.out::println);

      System.out.println(" -- employees having salary greater than 3000 order by salary --");
      //BooleanExpression implements Predicate
      //goe='greater than or equals'
      BooleanExpression booleanExpression = QEmployee.employee.salary.goe(3000);
      OrderSpecifier<Integer> orderSpecifier = QEmployee.employee.salary.asc();
      Iterable<Employee> employees2 = repo.findAll(booleanExpression, orderSpecifier);
      employees2.forEach(System.out::println);

      System.out.println(" -- employees in IT and Admin depts and salary between 3000 and 5000 --");
      BooleanExpression booleanExpression2 = QEmployee.employee.dept.in("IT", "Admin").and(
              QEmployee.employee.salary.between(3000, 5000));
              Iterable<Employee> employee3 = repo.findAll(booleanExpression2);
      employee3.forEach(System.out::println);

      System.out.println(" -- find employee Mike --");
      BooleanExpression booleanExpression3 = QEmployee.employee.name.eq("Mike");
      Optional<Employee> opt = repo.findOne(booleanExpression3);
      System.out.println(opt.get());
  }

  private List<Employee> createEmployees() {
      return Arrays.asList(
              Employee.create("Diana", "Admin", 2000),
              Employee.create("Mike", "Sales", 1000),
              Employee.create("Rose", "IT", 4000),
              Employee.create("Sara", "Admin", 3500),
              Employee.create("Randy", "Sales", 3000),
              Employee.create("Charlie", "IT", 2500)
      );
  }
}

Main class

public class ExampleMain {

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      ExampleClient exampleClient = context.getBean(ExampleClient.class);
      exampleClient.run();
      EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
      emf.close();
  }
}
-- employees persisted --
Employee{id=1, name='Diana', dept='Admin', salary=2000}
Employee{id=2, name='Mike', dept='Sales', salary=1000}
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='Admin', salary=3500}
Employee{id=5, name='Randy', dept='Sales', salary=3000}
Employee{id=6, name='Charlie', dept='IT', salary=2500}
-- employees having salary greater than 3000 order by salary --
Employee{id=5, name='Randy', dept='Sales', salary=3000}
Employee{id=4, name='Sara', dept='Admin', salary=3500}
Employee{id=3, name='Rose', dept='IT', salary=4000}
-- employees in IT and Admin depts and salary between 3000 and 5000 --
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='Admin', salary=3500}
-- find employee Mike --
Employee{id=2, name='Mike', dept='Sales', salary=1000}

Example Project

Dependencies and Technologies Used:

  • spring-data-jpa 2.0.7.RELEASE: Spring Data module for JPA repositories.
    Uses org.springframework:spring-context version 5.0.6.RELEASE
  • hibernate-core 5.3.1.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • h2 1.4.197: H2 Database Engine.
  • querydsl-apt 4.2.1: APT based Source code generation for Querydsl.
  • querydsl-jpa 4.2.1: JPA support for Querydsl.
  • JDK 1.8
  • Maven 3.3.9

Spring Data JPA - Querydsl Example Select All Download
  • spring-data-jpa-query-dsl
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleClient.java
          • resources
            • META-INF

    See Also