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) 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 JavaBeans component (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 3 through 14 use the Derby evaluation database included with the Application Server.

This section describes the following:

Populating the Example Database

To populate the database for the Duke's Bookstore examples, follow these steps:

  1. In a terminal window, go to <INSTALL>/javaeetutorial5/examples/web/bookstore/.
  2. Start the Derby database server. For instructions, see Starting and Stopping the Java DB Database Server (page 30).
  3. Run asant create-db_common. This task runs a Derby commander tool command to read the file tutorial.sql and execute the SQL commands contained in the file.
  4. At the end of the processing, you should see the following output:
  5. ...
    [sql] 8 of 8 SQL statements executed successfully 

When you are running create-db_common, don't worry if you see a message that an SQL statement failed. This usually happens the first time you run the command because it always tries to delete an existing database table first before it creates a new one. The first time through, there is no table yet, of course.

Creating a Data Source in the Application Server

Data sources in the Application Server implement connection pooling. To define the Duke's Bookstore data source, you use the installed Derby connection pool named DerbyPool.

You create the data source using the Application Server Admin Console, following this procedure:

  1. Expand the JDBC node.
  2. Select the JDBC Resources node.
  3. Click the New... button.
  4. Type jdbc/BookDB in the JNDI Name field.
  5. Choose DerbyPool for the Pool Name.
  6. Click OK.

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 cyle of the EntityManager instance.

A resource-local entity manager can not participate in global transaction. Secondly web container won't roll back pending transaction left behind by poorly written applications.

Creating an Entity Class

As explained in Accessing Databases from Web Applications, an Entity class is a JavaBeans 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 in 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 javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;


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;

  public String getBookId() {
    return this.bookId;

  public String getTitle() {
    return this.title;

  public void setBookId(String id) {

  public void setTitle(String 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 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 {
private EntityManagerFactory emf;

public void contextInitialized(ServletContexEvent event) {
  context = event.getServletContext();
  try {
    BookDBAO bookDB = new BookDBAO(emf);
    context.setAttribute("bookDB", bookDB);
  } catch (Exception ex) {
      "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 {

  private static 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 must 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").
  } 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 deprecating 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);
      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;
      } 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.

UserTransaction utx;
try {
} catch (Exception ex) {
  try {
  } catch (Exception exe) {
    System.out.println("Rollback failed: "+exe.getMessage());