Querying Entities

Domain Models discussed how to map entities to relational tables, Manipulating discussed how to use the EntityManager to use the CRUD operations (create, read, update and delete) , we now come to the final part which tackle the querying aspect, which we use the query API and the new Java Persistence Query Language (JPQL).

We will be using the following methods to retrieve entities and related data

Query API

EJB 3 has greatly improved the query capabilities of entities, the EntityManager.find method (discussed in Manipulating) only retrieves entities by their ID or primary key, the query API allows you to write custom queries to retrieve either one of a collection of entities. The EntityManager methods are used together with the javax.persistence.Query interface methods, which perform the actual query definition, parameter binding, execution and pagination.

The JPA query API allows you to use either JPQL or SQL to create the queries, the difference is that JPQL will return entities and SQL will return database records. If you have ever use JDBC queries then this is very similar, except that the database connection is different because the EntityManager hides the complex task for you.

Basic steps for JDBC Query using SQL Basic steps for a JPA Query using JPQL
Obtain a database connection Obtain an instance of an entity manager
Create a query statement Create a query instance
Execute the statement Execute the query
Retrieve the results (database records) Retrieve the results (entities)

The query API supports two types of queries

Dynamic example

@PersistenceContext(unitName="titanJPA");
private EntityManager em;

...
public List findAllCategories() {
  Query query = em.createQuery("SELECT C FROM CATEGORY C");

  return query.getResultList();
}

Note: you can perform this all in one if you like to keep the code short, the choice is yours

return em.createQuery("SELECT C FROM CATEGORY C").getResultList();

You can named queries, which must be created before being used, it can be defined in either the entity or in the XML file defining O/R mapping metadata. Named queries have a number of benefits

Named query example

@Entity
@NamedQueries({
  @NamedQuery(
    name="findAllShips",
    query="SELECT c FROM Ship c"
  ),
  @NamedQuery(
    name="findBigShips",
    query="SELECT c FROM Ship c where ship_size = :size "
  )
})

@Table(name="SHIP")

Note: you can set the :size by using the setParameter method (see below)

using the Named query Query query = em.createNamedQuery("findAllShips");

  return query.getResultList();

The Query interface defines a number of methods, you can use either JPQL or SQL, I list these methods below and leave you to the internet for examples

Method Signature Purpose
public List getResultList() Retrieves a result set for a query
public Object getSingleResult() Retrieves a single result or object
public int executeUpdate() Executes a JPQL update or delete statement
public Query setMaxResults(int maxResult) Set the maximum number of objects to be retrieved
public Query setFirstResult(int maxResult) set the initial position for the first result being retrieved by the query
public Query setHint(String name, Object value) set a vendor specific hint for the query
public Query setParameter(String name, Object value); sets the value of a named parameter
public Query setParameter(String name, Date value, TemporalType temporalType) set the value for a named parameter when the parameter is of the Date type
public Query setParameter(String name, Calendar value, TemporalType temporalType) set the value for a named parameter when the parameter is of the Calendar type
public Query setParameter(int position, Object value) Sets the value for a positional parameter
public Query setParameter(int postition, Calendar value, TemporalType temporalType) Set the value for a positional parameter when the parameter is of the Calendar type
public Query setFlushMode(FlushModeType flushMode) Sets the flush mode (see Manipulating for more details)
Example
  query = em.createNamedQuery("findCategoryByName");
query.setParameter("categoryName", categoryName);
query.setMaxResults(10);
query.setFirstResults(3);
List categories = query.getResultList();

You can set parameters for a query in two ways, I have supplied examples of both

Positional query.setParameter(500,1000);

SELECT i from Item where i.initialPrice > ?1 and i.initialprice < ?2
Named query.setParameter("min",500);
query.setParameter("max",1000);

SELECT i from Item where i.initialPrice > :min and i.initialprice < :max

When using query you can either return a single entity or a collection of entities

Single Result

try {
  ..
  Category c = (Catoegory) query.getSingleResult();
  ..
} catch (NonUniqueResultException ex) {
  ...
} catch (NoResultException ex) {
  ...
}

Note: make sure it is a single result otherwise you will receive a NonUniqueResultException error or a NoResultException error if no result is returned.

Result List List items = query.getResultList();

A query could return millions of entities, which you may need to iterator through you can use two methods to achieve this

Using iterator List<Item> itemList = query.getResultList();
Iterator i = itemList.iterator();
while ( i.hasNext() ) {
  Item item = (Item) i.next();
  System.out.println("Item: " + item.getName());
}
Using for each loop

List<Item> itemList = query.getResultList();

for (Item item : itemList)
{
   System.out.println("Item: " + item.getName());
}

A Query Hint is a tip that is used by the persistence provider while executing queries or retrieving entities. These hints are vendor specific so you need to look what your provider provides, in the table I have given you some hibernates examples of hints

Hibernate Hint Purpose
org.hibernate.fetchSize Specifies the number of rows fetched by the JDBC driver
org.hibernate.cacheMode Specifies how to use the cache
CacheMode.REFRESH Specifies whether the cache should be refreshed from the database
org.hibernate.timeout Specifies the query timeout period

JPQL

Java Persistence Query Language (JPQL) is an extension of EJB QL (the query language of EJB 2) after the limitations had been addressed. JPQL operates on classes and objects (entities) while SQL operates on tables, columns and rows, they operate in two very different worlds. The JPQL Query parser (Processor Engine) of a persistence provider is show below, it translates the JPQL query into native SQL for the database being used by the persistence provider, JQLP may look very similar to SQL but it has it's differences so beware.

If you are already familiar with SQL then JPQL will be easy to grasp, I am not going to go into much detail there are plenty of web sites and books that can do a better job than me, I will show you what is available and an number of examples.

Types Reserved Words
Statements and Clauses SELECT, UPDATE, DELETE, FROM, WHERE, GROUP,HAVING, ORDER, BY, ASC, DESC
Joins JOIN, OUTER, INNER, LEFT, FETCH
Conditions and Operators DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS, UNKNOWN, EMPTY, OF, IS, NEW, EXISTS, ALL, ANY, SOME
Functions AVG, MAX, MIN, SUM, COUNT, UPPER, LOWER, TRIM, POSITION, CHARACTER_LENGTH, CHAR_LENGTH, BIT_LENGTH, CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP
Examples
SELECT

SELECT c FROM category c
SELECT c FROM category c WHERE c.categoryName LIKE :categoryName ORDER BY categoryId
SELECT c FROM bid c WHERE c.user.lname = 'Valle'
SELECT c FROM bid c WHERE c.amount > 100;
SELECT c FROM category c WHERE c.categoryName = 'Fishing' OR c.categoryName = 'Football'
SELECT c FROM bid c WHERE c.amount BETWEEN :lowerRange OR :upperRange
SELECT c FROM category c WHERE c.category IN ('Fishing', 'Football', 'Computing')
SELECT c FROM category c WHERE c.category NOT IN ('Fishing', 'Football', 'Computing')
SELECT c FROM category c WHERE c.category IN (SELECT h FROM Hobbies h WHERE h.name LIKE 'Fish%')

UPDATE UPDATE Seller s SET s.status = 'G', s.commissionRate = 10 WHERE s.lastName like 'Valle'
DELETE DELETE Seller s WHERE s.lastName = 'Valle'

When dealing with NULL different databases can treat it differently, below is a table that details the result of Boolean operating involving NULL, you can use the IS NULL or the IS NOT NULL operators to check a value to see if it contains a null value. To check if a collection

Expression 1 Value
Boolean Operator
Expression 2 Value
Result
TRUE
AND
NULL
UNKNOWN
FALSE
AND
NULL
FALSE
NULL
AND
NULL
UNKNOWN
TRUE
OR
NULL
TRUE
NULL
OR
NULL
UNKNOWN
FALSE
OR
NULL
UNKNOWN
<blank>
NOT
NULL
UNKNOWN

To check if a collection is empty you use the IS EMPTY or IS NOT EMPTY operators

Check a collection SELECT c FROM Category c WHERE c.items IS EMPTY

You can also check the existence of an entity in a collection, by using the MEMBER OF operator

Check if an entity exists in a collection SELECT c FROM Category c WHERE :item MEMBER of c.items

There are a number of other functions you can use

String Functions CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE
Arithmetic Functions ABS, SQRT, MOD, SIZE
Temporal Functions CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
Aggregate Functions AVG, COUNT, MAX, MIN, SUM
Grouping GROUP BY, HAVING
Ordering ORDER BY [ASC | DESC]
Subqueries [NOT] IN, [NOT] EXISTS, ALL, ANY, SOME (subquery)
Joins Theta, [INNER] JOIN, LEFT [OUTER] JOIN, FETCH JOIN

Native SQL Queries

Native SQL is understood by many databases including Oracle, MySQL, etc, you should however be careful as you might be tying yourself to a particular vendor, but here are the details on how to use native queries

Native dynamic query

Query q = em.createNativeQuery ("select user_id, first_name, last_name from users", actionbazaar.persistence.User.class);
return q.getResultList();

Note: two parameters are passed the SQL statement and the entity class being returned.

Creating a SqlResultSetMapping @SqlResultSetMapping(name = "UserResults",
  entities = @EntityResult(
    entityClass = actionbazaar.persistence.User.class))

Query q = em.createNativeQuery ("select user_id, first_name, last_name from users", "UserResults");
return q.getResultList();
@NamedNativeQuery

@NamedNativeQuery(
  name = "findAllCategories"
  query = "select cateory_name from Category where category = ?",
  hints = {@QueryHint(name = "toplink.cache-usage", value="DoNotCheckCache")},
  resultClass = actionbazaar.persistence.User.class
)

# Using the named native query

return em.createNamedQuery("findAllCategories").setParameter(1,5).getResultList();

One thing to note is that JPA does not support stored procedures, however you can use simple store functions (without parameters) with a native SQL query.

Although I have discussed native SQL it is not recommended over JPA, it is time consuming and hard to debug.