Querydsl via JPA and Gradle

1. Owerview

Querydsl provides a typesafe querying on top of JPA. It is promotes as an alternative to both JPQL and Creteria Queries. Querydsl is distributed under Apach Licence 2.0 and has own community with current activity. StackOwerflow search engine gives 2,383 results on "querydsl" search. To compare "jpql" returns 6,774 and "criteria api" 5,731 results. Latest release for current time is 4.1.6 (30 June 2016). Querydsl has breaking changes not only in version 4.x, but from 3.6 to 3.7 too. Code examples in this article for version 3.6.9 if other not specified.

Small historic break. When I came to Java from .Net world I spent many houres to find eqivalent for LINQ to SQL. Indeed there are plenty of them both opensource and proprietary: jOOQ, JINQ, JaQue, JaQu, Linq4j, Quaere, JSR-341 etc. I strongly tried Jinq, but was stopped when queries became complicated. Framework was quite raw (2014) and its knowlege base was small. In my opinion relativly narrow knowlege base and community are the weekest parts of Querydsl project.

2. Test database configuration

Simple in-memory database was created.

3. JPQL, Criteria API and Querydsl comparision

JPQL and Criteria API are standart JPA ways to work with persistent entities:

	pros	cons
JPQL	expressiveness (SQL like) good choice for static queries	doesn't fit for dynamic queries doesn't provide code completion  in IDE easy to write syntactically invalid code
Criteria API	dynamic	too verbose

In my opinion Querydsl is trying to take place between those methods. It combines static typing, IDE code completeon, dynamic futures and it still SQL like. Querydsl appears as next layer on top of existing ORMs (implementations of JPA e.g.).

I create small gradle project with in-memory database to demonstrate it. Database contains only two tables Owner and Cat with one-to-many relationship. All code snippets do the same:  find Cats with Owner named Bill. They produce absolutly identical sql request to db. Generated sql queries are logged to console on application run.
JPQL:

TypedQuery queryJpql = em.createQuery(
        "SELECT c FROM Cat c JOIN c.owner o WHERE o.name=:ownerName", Cat.class);
queryJpql.setParameter("ownerName", "Bill");
List catsJpql = queryJpql.getResultList();

Querydsl:

JPAQuery queryFactory = new JPAQuery(em);
QCat cat = QCat.cat;
QOwner owner = QOwner.owner;
List catsQdsl = queryFactory.from(cat)
        .join(cat.owner, owner)
        .where(owner.name.eq("Bill"))
        .list(cat);

Criteria API:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery criteriaQuery = cb.createQuery(Cat.class);
Root catQ = criteriaQuery.from(Cat.class);
Join ownerQ = catQ.join(Cat_.owner);
criteriaQuery.where(cb.equal(ownerQ.get(Owner_.name), "Bill"));
TypedQuery query = em.createQuery(criteriaQuery);
List catsCrQ = query.getResultList();

4. Querydsl metamodel

Queries are constructed based on generated query types that reflect the properties of your domain types. Just like the Criteria API the JPAAnnotationProcessor finds domain types annotated with the javax.persistence.Entity annotation and generates query types (wich stands for querydsl meta-model objects) for them. For example consider class Cat

Cannonical generated meta-model looks like:

public abstract class Cat_ {
   public static volatile SingularAttribute owner;
   public static volatile SingularAttribute name;
   public static volatile SingularAttribute id;
   public static volatile SingularAttribute age;
}

Querydsl generated Q-types looks different and provides a much more approachable API. This classes implement functionality equals Criteria's CriteriaBuilder. It makes code more clean and readable. QCat can be used as a statically typed variable in Querydsl queries as a representative for the Cat type.

public class QCat extends EntityPathBase {
    private static final long serialVersionUID = 1195528346L;
    private static final PathInits INITS = PathInits.DIRECT2;
    public static final QCat cat = new QCat("cat");
    public final NumberPath age = createNumber("age", Integer.class);
    public final NumberPath id = createNumber("id", Integer.class);
    public final StringPath name = createString("name");
    public final QOwner owner;
    public QCat(String variable) {
        this(Cat.class, forVariable(variable), INITS);
    }
    public QCat(Path<? extends Cat> path) {
        this(path.getType(), path.getMetadata(), path.getMetadata().isRoot() ? INITS : PathInits.DEFAULT);
    }
    public QCat(PathMetadata<?> metadata) {
        this(metadata, metadata.isRoot() ? INITS : PathInits.DEFAULT);
    }
    public QCat(PathMetadata<?> metadata, PathInits inits) {
        this(Cat.class, metadata, inits);
    }
    public QCat(Class<? extends Cat> type, PathMetadata<?> metadata, PathInits inits) {
        super(type, metadata, inits);
        this.owner = inits.isInitialized("owner") ? new QOwner(forProperty("owner")) : null;
    }
}

5. General usage

5.1 Cascading methods

In general Querydsl using the cascading methods pipline.
from: Add the query sources here.
innerJoin, join, leftJoin, fullJoin, 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.

We use JPAQuery instances for our queries:

JPAQuery queryFactory = new JPAQuery(em);

Note that the em is a JPA EntityManager. Main query chain pretty SQL like. For example filter by joined table field.

List catsQdsl = queryFactory.from(cat)
        .join(cat.owner, owner)
        .where(owner.name.eq("Bill"))
        .list(cat);

Method .list(cat) define query projection. It's possible to return only Cat names.

List<String> catNames = queryFactory.from(cat).join(cat.owner, owner)
        .where(owner.name.eq("Bill")).list(cat.name);

Note that in version 4.x query structure was changed and now projection is defined by .select method.

List<String> catNames = queryFactory.select(cat.name).from(cat).join(cat.owner, owner)
        .where(owner.name.eq("Bill")).fetch();

5.2 Aggregation

Lets add more examples of Querydsl usage. Now some aggregate functions.

Find max cat age:

Integer maxAge = queryFactory.from(cat)
        .singleResult(cat.age.max());

Find max cat age grouped by name. The com.mysema.query.group.GroupBy class provides aggregation functionality which can be used to aggregate query results in memory.

Map results = queryFactory.from(cat)
        .transform(GroupBy.groupBy(cat.name).as(GroupBy.max(cat.age)));

Count cats by owner:

List results = queryFactory.from(cat)
        .join(cat.owner, owner)
        .groupBy(owner)
        .orderBy(owner.name.asc())
        .list(owner.name, cat.count());

5.3 Ordering

Order by own field.

List cats = queryFactory.from(cat)
        .orderBy(cat.name.asc())
        .list(cat);

Order by joined entity field.

List cats = queryFactory.from(cat)
        .orderBy(cat.owner.name.asc())
        .list(cat);

Note that according to documentation default allowed depth equals 2. Special annotations is used If you requre more.

5.4 Filter by field in one-to-many relationship and distinct

There are duplicates in result, caused by 2 Bill's cats. The simplest way to avoid it is to use DISTINCT.

List ownersDistinct = queryFactory
        .distinct()
        .from(owner)
        .innerJoin(owner.cats, cat)
        .where(cat.name.eq("Method"))
        .list(owner);

But it is unefficient way. Possible solution is to use EXISTS clause

com.mysema.query.types.Predicate catNamePredicate = new JPASubQuery()
        .from(cat).where(cat.owner.eq(owner).and(cat.name.eq("Method"))).exists();
List owners = queryFactory
        .from(owner)
        .where(catNamePredicate)
        .list(owner);

5.5 Dynamic conditions and paging

Often condition depends on user input. Querydsl provides handy approaches in this case.

List predicates = new ArrayList<>();
if (true) {
    predicates.add(cat.name.like("%ge%"));
}
if (true) {
    predicates.add(cat.age.eq(2));
}
com.mysema.query.types.Predicate where = ExpressionUtils.allOf(predicates);
JPAQuery queryFactory = new JPAQuery(em);
List catNames = queryFactory.from(cat)
        .where(where)
        .restrict(new QueryModifiers(10L, 1L))
        .orderBy(cat.name.asc(), cat.id.asc())
        .list(cat.name);

6. Conclusion

This post describe pros and cons Querydsl compare to standart  JPA queries. Also some usage exemples are added.

The full implementation can be found in the github project – this is an Gradle based project, so it should be easy to import and run as it is. In-memory database H2 is used so you need only locally installed Gradle. 

A quick note here is – run gradle build (gradle build) to generate the types into target/generated-sources. Also it's possible to run application (gradle run).

Gist is used for project Gradle configuration. JPA metamodel generation is added to compare with JPA Criteria Queries.