October 17, 2015

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