Skip to main content

Querydsl - A Type-Safe Query Writer for Java

· 8 min read
Jack

Sponsored by Mysema, Querydsl is similar to Hibernate, which offers programmable and type-safe query construction. For example

List<Tuple> tuples = queryFactory
        .select(person.lastName, person.firstName, person.yearOfBirth)
        .from(person)
        .fetch();

Querydsl was born out of the need to maintain HQL queries in a typesafe way. Incremental construction of HQL queries requires String concatenation and results in hard to read code. Unsafe references to domain types and properties via plain Strings were another issue with String based HQL construction.

With a changing domain model type-safety brings huge benefits in software development. Domain changes are directly reflected in queries and autocomplete in query construction makes query construction faster and safer.

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.

Type safety is the core principle of 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.

Consistency is another important principle. The query paths and operations are the same in all implementations and also the Query interfaces have a common base interface. This means the domain types are defined only once and they will be ready to be used against SQL, Lucene, MongoDB, etc.

JPA​

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.

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

Maven integration​

Add the following 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>

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

...

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

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

warning

If we use Hibernate annotations in our domain types we should use the APT processor of com.querydsl.apt.hibernate.HibernateAnnotationProcessor instead.

To generate source codes the query types, run mvn clean install and you will get your Query types generated into target/generated-sources/java.

Now you are able to construct JPA query instances and instances of the query domain model. For example, 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");

Querying​

The queries should be created via a JPAQueryFactory instance.

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

Note that the selectFrom call defines the query source and projection

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

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

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

JOIN​

Querydsl supports the following join variants in JPQL:

  • inner join
  • join
  • left join
  • right join.

Join 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

Quering Lucene​

Lucene Feature Maven integration​

Querydsl Lucene can be used via the querydsl-lucene3 module for Lucene 3, querydsl-lucene4 for Lucene 4 and querydsl-lucene5 for Lucene 5

Lucene 3​

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

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

Lucene 4​

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

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

Lucene 5​

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

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

Querying Lucene​

QDocument doc = new QDocument("doc");

IndexSearcher searcher = new IndexSearcher(index);
LuceneQuery query = new LuceneQuery(true, searcher);
List<Document> documents = query
        .where(doc.year.between("1800", "2000").and(doc.title.startsWith("Huckle"))
        .fetch();

which is transformed into the following Lucene query:

+year:[1800 TO 2000] +title:huckle*

Troubleshooting​

[Oracle] "not a GROUP BY expression" Error​

Suppose you have a SQL query that retrieves all unique names of a "Person" table. This query works perfectly in MySQL:

SELECT id, name
FROM Person
GROUP BY name

Oracle, databases, requires all projected columns to be in GROUP BY clause, i.e. the same query must be modified to the following:

SELECT id, name
FROM Person
GROUP BY name, id

Note that we've appended id column in the GROUP BY clause.

Oracle, in this case, brings up an important issue for Querydsl. How do we comply with DRY principle while maintaining 2 copies of columns, one in projection and one in group-by clause, in the code level?

The answer is deduplicating by reducing the 2 copies into 1 single-source of truth and use that to re-generate projections and GROUP BY clause.

For example, we could define projected columns first before specifying projections and group-bys:

/**
 * Returns an object including all of the projections for a query.
 * <p>
 * The object is a mapping from projected column to its column type.
 *
 * @return a new map
 */
@NotNull
public static Map<String, Class<?>> getQueryDslProjections() {
    return Stream.of(
            new AbstractMap.SimpleImmutableEntry<>("id", Long.class),
            new AbstractMap.SimpleImmutableEntry<>("name", String.class)
    )
            .collect(Collectors.collect(Map.Entry::getKey, Map.Entry::getValue));
}

Note that in order to generate projections and group bys later, all we need is the name(display/alias) of the column and the field type of the Database bean, as shown above.

To generate projections, we could use dynamic path to dynamic bind columns to our query:

/**
 * Generate the same group-by columns as the projected columns determined by {@link #getQueryDslProjections()}
 * <p>
 * For example, if the projection is
 * <pre>
 * {@code
 * SELECT person.id, person.name FROM ...
 * }
 * </pre>
 * Then this method returns
 * <pre>
 * GROUP BY person.id, person.name
 * </pre>
 * The purpose of this method is to make Oracle-compatible GROUP-BY, which must includes all projected columns
 *
 * @param projections a mapping from path suffix and the field type of the suffix path. For example:
 * {@code id: Long.class, graph: String.class}
 *
 * @return an array of group-by columns
 */
@NotNull
@SuppressWarnings("SuspiciousToArrayCall")
public static Expression<?>[] getGroupBys(final @NotNull  Map<String, Class<?>> projections) {
    Objects.requireNonNull(projections, "projections");

    final PathBuilder<Person> root = new PathBuilder<>(Person.class, "person");

    return projections.entrySet().stream()
            .map(entry -> {
                String property = entry.getKey();
                Class<?> type = entry.getValue();
                return root.get(property, type);
            })
            .collect(Collectors.toList()).toArray(new Expression[projections.size()]);
}

For projections, we use the dynamic expression again and specify the column alias in the return result using as() show below:

jpa.select(
        Projections.bean(
                Person.class,
                projections
                        .entrySet()
                        .stream()
                        .map(entry -> {
                            String property = entry.getKey();
                            Class<?> type = entry.getValue();
                            return root.get(property, type).as(property);
                        })
                        .collect(Collectors.toList()).toArray(new Expression[projections.size()])
                )
        )
                .from(qPerson)
                .leftJoin...;

[Error] Executing an update/delete query​

In order to translate this query in QueryDsl:

update myThings set firstColumn = 'newValue' where secondColumn in ('interesting', 'stuff')

We have to use new JPAUpdateClause(session, myThings):

JPAUpdateClause<myThings> update = new JPAUpdateClause(session, myThings);
update.set(myThings.firstColumn, "newValue")
        .where(myThings.secondColumn.in(interestingValues))
        .execute();