Chapter 7 Setting Up Database Connectivity

Web applications leverage the benefits of the component-based Java^TM 2 Platform, Standard Edition (J2SE) technology, which include scalability and speed.

Web components, Java Servlets or JSP pages provide the dynamic extension capabilities for Sun Java System Web Server 6.1. Servlets and JSP pages are supported by the services of a runtime platform called a web container. The web container provides services such as request dispatching, security, and concurrency. It also gives web components access to APIs such as naming, datasources, and e-mail.

Sample application that uses the Java Database Connectivity (JDBC) API to record greetings to a database is illustrated in this chapter.

JDBC Settings

In this section you can define the necessary JDBC-related settings in the web server environment prior to deploying and exercising the sample.

1. Install and Configure the JDBC driver

2. To start the database server

3. Create a Database and Table

4. To define the JDBC connection pool

5. To define the JDBC resource

Install and Configure the JDBC driver

JDBC configuration involves setting up a JDBC driver, defining a JDBC connection pool, and registering the JDBC resources used by your application.

A JDBC driver must be configured in the classpath of the web server before you can exercise applications that use JDBC. In this example, the PointBase JDBC driver is used.

To install and configure PointBase for use with the sample applications, follow these steps:

1. Download and Install PointBase Server and Client Products

2. To add PointBase Type 4 JDBC Driver to Classpath of the Web Server

Download and Install PointBase Server and Client Products

Download the PointBase evaluation software from:

http://www.pointbase.com/

Install the PointBase Server and Client products on your system. The PointBase Client product includes the PointBase Type 4 JDBC driver.

To add PointBase Type 4 JDBC Driver to Classpath of the Web Server

1. Open the server-instance/config/server.xml file of the web server instance. Add the location of the PointBase driver in the classpathsuffix.

   You can find the JDBC driver under < pointbase_install_dir>/lib/.

   The driver is named pbclientnn .jar where nn represents the version of PointBase.

2. Restart the web server to make the server aware of the driver.

   Alternatively, you can specify the location of the PointBase driver in the Classpath Suffix field in configuration of the Web Server:

   1. Check Java tab of the Web Server instance -> Java Path Settings page to make this change.

      If a PointBase JDBC driver is already configured in the Classpath Suffix field, replace it with the path to the newly installed driver.

   2. Once you have changed the Classpath Suffix field, apply the changes and restart the web server instance.

To start the database server

1. The database server can be started by performing one of the following actions:

   • On Windows Platforms:

     Execute: < pointbase_install_dir>\tools\server\startserver.bat

   • On UNIX Platforms:

     Execute: <pointbase_install_dir>/tools/server/startserver.sh

     Once you execute the startup script, the following text will be displayed:

     Server started, listening on port 9092, display level: 0 ...

Create a Database and Table

For information about creating and maintaining a database using PointBase, refer to the database documentation. After you have created a database, you need to use the following script to create the table ’Greeting’, which will record application entries:

<install_root>/plugins/java/samples/webapps/jdbc/simple/src/sql/jdbc-simple-pb.sql

To define the JDBC connection pool

Before running the sample application, you need to define a suitable JDBC connection pool that maps to the PointBase database server. You also need a JDBC resource that associates the JDBC references made in the sample application to the JDBC connection pool definition.

1. Through a web browser, access the administration server interface.

   ---

   Note –

   You might have to start the administration server if it is not running.

   ---

2. Select the Java tab in the server instance and click on JDBC Connection Pools

   Figure 7–1 Creating New JDBC Connection Pool

   
   

3. Click New, and select the JDBC driver, as shown in Figure 7-2: Selecting the JDBC Driver:

   Figure 7–2 Selecting the JDBC Driver

   
   

4. In the Add New JDBC Connection Pools page, specify the following settings and the rest unmodified:

   • jdbc-simple-pool as the name of the new connection pool.

     • jdbc:pointbase:server://localhost/sample as database name

     • PBPUBLIC as the user name

     • PBPUBLIC as the password

     • Transaction Isolation set to read-committed

   Figure 7–3 JDBC Connection Pool — General Information

   
   

5. Click OK and then Apply to save and apply your changes.

To define the JDBC resource

Now that the JDBC connection pool definition has been created, you are ready to define a JDBC resource and associate it with the connection pool entry.

1. Under the Java tab of the server instance, click the JDBC Resources link.

2. Click the New button to define a new resource entry.

   Figure 7–4 Creating a JDBC Rresource

   
   

3. Since the sun-web.xml file of the sample web application refers to the JNDI Name of the resource as "jdbc/jdbc-simple", you need to define the JDBC resource with this same JNDI name.

   Set the fields to the following values and click on the OK button to define the resource:

   Figure 7–5 JDBC Resource — General Information

   
   

4. Click on OK and Apply to save and apply your changes.

   After you’ have set up database connectivity, you are ready to deploy the jdbc-simple sample web application that uses these newly created JDBC resources. You can find this sample application at the following location:

   install_root/plugins/java/samples/webapps/jdbc/simple/jdbc-simple.war

   To deploy this sample application, follow the steps outlined in Chapter 6, Running Java Web Applications On Your Server.