October 17, 2015

JPA query languages compared (part 4 of 4)

Pages: 1 2 3 4

Criteria API queries

The Criteria API is the only part of the JPA standard which allows for fully dynamic query building as well as (optionally) for true strongly typed query building.

Note that Hibernate provides its own “Criteria” API as well which actually inspired the JPA Criteria API and thus shares its general “look and feel”. The Hibernate Criteria API is not discussed in this article. I believe one would either go with the standard (JPA), or look out for an API with a completely different design (such as Querydsl, as discussed below).

String-based Criteria API

1. Find customer by id 2. Find by id (generic)
CriteriaQuery<Customer> query = 
  em.getCriteriaBuilder()
  .createQuery(Customer.class);
Root<Customer> from = query.from(Customer.class);
query.where(em.getCriteriaBuilder()
  .equal(from.get("id"), id));
return em.createQuery(query).getSingleResult();
CriteriaQuery<Customer> query = 
  em.getCriteriaBuilder()
  .createQuery(getModelClass());
Root<Customer> from = query.from(getModelClass());
query.where(em.getCriteriaBuilder()
  .equal(from.get("id"), id));
return em.createQuery(query).getSingleResult();
3. Find all customers 4. Count all customers
CriteriaQuery<Customer> query = 
  em.getCriteriaBuilder()
  .createQuery(Customer.class);
Root<Customer> from = query.from(Customer.class);
return em.createQuery(query).getResultList();
CriteriaQuery<Long> query = 
  em.getCriteriaBuilder().createQuery(Long.class);
Root<Customer> from = query.from(Customer.class);
query.select(em.getCriteriaBuilder().count(from));
return em.createQuery(query).getSingleResult();
5. Find purchase by customer id 6. Count purchase by customer id
CriteriaQuery<Purchase> query = 
  em.getCriteriaBuilder()
  .createQuery(Purchase.class);
Root<Purchase> from = query.from(Purchase.class);
Join<Purchase, Customer> join = 
  from.join("customer");
query.where(em.getCriteriaBuilder()
  .equal(join.get("id"), id));
return em.createQuery(query).getResultList();
CriteriaQuery<Long> query = 
  em.getCriteriaBuilder().createQuery(Long.class);
Root<Purchase> from = query.from(Purchase.class);
Join<Purchase, Customer> join = 
  from.join("customer");
query.where(em.getCriteriaBuilder()
  .equal(join.get("id"), id));
query.select(em.getCriteriaBuilder().count(from));
return em.createQuery(query).getSingleResult();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
// main query on products
CriteriaQuery<Product> query = 
  em.getCriteriaBuilder()
  .createQuery(Product.class);
Root<Product> from = query.from(Product.class);

// subquery on product ids
Subquery<Long> subQuery = 
  query.subquery(Long.class);
Root<Customer> subFrom = 
  subQuery.from(Customer.class);
Join<Customer, Purchase> joinPurchase = 
  subFrom.join("purchases");
Join<Purchase, Product> joinProduct = 
  joinPurchase.join("products");
subQuery.select(joinProduct.get("id")
  .as(Long.class)).distinct(true);
subQuery.where(em.getCriteriaBuilder()
  .equal(subFrom.get("premium"), true));

query.select(from);
query.where(em.getCriteriaBuilder().in(
  from.get("id")).value(subQuery));
return em.createQuery(query).getResultList();
CriteriaQuery<Tuple> query = 
  em.getCriteriaBuilder().createTupleQuery();
Root<Customer> from = query.from(Customer.class);
Join<Customer, Purchase> joinPurchase = 
  from.join("purchases");
Join<Purchase, Product> joinProduct = 
  joinPurchase.join("products");
query.multiselect(from.get("id"), 
  em.getCriteriaBuilder().sum(joinProduct
  .get("price").as(Double.class)));
query.groupBy(from.get("id"));
List<Tuple> results = 
  em.createQuery(query).getResultList();

Map<Customer, Double> ret = new HashMap<>();
for (Tuple result : results) {
  Object[] arr = result.toArray();
  ret.put(customerService.findById((Long)arr[0]),
((Double)arr[1]));
}
return ret;

The String-based version of the Criteria API really is the same as the strongly typed Criteria API (discussed below), but without making use of the so called entity meta-models generated at compile time which provide type-safe references to entity property paths.

I will not discuss the String-based Criteria API in detail here. Let me just say that if you want to use Criterias because you want that extra type-safety, you should go all the way and use the entity meta-models to ensure complete type-safety. Otherwise, figuratively speaking, it’s like buying a car but then harnessing a horse in front of it.

Please compare above example queries with their equivalent in the next subsection to see how the entity meta-model improves type safety.

Bottom line:
  • Never use “Stringly typed” Criteria, always go for the strongly typed alternative.

Strongly typed Criteria API

1. Find customer by id 2. Find by id (generic)
CriteriaQuery<Customer> query = 
  em.getCriteriaBuilder()
  .createQuery(Customer.class);
Root<Customer> from = query.from(Customer.class);
query.where(em.getCriteriaBuilder()
  .equal(from.get(Customer_.id), id));
return em.createQuery(query).getSingleResult();
CriteriaQuery<Customer> query = 
  em.getCriteriaBuilder()
  .createQuery(getModelClass());
Root<Customer> from = 
  query.from(getModelClass());
query.where(em.getCriteriaBuilder()
  .equal(from.get(Customer_.id), id));
return em.createQuery(query).getSingleResult();
3. Find all customers 4. Count all customers
CriteriaQuery<Customer> query = 
  em.getCriteriaBuilder()
  .createQuery(Customer.class);
Root<Customer> from = query.from(Customer.class);
return em.createQuery(query).getResultList();
CriteriaQuery<Long> query = 
  em.getCriteriaBuilder().createQuery(Long.class);
Root<Customer> from = query.from(Customer.class);
query.select(em.getCriteriaBuilder().count(from));
return em.createQuery(query).getSingleResult();
5. Find purchase by customer id 6. Count purchase by customer id
CriteriaQuery<Purchase> query = 
  em.getCriteriaBuilder()
  .createQuery(Purchase.class);
Root<Purchase> from = query.from(Purchase.class);
Join<Purchase, Customer> join = 
  from.join(Purchase_.customer);
query.where(em.getCriteriaBuilder()
  .equal(join.get(BaseModel_.id), id));
return em.createQuery(query).getResultList();
CriteriaQuery<Long> query = 
  em.getCriteriaBuilder().createQuery(Long.class);
Root<Purchase> from = query.from(Purchase.class);
Join<Purchase, Customer> join = 
  from.join(Purchase_.customer);
query.where(em.getCriteriaBuilder()
  .equal(join.get(BaseModel_.id), id));
query.select(em.getCriteriaBuilder().count(from));
return em.createQuery(query).getSingleResult();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
// main query on products
CriteriaQuery<Product> query = 
  em.getCriteriaBuilder()
  .createQuery(Product.class);
Root<Product> from = query.from(Product.class);

// subquery on product ids
Subquery<Long> subQuery = 
  query.subquery(Long.class);
Root<Customer> subFrom = 
  subQuery.from(Customer.class);
ListJoin<Customer, Purchase> joinPurchase = 
  subFrom.join(Customer_.purchases);
ListJoin<Purchase, Product> joinProduct = 
  joinPurchase.join(Purchase_.products);
subQuery.select(joinProduct.get(Product_.id))
  .distinct(true);
subQuery.where(em.getCriteriaBuilder()
  .equal(subFrom.get(Customer_.premium), true));

query.select(from);
query.where(em.getCriteriaBuilder().in(
  from.get(Product_.id)).value(subQuery));
return em.createQuery(query).getResultList();
CriteriaQuery<Tuple> query = 
  em.getCriteriaBuilder().createTupleQuery();
Root<Customer> from = query.from(Customer.class);
ListJoin<Customer, Purchase> joinPurchase = 
  from.join(Customer_.purchases);
ListJoin<Purchase, Product> joinProduct = 
  joinPurchase.join(Purchase_.products);
query.multiselect(from.get(BaseModel_.id), 
  em.getCriteriaBuilder().sum(joinProduct
  .get(Product_.price)));
query.groupBy(from.get(BaseModel_.id));
List<Tuple> results = 
  em.createQuery(query).getResultList();

Map<Customer, Double> ret = new HashMap<>();
for (Tuple result : results) {
  Object[] arr = result.toArray();
  ret.put(customerService.findById((Long)arr[0]),
  ((Double)arr[1]));
}
return ret;

As with String-based Criterias, by invoking EntityManager#getCriteriaBuilder(), you get a builder instance to build statically typed queries by invoking methods on the builder or its child nodes, respectively. This is a very different approach to specifying a query in a String. Many builder nodes work with generics.

Moreover, in “strongly typed” mode, you use the entity meta-model to refer to entity property paths. The meta-model is generated from the entity classes at compile time; it is activated by simply adding the modelgen-dependency to the pom:
<dependency>
    <groupId>org.eclipse.persistence</groupId>
    <artifactId>org.eclipse.persistence.jpa.modelgen.processor</artifactId>
    <version>2.6.0</version>
    <scope>provided</scope>
</dependency>
The Criteria API thus makes is easy and safe to build queries at runtime.

Unfortunately however, its API is rightfully considered cumbersome, unintuitive and unflexible by many developers. Actually, “translating” even quite simple JPQL or SQL queries into their Criteria API typically leads to a monstrous, almost unreadable blob of code; see the example queries above. This highly harms real-world usefulness of the Criteria API.

On stackoverflow.com, you’ll find many workaround proposals to abstract the Criteria API and make it more easy to work with for everyday problems. However, I think that if you really need a solid, easy to use framework to build dynamic queries, you should just get an existing one. Don’t reinvent the wheel. Read on for a short overview of Querydsl, a very good contestant.

Bottom line:
  • If you already have all your optimized SQL / JPQL scripts nicely prepared, don’t waste your time manually “translating” them into Criteria queries. Just use named native queries instead.
  • Unless you need true dynamic queries, always use named queries instead.
  • If you need a means to build very simple dynamic / generic queries in a few places in your code (e.g. example query 2: the famous generic “find all” query you can’t realize with any other JPA query language), you may consider solving it using the Criteria API in order not to introduce an additional 3rd party dependency to the project.
  • If however you have to build many highly dynamic queries, you should use a 3rd party framework with a more reasonable API (as e.g. Querydsl, which is discussed below). But also, you should probably ask yourself whether you really need a highly dynamic solution. You may get a considerable performance boost if you’d stick with static, precompiled queries.

Mysema Querydsl

1. Find customer by id 2. Find by id (generic)
QCustomer qCustomer = QCustomer.customer;
return new JPAQueryFactory(em)
  .selectFrom(qCustomer)
  .where(qCustomer.id.eq(id))
  .fetchOne();
return new JPAQueryFactory(em)
  .selectFrom(getQModel())
  .where(getQModelId().eq(id))
  .fetchOne();
3. Find all customers 4. Count all customers
QCustomer qCustomer = QCustomer.customer;
return new JPAQueryFactory(em)
  .selectFrom(qCustomer)
.fetch();
QCustomer qCustomer = QCustomer.customer;
return new JPAQueryFactory(em)
  .selectFrom(qCustomer)
  .fetchCount();
5. Find purchase by customer id 6. Count purchase by customer id
QPurchase qPurchase = QPurchase.purchase;
QCustomer qCustomer = QCustomer.customer;
return new JPAQueryFactory(em)
  .selectFrom(qPurchase)
  .innerJoin(qPurchase.customer, qCustomer)
  .where(qCustomer.id.eq(id))
  .fetch();
QPurchase qPurchase = QPurchase.purchase;
QCustomer qCustomer = QCustomer.customer;
return new JPAQueryFactory(em)
  .selectFrom(qPurchase)
  .innerJoin(qPurchase.customer, qCustomer)
  .where(qCustomer.id.eq(id))
  .fetchCount();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
QProduct qProduct = QProduct.product;
QPurchase qPurchase = QPurchase.purchase;
QCustomer qCustomer = QCustomer.customer;
return new JPAQueryFactory(em)
  .selectFrom(qProduct)
  .where(qProduct.id.in(JPAExpressions
    .selectFrom(qCustomer)
    .innerJoin(qCustomer.purchases, qPurchase)
    .innerJoin(qPurchase.products, qProduct)
    .select(qProduct.id)
    .where(qCustomer.premium.eq(true))
))
.fetch();
QProduct qProduct = QProduct.product;
QPurchase qPurchase = QPurchase.purchase;
QCustomer qCustomer = QCustomer.customer;
List<Tuple> results = new JPAQueryFactory(em)
  .from(qCustomer)
  .innerJoin(qCustomer.purchases, qPurchase)
  .innerJoin(qPurchase.products, qProduct)
  .groupBy(qCustomer.id)
  .select(qCustomer.id, qProduct.price.sum())
  .fetch();

Map<Customer, Double> ret = new HashMap<>();
for (Tuple result : results) {
  ret.put(customerService.findById(
  result.get(qCustomer.id)),
  result.get(qProduct.price.sum()));
}
return ret;

Mysema Querydsl is a third party alternative to the JPA standard Criteria API to dynamically build strongly typed queries. It comes with its own entity meta model generation. Take a look at the demo project’s pom.xml file for its dependencies declaration and the entity meta model generation plugin setup.

There are also some other similar third party tools. Another popular example is jOOQ. This tool, however, works very different in that it builds its meta-model out of the database schema rather than the Java entity classes. Also, apparently, support for JOINs is not yet quite good enough. Iciql, another similar tool, requires its own non-standard annotations on the entity classes.

As you can see from the example queries, Querydsl’s syntax is in any aspect superior to that of Criteria API: It is much more simple, concise, and thus easier to learn and maintain.

Unfortunately, I have found that the official documentation has not yet been updated with some breaking changes introduced in the most recent major release 4.0, making the learning curve sharper than it should be.

Other than that, I consider Querydsl a pragmatic drop-in replacement for the highly inconvenient Criteria API.

As you can see from the demo project source code, I’d consider it best practice to declare the entity meta-model references in a single place, e.g. in the respective entity class, rather than declaring them locally in each method.

Much more than Criteria API, Querydsl also offers great opportunities to apply DRY (don’t repeat yourself) best practices by writing more generic but parameterizable queries. For instance, example queries 3 / 4 could reuse a general purpose method like this:
private JPAQuery<Customer> all() {
    return new JPAQueryFactory(em).selectFrom(qCustomer);
}

@Override
public List<Customer> findAll() {
    return all().fetch();
}

@Override
public long countAll() {
    return all().fetchCount();
}
This example, for instance, wouldn’t be possible with Criteria API where the SELECT and SELECT COUNT API differs considerably.

Bottom line:
  • If you have to build dynamic queries, you should consider using Querydsl. But again, you should probably ask yourself whether you really need a highly dynamic solution. You may get a considerable performance boost if you’d stick with static, precompiled queries.

Making a decision

Finally, I’d like to quickly recap what we learnt about those persistence query languages by giving you my recommendation on how I would make a technology decision:
  • For CRUD (create / read by id / update / delete) operations
    • Use the EntityManager methods 
  • If you already have all your optimized SQL scripts nicely prepared or if you make use of native SQL constructs such as views and stored procedures
    • Use named native Queries. Mind the loss of portability!
  • If you can build static queries from scratch
    • Use named JPQL queries
  • If you only need to build trivial dynamic / generic queries (such as “find all in x”) and portability is key
    • Use strongly typed Criteria API queries
  • If you need to build non-trivial dynamic / generic queries
    • Use Mysema Querydsl

Conclusion

In this blog post, I think I have exhaustively covered the most important techniques to write database queries in a Java EE 7 landscape. To further compact my conclusions from the previous sections, I’d say that named JPQL queries and Mysema Querydsl are the most solid choices for their respective use cases.

The most important advice I would give to any Java EE project however is to clearly define once which techniques will be used and then stick to this decision until it needs to be revised. Keeping the project tech stack clean and well-defined is an important prerequisite for long-term maintainability.

I hope this article helped you getting an insight in these technologies. Please use the comments section below to let me know whether you agree or disagree with my conclusions.


Pages: 1 2 3 4

No comments:

Post a Comment