The Java EE 5 Tutorial

Part V Persistence

Part Five explores the Java Persistence API.

Chapter 24 Introduction to the Java Persistence API

The Java Persistence API provides an object/relational mapping facility to Java developers for managing relational data in Java applications. Java Persistence consists of three areas:

Entities

An entity is a lightweight persistence domain object. Typically an entity represents a table in a relational database, and each entity instance corresponds to a row in that table. The primary programming artifact of an entity is the entity class, although entities can use helper classes.

The persistent state of an entity is represented either through persistent fields or persistent properties. These fields or properties use object/relational mapping annotations to map the entities and entity relationships to the relational data in the underlying data store.

Requirements for Entity Classes

An entity class must follow these requirements:

Persistent Fields and Properties in Entity Classes

The persistent state of an entity can be accessed either through the entity’s instance variables or through JavaBeans-style properties. The fields or properties must be of the following Java language types:

Entities may either use persistent fields or persistent properties. If the mapping annotations are applied to the entity’s instance variables, the entity uses persistent fields. If the mapping annotations are applied to the entity’s getter methods for JavaBeans-style properties, the entity uses persistent properties. You cannot apply mapping annotations to both fields and properties in a single entity.

Persistent Fields

If the entity class uses persistent fields, the Persistence runtime accesses entity class instance variables directly. All fields not annotated javax.persistence.Transient or not marked as Java transient will be persisted to the data store. The object/relational mapping annotations must be applied to the instance variables.

Persistent Properties

If the entity uses persistent properties, the entity must follow the method conventions of JavaBeans components. JavaBeans-style properties use getter and setter methods that are typically named after the entity class’s instance variable names. For every persistent property property of type Type of the entity, there is a getter method getProperty and setter method setProperty. If the property is a boolean, you may use isProperty instead of getProperty. For example, if a Customer entity uses persistent properties, and has a private instance variable called firstName, the class defines a getFirstName and setFirstName method for retrieving and setting the state of the firstName instance variable.

The method signature for single-valued persistent properties are as follows:

Type getProperty()
void setProperty(Type type)

Collection-valued persistent fields and properties must use the supported Java collection interfaces regardless of whether the entity uses persistent fields or properties. The following collection interfaces may be used:

If the entity class uses persistent fields, the type in the above method signatures must be one of these collection types. Generic variants of these collection types may also be used. For example, if the Customer entity has a persistent property that contains a set of phone numbers, it would have the following methods:

Set<PhoneNumber> getPhoneNumbers() {}
void setPhoneNumbers(Set<PhoneNumber>) {}

The object/relational mapping annotations for must be applied to the getter methods. Mapping annotations cannot be applied to fields or properties annotated @Transient or marked transient.

Primary Keys in Entities

Each entity has a unique object identifier. A customer entity, for example, might be identified by a customer number. The unique identifier, or primary key, enables clients to locate a particular entity instance. Every entity must have a primary key. An entity may have either a simple or a composite primary key.

Simple primary keys use the javax.persistence.Id annotation to denote the primary key property or field.

Composite primary keys must correspond to either a single persistent property or field, or to a set of single persistent properties or fields. Composite primary keys must be defined in a primary key class. Composite primary keys are denoted using the javax.persistence.EmbeddedId and javax.persistence.IdClass annotations.

The primary key, or the property or field of a composite primary key, must be one of the following Java language types:

Floating point types should never be used in primary keys. If you use a generated primary key, only integral types will be portable.

Primary Key Classes

A primary key class must meet these requirements:

The following primary key class is a composite key, the orderId and itemId fields together uniquely identify an entity.

public final class LineItemKey implements Serializable {
    public Integer orderId;
    public int itemId;

    public LineItemKey() {}

    public LineItemKey(Integer orderId, int itemId) {
        this.orderId = orderId;
        this.itemId = itemId;
    }

    public boolean equals(Object otherOb) {
        if (this == otherOb) {
            return true;
        }
        if (!(otherOb instanceof LineItemKey)) {
            return false;
        }
        LineItemKey other = (LineItemKey) otherOb;
        return (
                    (orderId==null?other.orderId==null:orderId.equals
                    (other.orderId)
                    )
                    &&
                    (itemId == other.itemId)
                );
    }

    public int hashCode() {
        return (
                    (orderId==null?0:orderId.hashCode())
                    ^
                    ((int) itemId)
                );
    }

    public String toString() {
        return "" + orderId + "-" + itemId;
    }
}

Multiplicity in Entity Relationships

There are four types of multiplicities: one-to-one, one-to-many, many-to-one, and many-to-many.

One-to-one: Each entity instance is related to a single instance of another entity. For example, to model a physical warehouse in which each storage bin contains a single widget, StorageBin and Widget would have a one-to-one relationship. One-to-one relationships use the javax.persistence.OneToOne annotation on the corresponding persistent property or field.

One-to-many: An entity instance can be related to multiple instances of the other entities. A sales order, for example, can have multiple line items. In the order application, Order would have a one-to-many relationship with LineItem. One-to-many relationships use the javax.persistence.OneToMany annotation on the corresponding persistent property or field.

Many-to-one: Multiple instances of an entity can be related to a single instance of the other entity. This multiplicity is the opposite of a one-to-many relationship. In the example just mentioned, from the perspective of LineItem the relationship to Order is many-to-one. Many-to-one relationships use the javax.persistence.ManyToOne annotation on the corresponding persistent property or field.

Many-to-many: The entity instances can be related to multiple instances of each other. For example, in college each course has many students, and every student may take several courses. Therefore, in an enrollment application, Course and Student would have a many-to-many relationship. Many-to-many relationships use the javax.persistence.ManyToMany annotation on the corresponding persistent property or field.

Direction in Entity Relationships

The direction of a relationship can be either bidirectional or unidirectional. A bidirectional relationship has both an owning side and an inverse side. A unidirectional relationship has only an owning side. The owning side of a relationship determines how the Persistence runtime makes updates to the relationship in the database.

Bidirectional Relationships

In a bidirectional relationship, each entity has a relationship field or property that refers to the other entity. Through the relationship field or property, an entity class’s code can access its related object. If an entity has a related field, then the entity is said to “know” about its related object. For example, if Order knows what LineItem instances it has and if LineItem knows what Order it belongs to, then they have a bidirectional relationship.

Bidirectional relationships must follow these rules:

Unidirectional Relationships

In a unidirectional relationship, only one entity has a relationship field or property that refers to the other. For example, LineItem would have a relationship field that identifies Product, but Product would not have a relationship field or property for LineItem. In other words, LineItem knows about Product, but Product doesn’t know which LineItem instances refer to it.

Queries and Relationship Direction

Java Persistence query language queries often navigate across relationships. The direction of a relationship determines whether a query can navigate from one entity to another. For example, a query can navigate from LineItem to Product but cannot navigate in the opposite direction. For Order and LineItem, a query could navigate in both directions, because these two entities have a bidirectional relationship.

Cascade Deletes and Relationships

Entities that use relationships often have dependencies on the existence of the other entity in the relationship. For example, a line item is part of an order, and if the order is deleted, then the line item should also be deleted. This is called a cascade delete relationship.

Cascade delete relationships are specified using the cascade=REMOVE element specification for @OneToOne and @OneToMany relationships. For example:

@OneToMany(cascade=REMOVE, mappedBy="customer")
public Set<Order> getOrders() { return orders; }

Entity Inheritance

Entities support class inheritance, polymorphic associations, and polymorphic queries. They can extend non-entity classes, and non-entity classes can extend entity classes. Entity classes can be both abstract and concrete.

The roster example application demonstrates entity inheritance, and is described in Entity Inheritance in the roster Application.

Abstract Entities

An abstract class may be declared an entity by decorating the class with @Entity. Abstract entities differ from concrete entities only in that they cannot be instantiated.

Abstract entities can be queried just like concrete queries. If an abstract entity is the target of a query, the query operates on all the concrete subclasses of the abstract entity.

@Entity
public abstract class Employee {
    @Id
    protected Integer employeeId;
    ...
}
@Entity
public class FullTimeEmployee extends Employee {
    protected Integer salary;
    ...
}
@Entity
public class PartTimeEmployee extends Employee {
    protected Float hourlyWage;
}

Mapped Superclasses

Entities may inherit from superclasses that contain persistent state and mapping information, but are not entities. That is, the superclass is not decorated with the @Entity annotation, and is not mapped as an entity by the Java Persistence provider. These superclasses are most often used when you have state and mapping information common to multiple entity classes.

Mapped superclasses are specified by decorating the class with the javax.persistence.MappedSuperclass annotation.

@MappedSuperclass
public class Employee {
    @Id
    protected Integer employeeId;
    ...
}
@Entity
public class FullTimeEmployee extends Employee {
    protected Integer salary;
    ...
}
@Entity
public class PartTimeEmployee extends Employee {
    protected Float hourlyWage;
    ...
}

Mapped superclasses are not queryable, and can’t be used in EntityManager or Query operations. You must use entity subclasses of the mapped superclass in EntityManager or Query operations. Mapped superclasses can’t be targets of entity relationships. Mapped superclasses can be abstract or concrete.

Mapped superclasses do not have any corresponding tables in the underlying datastore. Entities that inherit from the mapped superclass define the table mappings. For instance, in the code sample above the underlying tables would be FULLTIMEEMPLOYEE and PARTTIMEEMPLOYEE, but there is no EMPLOYEE table.

Non-Entity Superclasses

Entities may have non-entity superclasses, and these superclasses can be either abstract or concrete. The state of non-entity superclasses is non-persistent, and any state inherited from the non-entity superclass by an entity class is non-persistent. Non-entity superclasses may not be used in EntityManager or Query operations. Any mapping or relationship annotations in non-entity superclasses are ignored.

Entity Inheritance Mapping Strategies

You can configure how the Java Persistence provider maps inherited entities to the underlying datastore by decorating the root class of the hierarchy with the javax.persistence.Inheritance annotation. There are three mapping strategies that are used to map the entity data to the underlying database:

The strategy is configured by setting the strategy element of @Inheritance to one of the options defined in the javax.persistence.InheritanceType enumerated type:

public enum InheritanceType {
    SINGLE_TABLE,
    JOINED,
    TABLE_PER_CLASS
};

The default strategy is InheritanceType.SINGLE_TABLE, and is used if the @Inheritance annotation is not specified on the root class of the entity hierarchy.

The Single Table per Class Hierarchy Strategy

With this strategy, which corresponds to the default InheritanceType.SINGLE_TABLE, all classes in the hierarchy are mapped to a single table in the database. This table has a discriminator column, a column that contains a value that identifies the subclass to which the instance represented by the row belongs.

The discriminator column can be specified by using the javax.persistence.DiscriminatorColumn annotation on the root of the entity class hierarchy.

Table 24–1 @DiscriminatorColumn Elements

Type 

Name 

Description 

String

name

The name of the column in the table to be used as the discriminator column. The default is DTYPE. This element is optional.

DiscriminatorType

discriminatorType

The type of the column to be used as a discriminator column. The default is DiscriminatorType.STRING. This element is optional.

String

columnDefinition

The SQL fragment to use when creating the discriminator column. The default is generated by the Persistence provider, and is implementation-specific. This element is optional. 

String

length

The column length for String-based discriminator types. This element is ignored for non-String discriminator types. The default is 31. This element is optional.

The javax.persistence.DiscriminatorType enumerated type is used to set the type of the discriminator column in the database by setting the discriminatorType element of @DiscriminatorColumn to one of the defined types. DiscriminatorType is defined as:

public enum DiscriminatorType {
    STRING,
    CHAR,
    INTEGER
};

If @DiscriminatorColumn is not specified on the root of the entity hierarchy and a discriminator column is required, the Persistence provider assumes a default column name of DTYPE, and column type of DiscriminatorType.STRING.

The javax.persistence.DiscriminatorValue annotation may be used to set the value entered into the discriminator column for each entity in a class hierarchy. You may only decorate concrete entity classes with @DiscriminatorValue.

If @DiscriminatorValue is not specified on an entity in a class hierarchy that uses a discriminator column, the Persistence provider will provide a default, implementation-specific value. If the discriminatorType element of @DiscriminatorColumn is DiscriminatorType.STRING, the default value is the name of the entity.

This strategy provides good support for polymorphic relationships between entities and queries that cover the entire entity class hierarchy. However, it requires the columns that contain the state of subclasses to be nullable.

The Table per Concrete Class Strategy

In this strategy, which corresponds to InheritanceType.TABLE_PER_CLASS, each concrete class is mapped to a separate table in the database. All fields or properties in the class, including inherited fields or properties, are mapped to columns in the class’s table in the database.

This strategy provides poor support for polymorphic relationships, and usually requires either SQL UNION queries or separate SQL queries for each subclass for queries that cover the entire entity class hierarchy.

Support for this strategy is optional, and may not be supported by all Java Persistence API providers. The default Java Persistence API provider in the Application Server does not support this strategy.

The Joined Subclass Strategy

In this strategy, which corresponds to InheritanceType.JOINED, the root of the class hierarchy is represented by a single table, and each subclass has a separate table that only contains those fields specific to that subclass. That is, the subclass table does not contain columns for inherited fields or properties. The subclass table also has a column or columns that represent its primary key, which is a foreign key to the primary key of the superclass table.

This strategy provides good support for polymorphic relationships, but requires one or more join operations to be performed when instantiating entity subclasses. This may result in poor performance for extensive class hierarchies. Similarly, queries that cover the entire class hierarchy require join operations between the subclass tables, resulting in decreased performance.

Some Java Persistence API providers, including the default provider in the Application Server, require a discriminator column in the table that corresponds to the root entity when using the joined subclass strategy. If you are not using automatic table creation in your application, make sure the database table is set up correctly for the discriminator column defaults, or use the @DiscriminatorColumn annotation to match your database schema. For information on discriminator columns, see The Single Table per Class Hierarchy Strategy.

Managing Entities

Entities are managed by the entity manager. The entity manager is represented by javax.persistence.EntityManager instances. Each EntityManager instance is associated with a persistence context. A persistence context defines the scope under which particular entity instances are created, persisted, and removed.

The Persistence Context

A persistence context is a set of managed entity instances that exist in a particular data store. The EntityManager interface defines the methods that are used to interact with the persistence context.

The EntityManager Interface

The EntityManager API creates and removes persistent entity instances, finds entities by the entity’s primary key, and allows queries to be run on entities.

Container-Managed Entity Managers

With a container-managed entity manager, an EntityManager instance’s persistence context is automatically propagated by the container to all application components that use the EntityManager instance within a single Java Transaction Architecture (JTA) transaction.

JTA transactions usually involve calls across application components. To complete a JTA transaction, these components usually need access to a single persistence context. This occurs when an EntityManager is injected into the application components by means of the javax.persistence.PersistenceContext annotation. The persistence context is automatically propagated with the current JTA transaction, and EntityManager references that are mapped to the same persistence unit provide access to the persistence context within that transaction. By automatically propagating the persistence context, application components don’t need to pass references to EntityManager instances to each other in order to make changes within a single transaction. The Java EE container manages the life cycle of container-managed entity managers.

To obtain an EntityManager instance, inject the entity manager into the application component:

@PersistenceContext
EntityManager em;

Application-Managed Entity Managers

With application-managed entity managers, on the other hand, the persistence context is not propagated to application components, and the life cycle of EntityManager instances is managed by the application.

Application-managed entity managers are used when applications need to access a persistence context that is not propagated with the JTA transaction across EntityManager instances in a particular persistence unit. In this case, each EntityManager creates a new, isolated persistence context. The EntityManager, and its associated persistence context, is created and destroyed explicitly by the application.

Applications create EntityManager instances in this case by using the createEntityManager method of javax.persistence.EntityManagerFactory.

To obtain an EntityManager instance, you first must obtain an EntityManagerFactory instance by injecting it into the application component by means of the javax.persistence.PersistenceUnit annotation:

@PersistenceUnit
EntityManagerFactory emf;

Then, obtain an EntityManager from the EntityManagerFactory instance:

EntityManager em = emf.createEntityManager();

Finding Entities Using the EntityManager

The EntityManager.find method is used to look up entities in the data store by the entity’s primary key.

@PersistenceContext
EntityManager em;
public void enterOrder(int custID, Order newOrder) {
    Customer cust = em.find(Customer.class, custID);
    cust.getOrders().add(newOrder);
    newOrder.setCustomer(cust);
}

Managing an Entity Instance’s Life Cycle

You manage entity instances by invoking operations on the entity by means of an EntityManager instance. Entity instances are in one of four states: new, managed, detached, or removed.

New entity instances have no persistent identity and are not yet associated with a persistence context.

Managed entity instances have a persistent identity and are associated with a persistence context.

Detached entity instances have a persistent identify and are not currently associated with a persistence context.

Removed entity instances have a persistent identity, are associated with a persistent context, and are scheduled for removal from the data store.

Persisting Entity Instances

New entity instances become managed and persistent either by invoking the persist method, or by a cascading persist operation invoked from related entities that have the cascade=PERSIST or cascade=ALL elements set in the relationship annotation. This means the entity’s data is stored to the database when the transaction associated with the persist operation is completed. If the entity is already managed, the persist operation is ignored, although the persist operation will cascade to related entities that have the cascade element set to PERSIST or ALL in the relationship annotation. If persist is called on a removed entity instance, it becomes managed. If the entity is detached, persist will throw an IllegalArgumentException, or the transaction commit will fail.

@PersistenceContext
EntityManager em;
...
public LineItem createLineItem(Order order, Product product,
        int quantity) {
    LineItem li = new LineItem(order, product, quantity);
    order.getLineItems().add(li);
    em.persist(li);
    return li;
}

The persist operation is propagated to all entities related to the calling entity that have the cascade element set to ALL or PERSIST in the relationship annotation.

@OneToMany(cascade=ALL, mappedBy="order")
public Collection<LineItem> getLineItems() {
    return lineItems;
}

Removing Entity Instances

Managed entity instances are removed by invoking the remove method, or by a cascading remove operation invoked from related entities that have the cascade=REMOVE or cascade=ALL elements set in the relationship annotation. If the remove method is invoked on a new entity, the remove operation is ignored, although remove will cascade to related entities that have the cascade element set to REMOVE or ALL in the relationship annotation. If remove is invoked on a detached entity it will throw an IllegalArgumentException, or the transaction commit will fail. If remove is invoked on an already removed entity, it will be ignored. The entity’s data will be removed from the data store when the transaction is completed, or as a result of the flush operation.

public void removeOrder(Integer orderId) {
    try {
        Order order = em.find(Order.class, orderId);
        em.remove(order);
    }...

In this example, all LineItem entities associated with the order are also removed, as Order.getLineItems has cascade=ALL set in the relationship annotation.

Synchronizing Entity Data to the Database

The state of persistent entities is synchronized to the database when the transaction with which the entity is associated commits. If a managed entity is in a bidirectional relationship with another managed entity, the data will be persisted based on the owning side of the relationship.

To force synchronization of the managed entity to the data store, invoke the flush method of the entity. If the entity is related to another entity, and the relationship annotation has the cascade element set to PERSIST or ALL, the related entity’s data will be synchronized with the data store when flush is called.

If the entity is removed, calling flush will remove the entity data from the data store.

Creating Queries

The EntityManager.createQuery and EntityManager.createNamedQuery methods are used to query the datastore using Java Persistence query language queries. See Chapter 27, The Java Persistence Query Language for more information on the query language.

The createQuery method is used to create dynamic queries, queries that are defined directly within an application’s business logic.

public List findWithName(String name) {
return em.createQuery(
    "SELECT c FROM Customer c WHERE c.name LIKE :custName")
    .setParameter("custName", name)
    .setMaxResults(10)
    .getResultList();
}

The createNamedQuery method is used to create static queries, queries that are defined in metadata using the javax.persistence.NamedQuery annotation. The name element of @NamedQuery specifies the name of the query that will be used with the createNamedQuery method. The query element of @NamedQuery is the query.

@NamedQuery(
    name="findAllCustomersWithName",
    query="SELECT c FROM Customer c WHERE c.name LIKE :custName"
)

Here’s an example of createNamedQuery, which uses the @NamedQuery defined above.

@PersistenceContext
public EntityManager em;
...
customers = em.createNamedQuery("findAllCustomersWithName")
    .setParameter("custName", "Smith")
    .getResultList();

Named Parameters in Queries

Named parameters are parameters in a query that are prefixed with a colon (:). Named parameters in a query are bound to an argument by the javax.persistence.Query.setParameter(String name, Object value) method. In the following example, the name argument to the findWithName business method is bound to the :custName named parameter in the query by calling Query.setParameter.

public List findWithName(String name) {
    return em.createQuery(
        "SELECT c FROM Customer c WHERE c.name LIKE :custName")
        .setParameter("custName", name)
        .getResultList();
}

Named parameters are case-sensitive, and may be used by both dynamic and static queries.

Positional Parameters in Queries

You may alternately use positional parameters in queries, instead of named parameters. Positional parameters are prefixed with a question mark (?) followed the numeric position of the parameter in the query. The Query.setParameter(integer position, Object value) method is used to set the parameter values.

In the following example, the findWithName business method is rewritten to use input parameters:

public List findWithName(String name) {
    return em.createQuery(
        “SELECT c FROM Customer c WHERE c.name LIKE ?1”)
        .setParameter(1, name)
        .getResultList();
}

Input parameters are numbered starting from 1. Input parameters are case-sensitive, and may be used by both dynamic and static queries.

Persistence Units

A persistence unit defines a set of all entity classes that are managed by EntityManager instances in an application. This set of entity classes represents the data contained within a single data store.

Persistence units are defined by the persistence.xml configuration file. The JAR file or directory whose META-INF directory contains persistence.xml is called the root of the persistence unit. The scope of the persistence unit is determined by the persistence unit’s root.

Each persistence unit must be identified with a name that is unique to the persistence unit’s scope.

Persistent units can be packaged as part of a WAR or EJB JAR file, or can be packaged as a JAR file that can then be included in an WAR or EAR file.

If you package the persistent unit as a set of classes in an EJB JAR file, persistence.xml should be put in the EJB JAR’s META-INF directory.

If you package the persistence unit as a set of classes in a WAR file, persistence.xml should be located in the WAR file’s WEB-INF/classes/META-INF directory.

If you package the persistence unit in a JAR file that will be included in a WAR or EAR file, the JAR file should be located:

The persistence.xml File

persistence.xml defines one or more persistence units. The following is an example persistence.xml file.

<persistence>
    <persistence-unit name="OrderManagement">
        <description>This unit manages orders and customers.
            It does not rely on any vendor-specific features and can
            therefore be deployed to any persistence provider.
        </description>
        <jta-data-source>jdbc/MyOrderDB</jta-data-source>
        <jar-file>MyOrderApp.jar</jar-file>
        <class>com.widgets.Order</class>
        <class>com.widgets.Customer</class>
    </persistence-unit>
</persistence>

This file defines a persistence unit named OrderManagement, which uses a JTA-aware data source jdbc/MyOrderDB. The jar-file and class elements specify managed persistence classes: entity classes, embeddable classes, and mapped superclasses. The jar-file element specifies JAR files that are visible to the packaged persistence unit that contain managed persistence classes, while the class element explicitly names managed persistence classes.

The jta-data-source (for JTA-aware data sources) and non-jta-data-source (non-JTA-aware data sources) elements specify the global JNDI name of the data source to be used by the container.

Chapter 25 Persistence in the Web Tier

This chapter describes how to use the Java Persistence API from web applications. The material here focuses on the source code and settings of an example called bookstore, a web application that manages entities related to a book store. This chapter assumes that you are familiar with the concepts detailed in Chapter 24, Introduction to the Java Persistence API.

Accessing Databases from Web Applications

Data that is shared between web components and is persistent between invocations of a web application is usually maintained in a database. Web applications use the Java Persistence API (see Chapter 24, Introduction to the Java Persistence API) to access relational databases.

The Java Persistence API provides a facility for managing the object/relational mapping (ORM) of Java objects to persistent data (stored in a database). A Java object that maps to a database table is called an entity class. It is a regular Java object (also known as a POJO, or plain, old Java object) with properties that map to columns in the database table. The Duke’s Bookstore application has one entity class, called Book that maps to WEB_BOOKSTORE_BOOKS.

To manage the interaction of entities with the Java Persistence facility, an application uses the EntityManager interface. This interface provides methods that perform common database functions, such as querying and updating the database. The BookDBAO class of the Duke’s Bookstore application uses the entity manager to query the database for the book data and to update the inventory of books that are sold.

The set of entities that can be managed by an entity manager are defined in a persistence unit. It oversees all persistence operations in the application. The persistence unit is configured by a descriptor file called persistence.xml. This file also defines the data source, what type of transactions the application uses, along with other information. For the Duke’s Bookstore application, the persistence.xml file and the Book class are packaged into a separate JAR file and added to the application’s WAR file.

As in JDBC technology, a DataSource object has a set of properties that identify and describe the real world data source that it represents. These properties include information such as the location of the database server, the name of the database, the network protocol to use to communicate with the server, and so on.

An application that uses the Java Persistence API does not need to explicitly create a connection to the data source, as it would when using JDBC technology exclusively. Still, the DataSource object must be created in the Application Server.

To maintain the catalog of books, the Duke’s Bookstore examples described in Chapters Further Information about Web Applications through Including the Classes, Pages, and Other Resources use the Java DB evaluation database included with the Application Server.

To populate the database, follow the instructions in Populating the Example Database.

To create a data source, follow the instructions in Creating a Data Source in the Application Server.

This section describes the following:

Defining the Persistence Unit

As described in Accessing Databases from Web Applications, a persistence unit is defined by a persistence.xml file, which is packaged with the application WAR file. This file includes the following:

The jta-data-source element indicates that the transactions in which the entity manager takes part are JTA transactions, meaning that transactions are managed by the container. Alternatively, you can use resource-local transactions, which are transactions controlled by the application itself. In general, web application developers will use JTA transactions so that they don’t need to manually manage the life cycle of the EntityManager instance.

A resource-local entity manager cannot participate in global transactions. In addition, the web container will not roll back pending transactions left behind by poorly written applications.

Creating an Entity Class

As explained in Accessing Databases from Web Applications, an entity class is a component that represents a table in the database. In the case of the Duke’s Bookstore application, there is only one database table and therefore only one entity class: the Book class.

The Book class contains properties for accessing each piece of data for a particular book, such as the book’s title and author. To make it an entity class that is accessible to an entity manager, you need to do the following:

The following code shows part of the Book class:

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="WEB_BOOKSTORE_BOOKS")

public class Book implements Serializable {

    private String bookId;
    private String title;

    public Book() { }

    public Book(String bookId, String title, ...) {
        this.bookId = bookId;
        this.title = title;
        ...
    }

    @Id
    public String getBookId() {
        return this.bookId;
    }

    public String getTitle() {
        return this.title;
    }
    ...

    public void setBookId(String id) {
        this.bookId=id;
    }

    public void setTitle(String title) {
        this.title=title;
    }
    ...
}

Obtaining Access to an Entity Manager

The BookDBAO object of the Duke’s Bookstore application includes methods for getting the book data from the database and updating the inventory in the database when books are sold. In order to perform database queries, the BookDBAO object needs to obtain an EntityManager instance.

The Java Persistence API allows developers to use annotations to identify a resource so that the container can transparently inject it into an object. You can give an object access to an EntityManager instance by using the @PersistenceUnit annotation to inject an EntityManagerFactory, from which you can obtain an EntityManager instance.

Unfortunately for the web application developer, resource injection using annotations can only be used with classes that are managed by a Java EE compliant container. Because the web container does not manage JavaBeans components, you cannot inject resources into them. One exception is a request-scoped JavaServer Faces managed bean. These beans are managed by the container and therefore support resource injection. This is only helpful if your application is a JavaServer Faces application.

You can still use resource injection in a web application that is not a JavaServer Faces application if you can do it in an object that is managed by the container. These objects include servlets and ServletContextListener objects. These objects can then give the application’s beans access to the resources.

In the case of Duke’s Bookstore, the ContextListener object creates the BookDBAO object and puts it into application scope. In the process, it passes to the BookDBAO object the EntityManagerFactory object that was injected into ContextListener:

public final class ContextListener implements SerlvetContextListener {
...
@PersistenceUnit
private EntityManagerFactory emf;

public void contextInitialized(ServletContexEvent event) {
    context = event.getServletContext();
    ...
    try {
        BookDBAO bookDB = new BookDBAO(emf);
        context.setAttribute("bookDB", bookDB);
    } catch (Exception ex) {
        System.out.println(
            "Couldn’t create bookstore database bean: "
                 + ex.getMessage());
    }
}
}

The BookDBAO object can then obtain an EntityManager from the EntityManagerFactory that the ContextListener object passes to it:

private EntityManager em;

public BookDBAO (EntityManagerFactory emf) throws Exception {
    em = emf.getEntityManager();
    ...
}

The JavaServer Faces version of Duke’s Bookstore gets access to the EntityManager instance a little differently. Because managed beans allow resource injection, you can inject the EntityManagerFactory instance into BookDBAO.

In fact, you can bypass injecting EntityManagerFactory and instead inject the EntityManager directly into BookDBAO. This is because thread safety is not an issue with request-scoped beans. Conversely, developers need to be concerned with thread safety when working with servlets and listeners. Therefore, a servlet or listener needs to inject an EntityManagerFactory instance, which is thread-safe, whereas a persistence context is not thread-safe. The following code shows part of the BookDBAO object included in the JavaServer Faces version of Duke’s Bookstore:

import javax.ejb.*;
import javax.persistence.*;
import javax.transaction.NotSupportedException;
public class BookDBAO {

    @PersistenceContext
    private EntityManager em;
...

As shown in the preceding code, an EntityManager instance is injected into an object using the @PersistenceContext annotation. An EntityManager instance is associated with a persistence context, which is a set of entity instances that the entity manager is tasked with managing.

The annotation may specify the name of the persistence unit with which it is associated. This name must match a persistence unit defined in the application’s persistence.xml file.

The next section explains how the BookDBAO object uses the entity manager instance to query the database.

Accessing Data from the Database

After the BookDBAO object obtains an EntityManager instance, it can access data from the database. The getBooks method of BookDBAO calls the createQuery method of the EntityManager instance to retrieve a list of all books by bookId:

public List getBooks() throws BooksNotFoundException {
    try {
        return em.createQuery(
            "SELECT bd FROM Book bd ORDER BY bd.bookId").
                getResultList();
    } catch(Exception ex){
        throw new BooksNotFoundException("Could not get books: "
             + ex.getMessage());
    }
}

The getBook method of BookDBAO uses the find method of the EntityManager instance to search the database for a particular book and return the associated Book instance:

public Book getBook(String bookId) throws BookNotFoundException {
    Book requestedBook = em.find(Book.class, bookId);
    if (requestedBook == null) {
        throw new BookNotFoundException("Couldn’t find book: "
             + bookId);
    }
    return requestedBook;
}

The next section describes how Duke’s Bookstore performs updates to the data.

Updating Data in the Database

In the Duke’s Bookstore application, updates to the database involve decrementing the inventory count of a book when the user buys copies of the book. The BookDBAO performs this update in the buyBooks and buyBook methods:

public void buyBooks(ShoppingCart cart) throws OrderException{
    Collection items = cart.getItems();
    Iterator i = items.iterator();
    try {
        while (i.hasNext()) {
            ShoppingCartItem sci = (ShoppingCartItem)i.next();
            Book bd = (Book)sci.getItem();
            String id = bd.getBookId();
            int quantity = sci.getQuantity();
            buyBook(id, quantity);
        }
    } catch (Exception ex) {
        throw new OrderException("Commit failed: "
             + ex.getMessage());
    }
}
public void buyBook(String bookId, int quantity)
     throws OrderException {
    try {
        Book requestedBook = em.find(Book.class, bookId);
        if (requestedBook != null) {
            int inventory = requestedBook.getInventory();
            if ((inventory - quantity) >= 0) {
                int newInventory = inventory - quantity;
                requestedBook.setInventory(newInventory);
            } else{
                throw new OrderException("Not enough of "
                     + bookId + " in stock to complete order.");
            }
        }
    } catch (Exception ex) {
        throw new OrderException("Couldn’t purchase book: "
             + bookId + ex.getMessage());
    }
}

In the buyBook method, the find method of the EntityManager instance retrieves one of the books that is in the shopping cart. The buyBook method then updates the inventory on the Book object.

To ensure that the update is processed in its entirety, the call to buyBooks is wrapped in a single transaction. In the JSP versions of Duke’s Bookstore, the Dispatcher servlet calls buyBooks and therefore sets the transaction demarcations.

In the following code, the UserTransaction resource is injected into the Dispatcher servlet. UserTransaction is an interface to the underlying JTA transaction manager used to begin a new transaction and end a transaction. After getting the UserTransaction resource, the servlet calls to the begin and commit methods of UserTransaction to mark the boundaries of the transaction. The call to the rollback method of UserTransaction undoes the effects of all statements in the transaction so as to protect the integrity of the data.

@Resource
UserTransaction utx;
...
try {
    utx.begin();
    bookDBAO.buyBooks(cart);
    utx.commit();
} catch (Exception ex) {
    try {
        utx.rollback();
    } catch (Exception exe) {
        System.out.println("Rollback failed: "+exe.getMessage());
}
...

Chapter 26 Persistence in the EJB Tier

This chapter describes how to use the Java Persistence API from enterprise beans. The material here focuses on the source code and settings of two examples. The first example called order is an application that uses a stateful session bean to manage entities related to an ordering system. The second example is roster, an application that manages a community sports system. This chapter assumes that you are familiar with the concepts detailed in Chapter 24, Introduction to the Java Persistence API.

The order Application

The order application is a simple inventory and ordering application for maintaining a catalog of parts and placing an itemized order of those parts. It has entities that represent parts, vendors, orders, and line items. These entities are accessed using a stateful session bean that holds the business logic of the application. A simple command-line client adds data to the entities, manipulates the data, and displays data from the catalog.

The information contained in an order can be divided into different elements. What is the order number? What parts are included in the order? What parts make up that part? Who makes the part? What are the specifications for the part? Are there any schematics for the part? order is a simplified version of an ordering system that has all these elements.

The order application consists of two modules: order-ejb, an enterprise bean JAR file containing the entities, the support classes, and a stateful session bean that accesses the data in the entities; and order-app-client, the application client that populates the entities with data and manipulates the data, displaying the results in a terminal.

Entity Relationships in the order Application

The order application demonstrates several types of entity relationships: one-to-many, many-to-one, one-to-one, unidirectional, and self-referential relationships.

Self-Referential Relationships

A self-referential relationship is a relationship between relationship fields in the same entity. Part has a field bomPart that has a one-to-many relationship with the field parts, which is also in Part. That is, a part can be made up of many parts, and each of those parts has exactly one bill-of-material part.

The primary key for Part is a compound primary key, a combination of the partNumber and revision fields. It is mapped to the PARTNUMBER and REVISION columns in the EJB_ORDER_PART table.

...
@ManyToOne
@JoinColumns({
    @JoinColumn(name="BOMPARTNUMBER",
        referencedColumnName="PARTNUMBER"),
    @JoinColumn(name="BOMREVISION",
        referencedColumnName="REVISION")
})
public Part getBomPart() {
    return bomPart;
}
...
@OneToMany(mappedBy="bomPart")
public Collection<Part> getParts() {
    return parts;
}
...

One-to-One Relationships

Part has a field, vendorPart, that has a one-to-one relationship with VendorPart’s part field. That is, each part has exactly one vendor part, and vice versa.

Here is the relationship mapping in Part:

@OneToOne(mappedBy="part")
public VendorPart getVendorPart() {
    return vendorPart;
}

Here is the relationship mapping in VendorPart:

@OneToOne
@JoinColumns({
    @JoinColumn(name="PARTNUMBER",
        referencedColumnName="PARTNUMBER"),
    @JoinColumn(name="PARTREVISION",
        referencedColumnName="REVISION")
})
public Part getPart() {
    return part;
}

Note that, because Part uses a compound primary key, the @JoinColumns annotation is used to map the columns in the EJB_ORDER_VENDOR_PART table to the columns in EJB_ORDER_PART. EJB_ORDER_VENDOR_PART’s PARTREVISION column refers to EJB_ORDER_PART’s REVISION column.

One-to-Many Relationship Mapped to Overlapping Primary and Foreign Keys

Order has a field, lineItems, that has a one-to-many relationship with LineItem’s field order. That is, each order has one or more line item.

LineItem uses a compound primary key that is made up of the orderId and itemId fields. This compound primary key maps to the ORDERID and ITEMID columns in the EJB_ORDER_LINEITEM database table. ORDERID is a foreign key to the ORDERID column in the EJB_ORDER_ORDER table. This means that the ORDERID column is mapped twice: once as a primary key field, orderId; and again as a relationship field, order.

Here’s the relationship mapping in Order:

@OneToMany(cascade=ALL, mappedBy="order")
    public Collection<LineItem> getLineItems() {
    return lineItems;
}

Here is the relationship mapping in LineItem:

@ManyToOne
    public Order getOrder() {
    return order;
}

Unidirectional Relationships

LineItem has a field, vendorPart, that has a unidirectional many-to-one relationship with VendorPart. That is, there is no field in the target entity in this relationship.

@ManyToOne
    public VendorPart getVendorPart() {
    return vendorPart;
}

Primary Keys in the order Application

The order application uses several types of primary keys: single-valued primary keys, compound primary keys, and generated primary keys.

Generated Primary Keys

VendorPart uses a generated primary key value. That is, the application does not assign primary key values for the entities, but instead relies on the persistence provider to generate the primary key values. The @GeneratedValue annotation is used to specify that an entity will use a generated primary key.

In VendorPart, the following code specifies the settings for generating primary key values:

@TableGenerator(
    name="vendorPartGen",
    table="EJB_ORDER_SEQUENCE_GENERATOR",
    pkColumnName="GEN_KEY",
    valueColumnName="GEN_VALUE",
    pkColumnValue="VENDOR_PART_ID",
    allocationSize=10)
@Id
@GeneratedValue(strategy=GenerationType.TABLE,
    generator="vendorPartGen")
public Long getVendorPartNumber() {
    return vendorPartNumber;
}

The @TableGenerator annotation is used in conjunction with @GeneratedValue’s strategy=TABLE element. That is, the strategy used to generate the primary keys is use a table in the database. @TableGenerator is used to configure the settings for the generator table. The name element sets the name of the generator, which is vendorPartGen in VendorPart.

The EJB_ORDER_SEQUENCE_GENERATOR table, which has two columns GEN_KEY and GEN_VALUE, will store the generated primary key values. This table could be used to generate other entity’s primary keys, so the pkColumnValue element is set to VENDOR_PART_ID to distinguish this entity’s generated primary keys from other entity’s generated primary keys. The allocationSize element specifies the amount to increment when allocating primary key values In this case, each VendorPart’s primary key will increment by 10.

The primary key field vendorPartNumber is of type Long, as the generated primary key’s field must be an integral type.

Compound Primary Keys

A compound primary key is made up of multiple fields and follows the requirements described in Primary Key Classes. To use a compound primary key, you must create a wrapper class.

In order, two entities use compound primary keys: Part and LineItem.

Part uses the PartKey wrapper class. Part’s primary key is a combination of the part number and the revision number. PartKey encapsulates this primary key.

LineItem uses the LineItemKey class. LineItem’s primary key is a combination of the order number and the item number. LineItemKey encapsulates this primary key. This is the LineItemKey compound primary key wrapper class:

package order.entity;

public final class LineItemKey implements
             java.io.Serializable {

    private Integer orderId;
    private int itemId;

    public int hashCode() {
        return ((this.getOrderId()==null
                        ?0:this.getOrderId().hashCode())
                 ^ ((int) this.getItemId()));
    }

    public boolean equals(Object otherOb) {
        if (this == otherOb) {
            return true;
        }
        if (!(otherOb instanceof LineItemKey)) {
            return false;
        }
        LineItemKey other = (LineItemKey) otherOb;
        return ((this.getOrderId()==null
                        ?other.orderId==null:this.getOrderId().equals
                (other.orderId)) && (this.getItemId ==
                    other.itemId));
    }

    public String toString() {
        return "" + orderId + "-" + itemId;
    }
}

The @IdClass annotation is used to specify the primary key class in the entity class. In LineItem, @IdClass is used as follows:

@IdClass(order.entity.LineItemKey.class)
@Entity
...
public class LineItem {
...
}

The two fields in LineItem are tagged with the @Id annotation to mark those fields as part of the compound primary key:

@Id
public int getItemId() {
    return itemId;
}
...
@Id
@Column(name="ORDERID", nullable=false,
    insertable=false, updatable=false)
public Integer getOrderId() {
    return orderId;
}

For orderId, you also use the @Column annotation to specify the column name in the table, and that this column should not be inserted or updated, as it is an overlapping foreign key pointing at the EJB_ORDER_ORDER table’s ORDERID column (see One-to-Many Relationship Mapped to Overlapping Primary and Foreign Keys). That is, orderId will be set by the Order entity.

In LineItem’s constructor, the line item number (LineItem.itemId) is set using the Order.getNextId method.

public LineItem(Order order, int quantity, VendorPart
        vendorPart) {
    this.order = order;
    this.itemId = order.getNextId();
    this.orderId = order.getOrderId();
    this.quantity = quantity;
    this.vendorPart = vendorPart;
}

Order.getNextId counts the number of current line items, adds one, and returns that number.

public int getNextId() {
    return this.lineItems.size() + 1;
}

Part doesn’t require the @Column annotation on the two fields that comprise Part’s compound primary key. This is because Part’s compound primary key is not an overlapping primary key/foreign key.

@IdClass(order.entity.PartKey.class)
@Entity
...
public class Part {
...
    @Id
    public String getPartNumber() {
        return partNumber;
    }
...
    @Id
    public int getRevision() {
        return revision;
    }
...
}

Entity Mapped to More Than One Database Table

Part’s fields map to more than one database table: EJB_ORDER_PART and EJB_ORDER_PART_DETAIL. The EJB_ORDER_PART_DETAIL table holds the specification and schematics for the part. The @SecondaryTable annotation is used to specify the secondary table.

...
@Entity
@Table(name="EJB_ORDER_PART")
@SecondaryTable(name="EJB_ORDER_PART_DETAIL", pkJoinColumns={
    @PrimaryKeyJoinColumn(name="PARTNUMBER",
        referencedColumnName="PARTNUMBER"),
    @PrimaryKeyJoinColumn(name="REVISION",
        referencedColumnName="REVISION")
})
public class Part {
...
}

EJB_ORDER_PART_DETAIL shares the same primary key values as EJB_ORDER_PART. The pkJoinColumns element of @SecondaryTable is used to specify that EJB_ORDER_PART_DETAIL’s primary key columns are foreign keys to EJB_ORDER_PART. The @PrimaryKeyJoinColumn annotation sets the primary key column names and specifies which column in the primary table the column refers to. In this case, the primary key column names for both EJB_ORDER_PART_DETAIL and EJB_ORDER_PART are the same: PARTNUMBER and REVISION, respectively.

Cascade Operations in the order Application

Entities that have relationships to other entities often have dependencies on the existence of the other entity in the relationship. For example, a line item is part of an order, and if the order is deleted, then the line item should also be deleted. This is called a cascade delete relationship.

In order, there are two cascade delete dependencies in the entity relationships. If the Order to which a LineItem is related is deleted, then the LineItem should also be deleted. If the Vendor to which a VendorPart is related is deleted, then the VendorPart should also be deleted.

You specify the cascade operations for entity relationships by setting the cascade element in the inverse (non-owning) side of the relationship. The cascade element is set to ALL in the case of Order.lineItems. This means that all persistence operations (deletes, updates, and so on) are cascaded from orders to line items.

Here is the relationship mapping in Order:

@OneToMany(cascade=ALL, mappedBy="order")
public Collection<LineItem> getLineItems() {
    return lineItems;
}

Here is the relationship mapping in LineItem:

@ManyToOne
    public Order getOrder() {
    return order;
}

BLOB and CLOB Database Types in the order Application

The PARTDETAIL table in the database has a column, DRAWING, of type BLOB. BLOB stands for binary large objects, which are used for storing binary data such as an image. The DRAWING column is mapped to the field Part. drawing of type java.io.Serializable. The @Lob annotation is used to denote that the field is large object.

@Column(table="EJB_ORDER_PART_DETAIL")
@Lob
public Serializable getDrawing() {
    return drawing;
}

PARTDETAIL also has a column, SPECIFICATION, of type CLOB. CLOB stands for character large objects, which are used to store string data too large to be stored in a VARCHAR column. SPECIFICATION is mapped to the field Part.specification of type java.lang.String. The @Lob annotation is also used here to denote that the field is a large object.

@Column(table="EJB_ORDER_PART_DETAIL")
@Lob
public String getSpecification() {
    return specification;
}

Both of these fields use the @Column annotation and set the table element to the secondary table.

Temporal Types in the order Application

The Order.lastUpdate persistent property, which is of type java.util.Date, is mapped to the EJB_ORDER_ORDER.LASTUPDATE database field, which is of the SQL type TIMESTAMP. To ensure the proper mapping between these types, you must use the @Temporal annotation with the proper temporal type specified in @Temporal’s element. @Temporal’s elements are of type javax.persistence.TemporalType. The possible values are:

Here is the relevant section of Order:

@Temporal(TIMESTAMP)
public Date getLastUpdate() {
    return lastUpdate;
}

Managing the order Application’s Entities

The RequestBean stateful session bean contains the business logic and manages the entities of order.

RequestBean uses the @PersistenceContext annotation to retrieve an entity manager instance which is used to manage order’s entities in RequestBean’s business methods.

@PersistenceContext
private EntityManager em;

This EntityManager instance is a container-managed entity manager, so the container takes care of all the transactions involved in the managing order’s entities.

Creating Entities

The RequestBean.createPart business method creates a new Part entity. The EntityManager.persist method is used to persist the newly created entity to the database.

Part part = new Part(partNumber,
    revision,
    description,
    revisionDate,
    specification,
    drawing);
em.persist(part);

Finding Entities

The RequestBean.getOrderPrice business method returns the price of a given order, based on the orderId. The EntityManager.find method is used to retrieve the entity from the database.

Order order = em.find(Order.class, orderId);

The first argument of EntityManager.find is the entity class, and the second is the primary key.

Setting Entity Relationships

The RequestBean.createVendorPart business method creates a VendorPart associated with a particular Vendor. The EntityManager.persist method is used to persist the newly created VendorPart entity to the database, and the VendorPart.setVendor and Vendor.setVendorPart methods are used to associate the VendorPart with the Vendor.

PartKey pkey = new PartKey();
pkey.partNumber = partNumber;
pkey.revision = revision;

Part part = em.find(Part.class, pkey);
VendorPart vendorPart = new VendorPart(description, price,
    part);
em.persist(vendorPart);

Vendor vendor = em.find(Vendor.class, vendorId);
vendor.addVendorPart(vendorPart);
vendorPart.setVendor(vendor);

Using Queries

The RequestBean.adjustOrderDiscount business method updates the discount applied to all orders. It uses the findAllOrders named query, defined in Order:

@NamedQuery(
    name="findAllOrders",
    query="SELECT o FROM Order o"
)

The EntityManager.createNamedQuery method is used to run the query. Because the query returns a List of all the orders, the Query.getResultList method is used.

List orders = em.createNamedQuery(
    "findAllOrders")
    .getResultList();

The RequestBean.getTotalPricePerVendor business method returns the total price of all the parts for a particular vendor. It uses a named parameter, id, defined in the named query findTotalVendorPartPricePerVendor defined in VendorPart.

@NamedQuery(
    name="findTotalVendorPartPricePerVendor",
    query="SELECT SUM(vp.price) " +
    "FROM VendorPart vp " +
    "WHERE vp.vendor.vendorId = :id"
)

When running the query, the Query.setParameter method is used to set the named parameter id to the value of vendorId, the parameter to RequestBean.getTotalPricePerVendor.

return (Double) em.createNamedQuery(
    "findTotalVendorPartPricePerVendor")
    .setParameter("id", vendorId)
    .getSingleResult();

The Query.getSingleResult method is used for this query because the query returns a single value.

Removing Entities

The RequestBean.removeOrder business method deletes a given order from the database. It uses the EntityManager.remove method to delete the entity from the database.

Order order = em.find(Order.class, orderId);
em.remove(order);

Building and Running the order Application

This section describes how to build, package, deploy, and run the order application. To do this, you will create the database tables in the Java DB server, then build, deploy, and run the example.

Creating the Database Tables in NetBeans IDE

To create the database tables in Java DB, the database server included with Application Server, you need to create the database connection and execute the SQL commands in tut-install/examples/common/sql/javadb/tutorial.sql.

Creating the Database Connection

    To create the database connection do the following:

  1. Click the Services tab.

  2. Right-click the Databases node and select New Connection to open the New Connection dialog.

  3. Under Name, select Java DB (Network).

  4. Set Database URL to the following:


    jdbc:derby://localhost:1527/sun-appserv-samples
  5. Set User Name to APP.

  6. Set Password to APP.

  7. Select the Remember Password during this Session box.

  8. Click OK.

Creating the Tables

    To create the tutorial tables, do the following:

  1. Select File->Open File.

  2. Navigate to tut-install/examples/common/sql/javadb/ and open tutorial.sql.

  3. In the editor pane, select the connection URL to Java DB:


    jdbc:derby://localhost:1527/sun-appserv-samples
  4. Click the Run SQL button at the top of the editor pane.

    You will see the output from the SQL commands in the Output tab.

Deleting the Tables

    To delete the tutorial tables, do the following:

  1. Select File->Open File.

  2. Navigate to tut-install/examples/common/sql/javadb/ and open delete.sql.

  3. In the editor pane, select the connection URL to Java DB:


    jdbc:derby://localhost:1527/sun-appserv-samples
  4. Click the Run SQL button at the top of the editor pane.

    You will see the output from the SQL commands in the Output tab.

Creating the Database Tables Using Ant

    The database tables are automatically created by the create-tables task, which is called before you deploy the application with the ant deploy task. To manually create the tables, do the following:

  1. In a terminal window, navigate to tut-install/javaeetutorial5/examples/ejb/order/.

  2. Type the following command:


    ant create-tables
    

    Note –

    The first time the create-tables task is run, you will see error messages when the task attempts to remove tables that don’t exist. Ignore these error messages. Subsequent calls to create-tables will run with no errors and will reset the database tables.


Building, Packaging, Deploying, and Running order In NetBeans IDE

    Follow these instructions to build, package, deploy, and run the order example to your Application Server instance using NetBeans IDE.

  1. In NetBeans IDE, select File->Open Project.

  2. In the Open Project dialog, navigate to tut-install/javaeetutorial5/examples/ejb/.

  3. Select the order folder.

  4. Select the Open as Main Project and Open Required Projects check boxes.

  5. Click Open Project.

  6. In the Projects tab, right-click the order project and select Run.

You will see the following output from the application client in the Output tab:


...
Cost of Bill of Material for PN SDFG-ERTY-BN Rev: 7:  $241.86
Cost of Order 1111:  $664.68
Cost of Order 4312:  $2,011.44

Adding 5% discount
Cost of Order 1111:  $627.75
Cost of Order 4312:  $1,910.87

Removing 7% discount
Cost of Order 1111:  $679.45
Cost of Order 4312:  $2,011.44

Average price of all parts:  $117.55

Total price of parts for Vendor 100:  $501.06

Ordered list of vendors for order 1111
200 Gadget, Inc. Mrs. Smith
100 WidgetCorp Mr. Jones

Counting all line items
Found 6 line items

Removing Order 4312
Counting all line items
Found 3 line items

Found 1 out of 2 vendors with ’I’ in the name:
Gadget, Inc.
run-order-app-client:
run-ant:
run:
BUILD SUCCESSFUL (total time: 22 seconds)

Building, Packaging, Deploying, and Running order Using Ant

To build the application components of order, enter the following command:


ant

This runs the default task, which compiles the source files and packages the application into an EAR file located at tut-install/examples/ejb/order/dist/order.ear.

To deploy the EAR, make sure the Application Server is started, then enter the following command:


ant deploy

After order.ear is deployed, a client JAR, orderClient.jar, is retrieved. This contains the application client.

To run the application client, enter the following command:


ant run

You will see the following output:


...
run:
    [echo] Running appclient for Order.

appclient-command-common:
    [exec] Cost of Bill of Material for PN SDFG-ERTY-BN Rev: 7:
         $241.86
    [exec] Cost of Order 1111:  $664.68
    [exec] Cost of Order 4312:  $2,011.44

    [exec] Adding 5% discount
    [exec] Cost of Order 1111:  $627.75
    [exec] Cost of Order 4312:  $1,910.87

    [exec] Removing 7% discount
    [exec] Cost of Order 1111:  $679.45
    [exec] Cost of Order 4312:  $2,011.44

    [exec] Average price of all parts:  $117.55

    [exec] Total price of parts for Vendor 100:  $501.06

    [exec] Ordered list of vendors for order 1111
    [exec] 200 Gadget, Inc. Mrs. Smith
    [exec] 100 WidgetCorp Mr. Jones

    [exec] Counting all line items
    [exec] Found 6 line items

    [exec] Removing Order 4312
    [exec] Counting all line items
    [exec] Found 3 line items

    [exec] Found 1 out of 2 vendors with ’I’ in the name:
    [exec] Gadget, Inc.

BUILD SUCCESSFUL

Note –

Before re-running the application client, you must reset the database by running the create-tables task.


The all Task

As a convenience, the all task will build, package, deploy, and run the application. To do this, enter the following command:


ant all

Undeploying order

To undeploy order.ear, enter the following command:


ant undeploy

The roster Application

The roster application maintains the team rosters for players in recreational sports leagues. The application has four components: Java Persistence API entities (Player, Team, and League), a stateful session bean (RequestBean), an application client (RosterClient), and three helper classes (PlayerDetails, TeamDetails, and LeagueDetails).

Functionally, roster is similar to the order application described earlier in this chapter with three new features that order does not have: many-to-many relationships, entity inheritance, and automatic table creation at deploytime.

Relationships in the roster Application

A recreational sports system has the following relationships:

In roster this is reflected by the following relationships between the Player, Team, and League entities:

The Many-To-Many Relationship in roster

The many-to-many relationship between Player and Team is specified by using the @ManyToMany annotation.

In Team.java, the @ManyToMany annotation decorates the getPlayers method:

@ManyToMany
@JoinTable(
    name="EJB_ROSTER_TEAM_PLAYER",
    joinColumns=
        @JoinColumn(name="TEAM_ID", referencedColumnName="ID"),
    inverseJoinColumns=
        @JoinColumn(name="PLAYER_ID", referencedColumnName="ID")
)
public Collection<Player> getPlayers() {
    return players;
}

The @JoinTable annotation is used to specify a table in the database that will associate player IDs with team IDs. The entity that specifies the @JoinTable is the owner of the relationship, so in this case the Team entity is the owner of the relationship with the Player entity. Because roster uses automatic table creation at deploytime, the container will create a join table in the database named EJB_ROSTER_TEAM_PLAYER.

Player is the inverse, or non-owning side of the relationship with Team. As one-to-one and many-to-one relationships, the non-owning side is marked by the mappedBy element in the relationship annotation. Because the relationship between Player and Team is bidirectional, the choice of which entity is the owner of the relationship is arbitrary.

In Player.java, the @ManyToMany annotation decorates the getTeams method:

@ManyToMany(mappedBy="players")
public Collection<Team> getTeams() {
    return teams;
}

Entity Inheritance in the roster Application

The roster application demonstrates how to use entity inheritance, as described in Entity Inheritance.

The League entity in roster is an abstract entity with two concrete subclasses: SummerLeague and WinterLeague. Because League is an abstract class it cannot be instantiated:

...
@Entity
@Table(name = "EJB_ROSTER_LEAGUE")
public abstract class League implements java.io.Serializable {
...
}

Instead, SummerLeague or WinterLeague are used by clients when creating a league. SummerLeague and WinterLeague inherit the persistent properties defined in League, and only add a constructor that verifies that the sport parameter matches the type of sport allowed in that seasonal league. For example, here is the SummerLeague entity:

...
@Entity
public class SummerLeague extends League
         implements java.io.Serializable {

    /** Creates a new instance of SummerLeague */
    public SummerLeague() {
    }

    public SummerLeague(String id, String name,
             String sport) throws IncorrectSportException {
        this.id = id;
        this.name = name;
        if (sport.equalsIgnoreCase("swimming") ||
                sport.equalsIgnoreCase("soccer") ||
                sport.equalsIgnoreCase("basketball") ||
                sport.equalsIgnoreCase("baseball")) {
            this.sport = sport;
        } else {
            throw new IncorrectSportException(
                "Sport is not a summer sport.");
        }
    }
}

The roster application uses the default mapping strategy of InheritanceType.SINGLE_TABLE, so the @Inheritance annotation is not required. If you wanted to use a different mapping strategy, decorate League with @Inheritance and specify the mapping strategy in the strategy element:

@Entity
@Inheritance(strategy=JOINED)
@Table(name="EJB_ROSTER_LEAGUE")
public abstract class League implements java.io.Serializable {
    ...
}

roster uses the default discriminator column name, so the @DiscriminatorColumn annotation is not required. Because you are using automatic table generation in roster the Persistence provider will create a discriminator column in the EJB_ROSTER_LEAGUE table called DTYPE, which will store the name of the inherited entity used to create the league. If you want to use a different name for the discriminator column, decorate League with @DiscriminatorColumn and set the name element:

@Entity
@DiscriminatorColumn(name="DISCRIMINATOR")
@Table(name="EJB_ROSTER_LEAGUE")
public abstract class League implements java.io.Serializable {
    ...
}

Automatic Table Generation in the roster Application

At deploytime the Application Server will automatically drop and create the database tables used by roster. This is done by setting the toplink.ddl-generation property to drop-and-create-tables in persistence.xml.

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
version="1.0">
    <persistence-unit name="em" transaction-type="JTA">
        <jta-data-source>jdbc/__default</jta-data-source>
        <properties>
            <property name="toplink.ddl-generation"
                             value="drop-and-create-tables"/>
        </properties>
    </persistence-unit>
</persistence>

This feature is specific to the Java Persistence API provider used by the Application Server, and is non-portable across Java EE servers. Automatic table creation is useful for development purposes, however, and the toplink.ddl-generation property may be removed from persistence.xml when preparing the application for production use, or when deploying to other Java EE servers.

Building and Running the roster Application

This section describes how to build, package, deploy, and run the roster application. You can do this using either NetBeans IDE or Ant.

Building, Packaging, Deploying, and Running roster in NetBeans IDE

    Follow these instructions to build, package, deploy, and run the roster example to your Application Server instance using NetBeans IDE.

  1. In NetBeans IDE, select File->Open Project.

  2. In the Open Project dialog, navigate to tut-install/javaeetutorial5/examples/ejb/.

  3. Select the roster folder.

  4. Select the Open as Main Project and Open Required Projects check boxes.

  5. Click Open Project.

  6. In the Projects tab, right-click the roster project and select Run.

You will see the following partial output from the application client in the Output tab:


List all players in team T2:
P6 Ian Carlyle goalkeeper 555.0
P7 Rebecca Struthers midfielder 777.0
P8 Anne Anderson forward 65.0
P9 Jan Wesley defender 100.0
P10 Terry Smithson midfielder 100.0

List all teams in league L1:
T1 Honey Bees Visalia
T2 Gophers Manteca
T5 Crows Orland

List all defenders:
P2 Alice Smith defender 505.0
P5 Barney Bold defender 100.0
P9 Jan Wesley defender 100.0
P22 Janice Walker defender 857.0
P25 Frank Fletcher defender 399.0
...

Building, Packaging, Deploying, and Running roster Using Ant

To build the application components of roster, enter the following command:


ant

This runs the default task, which compiles the source files and packages the application into an EAR file located at tut-install/examples/ejb/roster/dist/roster.ear.

To deploy the EAR, make sure the Application Server is started, then enter the following command:


ant deploy

The build system will check to see if the Java DB database server is running and start it if it is not running, then deploy roster.ear. The Application Server will then drop and create the database tables during deployment, as specified in persistence.xml.

After roster.ear is deployed, a client JAR, rosterClient.jar, is retrieved. This contains the application client.

To run the application client, enter the following command:


ant run

You will see the output, which begins:


[echo] running application client container.
[exec] List all players in team T2:
[exec] P6 Ian Carlyle goalkeeper 555.0
[exec] P7 Rebecca Struthers midfielder 777.0
[exec] P8 Anne Anderson forward 65.0
[exec] P9 Jan Wesley defender 100.0
[exec] P10 Terry Smithson midfielder 100.0

[exec] List all teams in league L1:
[exec] T1 Honey Bees Visalia
[exec] T2 Gophers Manteca
[exec] T5 Crows Orland

[exec] List all defenders:
[exec] P2 Alice Smith defender 505.0
[exec] P5 Barney Bold defender 100.0
[exec] P9 Jan Wesley defender 100.0
[exec] P22 Janice Walker defender 857.0
[exec] P25 Frank Fletcher defender 399.0
...

The all Task

As a convenience, the all task will build, package, deploy, and run the application. To do this, enter the following command:


ant all

Undeploying order

To undeploy roster.ear, enter the following command:


ant undeploy

Chapter 27 The Java Persistence Query Language

The Java Persistence query language defines queries for entities and their persistent state. The query language allows you to write portable queries that work regardless of the underlying data store.

The query language uses the abstract persistence schemas of entities, including their relationships, for its data model, and it defines operators and expressions based on this data model. The scope of a query spans the abstract schemas of related entities that are packaged in the same persistence unit. The query language uses a SQL-like syntax to select objects or values based on entity abstract schema types and relationships among them.

This chapter relies on the material presented in earlier chapters. For conceptual information, see Chapter 24, Introduction to the Java Persistence API. For code examples, see Chapters The persistence.xml File and Updating Data in the Database.

Query Language Terminology

The following list defines some of the terms referred to in this chapter.

Simplified Query Language Syntax

This section briefly describes the syntax of the query language so that you can quickly move on to the next section, Example Queries. When you are ready to learn about the syntax in more detail, see the section Full Query Language Syntax.

Select Statements

A select query has six clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. The SELECT and FROM clauses are required, but the WHERE, GROUP BY, HAVING, and ORDER BY clauses are optional. Here is the high-level BNF syntax of a query language query:

QL_statement ::= select_clause from_clause 
  [where_clause][groupby_clause][having_clause][orderby_clause]

The SELECT clause defines the types of the objects or values returned by the query.

The FROM clause defines the scope of the query by declaring one or more identification variables, which can be referenced in the SELECT and WHERE clauses. An identification variable represents one of the following elements:

The WHERE clause is a conditional expression that restricts the objects or values retrieved by the query. Although it is optional, most queries have a WHERE clause.

The GROUP BY clause groups query results according to a set of properties.

The HAVING clause is used with the GROUP BY clause to further restrict the query results according to a conditional expression.

The ORDER BY clause sorts the objects or values returned by the query into a specified order.

Update and Delete Statements

Update and delete statements provide bulk operations over sets of entities. They have the following syntax:

update_statement :: = update_clause [where_clause] delete_statement :: = 
		delete_clause [where_clause]

The update and delete clauses determine the type of the entities to be updated or deleted. The WHERE clause may be used to restrict the scope of the update or delete operation.

Example Queries

The following queries are from the Player entity of the roster application, which is documented in Chapter 26, Persistence in the EJB Tier.

Simple Queries

If you are unfamiliar with the query language, these simple queries are a good place to start.

A Basic Select Query

SELECT p
FROM Player p

Data retrieved: All players.

Description: The FROM clause declares an identification variable named p, omitting the optional keyword AS. If the AS keyword were included, the clause would be written as follows:

FROM Player AS
 p

The Player element is the abstract schema name of the Player entity.

See also: Identification Variables

Eliminating Duplicate Values

SELECT DISTINCT
 p
FROM Player p
WHERE p.position = ?1

Data retrieved: The players with the position specified by the query’s parameter.

Description: The DISTINCT keyword eliminates duplicate values.

The WHERE clause restricts the players retrieved by checking their position, a persistent field of the Player entity. The ?1 element denotes the input parameter of the query.

See also: Input Parameters, The DISTINCT Keyword

Using Named Parameters

SELECT DISTINCT p
FROM Player p
WHERE p.position = :position AND p.name = :name

Data retrieved: The players having the specified positions and names.

Description: The position and name elements are persistent fields of the Player entity. The WHERE clause compares the values of these fields with the named parameters of the query, set using the Query.setNamedParameter method. The query language denotes a named input parameter using colon (:) followed by an identifier. The first input parameter is :position, the second is :name.

Queries That Navigate to Related Entities

In the query language, an expression can traverse (or navigate) to related entities. These expressions are the primary difference between the Java Persistence query language and SQL. Queries navigates to related entities, whereas SQL joins tables.

A Simple Query with Relationships

SELECT DISTINCT p
FROM Player p, IN(p.teams) t

Data retrieved: All players who belong to a team.

Description: The FROM clause declares two identification variables: p and t. The p variable represents the Player entity, and the t variable represents the related Team entity. The declaration for t references the previously declared p variable. The IN keyword signifies that teams is a collection of related entities. The p.teams expression navigates from a Player to its related Team. The period in the p.teams expression is the navigation operator.

You may also use the JOIN statement to write the same query:

SELECT DISTINCT p
FROM Player p JOIN p.teams t

This query could also be rewritten as:

SELECT DISTINCT p
FROM Player p
WHERE p.team IS NOT EMPTY

Navigating to Single-Valued Relationship Fields

Use the JOIN clause statement to navigate to a single-valued relationship field:

SELECT t
 FROM Team t JOIN t.league l
 WHERE l.sport = ’soccer’ OR l.sport =’football’

In this example, the query will return all teams that are in either soccer or football leagues.

Traversing Relationships with an Input Parameter

SELECT DISTINCT p
FROM Player p, IN (p.teams) AS t
WHERE t.city = :city

Data retrieved: The players whose teams belong to the specified city.

Description: This query is similar to the previous example, but it adds an input parameter. The AS keyword in the FROM clause is optional. In the WHERE clause, the period preceding the persistent variable city is a delimiter, not a navigation operator. Strictly speaking, expressions can navigate to relationship fields (related entities), but not to persistent fields. To access a persistent field, an expression uses the period as a delimiter.

Expressions cannot navigate beyond (or further qualify) relationship fields that are collections. In the syntax of an expression, a collection-valued field is a terminal symbol. Because the teams field is a collection, the WHERE clause cannot specify p.teams.city (an illegal expression).

See also: Path Expressions

Traversing Multiple Relationships

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league = :league

Data retrieved: The players that belong to the specified league.

Description: The expressions in this query navigate over two relationships. The p.teams expression navigates the Player-Team relationship, and the t.league expression navigates the Team-League relationship.

In the other examples, the input parameters are String objects, but in this example the parameter is an object whose type is a League. This type matches the league relationship field in the comparison expression of the WHERE clause.

Navigating According to Related Fields

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport

Data retrieved: The players who participate in the specified sport.

Description: The sport persistent field belongs to the League entity. To reach the sport field, the query must first navigate from the Player entity to Team (p.teams) and then from Team to the League entity (t.league). Because the league relationship field is not a collection, it can be followed by the sport persistent field.

Queries with Other Conditional Expressions

Every WHERE clause must specify a conditional expression, of which there are several kinds. In the previous examples, the conditional expressions are comparison expressions that test for equality. The following examples demonstrate some of the other kinds of conditional expressions. For descriptions of all conditional expressions, see the section WHERE Clause.

The LIKE Expression

SELECT p
 FROM Player p
 WHERE p.name LIKE ’Mich%’

Data retrieved: All players whose names begin with “Mich.”

Description: The LIKE expression uses wildcard characters to search for strings that match the wildcard pattern. In this case, the query uses the LIKE expression and the % wildcard to find all players whose names begin with the string “Mich.” For example, “Michael” and “Michelle” both match the wildcard pattern.

See also: LIKE Expressions

The IS NULL Expression

SELECT t
 FROM Team t
 WHERE t.league IS NULL

Data retrieved: All teams not associated with a league.

Description: The IS NULL expression can be used to check if a relationship has been set between two entities. In this case, the query checks to see if the teams are associated with any leagues, and returns the teams that do not have a league.

See also: NULL Comparison Expressions, NULL Values

The IS EMPTY Expression

SELECT p
FROM Player p
WHERE p.teams IS EMPTY

Data retrieved: All players who do not belong to a team.

Description: The teams relationship field of the Player entity is a collection. If a player does not belong to a team, then the teams collection is empty and the conditional expression is TRUE.

See also: Empty Collection Comparison Expressions

The BETWEEN Expression

SELECT DISTINCT p
FROM Player p
WHERE p.salary BETWEEN :lowerSalary AND :higherSalary

Data retrieved: The players whose salaries fall within the range of the specified salaries.

Description: This BETWEEN expression has three arithmetic expressions: a persistent field (p.salary) and the two input parameters (:lowerSalary and :higherSalary). The following expression is equivalent to the BETWEEN expression:

p.salary >= :lowerSalary AND p.salary <= :higherSalary

See also: BETWEEN Expressions

Comparison Operators

SELECT DISTINCT p1
FROM Player p1, Player p2
WHERE p1.salary > p2.salary AND p2.name = :name

Data retrieved: All players whose salaries are higher than the salary of the player with the specified name.

Description: The FROM clause declares two identification variables (p1 and p2) of the same type (Player). Two identification variables are needed because the WHERE clause compares the salary of one player (p2) with that of the other players (p1).

See also: Identification Variables

Bulk Updates and Deletes

The following examples show how to use the UPDATE and DELETE expressions in queries. UPDATE and DELETE operate on multiple entities according to the condition or conditions set in the WHERE clause. The WHERE clause in UPDATE and DELETE queries follows the same rules as SELECT queries.

Update Queries

UPDATE Player p
SET p.status = ’inactive’
WHERE p.lastPlayed < :inactiveThresholdDate

Description: This query sets the status of a set of players to inactive if the player’s last game was longer than the date specified in inactiveThresholdDate.

Delete Queries

DELETE
FROM Player p
WHERE p.status = ’inactive’
AND p.teams IS EMPTY

Description: This query deletes all inactive players who are not on a team.

Full Query Language Syntax

This section discusses the query language syntax, as defined in the Java Persistence specification. Much of the following material paraphrases or directly quotes the specification.

BNF Symbols

Table 27–1 describes the BNF symbols used in this chapter.

Table 27–1 BNF Symbol Summary

Symbol 

Description 

::=

The element to the left of the symbol is defined by the constructs on the right. 

*

The preceding construct may occur zero or more times. 

{...}

The constructs within the curly braces are grouped together. 

[...]

The constructs within the square brackets are optional. 

|

An exclusive OR.

BOLDFACE

A keyword (although capitalized in the BNF diagram, keywords are not case-sensitive). 

White space 

A white space character can be a space, a horizontal tab, or a line feed. 

BNF Grammar of the Java Persistence Query Language

Here is the entire BNF diagram for the query language:

QL_statement ::= select_statement | update_statement | delete_statement
select_statement ::= select_clause from_clause [where_clause] [groupby_clause] 
    [having_clause] [orderby_clause]
update_statement ::= update_clause [where_clause]
delete_statement ::= delete_clause [where_clause]
from_clause ::=
    FROM identification_variable_declaration
        {, {identification_variable_declaration |
            collection_member_declaration}}*
identification_variable_declaration ::=
        range_variable_declaration { join | fetch_join }*
range_variable_declaration ::= abstract_schema_name [AS]
        identification_variable
join ::= join_spec join_association_path_expression [AS]
        identification_variable
fetch_join ::= join_specFETCH join_association_path_expression
association_path_expression ::=
        collection_valued_path_expression |
        single_valued_association_path_expression
join_spec::= [LEFT [OUTER] |INNER] JOIN
join_association_path_expression ::=
        join_collection_valued_path_expression |
        join_single_valued_association_path_expression
join_collection_valued_path_expression::=
    identification_variable.collection_valued_association_field
join_single_valued_association_path_expression::=
        identification_variable.single_valued_association_field
collection_member_declaration ::=
        IN (collection_valued_path_expression) [AS]
        identification_variable
single_valued_path_expression ::=
        state_field_path_expression |
        single_valued_association_path_expression
state_field_path_expression ::=
    {identification_variable |
    single_valued_association_path_expression}.state_field
single_valued_association_path_expression ::=
    identification_variable.{single_valued_association_field.}*
    single_valued_association_field
collection_valued_path_expression ::=
    identification_variable.{single_valued_association_field.}*
    collection_valued_association_field
state_field ::=
    {embedded_class_state_field.}*simple_state_field
update_clause ::=UPDATE abstract_schema_name [[AS]
    identification_variable] SET update_item {, update_item}*
update_item ::= [identification_variable.]{state_field |
    single_valued_association_field} = new_value
new_value ::=
     simple_arithmetic_expression |
    string_primary |
    datetime_primary |
    boolean_primary |
    enum_primary simple_entity_expression |
    NULL
delete_clause ::= DELETE FROM abstract_schema_name [[AS]
    identification_variable]
select_clause ::= SELECT [DISTINCT] select_expression {,
    select_expression}*
select_expression ::=
    single_valued_path_expression |
    aggregate_expression |
    identification_variable |
    OBJECT(identification_variable) |
    constructor_expression
constructor_expression ::=
    NEW constructor_name(constructor_item {,
    constructor_item}*)
constructor_item ::= single_valued_path_expression |
    aggregate_expression
aggregate_expression ::=
    {AVG |MAX |MIN |SUM} ([DISTINCT]
        state_field_path_expression) |
    COUNT ([DISTINCT] identification_variable |
        state_field_path_expression |
        single_valued_association_path_expression)
where_clause ::= WHERE conditional_expression
groupby_clause ::= GROUP BY groupby_item {, groupby_item}*
groupby_item ::= single_valued_path_expression
having_clause ::= HAVING conditional_expression
orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
orderby_item ::= state_field_path_expression [ASC |DESC]
subquery ::= simple_select_clause subquery_from_clause
    [where_clause] [groupby_clause] [having_clause]
subquery_from_clause ::=
    FROM subselect_identification_variable_declaration
        {, subselect_identification_variable_declaration}*
subselect_identification_variable_declaration ::=
    identification_variable_declaration |
    association_path_expression [AS] identification_variable |
    collection_member_declaration
simple_select_clause ::= SELECT [DISTINCT]
    simple_select_expression
simple_select_expression::=
    single_valued_path_expression |
    aggregate_expression |
    identification_variable
conditional_expression ::= conditional_term |
    conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND
    conditional_factor
conditional_factor ::= [NOT] conditional_primary
conditional_primary ::= simple_cond_expression |(
    conditional_expression)
simple_cond_expression ::=
    comparison_expression |
    between_expression |
    like_expression |
    in_expression |
    null_comparison_expression |
    empty_collection_comparison_expression |
    collection_member_expression |
    exists_expression
between_expression ::=
    arithmetic_expression [NOT] BETWEEN
        arithmetic_expressionAND arithmetic_expression |
    string_expression [NOT] BETWEEN string_expression AND
        string_expression |
    datetime_expression [NOT] BETWEEN
        datetime_expression AND datetime_expression
in_expression ::=
    state_field_path_expression [NOT] IN (in_item {, in_item}*
    | subquery)
in_item ::= literal | input_parameter
like_expression ::=
    string_expression [NOT] LIKE pattern_value [ESCAPE
        escape_character]
null_comparison_expression ::=
    {single_valued_path_expression | input_parameter} IS [NOT]
        NULL
empty_collection_comparison_expression ::=
    collection_valued_path_expression IS [NOT] EMPTY
collection_member_expression ::= entity_expression
    [NOT] MEMBER [OF] collection_valued_path_expression
exists_expression::= [NOT] EXISTS (subquery)
all_or_any_expression ::= {ALL |ANY |SOME} (subquery)
comparison_expression ::=
    string_expression comparison_operator {string_expression |
    all_or_any_expression} |
    boolean_expression {= |<> } {boolean_expression |
    all_or_any_expression} |
    enum_expression {= |<> } {enum_expression |
    all_or_any_expression} |
    datetime_expression comparison_operator
        {datetime_expression | all_or_any_expression} |
    entity_expression {= |<> } {entity_expression |
    all_or_any_expression} |
    arithmetic_expression comparison_operator
        {arithmetic_expression | all_or_any_expression}
comparison_operator ::= = |> |>= |< |<= |<>
arithmetic_expression ::= simple_arithmetic_expression |
    (subquery)
simple_arithmetic_expression ::=
    arithmetic_term | simple_arithmetic_expression {+ |- }
        arithmetic_term
arithmetic_term ::= arithmetic_factor | arithmetic_term {* |/ }
    arithmetic_factor
arithmetic_factor ::= [{+ |- }] arithmetic_primary
arithmetic_primary ::=
    state_field_path_expression |
    numeric_literal |
    (simple_arithmetic_expression) |
    input_parameter |
    functions_returning_numerics |
    aggregate_expression
string_expression ::= string_primary | (subquery)
string_primary ::=
    state_field_path_expression |
    string_literal |
    input_parameter |
    functions_returning_strings |
    aggregate_expression
datetime_expression ::= datetime_primary | (subquery)
datetime_primary ::=
    state_field_path_expression |
    input_parameter |
    functions_returning_datetime |
    aggregate_expression
boolean_expression ::= boolean_primary | (subquery)
boolean_primary ::=
    state_field_path_expression |
    boolean_literal |
    input_parameter
 enum_expression ::= enum_primary | (subquery)
enum_primary ::=
    state_field_path_expression |
    enum_literal |
    input_parameter
entity_expression ::=
    single_valued_association_path_expression |
        simple_entity_expression
simple_entity_expression ::=
    identification_variable |
    input_parameter
functions_returning_numerics::=
    LENGTH(string_primary) |
    LOCATE(string_primary, string_primary[,
        simple_arithmetic_expression]) |
    ABS(simple_arithmetic_expression) |
    SQRT(simple_arithmetic_expression) |
    MOD(simple_arithmetic_expression,
        simple_arithmetic_expression) |
    SIZE(collection_valued_path_expression)
functions_returning_datetime ::=
    CURRENT_DATE |
    CURRENT_TIME |
    CURRENT_TIMESTAMP
functions_returning_strings ::=
    CONCAT(string_primary, string_primary) |
    SUBSTRING(string_primary,
        simple_arithmetic_expression,
        simple_arithmetic_expression)|
    TRIM([[trim_specification] [trim_character] FROM]
        string_primary) |
    LOWER(string_primary) |
    UPPER(string_primary)
trim_specification ::= LEADING | TRAILING | BOTH

FROM Clause

The FROM clause defines the domain of the query by declaring identification variables.

Identifiers

An identifier is a sequence of one or more characters. The first character must be a valid first character (letter, $, _) in an identifier of the Java programming language (hereafter in this chapter called simply “Java”). Each subsequent character in the sequence must be a valid non-first character (letter, digit, $, _) in a Java identifier. (For details, see the Java SE API documentation of the isJavaIdentifierStart and isJavaIdentifierPart methods of the Character class.) The question mark (?) is a reserved character in the query language and cannot be used in an identifier.

A query language identifier is case-sensitive with two exceptions:

An identifier cannot be the same as a query language keyword. Here is a list of query language keywords:

ALL

AND

ANY

AS

ASC

AVG

BETWEEN

BY

COUNT

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DELETE

DESC

DISTINCT

EMPTY

EXISTS

FALSE

FETCH

FROM

GROUP

HAVING

IN

INNER

IS

JOIN

LEFT

LIKE

MAX

MEMBER

MIN

MOD

NEW

NOT

NULL

OBJECT

OF

OUTER

OR

ORDER

SELECT

SOME

SUM

TRIM

TRUE

UNKNOWN

UPDATE

UPPER

WHERE

It is not recommended that you use a SQL keyword as an identifier, because the list of keywords may expand to include other reserved SQL words in the future.

Identification Variables

An identification variable is an identifier declared in the FROM clause. Although the SELECT and WHERE clauses can reference identification variables, they cannot declare them. All identification variables must be declared in the FROM clause.

Because an identification variable is an identifier, it has the same naming conventions and restrictions as an identifier with the exception that an identification variables is case-insensitive. For example, an identification variable cannot be the same as a query language keyword. (See the preceding section for more naming rules.) Also, within a given persistence unit, an identification variable name must not match the name of any entity or abstract schema.

The FROM clause can contain multiple declarations, separated by commas. A declaration can reference another identification variable that has been previously declared (to the left). In the following FROM clause, the variable t references the previously declared variable p:

FROM Player p, IN (p.teams) AS t

Even if an identification variable is not used in the WHERE clause, its declaration can affect the results of the query. For an example, compare the next two queries. The following query returns all players, whether or not they belong to a team:

SELECT p
FROM Player p

In contrast, because the next query declares the t identification variable, it fetches all players that belong to a team:

SELECT p
FROM Player p, IN (p.teams) AS t

The following query returns the same results as the preceding query, but the WHERE clause makes it easier to read:

SELECT p
FROM Player p
WHERE p.teams IS NOT EMPTY

An identification variable always designates a reference to a single value whose type is that of the expression used in the declaration. There are two kinds of declarations: range variable and collection member.

Range Variable Declarations

To declare an identification variable as an abstract schema type, you specify a range variable declaration. In other words, an identification variable can range over the abstract schema type of an entity. In the following example, an identification variable named p represents the abstract schema named Player:

FROM Player p

A range variable declaration can include the optional AS operator:

FROM Player AS p

In most cases, to obtain objects a query uses path expressions to navigate through the relationships. But for those objects that cannot be obtained by navigation, you can use a range variable declaration to designate a starting point (or root).

If the query compares multiple values of the same abstract schema type, then the FROM clause must declare multiple identification variables for the abstract schema:

FROM Player p1, Player p2

For a sample of such a query, see Comparison Operators.

Collection Member Declarations

In a one-to-many relationship, the multiple side consists of a collection of entities. An identification variable can represent a member of this collection. To access a collection member, the path expression in the variable’s declaration navigates through the relationships in the abstract schema. (For more information on path expressions, see the following section.) Because a path expression can be based on another path expression, the navigation can traverse several relationships. See Traversing Multiple Relationships.

A collection member declaration must include the IN operator, but it can omit the optional AS operator.

In the following example, the entity represented by the abstract schema named Player has a relationship field called teams. The identification variable called t represents a single member of the teams collection.

FROM Player p, IN (p.tea
ms) t

Joins

The JOIN operator is used to traverse over relationships between entities, and is functionally similar to the IN operator.

In the following example, the query joins over the relationship between customers and orders:

SELECT c
 FROM Customer c JOIN c.orders o
 WHERE c.status = 1 AND o.totalPrice > 10000

The INNER keyword is optional:

SELECT c
 FROM Customer c INNER JOIN c.orders o
 WHERE c.status = 1 AND o.totalPrice > 10000

These examples are equivalent to the following query, which uses the IN operator:

SELECT c
 FROM Customer c, IN(c.orders) o
 WHERE c.status = 1 AND o.totalPrice > 10000

You can also join a single-valued relationship.

SELECT t
 FROM Team t JOIN t.league l
 WHERE l.sport = :sport

A LEFT JOIN or LEFT OUTER JOIN retrieves a set of entities where matching values in the join condition may be absent. The OUTER keyword is optional.

SELECT c.name, o.totalPrice
FROM Order o LEFT JOIN o.customer c

A FETCH JOIN is a join operation that returns associated entities as a side-effect of running the query. In the following example, the query returns a set of departments, and as a side-effect, the associated employees of the departments, even though the employees were not explicitly retrieved by the SELECT clause.

SELECT d
FROM Department d LEFT JOIN FETCH d.employees
WHERE d.deptno = 1

Path Expressions

Path expressions are important constructs in the syntax of the query language, for several reasons. First, they define navigation paths through the relationships in the abstract schema. These path definitions affect both the scope and the results of a query. Second, they can appear in any of the main clauses of a query (SELECT, DELETE, HAVING, UPDATE, WHERE, FROM, GROUP BY, ORDER BY). Finally, although much of the query language is a subset of SQL, path expressions are extensions not found in SQL.

Examples of Path Expressions

Here, the WHERE clause contains a single_valued_path_expression. The p is an identification variable, and salary is a persistent field of Player.

SELECT DISTINCT p
FROM Player p
 WHERE p.salary BETWEEN :lowerSalary AND :higherSalary

Here, the WHERE clause also contains a single_valued_path_expression. The t is an identification variable, league is a single-valued relationship field, and sport is a persistent field of league.

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
 WHERE t.league.sport = :sport

Here, the WHERE clause contains a collection_valued_path_expression. The p is an identification variable, and teams designates a collection-valued relationship field.

SELECT DISTINCT p
FROM Player p
 WHERE p.teams IS EMPTY

Expression Types

The type of a path expression is the type of the object represented by the ending element, which can be one of the following:

For example, the type of the expression p.salary is double because the terminating persistent field (salary) is a double.

In the expression p.teams, the terminating element is a collection-valued relationship field (teams). This expression’s type is a collection of the abstract schema type named Team. Because Team is the abstract schema name for the Team entity, this type maps to the entity. For more information on the type mapping of abstract schemas, see the section Return Types.

Navigation

A path expression enables the query to navigate to related entities. The terminating elements of an expression determine whether navigation is allowed. If an expression contains a single-valued relationship field, the navigation can continue to an object that is related to the field. However, an expression cannot navigate beyond a persistent field or a collection-valued relationship field. For example, the expression p.teams.league.sport is illegal, because teams is a collection-valued relationship field. To reach the sport field, the FROM clause could define an identification variable named t for the teams field:

FROM Player AS p, IN (p.teams) t
 WHERE t.league.sport = ’soccer’

WHERE Clause

The WHERE clause specifies a conditional expression that limits the values returned by the query. The query returns all corresponding values in the data store for which the conditional expression is TRUE. Although usually specified, the WHERE clause is optional. If the WHERE clause is omitted, then the query returns all values. The high-level syntax for the WHERE clause follows:

where_clause ::= WHERE conditional_expression

Literals

There are four kinds of literals: string, numeric, Boolean, and enum.

String Literals

A string literal is enclosed in single quotes:

’Duke’

If a string literal contains a single quote, you indicate the quote by using two single quotes:

’Duke’’s’

Like a Java String, a string literal in the query language uses the Unicode character encoding.

Numeric Literals

There are two types of numeric literals: exact and approximate.

An exact numeric literal is a numeric value without a decimal point, such as 65,– 233, and +12. Using the Java integer syntax, exact numeric literals support numbers in the range of a Java long.

An approximate numeric literal is a numeric value in scientific notation, such as 57.,– 85.7, and +2.1. Using the syntax of the Java floating-point literal, approximate numeric literals support numbers in the range of a Java double.

Boolean Literals

A Boolean literal is either TRUE or FALSE. These keywords are not case-sensitive.

Enum Literals

The Java Persistence Query Language supports the use of enum literals using the Java enum literal syntax. The enum class name must be specified as fully qualified class name.

SELECT e
 FROM Employee e
 WHERE e.status = com.xyz.EmployeeStatus.FULL_TIME

Input Parameters

An input parameter can be either a named parameter or a positional parameter.

A named input parameter is designated by a colon (:) followed by a string. For example, :name.

A positional input parameter is designated by a question mark (?) followed by an integer. For example, the first input parameter is ?1, the second is ?2, and so forth.

The following rules apply to input parameters:

Conditional Expressions

A WHERE clause consists of a conditional expression, which is evaluated from left to right within a precedence level. You can change the order of evaluation by using parentheses.

Operators and Their Precedence

Table 27–2 lists the query language operators in order of decreasing precedence.

Table 27–2 Query Language Order Precedence

Type 

Precedence Order 

Navigation 

. (a period)

Arithmetic 

+ – (unary)

* / (multiplication and division)

+ – (addition and subtraction)

Comparison 

=

>

>=

<

<=

<> (not equal)

[NOT] BETWEEN

[NOT] LIKE

[NOT] IN

IS [NOT] NULL

IS [NOT] EMPTY

[NOT] MEMBER OF

Logical 

NOT

AND

OR

BETWEEN Expressions

A BETWEEN expression determines whether an arithmetic expression falls within a range of values.

These two expressions are equivalent:

p.age BETWEEN 15 AND 19
 p.age >= 15 AND p.age <= 19

The following two expressions are also equivalent:

p.age NOT BETWEEN 15 AND 19
 p.age < 15 OR p.age > 19

If an arithmetic expression has a NULL value, then the value of the BETWEEN expression is unknown.

IN Expressions

An IN expression determines whether or not a string belongs to a set of string literals, or whether a number belongs to a set of number values.

The path expression must have a string or numeric value. If the path expression has a NULL value, then the value of the IN expression is unknown.

In the following example, if the country is UK the expression is TRUE. If the country is Peru it is FALSE.

o.country IN (’UK’, ’US’, ’France’)

You may also use input parameters:

o.country IN (’UK’, ’US’, ’France’, :country)

LIKE Expressions

A LIKE expression determines whether a wildcard pattern matches a string.

The path expression must have a string or numeric value. If this value is NULL, then the value of the LIKE expression is unknown. The pattern value is a string literal that can contain wildcard characters. The underscore (_) wildcard character represents any single character. The percent (%) wildcard character represents zero or more characters. The ESCAPE clause specifies an escape character for the wildcard characters in the pattern value. Table 27–3 shows some sample LIKE expressions.

Table 27–3 LIKE Expression Examples

Expression 

TRUE 

FALSE 

address.phone LIKE ’12%3’

’123’

’12993’

’1234’

asentence.word LIKE ’l_se’

’lose’

’loose’

aword.underscored LIKE ’\_%’ ESCAPE ’\’

’_foo’

’bar’

address.phone NOT LIKE ’12%3’

’1234’

’123’

’12993’

NULL Comparison Expressions

A NULL comparison expression tests whether a single-valued path expression or an input parameter has a NULL value. Usually, the NULL comparison expression is used to test whether or not a single-valued relationship has been set.

SELECT t
 FROM Team t
 WHERE t.league IS NULL

This query selects all teams where the league relationship is not set. Please note, the following query is not equivalent:

SELECT t
 FROM Team t
 WHERE t.league = NULL

The comparison with NULL using the equals operator (=) always returns an unknown value, even if the relationship is not set. The second query will always return an empty result.

Empty Collection Comparison Expressions

The IS [NOT] EMPTY comparison expression tests whether a collection-valued path expression has no elements. In other words, it tests whether or not a collection-valued relationship has been set.

If the collection-valued path expression is NULL, then the empty collection comparison expression has a NULL value.

Here is an example that finds all orders that do not have any line items:

SELECT o
FROM Order o
WHERE o.lineItems IS EMPTY

Collection Member Expressions

The [NOT] MEMBER [OF] collection member expression determines whether a value is a member of a collection. The value and the collection members must have the same type.

If either the collection-valued or single-valued path expression is unknown, then the collection member expression is unknown. If the collection-valued path expression designates an empty collection, then the collection member expression is FALSE.

The OF keyword is optional.

The following example tests whether a line item is part of an order:

SELECT o
 FROM Order o
 WHERE :lineItem MEMBER OF o.lineItems

Subqueries

Subqueries may be used in the WHERE or HAVING clause of a query. Subqueries must be surrounded by parentheses.

The following example find all customers who have placed more than 10 orders:

SELECT c
FROM Customer c
WHERE (SELECT COUNT(o) FROM c.orders o) > 10

EXISTS Expressions

The [NOT] EXISTS expression is used with a subquery, and is true only if the result of the subquery consists of one or more values and is false otherwise.

The following example finds all employees whose spouse is also an employee:

SELECT DISTINCT emp
FROM Employee emp
WHERE EXISTS (
    SELECT spouseEmp
    FROM Employee spouseEmp
    WHERE spouseEmp = emp.spouse)

ALL and ANY Expressions

The ALL expression is used with a subquery, and is true if all the values returned by the subquery are true, or if the subquery is empty.

The ANY expression is used with a subquery, and is true if some of the values returned by the subquery are true. An ANY expression is false if the subquery result is empty, or if all the values returned are false. The SOME keyword is synonymous with ANY.

The ALL and ANY expressions are used with the =, <, <=, >, >=, <> comparison operators.

The following example finds all employees whose salary is higher than the salary of the managers in the employee’s department:

SELECT emp
FROM Employee emp
WHERE emp.salary > ALL (
    SELECT m.salary
    FROM Manager m
    WHERE m.department = emp.department)

Functional Expressions

The query language includes several string and arithmetic functions which may be used in the WHERE or HAVING clause of a query. The functions are listed in the following tables. In Table 27–4, the start and length arguments are of type int. They designate positions in the String argument. The first position in a string is designated by 1. In Table 27–5, the number argument can be either an int, a float, or a double.

Table 27–4 String Expressions

Function Syntax 

Return Type 

CONCAT(String, String)

String

LENGTH(String)

int

LOCATE(String, String [, start])

int

SUBSTRING(String, start, length)

String

TRIM([[LEADING|TRAILING|BOTH] char) FROM] (String)

String

LOWER(String)

String

UPPER(String)

String

The CONCAT function concatenates two strings into one string.

The LENGTH function returns the length of a string in characters as an integer.

The LOCATE function returns the position of a given string within a string. It returns the first position at which the string was found as an integer. The first argument is the string to be located. The second argument is the string to be searched. The optional third argument is an integer that represents the starting string position. By default, LOCATE starts at the beginning of the string. The starting position of a string is 1. If the string cannot be located, LOCATE returns 0.

The SUBSTRING function returns a string that is a substring of the first argument based on the starting position and length.

The TRIM function trims the specified character from the beginning and/or end of a string. If no character is specified, TRIM removes spaces or blanks from the string. If the optional LEADING specification is used, TRIM removes only the leading characters from the string. If the optional TRAILING specification is used, TRIM removes only the trailing characters from the string. The default is BOTH, which removes the leading and trailing characters from the string.

The LOWER and UPPER functions convert a string to lower or upper case, respectively.

Table 27–5 Arithmetic Expressions

Function Syntax 

Return Type 

ABS(number)

int, float, or double

MOD(int, int)

int

SQRT(double)

double

SIZE(Collection)

int

The ABS function takes a numeric expression and returns a number of the same type as the argument.

The MOD function returns the remainder of the first argument divided by the second.

The SQRT function returns the square root of a number.

The SIZE function returns an integer of the number of elements in the given collection.

NULL Values

If the target of a reference is not in the persistent store, then the target is NULL. For conditional expressions containing NULL, the query language uses the semantics defined by SQL92. Briefly, these semantics are as follows:

Table 27–6 AND Operator Logic

AND 

Table 27–7 OR Operator Logic

OR 

Equality Semantics

In the query language, only values of the same type can be compared. However, this rule has one exception: Exact and approximate numeric values can be compared. In such a comparison, the required type conversion adheres to the rules of Java numeric promotion.

The query language treats compared values as if they were Java types and not as if they represented types in the underlying data store. For example, if a persistent field could be either an integer or a NULL, then it must be designated as an Integer object and not as an int primitive. This designation is required because a Java object can be NULL but a primitive cannot.

Two strings are equal only if they contain the same sequence of characters. Trailing blanks are significant; for example, the strings ’abc’ and ’abc ’ are not equal.

Two entities of the same abstract schema type are equal only if their primary keys have the same value. Table 27–8 shows the operator logic of a negation, and Table 27–9 shows the truth values of conditional tests.

Table 27–8 NOT Operator Logic

NOT Value 

Value 

Table 27–9 Conditional Test

Conditional Test 

Expression IS TRUE

Expression IS FALSE

Expression is unknown 

SELECT Clause

The SELECT clause defines the types of the objects or values returned by the query.

Return Types

The return type of the SELECT clause is defined by the result types of the select expressions contained within it. If multiple expressions are used, the result of the query is an Object[], and the elements in the array correspond to the order of the expressions in the SELECT clause, and in type to the result types of each expression.

A SELECT clause cannot specify a collection-valued expression. For example, the SELECT clause p.teams is invalid because teams is a collection. However, the clause in the following query is valid because the t is a single element of the teams collection:

SELECT t
FROM Player p, IN (p.teams) t

The following query is an example of a query with multiple expressions in the select clause:

SELECT c.name, c.country.name
 FROM customer c
 WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

It returns a list of Object[] elements where the first array element is a string denoting the customer name and the second array element is a string denoting the name of the customer’s country.

Aggregate Functions in the SELECT Clause

The result of a query may be the result of an aggregate function, listed in Table 27–10.

Table 27–10 Aggregate Functions in Select Statements

Name 

Return Type 

Description 

AVG

Double

Returns the mean average of the fields. 

COUNT

Long

Returns the total number of results. 

MAX

the type of the field 

Returns the highest value in the result set. 

MIN

the type of the field 

Returns the lowest value in the result set. 

SUM

Long (for integral fields)Double (for floating point fields)BigInteger (for BigInteger fields)BigDecimal (for BigDecimal fields)

Returns the sum of all the values in the result set. 

For select method queries with an aggregate function (AVG, COUNT, MAX, MIN, or SUM) in the SELECT clause, the following rules apply:

The following example returns the average order quantity:

SELECT AVG(o.quantity)
 FROM Order o

The following example returns the total cost of the items ordered by Roxane Coss:

SELECT SUM(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

The following example returns the total number of orders:

SELECT COUNT(o)
FROM Order o

The following example returns the total number of items in Hal Incandenza’s order that have prices:

SELECT COUNT(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = ’Incandenza’ AND c.firstname = ’Hal’

The DISTINCT Keyword

The DISTINCT keyword eliminates duplicate return values. If a query returns a java.util.Collection, which allows duplicates, then you must specify the DISTINCT keyword to eliminate duplicates.

Constructor Expressions

Constructor expressions allow you to return Java instances that store a query result element instead of an Object[].

The following query creates a CustomerDetail instance per Customer matching the WHERE clause. A CustomerDetail stores the customer name and customer’s country name. So the query returns a List of CustomerDetail instances:

SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
 FROM customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

ORDER BY Clause

As its name suggests, the ORDER BY clause orders the values or objects returned by the query.

If the ORDER BY clause contains multiple elements, the left-to-right sequence of the elements determines the high-to-low precedence.

The ASC keyword specifies ascending order (the default), and the DESC keyword indicates descending order.

When using the ORDER BY clause, the SELECT clause must return an orderable set of objects or values. You cannot order the values or objects for values or objects not returned by the SELECT clause. For example, the following query is valid because the ORDER BY clause uses the objects returned by the SELECT clause:

SELECT o
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = ’CA’
ORDER BY o.quantity, o.totalcost

The following example is not valid because the ORDER BY clause uses a value not returned by the SELECT clause:

SELECT p.product_name
FROM Order o, IN(o.lineItems) l JOIN o.customer c
WHERE c.lastname = ’Faehmel’ AND c.firstname = ’Robert’
ORDER BY o.quantity

The GROUP BY Clause

The GROUP BY clause allows you to group values according to a set of properties.

The following query groups the customers by their country and returns the number of customers per country:

SELECT c.country, COUNT(c)
 FROM Customer c GROUP BY c.country

The HAVING Clause

The HAVING clause is used with the GROUP BY clause to further restrict the returned result of a query.

The following query groups orders by the status of their customer and returns the customer status plus the average totalPrice for all orders where the corresponding customers has the same status. In addition, it considers only customers with status 1, 2, or 3, so orders of other customers are not taken into account:

SELECT c.status, AVG(o.totalPrice)
 FROM Order o JOIN o.customer c
GROUP BY c.status HAVING c.status IN (1, 2, 3)