2010-04-13

Querydsl as an alternative to the JPA 2 Criteria API

Querydsl is our flagship Open Source project for type-safe queries in JDO, JPA, SQL and other querying languages.

Last weekend I posted a thread in our Mysema Source Forum related to the verbosity of JPA 2 Criteria queries.

The thread presents JPA 2 and Querydsl queries side by side. Just that, not more. Here is one query comparison example.

The use case of this example is to query for single men and women as an cartesian view. At first the query expressed via the JPA 2 Criteria API :

CriteriaQuery query = builder.createQuery();
Root<Person> men = query.from( Person.class );
Root<Person> women = query.from( Person.class );
Predicate menRestriction = builder.and(
    builder.equal( men.get( Person_.gender ), Gender.MALE ),
    builder.equal( men.get( Person_.relationshipStatus ),
RelationshipStatus.SINGLE ));
Predicate womenRestriction = builder.and(
    builder.equal( women.get( Person_.gender ), Gender.FEMALE ),
    builder.equal( women.get( Person_.relationshipStatus ),
RelationshipStatus.SINGLE ));
query.where( builder.and( menRestriction, womenRestriction ) );

and now via Querydsl

JPAQuery query = new JPAQuery(em);
QPerson men = new QPerson("men");
QPerson women = new QPerson("women");
query.from(men, women).where(
      men.gender.eq(Gender.MALE),
      men.relationshipStatus.eq(RelationshipStatus.SINGLE),
      women.gender.eq(Gender.FEMALE),
      women.relationshipStatus.eq(RelationshipStatus.SINGLE));    

The guys from the JPA group decided that fluent expression construction is not an option so a CriteriaBuilder for criteria construction was needed. CriteriaBuilder contains methods for all possible operation constructs, not grouped in any way.

Compare this with Querydsl, which has a dynamic query model with a fluent API, which supports IDE autocomplete and a much faster and compact way to write queries.

Also the distinction of paths, joins and the metamodel doesn't really make query construction easy.

A root path for the Person type is constructed like this in JPA 2 :

Root<Person> men = query.from( Person.class );

In Querydsl you just create an instance of the metatype for Person :

QPerson men = new QPerson("men");

In most cases though you can use the default instance of the metatype like this :

QPerson men = QPerson.person;

And to refer to the gender property of the Person path :

men.get(Person_.gender)

whereas in Querydsl you just use a field of the metatype instance :

men.gender

The dynamic metatypes of Querydsl combine a type-safe representation of the query domain, path references and operation construction in a fluent and concise way.

In addition to JPAQL and HQL Querydsl supports JDOQL, SQL, RDF, Lucene, Hibernate Search and Java collections.

We at Mysema use Querydsl in all our own projects with multiple backends and are 100% happy with it. ;)

11 comments:

  1. It looks like that fragment of your sample code was never run:
    men.relationshipStatus.eq(RelationshipStatsu.SINGLE),
    women.gender.eq(Gender.FEMALE),
    women.relationshipStatsu.eq(RelationshipStatus.SINGLE));

    Find the error, it shouldn't be too hard ;-)

    ReplyDelete
  2. Werner, thanks for the hint. Fixed this in the blog post.

    ReplyDelete
  3. Hi,
    I am facing an issue when i am trying to access 2nd level nested element in an entity,I am getting a NullPointerException. I saw this link https://bugs.launchpad.net/querydsl/+bug/500163 but could not use it. Maybe some help. I already posted my question in http://stackoverflow.com/questions/6385036/querydsl-generated-classes-not-able-to-access-second-level-elements-for-querying .

    ReplyDelete
  4. Hi Abhishek. Sorry for the late reply. Answered on SO.

    ReplyDelete
  5. sounds interesting, is there any usage in an enterprise?

    ReplyDelete
  6. Yes, we use it in most of our customer projects and I believe thorough Spring Data, there are also lots of enterprise references available.

    ReplyDelete
  7. Hi,

    When added the mavn-apt-plugin.1.0.39.jar in the repository and the mentioned maven plug-in in the pom file below error came:

    Could not find goal 'process' in plugin org.apache.myfaces.tobago:maven-apt-plugin:1.0.39 among available goals testExecute, execute

    Can you please help with this one?

    Nileshm

    ReplyDelete
    Replies
    1. Hi Nileshm.

      You used the wrong plugin. The right one is this one https://github.com/mysema/maven-apt-plugin

      Usage instructions with Querydsl are here http://www.querydsl.com/static/querydsl/2.7.2/reference/html/ch02.html#d0e114

      Br,
      Timo

      Delete
    2. Nileshm, please use the Querydsl Google Group https://groups.google.com/forum/#!forum/querydsl for further questions.

      Delete
  8. I love the conciseness and clarity of QueryDSL over the criteria API. I need dynamic queries to remove JPQL/SQL code duplication (I have a query being used standalone and as a subquery, I have predicates and order value calculations that are reused by different queries.)

    However I finally chose to use the criteria API because I need to aggregate order values by month. With the criteria API this can be done by calling the native SQL month function. I couldn't find an easy way to do this with QueryDSL, any chance this gets added?

    ReplyDelete
    Replies
    1. Did you try the month() method: http://www.querydsl.com/static/querydsl/3.1.1/apidocs/com/mysema/query/types/expr/DateExpression.html

      Delete