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

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 PointBase evaluation database included with the Application Server. See the PointBase support site at

Populate the database with bookstore data

Create a data source in the Application Server

Specify a Web application's resource reference

Map the resource reference to the data source defined in the Application Server

Populating the Example Database

In a terminal window, go to <INSTALL>/j2eetutorial14/examples/web/bookstore/.

Run asant create-db_common. This task runs a PointBase commander tool command to read the file books.sql and execute the SQL commands contained in the file. The table named books is created for the user pbpublic in the sun-appserv-samples PointBase database.

At the end of the processing, you should see the following output:

...
[java] SQL> INSERT INTO books VALUES('207', 'Thrilled', 'Ben',
[java]  'The Green Project: Programming for Consumer Devices',
[java]  30.00, false, 1998, 'What a cool book', 20);
[java] 1 row(s) affected

[java] SQL> INSERT INTO books VALUES('208', 'Tru', 'Itzal',
[java]  'Duke: A Biography of the Java Evangelist',
[java]  45.00, true, 2001, 'What a cool book.', 20);
[java] 1 row(s) affected

In a terminal window, go to <J2EE_HOME>/pointbase/tools/serveroption/.

Execute startconsole.

In the Connect to Database dialog box:

Enter jdbc:pointbase:server://localhost/sun-appserv-samples in the URL field.

Enter PBPUBLIC in the password field.

Click OK.

Expand the SCHEMAS Right Arrow

PBPUBLIC

TABLES nodes. Notice that there is a table named BOOKS.

To see the contents of the books table:

In the Enter SQL commands text area, enter select * from books;.

Click the Execute button.

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 PointBase connection pool named PointBasePool.

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

Expand the JDBC node.

Select the JDBC Resources node.

Click the New... button.

Type jdbc/BookDB in the JNDI Name field.

Choose PointBasePool for the Pool Name.

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:

Select the WAR (created in Chapters 11 through 22).

Select the Resource Ref's tab.

Click Add.

Type jdbc/BookDB in the Coded Name field.

Accept the default type javax.sql.DataSource.

Accept the default authorization Container.

Accept the default Sharable selected.

To create the connection to the database, the database helper class database.BookDB looks up the JNDI name of the bookstore data source object:

public BookDB () 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:

Select localhost:4848 in the Servers list to retrieve the data sources defined in the Application Server.

Select the WAR.

Select the Resource Ref's tab.

Select the Resource Reference Name, jdbc/BookDB, defined in the previous section.

In the Sun-specific Settings frame, select jdbc/BookDB from the JNDI Name drop-down list.