October 17, 2015

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