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

JPA query languages compared (part 3 of 4)

Pages: 1 2 3 4

Named queries

The standard way for working with DB query Strings is to specify them as so-called named queries. As for dynamic queries, SQL and JPQL commands are supported.

Named native (SQL) queries

1. Find customer by id 2. Find by id (generic)
@NamedNativeQuery(name = Customer.SQL_FIND_BY_ID,
  query = "SELECT * FROM CUSTOMER WHERE ID = ?1",
  resultClass = Customer.class)
...
public static final String SQL_FIND_BY_ID =
  "SQL.Customer.findbyId";
...
return em.createNamedQuery(
  Customer.SQL_FIND_BY_ID, Customer.class)
  .setParameter(1, id)
  .getSingleResult();
(dynamic table names not supported)
3. Find all customers 4. Count all customers
@NamedNativeQuery(name = Customer.SQL_FIND_ALL,
  query = "SELECT * FROM CUSTOMER")
...
return em.createNamedQuery(Customer.SQL_FIND_ALL,
  Customer.class)
  .getResultList();
@NamedNativeQuery(name = Customer.SQL_COUNT_ALL,
  query = "SELECT COUNT(ID) FROM CUSTOMER")
...
return em.createNamedQuery(Customer.SQL_COUNT_ALL,
  Customer.class)
  .getResultList();
5. Find purchase by customer id 6. Count purchase by customer id
@NamedNativeQuery(name = 
  Purchase.SQL_FIND_BY_CUSTOMER_ID, 
  query = "SELECT PURCHASE.* FROM PURCHASE 
  INNER JOIN CUSTOMER ON PURCHASE.CUSTOMER_ID=
  CUSTOMER.ID WHERE CUSTOMER.ID = ?1")
...
return em.createNamedQuery(
  Purchase.SQL_FIND_BY_CUSTOMER_ID, Purchase.class)
  .setParameter(1, id)
  .getResultList();
@NamedNativeQuery(name = 
  Purchase.SQL_COUNT_BY_CUSTOMER_ID,
  query = "SELECT COUNT(PURCHASE.ID) FROM PURCHASE 
  INNER JOIN CUSTOMER ON PURCHASE.CUSTOMER_ID=
  CUSTOMER.ID WHERE CUSTOMER.ID = ?1")
...
return ((Number)em.createNamedQuery(
  Purchase.SQL_COUNT_BY_CUSTOMER_ID, Long.class)
  .setParameter(1, id)
  .getSingleResult()).longValue();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
@NamedNativeQuery(name = 
  Product.SQL_FIND_FOR_PURCHASE_CUSTOMER_PREMIUM, 
  query = "SELECT PRODUCT.* FROM PRODUCT 
  WHERE PRODUCT.ID IN (SELECT DISTINCT PRODUCT.ID 
  FROM PRODUCT INNER JOIN PURCHASE_PRODUCT 
  ON PURCHASE_PRODUCT.PRODUCTS_ID=PRODUCT.ID 
  INNER JOIN PURCHASE 
  ON PURCHASE.ID=PURCHASE_PRODUCT.PURCHASE_ID 
  INNER JOIN CUSTOMER 
  ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID 
  WHERE CUSTOMER.PREMIUM = 1)",
  resultClass = Product.class)
...
return em.createNamedQuery(
Product.SQL_FIND_FOR_PURCHASE_CUSTOMER_PREMIUM,
  Product.class)
  .getResultList();
@NamedNativeQuery(name = 
Product.SQL_SUM_PRICE_BY_PURCHASE_CUSTOMER,
  query = "SELECT CUSTOMER.ID, SUM(PRODUCT.PRICE) 
  FROM CUSTOMER INNER JOIN PURCHASE 
  ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID 
  INNER JOIN PURCHASE_PRODUCT 
  ON PURCHASE.ID=PURCHASE_PRODUCT.PURCHASE_ID 
  INNER JOIN PRODUCT 
  ON PURCHASE_PRODUCT.PRODUCTS_ID=PRODUCT.ID 
  GROUP BY CUSTOMER.ID")
...
List results = em.createNamedQuery(
  Product.SQL_SUM_PRICE_BY_PURCHASE_CUSTOMER)
  .getResultList();
Map<Customer, Double> ret = new HashMap<>();
for (Object result : results) {
  Object[] arr = (Object[]) result;
  ret.put(customerService.findById((Long)arr[0]),
  ((Double)arr[1]));
}
return ret;

Named native queries are specified on the entity class using the @NamedNativeQuery annotation. Because Java SE 7 doesn’t support multiple instances of the same annotation type on the same entity, multiple query annotations must be combined in a @NamedNativeQueries umbrella annotation.

They can then be invoked on the EntityManager, much similar to dynamic native queries.

Named native queries still share quite a few disadvantages with dynamic native queries because they depend on native SQL:
  • You have to deal with the full complexity of SQL queries (e.g. JOIN clauses)
  • Native SQL queries work directly with the native data types of the underlying database, which severely harms portability. For instance, note that in the example, the data type returned by a COUNT query (e.g. int or long) depends on the underlying database implementation.
  • JPA does not allow named parameters for native SQL queries, only indexed parameters, which further decreases maintainability.
Support for generics is slightly better than with dynamic named queries:
  • By specifying resultClass in the annotation, a getResultList() call doesn’t have to be casted.
As with dynamic queries, always build TypedQuery instances for better generics support and less casting.

There is, however, an important advantage over dynamic queries in general:
  • JPA is able to apply performance optimization (preparsing, precompilation).
In fact, this is why you should never use dynamic queries at all, and replace them with named queries.

As you can see in the code examples, it’s a widespread best practice to use constants for query names in order to prevent mistyping them when referenced in an EntityManager call. Introducing constants for every named parameter however I do consider overkill.

If you have non-trivial query Strings, it’s also best practice to load them from a static XML file in order not to clutter the entity Java source code file.

Note that because of the nature of annotations, you can only provide static query Strings; there’s no way to create a query String dynamically which really is what named queries are all about.

Bottom line:
  • Depending on how your development project is organized, named native queries may be useful: If your query logic resides in SQL scripts rather than in the Java code, and if you make use of native SQL constructs such as views and stored procedures, named native queries are the facility of choice for native SQL invocation. Note however that you thus create a strong dependency on the database’s native SQL dialect and hence lose portability.
  • If you already have all your optimized SQL scripts nicely prepared, don’t waste your time manually “translating” them into JPQL. Just use native queries instead.
  • If however you don’t have SQL expert knowledge in the team and you work from a Java perspective, take JPQL as a chance to abstract away nasty SQL.

Named JPQL queries

1. Find customer by id 2. Find by id (generic)
@NamedQuery(name = Customer.FIND_BY_ID, query =
  "SELECT e FROM Customer e WHERE e.id = :id")
...
public static final String FIND_BY_ID =
  "Customer.findbyId";
...
return em.createNamedQuery(
  Customer.FIND_BY_ID, Customer.class)
  .setParameter("id", id)
  .getSingleResult();
(dynamic table names not supported)
3. Find all customers 4. Count all customers
@NamedQuery(name = Customer.FIND_ALL,
  query = "SELECT e FROM Customer e")
...
return em.createNamedQuery(
  Customer.FIND_ALL, Customer.class)
  .getResultList();
@NamedQuery(name = Customer.COUNT_ALL,
  query = "SELECT COUNT(e.id) FROM Customer e")
...
return em.createNamedQuery(
  Customer.COUNT_ALL, Long.class)
  .getSingleResult();
5. Find purchase by customer id 6. Count purchase by customer id
@NamedQuery(name = Purchase.FIND_BY_CUSTOMER_ID,
  query = "SELECT e FROM Purchase e 
  INNER JOIN e.customer _customer 
  WHERE _customer.id = :id")
...
return em.createNamedQuery(
  Purchase.FIND_BY_CUSTOMER_ID, Purchase.class)
  .setParameter("id", id)
  .getResultList();
@NamedQuery(name = Purchase.COUNT_BY_CUSTOMER_ID, 
  query = "SELECT COUNT(e.id) 
  FROM Purchase e INNER JOIN e.customer _customer 
  WHERE _customer.id = :id")
...
return em.createNamedQuery(
  Purchase.COUNT_BY_CUSTOMER_ID, Long.class)
  .setParameter("id", id)
  .getSingleResult();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
@NamedQuery(name = 
  Product.FIND_FOR_PURCHASE_CUSTOMER_PREMIUM,
  query = "SELECT e from Product e where e.id IN (
  SELECT DISTINCT _products.id FROM Customer _customer 
  INNER JOIN _customer.purchases _purchases 
  INNER JOIN _purchases.products _products 
  WHERE _customer.premium = true)")
...
return em.createNamedQuery(
  Product.FIND_FOR_PURCHASE_CUSTOMER_PREMIUM,
  Product.class)
.getResultList();
@NamedQuery(name = 
  Product.SUM_PRICE_BY_PURCHASE_CUSTOMER, 
  query = "SELECT _customer.id, SUM(_products.price) 
  FROM Customer _customer 
  INNER JOIN _customer.purchases _purchases 
  INNER JOIN _purchases.products _products 
  GROUP BY _customer.id")
...
List results = em.createNamedQuery(
  Product.SUM_PRICE_BY_PURCHASE_CUSTOMER)
  .getResultList();
Map<Customer, Double> ret = new HashMap<>();
for (Object result : results) {
  Object[] arr = (Object[]) result;
  ret.put(customerService.findById((Long)arr[0]),
  ((Double)arr[1]));
}
return ret;

Similar to named native queries, named JPQL queries are specified on the entity class using the @NamedQuery annotation / @NamedQueries collection annotation.

Finally, named JPQL queries combine the advantages of named queries with the advantages of the JPQL syntax. Most importantly, because they are precompiled, JPQL syntax errors are detected early during compilation stage (or even by the IDE) rather than on query execution.

The common best practices explained above for named native queries apply to JPQL queries as well.

They really are the best fit for all but two specific situations:
  • You need to build queries dynamically
  • You want to or have to depend on native SQL constructs such as views and stored procedures.
Bottom line:
  • Static queries should be used with named JPQL queries for maximum static checking, query optimization and generics support as well as overall maintainability.
Pages: 1 2 3 4

JPA query languages compared (part 2 of 4)


Pages: 1 2 3 4

EntityManager operations

As discussed in the preliminary section, the EntityManager supports some very basic CRUD operations directly:
1. Find customer by id 2. Find by id (generic)
return em.find(Customer.class, id);
return em.find(getModelClass(), id);
3. Find all customers 4. Count all customers
(“read all” not supported) (“count all” not supported)
5. Find purchase by customer id 6. Count purchase by customer id
Customer customer = 
  em.find(Customer.class, id);
if (customer == null) {
  return new ArrayList<>();
}

return customer.getPurchases();
Customer customer = 
  em.find(Customer.class, id);
if (customer == null) {
  return new ArrayList<>();
}

return customer.getPurchases().size();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
(“read all” not supported) (“read all” not supported)

It’s thus best practice to use EntityManager directly for simple CRUD operations, and not write any explicit DB queries.

Note that EntityManager only supports read with id operation, not “read all”. You’ll have to use an alternate implementation for these cases.

Also note that whilst it may be fine to “explicitly” load all children for an entity using a plain EntityManager call (query 5), it would be wasteful to do so just to get the number (size()) of children (query 6): As pointed out in this stackoverflow answer, calling #size() would trigger a full fetch of all child entities, despite lazy loading settings. It’s best practice to use an alternate implementation for this case.

Bottom line:
  • For use with simple CRUD operations only and exclusively.

Short digression: CRUD and REST

As a side note: If you ever find yourself forced to write a lot of queries with conditional “where” clauses other than the entity id, this may be a sign of an architectural misconception.

Especially in a RESTful environment, it’s most typical to once receive a list of the ids of all the entities fulfilling a given condition, and then work with each individual entity based on its id.

For instance, in order to switch the “premium user” flag to true for a customer with a given customer name, you would do in REST (in pseudo-code):
Customer customer = em.createNamedQuery("Customer.findbyName")
    .setParameter("name", name)
    .getSingleResult();
customer.setPremium(true);
save(customer);
whereas in a more service-oriented architecture, you would do (in pseudo-code):
em.createNamedQuery("Customer.setPremiumByName")
    .setParameter("name", name)
    .executeUpdate();
where "Customer.setPremiumByName" is a specialized named update query implementing the business logic.

Even though in the service-oriented version, we fire only one query instead of two, the number of queries we have to define will be much higher than in a REST architecture which reuses the generic CRUD operations wherever possible. If however throughput is very critical, a service-oriented approach may be your last resort.

For any other situation, RESTful service interfaces do in general increase reusability and maintainability and match better an object-oriented environment. In any case, it’s key to define once whether you are building a RESTful or a service-oriented architecture, and to then stick to it.

Dynamic queries

As the most simple (and dynamic) option, you can run queries specified by a String on the EntityManager.

Dynamic native (SQL) queries

In the utmost simple case, you can use plain SQL (hence “native”) queries.
1. Find customer by id 2. Find by id (generic)
return (Customer) em.createNativeQuery(
  "SELECT * FROM CUSTOMER WHERE ID = ?1", 
  Customer.class)
  .setParameter(1, id)
  .getSingleResult();
(dynamic table names not supported)
3. Find all customers 4. Count all customers
return em.createNativeQuery(
  "SELECT * FROM CUSTOMER", Customer.class)
  .getResultList();
return ((Number) em.createNativeQuery(
  "SELECT COUNT(ID) FROM CUSTOMER")
  .getSingleResult()).longValue();
5. Find purchase by customer id 6. Count purchase by customer id
return em.createNativeQuery(
  "SELECT PURCHASE.* FROM PURCHASE\n" +
  "INNER JOIN CUSTOMER\n" +
  "ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID\n" +
  "WHERE CUSTOMER.ID = ?1", Purchase.class)
  .setParameter(1, id)
  .getResultList();
return em.createNativeQuery(
  "SELECT COUNT(PURCHASE.ID) FROM PURCHASE\n" +
  "INNER JOIN CUSTOMER\n" +
  "ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID\n" +
  "WHERE CUSTOMER.ID = ?1", Purchase.class)
  .setParameter(1, id)
  .getResultList();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
return em.createNativeQuery(
  "SELECT PRODUCT.* FROM PRODUCT\n" +
  "WHERE PRODUCT.ID IN (\n" +
  "SELECT DISTINCT PRODUCT.ID FROM PRODUCT\n" +
  "INNER JOIN PURCHASE_PRODUCT\n" +
  "ON PURCHASE_PRODUCT.PRODUCTS_ID=PRODUCT.ID\n" +
  "INNER JOIN PURCHASE\n" +
  "ON PURCHASE.ID=PURCHASE_PRODUCT.PURCHASE_ID\n" +
  "INNER JOIN CUSTOMER\n" +
  "ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID\n" +
  "WHERE CUSTOMER.PREMIUM = 1\n" +
  ")", Product.class)
  .getResultList();
}
List results = em.createNativeQuery(
  "SELECT CUSTOMER.ID, " +
  "SUM(PRODUCT.PRICE) FROM CUSTOMER\n" +
  "INNER JOIN PURCHASE\n" +
  "ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID\n" +
  "INNER JOIN PURCHASE_PRODUCT\n" +
  "ON PURCHASE.ID=PURCHASE_PRODUCT.PURCHASE_ID\n" +
  "INNER JOIN PRODUCT\n" +
  "ON PURCHASE_PRODUCT.PRODUCTS_ID=PRODUCT.ID\n" +
  "GROUP BY CUSTOMER.ID")
  .getResultList();
Map<Customer, Double> ret = new HashMap<>();
for (Object result : results) {
  Object[] arr = (Object[]) result;
  ret.put(customerService.findById((Long)arr[0]), 
    ((Double)arr[1]));
}
return ret;

Important note: in order to prevent SQL injection, we always provide dynamic parameters values with the #setParameter(…) method, which sanitizes potentially harmful input, rather than using query String concatenation.

This most basic DB query facility also suffers from the most inconveniences:
  • Because the query String is built dynamically, no performance optimization (preparsing, precompilation) is possible.
  • Declaring the query as a String of course is not typesafe at all, making the implementation highly brittle.
  • It’s also arguably hard to read and maintain.
  • Even though dynamic parameters are supported, it doesn’t support to use them for dynamic table names. The only way to support dynamic table names (for a very generic solution as in example query 2) would be String concatenation which we have already revoked as being a terrible idea. The same is true for any dynamic query clauses building.
  • Support for aggregate functions (using GROUP BY clause) is bad: it can only return value tuples, not entity-value Maps (we’ll later notice that unfortunately, this is basically true for every Java query language discussed in this article).
  • You have to deal with the full complexity of SQL queries (e.g. JOIN clauses)
  • Native SQL queries work directly with the native data types of the underlying database, which severely harms portability. For instance, note that in the example, the data type returned by a COUNT query (e.g. int or long) depends on the underlying database implementation.
  • Native SQL query results have to be casted because generics are not fully supported. Still, it’s best practice to always use the #create…Query(…) method which takes an additional parameter, turning the result into a TypedQuery with basic generics support.
  • JPA does not allow named parameters for native SQL queries, only indexed parameters, which further decreases maintainability. It’s best practice to at least use explicit parameter references (?0, ?1,… instead of ?, ?,…) to make things explicit. Note that query parameter indices start with 1.
Bottom line:
  • Don’t use this facility ever. Check out named native queries if you need native SQL query support.

Dynamic JPQL queries

1. Find customer by id 2. Find by id (generic)
return em.createQuery(
  "SELECT e FROM Customer e WHERE e.id = :id",
  Customer.class)
  .setParameter("id", id)
  .getSingleResult();
(dynamic table names not supported)
3. Find all customers 4. Count all customers
return em.createQuery(
  "SELECT e FROM Customer e", Customer.class)
  .getResultList();
return em.createQuery(
  "SELECT COUNT(e.id) FROM Customer e", Long.class)
  .getSingleResult();
5. Find purchase by customer id 6. Count purchase by customer id
return em.createQuery("SELECT e FROM Purchase e\n" +
  "INNER JOIN e.customer _customer\n" +
  "WHERE _customer.id = :id", Purchase.class)
  .setParameter("id", id)
  .getResultList();
return em.createQuery(
  "SELECT COUNT(e.id) FROM Purchase e\n" +
  "INNER JOIN e.customer _customer\n" +
  "WHERE _customer.id = :id", Purchase.class)
  .setParameter("id", id)
  .getResultList();
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
return em.createQuery("SELECT e from Product e " +
  "where e.id IN (" +
  "SELECT DISTINCT _products.id" +
  "FROM Customer _customer\n" +
  "INNER JOIN _customer.purchases _purchases\n" +
  "INNER JOIN _purchases.products _products\n" +
  "WHERE _customer.premium = true)", Product.class)
  .getResultList();
List results = em.createQuery(
  "SELECT _customer.id, " +
  "SUM(_products.price)"
  "FROM Customer _customer\n" +
  "INNER JOIN _customer.purchases _purchases\n" +
  "INNER JOIN _purchases.products _products\n" +
  "GROUP BY _customer.id")
 .getResultList();
Map<Customer, Double> ret = new HashMap<>();
for (Object result : results) {
  Object[] arr = (Object[]) result;
  ret.put(customerService.findById((Long)arr[0]),
  ((Double)arr[1]));
}
return ret;

Another option for building queries dynamically is to specify them using a JPQL String rather than a SQL String. The JPQL (Java Persistence Query Language) is a DSL closely related to SQL, but adhering to the object-oriented domain model view on entities and their properties rather than on tables and columns, and generally making the language easier to use.

Historically, the JPQL was heavily inspired by Hibernate’s HQL which itself is still in use, providing a superset of JPQL functionality. HQL is not discussed in this article. If you use Hibernate rather than EclipseLink or any other persistence provider, you may want to take a look at it. Keep in mind, however, that you loose portability if you go for a proprietary solution. I would advise you to stick to JPQL which, as of now, offers a mature feature set covering virtually any DB query requirement.

When compared with dynamic native (SQL) queries, JPQL-based queries offer the following advantages:
  • Diminished complexity of the query language and close resemblance to object-oriented thinking (note the more simple JOIN clause and the object property references).
  • No dependency on native database data types, thus full portability.
  • Full support for generics.
  • Full support for named parameters.
A reasonable IDE such as NetBeans will even statically detect syntax errors and offer auto-completion within the query Strings.

As for dynamic named queries, it’s best practice to make use of the generics support provided by TypedQuery.

Still, some of the general drawbacks of String-based dynamic queries remain which would be solved using named queries (see below).

Bottom line:
  • Don’t use this facility either. Use named JPQL queries, or more strongly typed query building facilities if you still need the “dynamic” part.

Pages: 1 2 3 4

JPA query languages compared (part 1 of 4)



Accessing persistent storage with Java EE 7 comes with the choice of the right query language. This article aims to give you an overview as well as a comparison of using SQL, JPQL, the Criteria API and Querydsl for writing database queries.

JPA basics

Feel free to skip this introductory section if you’re familiar with the JPA specification and its configuration already.

The Java EE 7 umbrella specification provides two complementary standards dealing with persistent storage: JPA and JTA.
  • JPA (Java Java Persistence) 2.1 (JSR 338, 2013) abstracts DB access, providing object-relational mapping (OR/M) and domain specific languages (DSLs) to write DB queries. As of V. 2.0, the reference implementation is EclipseLink (previously, it was Hibernate).
  • JTA (Java Transaction API) 1.2 (JSR 907, 2013) abstracts transaction control, allowing declarative control over ACID properties. The reference implementation is GlassFish. JTA is not covered by this article.

Query languages

This article concentrates on how to write DB queries (the Java equivalent to SQL queries) using the query DSLs provides by JPA. In fact, JPA provides many means to write DB queries:
  • Dynamic native queries (optionally as TypedQuery) in SQL
  • Dynamic queries (optionally as TypedQuery) in JPQL
  • Named native queries (optionally as TypedQuery) in SQL
  • Named queries (optionally as TypedQuery) in JQPL
  • String-based or strongly typed Criteria API queries
There are also a few third party frameworks which provide their own query DSL for use within a JPA environment. In my experience, this one is within the most frequently used:
In practice, I often see a wild mix of these approaches, resulting in inconsistent code and a confusing technology mismatch. Of course, each of these approaches has their advantages and disadvantages, and choosing the right tool for the right job is key to create a consistent code base.

Getting started with JPA: Configuration

TL;DR: Feel free to take a look at the persistence.xml file of the demo project.

In order to get started with a JPA persistence project, you need the org.eclipse.persistence:eclipselink dependency or any other JPA implementation on the PATH, and configure a persistence-unit in persistence.xml. The NetBeans IDE provides a wizard to create a persistence unit. You may also want to refer to other online tutorials.

For a real-world JEE project, you’ll typically use transaction-type="JTA" for implicit, annotation-based transaction handling. Outside of the container, e.g. in unit tests, you’ll have to use a transaction-type="RESOURCE_LOCAL" persistence-unit.

Also, you’ll typically want to log the actual SQL statements. The configuration is JPA implementation specific. For EclipseLink, it is:
<property name="eclipselink.logging.level.sql" value="FINE"/>
<property name="eclipselink.logging.parameters" value="true"/>
This will print SQL statements as well as resolve query parameters (otherwise printed as ‘?’).

CRUD with JPA

For simple create / read by id / update / delete DB operations, there is no need to create any DB queries at all. Instead, use the EntityManager’s API to operate on an @Entity using plain OR/M. Hence, a basic abstract CrudService can be implemented like this:
@TransactionAttribute(TransactionAttributeType.MANDATORY)
public abstract class BaseService<T extends BaseModel> {
    @PersistenceContext
    EntityManager em;
    
    public abstract T create();
    
    public T findById(Long id) {
        return em.find(getModelClass(), id);
    }
    
    public List<T> findAll() {
        CriteriaQuery<T> query = em.getCriteriaBuilder().createQuery(getModelClass());
        query.select(query.from(getModelClass()));
        return (List<T>) em.createQuery(query).getResultList();
    }
    
    public long countAll() {
        CriteriaQuery<Long> query = em.getCriteriaBuilder().createQuery(Long.class);
        query.select(em.getCriteriaBuilder().count(query.from(getModelClass())));
        return em.createQuery(query).getSingleResult();
    }
    
    public T save(T entity) {        
        if (entity.getId() == null) {
            em.persist(entity);
        }
        else {
            entity = em.merge(entity);
        }
        em.flush();
        return entity;
    }
    
    public void delete(Long id) {
        T entity = em.getReference(getModelClass(), id);
        em.remove(entity);
    }
    
    protected abstract Class<T> getModelClass();
}
Building a concrete service, e.g. for an entity of type Customer, is then as simple as
public class CustomerService extends BaseService<Customer> {
    @Override
    public Customer create() {
        return new Customer();
    }

    @Override
    protected Class<Customer> getModelClass() {
        return Customer.class;
    }
}
A few notes:
  • findAll() and countAll() operations are not supported by the EntityManager API. Thus, they are here implemented using Criteria API queries. Details will follow.
  • Why building a dedicated create() method and not just invoke the entity’s constructor in the client code? Because that way, we could swap the service implementation with a service which uses a so-called extended persistence context which “magically” keeps an entity attached. This is what e.g. Adam Bien refers to as the JEE “gateway” pattern. Whether building a gateway is the right choice depends on the use case; its discussion would exceed the scope of this article.
On the entity side, thanks to OR/M, we typically make excessive use of relations cascading, e.g. when saving a parent, have changes to its children saved as well. This is accomplished using the respective JPA annotations in the entity class:
@OneToMany(mappedBy = "customer", cascade = {CascadeType.PERSIST, CascadeType.MERGE}, orphanRemoval = true)
private List<Purchase> purchases = new ArrayList<>();
In fact, we will never use explicit DB queries for CRUD operations, but use these EntityManager operations instead. Hence, the queries discussed in this article are all read-only find by / count by + parameter queries.

For performance reasons, you may want to use bulk update queries as well, but this should really be well justified (keeping in mind that premature optimization is the root of all evil).

Example queries

Throughout this article, I will use this simple business model to demonstrate query building:

  • A customer has multiple purchases (bidirectional).
  • A purchase has multiple products and vise versa (unidirectional from purchase to products).
Here’s how it would look like in a table structure:
CUSTOMER
ID NAME PREMIUM
PURCHASE
ID ORDERDATE CUSTOMER_ID
PRODUCT
ID NAME PRICE
PURCHASE_PRODUCT
PURCHASE_ID PRODUCTS_ID

For each DB query mechanism, I will show how to build the example queries presented in the following table (here, their plain SQL implementation is shown):
1. Find customer by id 2. Find by id (generic)
SELECT * FROM CUSTOMER WHERE (ID = 1)
SELECT * FROM x WHERE (ID = 1)
(where x is the table name provided; not actually supported by plain SQL)
3. Find all customers 4. Count all customers
SELECT * FROM CUSTOMER
SELECT COUNT(ID) FROM CUSTOMER
5. Find purchase by customer id 6. Count purchase by customer id
SELECT PURCHASE.* FROM PURCHASE
INNER JOIN CUSTOMER
ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID
WHERE CUSTOMER.ID = 1
SELECT COUNT(PURCHASE.ID) FROM PURCHASE
INNER JOIN CUSTOMER
ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID
WHERE CUSTOMER.ID = 1
7. Find distinct product with purchase’s customer’s special condition 8. Aggregate product price sum by purchase’s customers
SELECT PRODUCT.* FROM PRODUCT
WHERE PRODUCT.ID IN (
  SELECT DISTINCT PRODUCT.ID FROM PRODUCT
  INNER JOIN PURCHASE_PRODUCT
  ON PURCHASE_PRODUCT.PRODUCTS_ID=PRODUCT.ID
  INNER JOIN PURCHASE
  ON PURCHASE.ID=PURCHASE_PRODUCT.PURCHASE_ID
  INNER JOIN CUSTOMER
  ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID
  WHERE CUSTOMER.PREMIUM = 1
)
SELECT CUSTOMER.ID, SUM(PRODUCT.PRICE) FROM CUSTOMER
INNER JOIN PURCHASE
ON PURCHASE.CUSTOMER_ID=CUSTOMER.ID
INNER JOIN PURCHASE_PRODUCT
ON PURCHASE.ID=PURCHASE_PRODUCT.PURCHASE_ID
INNER JOIN PRODUCT
ON PURCHASE_PRODUCT.PRODUCTS_ID=PRODUCT.ID
GROUP BY CUSTOMER.ID

The accompanying GitHub project contains all the code examples in this article as a unit test which you can execute against your local database.
Pages: 1 2 3 4