2. Tutorials

Add the following dependencies to your Maven project:

<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>

And now, 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/java</outputDirectory>
            <processor>com.querydsl.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.querydsl.apt.hibernate.HibernateAnnotationProcessor instead.

Run clean install and you will get your Query types generated into target/generated-sources/java.

If you use Eclipse, run mvn eclipse:eclipse to update your Eclipse project to include target/generated-sources/java as a source folder.

Now you are able to construct JPA query instances and instances of the query domain model.

Place the jar files from the full-deps bundle on your classpath and use the following tasks for Querydsl code generation:

    <!-- APT based code generation -->
    <javac srcdir="${src}" classpathref="cp">
      <compilerarg value="-proc:only"/>
      <compilerarg value="-processor"/>
      <compilerarg value="com.querydsl.apt.jpa.JPAAnnotationProcessor"/>
      <compilerarg value="-s"/>
      <compilerarg value="${generated}"/>
    </javac>

    <!-- compilation -->
    <javac classpathref="cp" destdir="${build}">
      <src path="${src}"/>
      <src path="${generated}"/>
    </javac>

Replace src with your main source folder, generated with your folder for generated sources and build with your target folder.

If you are using Querydsl JPA with Spring Roo you can replace com.querydsl.apt.jpa.JPAAnnotationProcessor with com.querydsl.apt.roo.RooAnnotationProcessor which will handle @RooJpaEntity and @RooJpaActiveRecord annotated classes instead of @Entity annotated classes.

APT based code generation doesn't work well with AspectJ IDTs.

If you are using Hibernate with an XML based configuration, you can use the XML metadata to create your Querydsl model.

com.querydsl.jpa.codegen.HibernateDomainExporter provides the functionality for this:

HibernateDomainExporter exporter = new HibernateDomainExporter(
  "Q",                     // name prefix
  new File("target/gen3"), // target folder
  configuration);          // instance of org.hibernate.cfg.Configuration

exporter.export();

The HibernateDomainExporter needs to be executed within a classpath where the domain types are visible, since the property types are resolved via reflection.

All JPA annotations are ignored, but Querydsl annotations such as @QueryInit and @QueryType are taken into account.

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");

The Querydsl JPA module supports both the JPA and the Hibernate API.

To use the JPA API you use JPAQuery instances for your queries like this:

// where entityManager is a JPA EntityManager
JPAQuery<?> query = new JPAQuery<Void>(entityManager);

If you are using the Hibernate API instead, you can instantiate a HibernateQuery like this:

// where session is a Hibernate session
HibernateQuery<?> query = new HibernateQuery<Void>(session);

Both JPAQuery and HibernateQuery implement the JPQLQuery interface.

For the examples of this chapter the queries are created via a JPAQueryFactory instance. JPAQueryFactory should be the preferred option to obtain JPAQuery instances.

For the Hibernate API HibernateQueryFactory can be used

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

QCustomer customer = QCustomer.customer;
Customer bob = queryFactory.selectFrom(customer)
  .where(customer.firstName.eq("Bob"))
  .fetchOne();

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

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

QCustomer customer = QCustomer.customer;
QCompany company = QCompany.company;
query.from(customer, company);

And to use multiple filters use it like this

queryFactory.selectFrom(customer)
    .where(customer.firstName.eq("Bob"), customer.lastName.eq("Wilson"));

Or like this

queryFactory.selectFrom(customer)
    .where(customer.firstName.eq("Bob").and(customer.lastName.eq("Wilson")));

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

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

If you want to combine the filters via "or" then use the following pattern

queryFactory.selectFrom(customer)
    .where(customer.firstName.eq("Bob").or(customer.lastName.eq("Wilson")));

Querydsl supports the following join variants in JPQL: inner join, join, left join and right join. Join usage is typesafe, and follows the following pattern:

QCat cat = QCat.cat;
QCat mate = new QCat("mate");
QCat kitten = new QCat("kitten");
queryFactory.selectFrom(cat)
    .innerJoin(cat.mate, mate)
    .leftJoin(cat.kittens, kitten)
    .fetch();

The native JPQL version of the query would be

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

Another example

queryFactory.selectFrom(cat)
    .leftJoin(cat.kittens, kitten)
    .on(kitten.bodyWeight.gt(10.0))
    .fetch();

With the following JPQL version

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

Use the the cascading methods of the JPQLQuery interface like this

select: Set the projection of the query. (Not necessary if created via query factory)

from: Add the query sources here.

innerJoin, join, leftJoin, rightJoin, on: Add join elements using these constructs. For the join methods the first argument is the join source and the second the target (alias).

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

groupBy: Add group by arguments in varargs form.

having: Add having filters of the "group by" grouping as an varags array of Predicate expressions.

orderBy: Add 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: Set the paging of the result. Limit for max results, offset for skipping rows and restrict for defining both in one call.

The syntax for declaring ordering is

QCustomer customer = QCustomer.customer;
queryFactory.selectFrom(customer)
    .orderBy(customer.lastName.asc(), customer.firstName.desc())
    .fetch();

which is equivalent to the following native JPQL

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

Grouping can be done in the following form

queryFactory.select(customer.lastName).from(customer)
    .groupBy(customer.lastName)
    .fetch();

which is equivalent to the following native JPQL

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

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

QCustomer customer = QCustomer.customer;
// delete all customers
queryFactory.delete(customer).execute();
// delete all customers with a level less than 3
queryFactory.delete(customer).where(customer.level.lt(3)).execute();

The where call is optional and the execute call performs the deletion and returns the amount of deleted entities.

DML clauses in JPA don't take JPA level cascade rules into account and don't provide fine-grained second level cache interaction.

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

QCustomer customer = QCustomer.customer;
// rename customers named Bob to Bobby
queryFactory.update(customer).where(customer.name.eq("Bob"))
    .set(customer.name, "Bobby")
    .execute();

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.

DML clauses in JPA don't take JPA level cascade rules into account and don't provide fine-grained second level cache interaction.

To create a subquery you use the static factory methods of JPAExpressions and define the query parameters via from, where etc.

QDepartment department = QDepartment.department;
QDepartment d = new QDepartment("d");
queryFactory.selectFrom(department)
    .where(department.size.eq(
        JPAExpressions.select(d.size.max()).from(d)))
     .fetch();

Another example

QEmployee employee = QEmployee.employee;
QEmployee e = new QEmployee("e");
queryFactory.selectFrom(employee)
    .where(employee.weeklyhours.gt(
        JPAExpressions.select(e.weeklyhours.avg())
            .from(employee.department.employees, e)
            .where(e.manager.eq(employee.manager))))
    .fetch();

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

Query jpaQuery = queryFactory.selectFrom(employee).createQuery();
// ...
List results = jpaQuery.getResultList();

Querydsl supports Native SQL in JPA via the JPASQLQuery class.

To use it, you must generate Querydsl query types for your SQL schema. This can be done for example with the following Maven configuration:

<project>
  <build>
    <plugins>
      ...
      <plugin>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-maven-plugin</artifactId>
        <version>${querydsl.version}</version>
        <executions>
          <execution>
            <goals>
              <goal>export</goal>
            </goals>
          </execution>
        </executions>
        <configuration>
          <jdbcDriver>org.apache.derby.jdbc.EmbeddedDriver</jdbcDriver>
          <jdbcUrl>jdbc:derby:target/demoDB;create=true</jdbcUrl>
          <packageName>com.mycompany.mydomain</packageName>
          <targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
        </configuration>
        <dependencies>
          <dependency>
            <groupId>org.apache.derby</groupId>
            <artifactId>derby</artifactId>
            <version>${derby.version}</version>
          </dependency>
        </dependencies>
      </plugin>
      ...
    </plugins>
  </build>
</project>

When the query types have successfully been generated into the location of your choice, you can use them in your queries.

Single column query:

// serialization templates
SQLTemplates templates = new DerbyTemplates();
// query types (S* for SQL, Q* for domain types)
SAnimal cat = new SAnimal("cat");
SAnimal mate = new SAnimal("mate");
QCat catEntity = QCat.cat;

JPASQLQuery<?> query = new JPASQLQuery<Void>(entityManager, templates);
List<String> names = query.select(cat.name).from(cat).fetch();

If you mix entity (e.g. QCat) and table (e.g. SAnimal) references in your query you need to make sure that they use the same variable names. SAnimal.animal has the variable name "animal", so a new instance (new SAnimal("cat")) was used instead.

An alternative pattern could be

QCat catEntity = QCat.cat;
SAnimal cat = new SAnimal(catEntity.getMetadata().getName());

Query multiple columns:

query = new JPASQLQuery<Void>(entityManager, templates);
List<Tuple> rows = query.select(cat.id, cat.name).from(cat).fetch();

Query all columns:

List<Tuple> rows = query.select(cat.all()).from(cat).fetch();
 

Query in SQL, but project as entity:

query = new JPASQLQuery<Void>(entityManager, templates);
List<Cat> cats = query.select(catEntity).from(cat).orderBy(cat.name.asc()).fetch();

Query with joins:

query = new JPASQLQuery<Void>(entityManager, templates);
cats = query.select(catEntity).from(cat)
    .innerJoin(mate).on(cat.mateId.eq(mate.id))
    .where(cat.dtype.eq("Cat"), mate.dtype.eq("Cat"))
    .fetch();

Query and project into DTO:

query = new JPASQLQuery<Void>(entityManager, templates);
List<CatDTO> catDTOs = query.select(Projections.constructor(CatDTO.class, cat.id, cat.name))
    .from(cat)
    .orderBy(cat.name.asc())
    .fetch();

If you are using the Hibernate API instead of the JPA API, then use HibernateSQLQuery instead.