Pages

JDO Typesafe .vs. JPA Criteria

Monday, November 8, 2010
JPA2 introduced its "Criteria" queries, providing an API for typesafe query generation without the need to hardcode field names etc in queries; it built on the approach of Hibernate Criteria. DataNucleus includes a proposal for JDO "Typesafe" queries. It takes a slightly different approach aiming at usability and elegance. In this blog post we compare some queries using the two APIs.

In these examples we have two classes, Inventory and Product, where Inventory has a set of products.


Select of persistable objects with simple filter

JPQL single-string would be
SELECT p FROM Product p WHERE p.name = 'MP3 Extra'

whilst JDOQL single-string would be
SELECT FROM Product WHERE this.name == 'MP3 Extra'

JPA Criteria would be
CriteriaQuery<Product> criteria = builder.createQuery(Product.class);
Root<Product> productRoot = criteria.from(Product.class);
criteria.select(productRoot);
criteria.where(builder.equal(productRoot.get(Product_.name), "MP3 Extra"));
List<Product> products = em.createQuery(criteria).getResultList();

JDO Typesafe is
TypesafeQuery<Product> tq = pm.newTypesafeQuery(Product.class);
QProduct cand = QProduct.candidate();
List<Product> results = tq.filter(cand.name.eq("MP3 Extra")).executeList();


Select of result of attributes of persistable objects

JPQL single-string would be
SELECT p.value, p.manufacturer FROM Product p WHERE p.name = 'MP3 Extra'

JDOQL single-string would be
SELECT this.value, this.manufacturer FROM Product WHERE this.name == 'MP3 Extra'

JPA Criteria would be
CriteriaQuery criteria = builder.createQuery();
Root<Product> productRoot = criteria.from(Product.class);
criteria.multiselect(productRoot.get(Product_.value), 
productRoot.get(Product_.manufacturer);
criteria.where(builder.equal(productRoot.get(Product_.name), "MP3 Extra"));
List<Object[]> results = em.createQuery(criteria).getResultList();


JDO Typesafe is
TypesafeQuery<Product> tq = pm.newTypesafeQuery(Product.class);
QProduct cand = QProduct.candidate();
List<Object[]> results =
tq.filter(cand.name.eq("MP3 Extra"))
.executeResultList(cand.value, cand.manufacturer);


Select of aggregate of attribute of persistable objects

JPQL single-string would be
SELECT MAX(p.value) FROM Product p WHERE p.name = "MP3 Extra"

JDOQL single-string would be
SELECT MAX(this.value) FROM Product WHERE this.name == "MP3 Extra"

JPA Criteria would be
CriteriaQuery<Integer> criteria = builder.createQuery(Integer.class);
Root<Product> productRoot = criteria.from(Product.class);
criteria.select(builder.max(productRoot.get(Product_.value)));
criteria.where(builder.equal(productRoot.get(Product_.name), "MP3 Extra"));
Object result = em.createQuery(criteria).getSingleResult();

JDO Typesafe is
TypesafeQuery<Product> tq = pm.newTypesafeQuery(Product.class);
QProduct cand = QProduct.candidate();
Integer result =
tq.filter(cand.name.eq("MP3 Extra")).executeResultUnique(Integer.class, cand.value.max());


Select of persistable objects with simple filter and parameter

JPQL single-string would be
SELECT p FROM Product p WHERE p.name = :param

whilst JDOQL single-string would be
SELECT FROM Product WHERE this.name == :param

JPA Criteria would be
CriteriaQuery<Product> criteria = builder.createQuery(Product.class);
Root<Product> productRoot = criteria.from(Product.class);
criteria.select(productRoot);
ParameterExpression<String> valueParam = builder.parameter(String.class);
criteria.where(builder.equal(productRoot.get(Product_.name), valueParam));
TypedQuery<Product> query = em.createQuery(criteria);
query.setParameter(valueParam, "MP3 Extra");
List<Product> products = query.getResultList();

JDO Typesafe is
TypesafeQuery<Product> tq = pm.newTypesafeQuery(Product.class);
QProduct cand = QProduct.candidate();
List<Product> results =
tq.filter(cand.name.eq(tq.stringParameter("prefix")))
.setParameter("prefix", "MP3 Extra").executeList();


Select of persistable objects with joined filter condition

JPQL single-string would be
SELECT i FROM Inventory i JOIN i.products p WHERE (p.name = 'MP3 Extra')

JDOQL single-string would be
SELECT FROM Inventory WHERE this.products.contains(var) && var.name == "MP3 Extra"

JPA Criteria would be
CriteriaQuery<Inventory> criteria = builder.createQuery(Inventory.class);
Root<Inventory> invRoot = criteria.from(Inventory.class);
criteria.select(invRoot);
Join<Inventory, Product> productJoin = invRoot.join(Inventory_.products);
criteria.where(builder.equal(productJoin.get(Product_.name), "MP3 Extra"));
List<Inventory> inventories = em.createQuery(criteria).getResultList();

JDO Typesafe is
TypesafeQuery<Inventory> tq = pm.newTypesafeQuery(Inventory.class);
QProduct var = QProduct.variable("var");
QInventory cand = QInventory.candidate();
List<Inventory> results =
tq.filter(cand.products.contains(var).and(var.name.eq("MP3 Extra"))).executeList();


Select of persistable objects with subquery filter

JPQL single-string would be
SELECT p FROM Product p WHERE p.value < (SELECT AVG(q.value) FROM Product q)
JDOQL single-string would be
SELECT FROM Product WHERE this.value < (SELECT AVG(q.value) FROM Product q)
JPA Criteria would be
CriteriaQuery<Product> criteria = builder.createQuery(Product.class);
Root productRoot = criteria.from(Product.class);
criteria.select(productRoot);
Subquery<Double> sub = criteria.subquery(Double.class);
Root<Product> subRoot = sub.from(Product.class);
criteria.where(builder.lt(productRoot.get(Product_.value), 
sub.select(builder.avg(subRoot.get(Product_.value)))));
List<Product> products = em.createQuery(criteria).getResultList();
JDO Typesafe is
TypesafeQuery<Product> tq = pm.newTypesafeQuery(Product.class);
QProduct cand = QProduct.candidate();
TypesafeSubquery<Product> tqsub = tq.subquery(Product.class, "q");
QProduct candsub = QProduct.candidate("q");
List<Product> results = 
tq.filter(cand.value.lt(tqsub.selectUnique(candsub.value.avg()))).executeList();



As you have seen, for all typesafe queries JDO Typesafe is more elegant and shorter. It requires no "builder" too. Access of a field of a class is one particular example, typing "cand.value" instead of "productRoot.get(Product_.value)"

9 comments:

  1. In my opinion, Additional entity class with Q prefix shouldn't be the right way. More linq style approach is needed. More developper friendly one is sienaproject. Another one is playframework extendable Model class.

    ReplyDelete
  2. Siena requires hard-coded field names; that is what we want to avoid (and the whole reason for having autogenerated query classes), and hence allow refactoring, so I don't see its "more developer friendly" tag.

    If you have a particular proposal then we'd be very interested to see it; the sooner the better for it to be considered for next version of JDO.

    ReplyDelete
  3. As for Playframework "Model", this requires users to extend this for all of their 'model' classes (that will be queried), yes ? That is an imposition on the developer, and even less developer friendly IMHO. The whole point here is *typesafe* and only allowing methods appropriate to a particular component. The query mechanism defined in this blog post just takes any java classes and allows them to be queried.

    ReplyDelete
  4. But the JDO solution is much more dependent upon code-generating much more complex QProduct classes, no?

    The code generation required with the JPA solution is much more lightweight, is that correct?

    ReplyDelete
  5. No. The JPA solution relies on classes like Product_, Inventory_. These are generated using an annotation processor in the exact same way. I also fail to see how a "Q" class is more complex. They have a field for each persistable member in the original class (just like in JPA metamodel) and nothing much more

    ReplyDelete
  6. JDO rocks! JPA sucks!

    ReplyDelete
  7. Hi Andy,

    JDO Typesafe looks surprisingly similar to Querydsl, which has been around for quite a while now. How are your tools affiliated? While I find a lot of independent references on the web related to Querydsl, I mostly find links to this blog post concerning JDO Typesafe...

    Cheers
    Lukas

    ReplyDelete
  8. Hi Lukas, as mentioned in some of our previous blog posts, we took QueryDSL as the guide for what we proposed for JDO Typesafe queries, and arrived at what you see in the blog post, with help from Timo Westkamper (QueryDSL). This is what we are standardising in JDO3.1 (see Apache JDO project JIRA).

    ReplyDelete
  9. Well done! JDO Typesafe could prove to be a serious alternative to the JPA2/CriteriaQuery mainstream, and also proves that I myself am not so wrong with my ideas that I have put in jOOQ, a very similar but more specialised product: http://jooq.sourceforge.net :)

    I wish you luck!

    ReplyDelete