announcement-icon.png

JPA can behave very differently depending on the exact circumstances under which it is used. Code that works in our local environment or in staging performs very poorly (or even flat out fails) when thrown against real-scale databases in production environments.

Debugging these JPA issues in production is pretty difficult - existing APMs don’t provide enough granular insights at the code level, and tracking every single place someone queried entities one by one instead of in bulk can be a grueling, time-consuming task.

Lightrun is a new approach to debugging in production. Using Lightrun’s Logs and Snapshots, you can now get debugger-level granularity in production without opening inbound ports, redeploying, restarting, or even stropping the running application.

In addition, instrumenting Lightrun Metrics at runtime allows you to track down persistence issues securely and in real-time. Want to see it in action? Check out our 2-minute tutorial for debugging JPA performance issues in production using Lightrun:

>> Debugging Spring Persistence and JPA Issues Using Lightrun

1. Overview

Querydsl is an extensive Java framework, which helps with creating and running type-safe queries in a domain specific language that is similar to SQL.

In this article we'll explore Querydsl with the Java Persistence API.

A quick side note here is that HQL for Hibernate was the first target language for Querydsl, but nowadays it supports JPA, JDO, JDBC, Lucene, Hibernate Search, MongoDB, Collections and RDFBean as backends.

2. Preparations

Let's first add the necessary dependencies into our Maven project:

<properties>
    <querydsl.version>2.5.0</querydsl.version>
</properties>

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <version>${querydsl.version}</version>
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <version>${querydsl.version}</version>
</dependency>

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.6.1</version>
</dependency>

And now let's configure the Maven APT plugin:

<project>
    <build>
    <plugins>
    ...
    <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</outputDirectory>
                <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
        </execution>
        </executions>
    </plugin>
    ...
    </plugins>
    </build>
</project>

The JPAAnnotationProcessor will find domain types annotated with javax.persistence.Entity annotation and generates query types for them.

3. Queries With Querydsl

Queries are constructed based on generated query types that reflect the properties of your domain types. Also function/method invocations are constructed in a fully type-safe manner.

The query paths and operations are the same in all implementations and also the Query interfaces have a common base interface.

3.1. An Entity and the Querydsl Query Type

Let's first define a simple entity we're going to make use of as we go through examples:

@Entity
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column
    private String firstname;

    @Column
    private String surname;
    
    Person() {
    }

    public Person(String firstname, String surname) {
        this.firstname = firstname;
        this.surname = surname;
    }

    // standard getters and setters

}

Querydsl will generate a query type with the simple name QPerson into the same package as Person. QPerson can be used as a statically typed variable in Querydsl queries as a representative for the Person type.

First – QPerson has a default instance variable which can be accessed as a static field:

QPerson person = QPerson.person;

Alternatively you can define your own Person variables like this:

QPerson person = new QPerson("Erich", "Gamma");

3.2. Build Query Using JPAQuery

We can now use JPAQuery instances for our queries:

JPAQuery query = new JPAQuery(entityManager);

Note that the entityManager is a JPA EntityManager.

Let's now retrieve all the persons with the first name “Kent” as a quick example:

QPerson person = QPerson.person;
List<Person> persons = query.from(person).where(person.firstName.eq("Kent")).list(person);

The from call defines the query source and projection, the where part defines the filter and list tells Querydsl to return all matched elements.

We can also use multiple filters:

query.from(person).where(person.firstName.eq("Kent"), person.surname.eq("Beck"));

Or:

query.from(person).where(person.firstName.eq("Kent").and(person.surname.eq("Beck")));

In native JPQL form the query would be written like this:

select person from Person as person where person.firstName = "Kent" and person.surname = "Beck"

If you want to combine the filters via “or” then use the following pattern:

query.from(person).where(person.firstName.eq("Kent").or(person.surname.eq("Beck")));

4. Ordering and Aggregation in Querydsl

Let's now have a look at how ordering and aggregation work within the Querydsl library.

4.1. Ordering

We'll start by ordering our results in descending order by the surname field:

QPerson person = QPerson.person;
List<Person> persons = query.from(person)
    .where(person.firstname.eq(firstname))
    .orderBy(person.surname.desc())
    .list(person);

4.2. Aggregation

Let's now use a simple aggregation, as we do have a few available (Sum, Avg, Max, Min):

QPerson person = QPerson.person;    
int maxAge = query.from(person).list(person.age.max()).get(0);

4.3. Aggregation With GroupBy

The com.mysema.query.group.GroupBy class provides aggregation functionality which we can use to aggregate query results in memory.

Here's a quick example where the result are returned as Map with firstname as the key and max age as the value:

QPerson person = QPerson.person;   
Map<String, Integer> results = 
  query.from(person).transform(
      GroupBy.groupBy(person.firstname).as(GroupBy.max(person.age)));

5. Testing With Querydsl

Now, let's define a DAO implementation using Querydsl – and let's define the following search operation:

public List<Person> findPersonsByFirstnameQuerydsl(String firstname) {
    JPAQuery query = new JPAQuery(em);
    QPerson person = QPerson.person;
    return query.from(person).where(person.firstname.eq(firstname)).list(person);
}

And now let's build a few tests using this new DAO and let's use Querydsl to search for newly created Person objects (implemented in PersonDao class) and in another test aggregation using GroupBy class is tested:

@Autowired
private PersonDao personDao;

@Test
public void givenExistingPersons_whenFindingPersonByFirstName_thenFound() {
    personDao.save(new Person("Erich", "Gamma"));
    Person person = new Person("Kent", "Beck");
    personDao.save(person);
    personDao.save(new Person("Ralph", "Johnson"));

    Person personFromDb =  personDao.findPersonsByFirstnameQuerydsl("Kent").get(0);
    Assert.assertEquals(person.getId(), personFromDb.getId());
}

@Test
public void givenExistingPersons_whenFindingMaxAgeByName_thenFound() {
    personDao.save(new Person("Kent", "Gamma", 20));
    personDao.save(new Person("Ralph", "Johnson", 35));
    personDao.save(new Person("Kent", "Zivago", 30));

    Map<String, Integer> maxAge = personDao.findMaxAgeByName();
    Assert.assertTrue(maxAge.size() == 2);
    Assert.assertSame(35, maxAge.get("Ralph"));
    Assert.assertSame(30, maxAge.get("Kent"));
}

6. Conclusion

This tutorial illustrated how to build JPA project using Querydsl.

The full implementation of this article can be found in the github project – this is an Eclipse based maven project, so it should be easy to import and run as it is.

A quick note here is – run a simple maven build (mvn clean install) to generate the types into target/generated-sources – and then, if you're using Eclipse – include the folder as a source folder of the project.

Persistence bottom
Get started with Spring Data JPA through the reference Learn Spring Data JPA course: >> CHECK OUT THE COURSE
Persistence footer banner
2 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Comments are closed on this article!