The Java EE 5 Tutorial

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 Chapter 4, Java Servlet Technology through Chapter 15, Internationalizing and Localizing Web Applications 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, Introduction to the Java Persistence API). See Chapter 25, Persistence in the Web Tier 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 or are using NetBeans IDE to deploy the application, follow these steps:

  1. In a terminal window, go to the books directory or any one of the bookstore1 through bookstore6 example directories.

  2. Start the Java DB database server. For instructions, see Starting and Stopping the Java DB Database Server. You don’t have to do this if you are using NetBeans IDE. It starts the database server automatically.

  3. Type 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:

    [sql] 181 of 181 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 Resources node.

  2. Expand the JDBC node.

  3. Select the JDBC Resources node.

  4. Click the New... button.

  5. Type jdbc/BookDB in the JNDI Name field.

  6. Choose DerbyPool for the Pool Name.

  7. Click OK.