Accessing Databases from Web Applications

Data that is shared between Web components and persistent between invocations of a Web application is usually maintained in a database. Web applications use the JDBC 2.0 API to access relational databases. 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 like the location of the database server, the name of the database, the network protocol to use to communicate with the server, and so on.

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 method DataSource.getConnection returns a connection object that is a physical connection to the data source.

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.

DataSource objects that implement connection pooling also produce a connection to the particular data source that the DataSource class represents. The connection object that the method DataSource.getConnection returns is a handle to a PooledConnection object rather than being a physical connection. An application uses the connection object just as it usually does and is generally unaware that it is in any way different. Connection pooling has no effect whatever on application code except that a pooled connection, as is true with all connections, should always be explicitly closed. When an application closes a connection that is pooled, the connection is returned to a pool of reusable connections. The next time DataSource.getConnection is called, a handle to one of these pooled connections will be returned if one is available. Because connection pooling avoids creating a new physical connection every time one is requested, it can help to make applications run significantly faster.

The Duke's Bookstore examples use the PointBase database shipped with Sun ONE Application Server 7 to maintain the catalog of books. This section describes how to:

Start the PointBase database server
Populate the database
Add the PointBase JDBC driver to the application server's classpath
Define a data source in the application server
Configure a Web application to reference the data source with a JNDI name
Map the JNDI name to the data source defined in the application server

Starting the PointBase Database Server

The Sun ONE Application Server 7 is distributed with a development version of the PointBase database server. To start the server:

Open a terminal window.
Navigate to <S1AS7_HOME>/pointbase/server.
Execute StartServer.

Populating the Example Database

The Sun ONE Application Server 7 is distributed with a sample database that contains the table required for the Duke's Bookstore examples. The table is accessible only to the user name BOOKSTORE. If you need to repopulate the database:

Start the PointBase console tool:
Open a terminal window.
Navigate to <S1AS7_HOME>/pointbase/client_tools.
Execute PB_console.
In the PointBase console window, connect to the database jdbc:pointbase:server://localhost/sun-appserv-samples with user name PBPUBLIC and password PBPUBLIC.
In the IDE, mount the filesystem
<INSTALL>/j2eetutorial/examples/web/bookstore.
Expand the bookstore node.
In the PointBase console window, execute the SQL statements in createUser.sql:
Copy the SQL statements in createUser.sql.
Paste the SQL statements into the text area labeled Enter SQL Commands by choosing EditRight ArrowPaste in the PointBase console window.
Choose SQLRight ArrowExecute All.
Choose DBARight ArrowDisconnect from Database.
Choose DBARight ArrowConnect to Database.
Type BOOKSTORE for the user name and BOOKSTORE for the password.
In the PointBase console window, execute the SQL statements in bookstore.sql. At the end of the processing, you should see the following output:

[java] ID
[java] ----------
[java] 201
[java] 202
[java] 203
[java] 204
[java] 205
[java] 206
[java] 207
[java]
[java] 7 Rows Selected.
[java]
[java] SQL>
[java]
[java] COMMIT;
[java] OK

Add PointBase JDBC Driver to the Application Server's Classpath

You can add the PointBase JDBC driver to the application server's classpath in one of two ways:

Copy the PointBase JDBC driver library <S1AS7_HOME>/pointbase/client_tools/lib/pbclient42RE.jar to the lib/ directory of your application server instance. For example: <S1AS7_HOME>/domains/domain1/server1/lib/.
Specify the location of the PointBase driver in the Classpath Suffix field in the application server's configuration:
Start the administration console by opening the URL http://localhost:4848 in a browser.
Select the server1 node.
Select the JVM Settings tab.
Click the Path Settings link.
Add <S1AS7_HOME>/pointbase/client_tools/lib/pbclient42RE.jar to the Classpath Suffix text area.
Click Save.

Then, restart the application server to make the server aware of the driver:

Click the General tab of the administration console.
Click Stop to stop the server and Start to restart it.

Defining a Data Source in Sun ONE Application Server 7

Data sources in the Sun ONE Application Server 7 implement connection pooling. Thus, to define the Duke's Bookstore data source, you first need to define a data pool as follows:

In the IDE, select the Runtime tab of the Explorer.
Expand the nodes Server RegistryRight ArrowInstalled ServersRight ArrowSun ONE Application Server 7Right Arrowlocalhost:4848.
Right-click Unregistered JDBC Connection Pools and select Add New JDBC Data Pool.
Type com.pointbase.jdbc.jdbcDataSource for the DataSource Classname.
Type bookstore-pool for the Name.
Click Properties and open the property editor.
Add the properties and values listed in Table 2-1:

Table 2-1 Bookstore Pool Properties

Property	Value
DatabaseName	jdbc:pointbase:server://localhost/sun-appserv-samples
User	BOOKSTORE
Password	BOOKSTORE

Right-click the bookstore-pool data pool and choose Register.
Select the application server that you want the pool to be registered in and click Register.
Click OK.
Expand the Registered JDBC Connection Pools node and notice that bookstore-pool is listed.

Then, create the data source as follows:

In the IDE, select the Runtime tab of the Explorer.
Expand the nodes Server RegistryRight ArrowInstalled ServersRight ArrowSun ONE Application Server 7Right Arrowlocalhost:4848.
Right-click Unregistered JDBC Data Sources and select Add a new JDBC Data Source.
Type jdbc/BookDB for the JNDI Name.
Choose bookstore-pool for the Pool Name.
Right-click the jdbc/BookDB data source and choose Register.
Select the application server that you want the data source to be registered in and click Register.
Click OK.
Expand the Registered JDBC Connection Data Sources node and notice that jdbc/BookDB is listed.

Configuring the Web Application to Reference a Data Source with JNDI

In order to access a database from a Web application, you must declare resource reference in the application's Web application deployment descriptor (see References to Environment Entries, Resource Environment Entries, or Resources). The resource reference declares a JNDI name, the type of the data resource, and the kind of authentication used when the resource is accessed. The JNDI name is used to create a data source object in the database helper class database.BookDB:

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());
 } 

To specify a resource reference to the bookstore data source:

Select the Web module.
Expand the WEB-INF node.
Select the web.xml.
Select the References tab in the property sheet.
Click the Resource References Property and open the property editor.
Click Add.
Type jdbc/BookDB in the Name field.
Leave the default type javax.sql.DataSource.
Leave the default authorization Container.
Choose Shareable for Sharing Scope.

Mapping the Web Application JNDI Name to a Data Source

Since the resource reference declared in the Web application deployment descriptor uses a JNDI name to refer to the data source, you must connect the name to a data source defined by the application server as follows:

Select the Web module.
Expand the WEB-INF node.
Select the web.xml file.
Select the Sun ONE AS property sheet.
Select the Resource Reference Mapping property and open the property editor.
For the Resource Reference Name jdbc/BookDB, type jdbc/BookDB in the JNDI Name field.

All of the material in The J2EE Tutorial for the Sun ONE Platform is copyright-protected and may not be published in other works without express written permission from Sun Microsystems.