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
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"); Note: you can perform this all in one if you like to keep the code short, the choice is yours |
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 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 { 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) |
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 |
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 |
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 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); 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( # 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.