2.2. Querying JPA/Hibernate sources

Querydsl defines a general statically typed syntax for querying on top of persisted domain model data. JDO and JPA are the primary integration technologies for Querydsl. This guide describes how to use Querydsl in combination with JPA/Hibernate.

Querydsl for JPA/Hibernate is an alternative to both HQL and Criteria queries. It combines the dynamic nature of Criteria queries with the expressiveness of HQL and all that in a fully typesafe manner.

2.2.1. Maven integration

Add the following dependencies to your Maven project and make sure that the Maven 2 repo of Mysema Source (http://source.mysema.com/maven2/releases) is accessible from your POM :

<dependency>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-apt</artifactId>
  <version>0.5.4</version>
  <scope>provided</scope>
</dependency>    
    
<dependency>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-hql</artifactId>
  <version>0.5.4</version>
</dependency>

And now, configure the Maven APT plugin :

<project>
  <build>
  <plugins>
    ...
    <plugin>
      <groupId>com.mysema.maven</groupId>
      <artifactId>maven-apt-plugin</artifactId>
      <version>0.3.0</version>
      <executions>
        <execution>
          <goals>
            <goal>process</goal>
          </goals>
          <configuration>
            <outputDirectory>target/generated-sources/java</outputDirectory>
            <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
          </configuration>
        </execution>
      </executions>
    </plugin>
    ...
  </plugins>
  </build>
</project>

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

If you use Hibernate annotations in your domain types you should use the APT processor com.mysema.query.apt.hibernate.HibernateAnnotationProcessor> instead.

Run mvn eclipse:eclipse or clean install and you will get your Query types generated into target/generated-sources/java. Now you are able to construct JPAQL/HQL query instances and instances of the query domain model.

2.2.2. Using query types

To create queries with Querydsl you need to instantiate variables and Query implementations. We will start with the variables.

Let's assume that your project has the following domain type :

@Entity
public class Customer {
    private String firstName;
    private String lastName;

    public String getFirstName(){
        return firstName;
    }

    public String getLastName(){
        return lastName;
    }

    public void setFirstName(String fn){
        firstName = fn;
    }

    public void setLastName(String ln)[
        lastName = ln;
    }
}

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

QCustomer has a default instance variable which can be accessed as a static field :

QCustomer customer = QCustomer.customer;

Alternatively you can define your own Customer variables like this :

QCustomer customer = new QCustomer("myCustomer");

2.2.3. Querying with HQL

For the HQL-module HibernateQuery is the main Query implementation. It is instantiated like this :

// where session is a Hibernate session
HQLQuery query = new HibernateQuery (session); 

To use the JPA API instead of the Hibernate API, you can instantiate a HQLQuery like this :

// where entityManager is a JPA EntityManager   
HQLQuery query = new JPAQuery (entityManager); 

To retrieve the customer with the first name Bob you would construct a query like this :

QCustomer customer = QCustomer.customer;
HQLQuery query = new HibernateQuery (session);
Customer bob = query.from(customer)
  .where(customer.firstName.eq("Bob"))
  .uniqueResult(customer);

The from call defines the query source, the where part defines the filter and uniqueResult defines the projection and tells Querydsl to return a single element. Easy, right?

To create a query with multiple sources you just use the HQLQuery interface like this :

query.from(customer, company);    

And to use multiple filters use it like this

query.from(customer)
    .where(customer.firstName.eq("Bob"), customer.lastName.eq("Wilson"));   

Or like this

query.form(customer)
    .where(customer.firstName.eq("Bob").and(customer.lastName.eq("Wilson")));

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

from Customer as customer
    where customer.firstName = "Bob" and customer.lastName = "Wilson"

2.2.4. Using joins

Querydsl supports the following join variants in HQL/JPAQL : inner join, join, left join and full join. Join usage is typesafe, and follows the following pattern :

  
query.from(cat)
    .innerJoin(cat.mate, mate)
    .leftJoin(cat.kittens, kitten)
    .list(cat);

The native HQL version of the query would be

from Cat as cat
    inner join cat.mate as mate
    left outer join cat.kittens as kitten

Another example

 
query.from(cat)
    .leftJoin(cat.kittens, kitten)
    .on(kitten.bodyWeight.gt(10.0))
    .list(cat);

With the following HQL version

  
from Cat as cat
    left join cat.kittens as kitten
    with kitten.bodyWeight > 10.0  

2.2.5. General usage

Use the the cascading methods of the HQLQuery method like this

from : Define the query sources here.

innerJoin, join, leftJoin, fullJoin, on : Defined join elements using these constructs. For the join methods the first argument is the join expression and the second the shorter alias.

where : Define the query filters, either in varargs form separated via commas or cascaded via the and-operator.

groupBy : Define the group by arguments in varargs form.

having : Define the having filter of the "group by" grouping as an varags array of EBoolean expressions.

orderBy : Define the ordering of the result as an varargs array of order expressions. Use asc() and desc() on numeric, string and other comparable expression to access the OrderSpecifier instances.

limit, offset, restrict : Define the paging of the result. Limit for max results, offset for skipping rows and restrict for defining both in one call.

2.2.6. Ordering

The syntax for declaring ordering is

 
query.from(customer)
    .orderBy(customer.lastName.asc(), customer.firstName.desc())
    .list(customer);

which is equivalent to the following native HQL

  
from Customer as customer
    order by customer.lastName asc, customer.firstName desc

2.2.7. Grouping

Grouping can be done in the following form

 
query.from(customer)
    .groupBy(customer.lastName)
    .list(customer.lastName);

which is equivalent to the following native HQL

  
select customer.lastName
    from Customer as customer
    group by customer.lastName

2.2.8. Delete clauses

Delete clauses in Querydsl HQL follow a simple delete-where-execute form. Here are some examples :

   
QCat cat = QCat.cat;
// delete all cats
new HibernateDeleteClause(session, cat).execute();
// delete all cats with kittens
new HibernateDeleteClause(session, cat).where(cat.kittens.isNotEmpty()).execute();  

The second parameter of the HibernateDeleteClause constructor is the entity to be deleted. The where call is optional and the execute call performs the deletion and returns the amount of deleted entities.

For JPA based Delete usage, use the JPADeleteClause instead.

2.2.9. Update clauses

Update clauses in Querydsl HQL follow a simple update-set/where-execute form. Here are some examples :

   
QCat cat = QCat.cat;
// rename cats named Bob to Bobby
new HibernateUpdateClause(session, cat).where(cat.name.eq("Bob"))
    .set(cat.name, "Bobby")
    .execute();  

The second parameter of the HibernateUpdateClause constructor is the entity to be updated. The set invocations define the property updates in SQL-Update-style and the execute call performs the Update and returns the amount of updated entities.

For JPA based Update usage, use the JPAUpdateClause instead.

2.2.10. Subqueries

To create a subquery you create a HQLSubQuery instance, define the query parameters via from, where etc and use unique or list to create a subquery, which is just a type-safe Querydsl expression for the query. unique is used for a unique (single) result and list for a list result.

query().from(department)
    .where(department.employees.size().eq(
        new HQLSubQuery().from(d).unique(d.employees.size().max())
     )).list(department);

Another example

query().from(employee)
    .where(employee.weeklyhours.gt(
        new HQLSubQuery().from(employee.department.employees, e)
        .where(e.manager.eq(employee.manager))
        .unique(AggregationFunctions.avg(e.weeklyhours))
    )).list(employee);

2.2.11. Exposing the original query

If you need to do tune the original Query before the execution of the query you can expose it like this :

HibernateQuery query = new HibernateQuery(session);
org.hibernate.Query hibQuery = query.from(employee).createQuery(employee);
hibQuery.setResultTransformer(someTransformer);
List results = hibQuery.list();