There are two primary Java EE technologies for querying a database: Java Persistence Query Language (JPQL) and the Criteria API. JPQL is similar in appearance and usage to SQL while the Criteria API provides a more type-safe and object-oriented entity querying capability.

When using EJB 2.0, developers used EJB QL as the query language. With the introduction of J2EE 5.0, JPQL was introduced and replaced EJB QL. JPQL is based on the Hiberate Query Language (HQL) and is a subset of HQL. It is portable and allows queries to be executed against different data stores. This is in contrast to the use of a native query where the query is expressed in the native SQL of the data store. Another older, yet still viable technique is the Java database classes and interfaces found in the java.sql package. In this chapter we will focus on JPQL and the Criteria API.

JPQL statements are similar to those of SQL and are written as a string. However, JPQL is not type-safe. When the query is processed its string representation is evaluated and executed. If there are any errors in the query, they cannot be caught at compile-time and cannot be handled very easily during run-time. Another difference between JPQL and SQL is the focus of the languages. JPQL deals with entities while SQL is concerned with records.

The Criteria API offers better performance than JPQL but it is more verbose and difficult to use. It is essentially another Java API that has the benefit of being type-safe. Exceptions occurring during execution are more easily handled. The Using the Criteria API recipe provides an introduction to the Criteria API.

There are two types of JPQL queries: dynamic and static. A dynamic query is used as an argument of the Query's createQuery method. A static query is also called a named query. A named query is declared as part of an entity class and used with the createNamedQuery method. Static queries are more efficient and are detailed in the Using a Named query recipe. Most of the recipes used in this chapter are dynamic queries as they simplify the presentation of the various JPQL techniques. Regardless of the type of query used, JPQL works in the same manner.

The recipes found in this chapter are built around two tables: Patient and Medication. The Patient table maintains information about patients such as the patient's name and medications. The Medication table maintains information about a specific medication a patient is taking such as its name and dosage level. These two tables are related. One Patient may have zero or more medications. This is reflected in the methods of the two entity classes and the @OneToMany and @ManyToOne annotations used in defining the entity classes.

We will create two facade classes for these entity classes: PatientFacade and MedicationFacade. Both of these use the AbstractFacade as their base class. These entities, facades and their relational mapping are explained in the Populating the Patient and Medication tables recipe.

By the way, these tables are used throughout the recipes addressed in this chapter. We will populate the database in the PatientServlet. Populating the tables in code does not impose on the reader the need to import or otherwise use an existing database. The execution of multiple queries against a table will result in a table whose state may be different from what we expect. If we always execute queries against a table with the same initial state we can more easily understand and verify the results. This is the approach we will use in this chapter.

To create a query we need to use an instance of the EntityManager class. The entity facade classes expose the EntityManager class. This means we should normally execute our queries as a method of the facade class.

The JPQL language is similar to SQL. However, the parts of the query are expressed using the entity's names and its fields as opposed to specifying tables and columns. These fields are selected using an identification variable as illustrated in the Using the Select query recipe. One nice benefit of this approach is if the table or column names are changed, it does not affect the query. However, if we modify the entities then this may affect the JPQL queries.

There are three basic types of queries supported by JPQL.

In addition, the Where clause is frequently used in conjunction with these queries. This clause provides considerable control over which entities will be affected by a query. The Using the Where clause recipe covers this clause.