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. To maintain a catalog of books, the Duke's Bookstore examples described in Chapters 3 through 14 use the Java DB database included with the Application Server.

To access the data in a database, web applications use the new Java Persistence API (see chapter 24). See chapter 25 to learn how the Duke's Bookstore applications use this API to access the book data.

To run the Duke's Bookstore applications, you need to first populate the database with the book data and create a data source in the application server. The rest of this section explains how to perform these tasks.

Populating the Example Database

When you deploy any of the Duke's Bookstore applications using ant deploy, the database is automatically populated at the same time. If you want to populate the database separately from the deploy task, follow these steps:

  1. In a terminal window, go to any of the top-level web component example directories.
  2. Start the Java DB database server. For instructions, see Starting and Stopping the Java DB Database Server (page 30).
  3. Run ant create-tables. This task runs a 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] 185 of 185 SQL statements executed successfully 

When you are running create-tables, 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

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.

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.