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 JDBC API to access relational databases. For information on this API, see 

http://java.sun.com/docs/books/tutorial/jdbc

In the JDBC API, databases are accessed via DataSource objects. A DataSource 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.

Web applications access a data source using a connection, and a DataSource object can be thought of as a factory for connections to the particular data source that the DataSource instance represents. In a basic DataSource implementation, a call to the getConnection method returns a connection object that is a physical connection to the data source. In the Application Server, a data source is referred to as a JDBC resource. See DataSource Objects and Connection Pools for further information about data sources in the Application Server.

If a DataSource object is registered with a JNDI naming service, an application can use the JNDI API to access that DataSource object, which can then be used to connect to the data source it represents.

To maintain the catalog of books, the Duke's Bookstore examples described in Chapters 11 through 22 use the Derby open source database included with the Application Server.

This section describes how to

Populating the Example Database

Note: Application Server 8.2 includes a copy of the open source Derby database server. Application Server 8.0/ 8.1 includes the PointBase database server. If you are using Application Server 8.0/8.1, either follow the instructions in the J2EE Tutorial at http://java.sun.com/j2ee/1.4/docs/tutorial-update6/doc/index.html that works with Application Server 8.0/8.1 or upgrade to Application Server 8.2 (see http://java.sun.com/j2ee/1.4/download.html#appserv to download).

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

  1. Start the Application Server, if it has not been started.
  2. In a terminal window, go to <INSTALL>/j2eetutorial14/examples/web/bookstore/.
  3. Run asant create-db_common. This task starts the database, if it has not been started, and executes the SQL commands contained in the books.sql file.
  4. At the end of the processing, you should see the following output:
    5. ...
[sql] Executing file:
         <j2eetutorial.home>\examples\web\bookstore\books.sql
[sql] 8 of 8 SQL statements executed successfully

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.

Specifying a Web Application's Resource Reference

To access a database from a web application, you must declare a resource reference in the application's web application deployment descriptor (see Declaring Resource References). The resource reference specifies a JNDI name, the type of the data resource, and the kind of authentication used when the resource is accessed. To specify a resource reference for a Duke's Bookstore example using deploytool, follow these steps:

  1. Select the WAR (created in Chapters 11 through 22).
  2. Select the Resource Ref's tab.
  3. Click Add.
  4. Type jdbc/BookDB in the Coded Name field.
  5. Accept the default type javax.sql.DataSource.
  6. Accept the default authorization Container.
  7. Accept the default Sharable selected.

To create the connection to the database, the data access object database.BookDBAO looks up the JNDI name of the bookstore data source object: 

public BookDBAO () throws Exception {
  try  { 
    Context initCtx = new InitialContext();
    Context envCtx = (Context)
      initCtx.lookup("java:comp/env");
    DataSource ds = (DataSource) envCtx.lookup("jdbc/BookDB");
    con =  ds.getConnection();
    System.out.println("Created connection to database.");
  } catch (Exception ex) {
    System.out.println("Couldn't create connection." + 
      ex.getMessage());
    throw new 
      Exception("Couldn't open connection to database: "
      + ex.getMessage());
  }

Mapping the Resource Reference to a Data Source

Both the web application resource reference and the data source defined in the Application Server have JNDI names. See JNDI Naming for a discussion of the benefits of using JNDI naming for resources.

To connect the resource reference to the data source, you must map the JNDI name of the former to the latter. This mapping is stored in the web application runtime deployment descriptor. To create this mapping using deploytool, follow these steps:

  1. Select localhost:4848 in the Servers list to retrieve the data sources defined in the Application Server.
  2. Select the WAR in the Web WARs list.
  3. Select the Resource Ref's tab.
  4. Select the Resource Reference Name, jdbc/BookDB, defined in the previous section.
  5. In the Sun-specific Settings frame, select jdbc/BookDB from the JNDI Name drop-down list.