Getting Started: Setting Up Database Connectivity


sun.com	How To Buy \| My Sun \| Worldwide Sites

Sun[tm] ONE Application Server 7

Setting Up Database Connectivity

The sample application uses the Java[tm] Database Connectivity (JDBC[tm]) API to record greetings to a database. In this section you will define the necessary JDBC-related settings in the application server environment prior to deploying and exercising the sample.

JDBC configuration involves setting up a JDBC driver, defining a JDBC connection pool and registering the JDBC resources used by your application. By default, the sample application used in this guide is configured to work with the PointBase Server database product. All distributions of Sun ONE Application Server 7 except for the application server that is installed as part of Solaris[tm] 9 include the PointBase Server database product. If the directory <appserver_install_dir>/pointbase/ exists in your application server installation, then PointBase has already been installed and you can proceed with the following instructions to define the JDBC connection pool and resource objects.

If the PointBase Server product has not been installed as part of your application server installation, you can follow the steps in the section Installing and Configuring PointBase to set up a PointBase environment before proceeding with the JDBC connection pool and resource set up steps. If you are using the application server that has been installed as part of a Solaris 9 installation, you should follow the PointBase installation and configuration instructions.

	1. Configure the JDBC Driver
	2. Define the JDBC Connection Pool
	3. Define the JDBC Resource
	4. Start the PointBase Database

1. Configure the JDBC Driver

A JDBC driver must be configured in the classpath of the application server before you can exercise applications that use JDBC.

Since the PointBase Type 4 JDBC driver should already be configured in your application server instance's environment (based on either the combined installation of the application server with PointBase or through your installation and configuration of PointBase), there is no need for you to perform any additional JDBC driver set up steps.

Adding JDBC Driver to Classpath: When you start exercising the application server with a database other than PointBase, you can easily add a JDBC driver to the application server's classpath through the administrative console.

1. In the console, select the server instance node, the JVM Settings tab, the Path Settings sub tab.

2. In the Path Settings tab, modify the Classpath Suffix setting to reference the appropriate JDBC driver .zip or .jar file. If you are using a Type 2 driver, also modify the Native Library Path Suffix setting to reference the appropriate native library.

3. Save your changes and go back to the General tab of the server instance.

4. Click Apply Changes and then restart the application server to pick up the changes.

Alternatively, you can simply copy a JDBC driver's archive to the following directory:

<domain_config_dir>/domain1/server1/lib/

All Java libraries that are present in the instance's lib/ directory are automatically added to the end of the server's classpath during server instance restart.

Preconfigured PointBase Database: To learn more about how the sample applications use PointBase, see the Sample Applications documentation.

2. 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 and a JDBC resource that associates the JDBC references made in the sample application to the JDBC connection pool definition.

Server Instance Configuration: Resources used by J2EE applications and other server settings are maintained in the server.xml file under:

<domain_config_dir>/domain1/server1/config/

You can manually modify to this file, but keep in mind that when doing so, other users could be updating the file through the either administrative console or the asadmin command line utility. Manual modifications to the server.xml file require a restart of the application server instance in order to make the changes take effect.

1. Through a web browser, access the administrative console. (You might have to start the application server if it is not running).

2. Expand the JDBC node under the application server instance named "server1". Then click the Connection Pools node and click New... to define a new connection pool.

3. Enter the following information in the first screen of the create new connection pool wizard and then click the Next button:

Field	Value
Name	PointBasePool
Database Vendor	select: PointBase 4.2

Create New JDBC Connection Pool Screenshot

4. The following screen appears:

5. Scroll down to the Properties section and specify the following values:

Property	Value
user	jdbc
password	jdbc
dataBaseName	jdbc:pointbase:server://localhost/sun-appserv-samples

Connection Pool Properties and Using Other Databases: Although the "dataBaseName" property is supported by PointBase Server, it is not supported by all other databases. For example, for an Oracle RDBMS, use the "URL" property name to specify the URL to be used by the JDBC driver to connect to the Oracle RDBMS. Consult your JDBC driver's documentation for details on exactly which properties are supported. For an example of using an Oracle JDBC driver with the sample, see the note on Using an Oracle Type 4 JDBC Driver for details.

If you see any additional properties in this list, select the checkbox to the left of each of these properties and click the Delete button to remove these unused properties.

Set JDBC Connection Pool Properties Screenshot

6. Click the Finish button to complete the wizard. As a result of completing the JDBC connection pool wizard, the administrative server recognizes that although changes have been made to the server's configuration, the changes have not yet been applied to the active configuration. Consequently, several warning icons are displayed in the administrative console to inform you that pending changes need to be applied before they take effect.

Before you apply these changes, proceed to the next step to define the JDBC resource for the sample. After defining the JDBC resource, you will apply both the outstanding connection pool and JDBC resource changes at the same time.

3. 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 JDBC node, click the JDBC Resources node and click New... to define a new resource entry.

2. 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 the OK button to define the resource:

Field	Value
JNDI Name	jdbc/jdbc-simple
Pool Name	select: PointBasePool

3. Now that you have defined both the JDBC connection pool and resource for the sample application, you are ready to apply the changes so to make the application server instance aware of the changes.

3a. Click the Apply Changes Required link in the upper right hand side of the window to apply the outstanding JDBC connection pool and resource changes.

3b. A message is displayed stating that the changes have been applied. Note that a server instance restart is not required in this case.

What Happens During Apply Changes? When you either define new objects such as JDBC connection pool and resources or modify such objects, the administrative server saves the configuration settings to a server.xml file in a backup directory under the application server instance's configuration area. For example:

<domain_config_dir>/domain1/server1/config/backup/server.xml

As you continue to make and save changes, the changes continue to be made to the server.xml file in the backup area.

In order to make pending changes visible to the application server instance, you must Apply Changes to force the administrative server to copy the server.xml file being maintained in the backup area to the live location under:

<domain_config_dir>/domain1/server1/config/server.xml

Although some changes that are applied to the configuration area are picked up immediately by the application server instance, other changes require a server instance restart in order for the changes to become effective. The administrative console notifies you when such changes require a server restart.

Using an Oracle Type 4 JDBC Driver: Using another RDBMS is simple:

1. Use the supplied SQL file to create the table in an Oracle RDBMS:

<install_dir>/samples/jdbc/simple/src/sql/jdbc-simple-ora.sql

2. Add the Oracle Type 4 driver to the server classpath by either:

a) simply dropping the driver library into the application server instance's lib/ directory:

<domain_config_dir>/domain1/server1/lib/

All libraries in this directory are automatically added the end of the server's classpath during restart.

or b) adding the driver library to the Classpath Suffix under the application server instance's JVM Settings->Path Settings area.

3. Create a JDBC Connection Pool object for the Oracle Type 4 driver:

Datasource Classname = oracle.jdbc.pool.OracleDataSource

Connection Pool Properties:

Property

Value

URL

jdbc:oracle:thin:@$ORACLE_SERVER:1521:$ORACLE_SID

where $ORACLE_SERVER and $ORACLE_SID must be replaced with the appropriate value for the target database. For example:

jdbc:oracle:thin:@localhost:1521:orcl

User

$USER (set as appropriate for your database)

Password

$PASSWORD (set as appropriate for your database)

4. Remap the "jdbc/jdbc-simple" JDBC resource from the PointBasePool connection pool to the newly defined Oracle connection pool.

5. Apply the changes and restart the application server.

6. Rerun your application.

4. Start the PointBase Server Database

Before exercising the sample application, you must start the PointBase Server database.

If you are either sharing your application server installation with other users or your system user ID does not have write permissions to the area in which PointBase is installed (which is typically the case when the application server is installed by the root user on UNIX systems and you are using a non-root user ID), you should make a copy of the pre-built PointBase databases and the PointBase Server startup script.

If you are not sharing the PointBase installation with other users, proceed to Starting the PointBase Server.

Making Your Own Copy of the Database

You can create your own copy of the PointBase Server environment by following these steps:

1. Create a directory named "pointbase/" under a directory in which you have write permissions.

2. Copy the prepopulated PointBase database files from the samples installation area to your own PointBase area.

To copy the prepopulated database files to your own PointBase installation, copy the files contained in the following directory:

<appserver_install_dir>/samples/pointbase/databases/*

to:

<personal_pointbase_dir>/pointbase/databases/

This operation will copy the files named sun-appserv-samples$1.wal and sun-appserv-samples.dbn to the your own PointBase area.

3. Copy the the following files from the PointBase installation area to <personal_pointbase_dir>/pointbase/ directory:

Files	Comments
StartServer.sh or StartServer.bat	Start up script containing port number on which the PointBase Server listens.
pointbase.ini	Initialization file specifying location of database files.

If PointBase was installed as part of the application server installation, these files are located at:

<appserver_install_dir>/pointbase/server/

If you downloaded an installed the evaluation distribution of PointBase, these files are located at:

<pointbase_install_dir>/tools/server/

4. Edit the StartServer.sh or StartServer.bat script to specify a port number that does not conflict with other running instances of the PointBase Server.

Using a Non-default Port Number: If you change the port number on which the PointBase Server listens from the default value of 9092, you must modify the JDBC Connection Pool properties to reflect the non-default port number. Otherwise the application server will not be able to contact your copy of the PointBase Server.

The dataBaseName property specified for the JDBC Connection Pool must be changed from:

jdbc:pointbase:server://localhost/sun-appserv-samples

to:

jdbc:pointbase:server://localhost:<port>/sun-appserv-samples

Where <port> is the port number specified in the StartServer script.

To add the port number to the dataBaseName property, follow these steps:

1. Select the PointBasePool node in the application server administrative console.

2. Scroll down and click the Properties... button to edit the connection pool properties.

3. Modify the dataBaseName property value to reflect the appropriate port number and click OK to save the changes.

4. Apply the changes and restart the application server instance to make the connection pool changes take effect.

Now you are ready to start the PointBase Server from your own directory area.

Starting the PointBase Server

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

Windows Platforms:

PointBase installed with the application server:

Start -> Programs -> Sun Microsystems -> Sun ONE Application Server 7 -> Start PointBase

PointBase downloaded and installed separately:

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

UNIX Platforms:

PointBase installed with the application server:

Execute: <appserver_install_dir>/pointbase/server/StartServer.sh

PointBase downloaded and installed separately:

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

Once you execute the startup script, you will see the following text in either a command window or at the UNIX terminal prompt:

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

Don't Need A Console Window? On UNIX, if you want to run the PointBase server in the background, you can edit the StartServer.sh script and place the option "/noconsole" at the end of the command line.

.../java -classpath ./lib/pbserver42RE.jar com.pointbase.net.netServer /port:9092 /noconsole

Use "&" at the end of the StartServer.sh command to run the server in the background.

./StartServer.sh &
[1] 26418

See the PointBase server documentation in the following location for more information on starting and stopping the server:

<pointbase_install_dir>/docs/

Proceed to Deploying and Running the Sample Application to exercise the application.

Installing and Configuring PointBase Server

PointBase is not installed when either the application server is installed as part of a Solaris 9 installation or, during installation of the application server, the PointBase component was deselected as a component to be installed.

To determine if pointbase is installed and configured to work with sample applications, check your application server installation for the following directory:

<appserver_install_dir>/pointbase/

If this directory exists, then PointBase has already been installed as part of the application server installation. Continue with the database set up steps at the beginning of this document.

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

	1. Download and Install PointBase Server and Client Products
	2. Copy Samples PointBase Database Files
	3. Add PointBase Type 4 JDBC[tm] Driver to Application Server's Classpath

1. Download and Install PointBase Server and Client Products

Download the PointBase evaluation software from http://www.pointbase.com.

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

2. Copy Samples PointBase Database Files

The samples component of Sun ONE Application Server 7 contains a prepopulated PointBase database file for the sample applications. If you do not intend to share your PointBase installation with other users, you must copy the prepopulated database files from the samples area to the PointBase product installation area.

If you intend to share the PointBase installation with other users, then you will learn how to create your own PointBase runtime environment in a subsequent section. If this is the case, then proceed to the next step to add the PointBase JDBC driver to your application server's classpath.

To copy the prepopulated database files to your PointBase installation, copy the files contained in the following directory:

<appserver_install_dir>/samples/pointbase/databases/*

to:

<pointbase_install_dir>/databases/

This operation will copy the files named sun-appserv-samples$1.wal and sun-appserv-samples.dbn to the PointBase installation area.

3. Add PointBase Type 4 JDBC Driver to Server Application Server's Classpath

Copy the PointBase Type 4 JDBC driver library from the PointBase installation directory to the lib/ directory of your application server instance. For example:

.../domains/domain1/server1/lib/

You can find the JDBC driver under <pointbase_install_dir>/lib/. The driver is named pbclientnn.jar where nn represents the version of PointBase.

Restart the application 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 the application server's configuration. Start the administrative console and access the application server instance's JVM Settings -> Path Settings area 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. Once you've changed the Classpath Suffix field, apply the changes and restart the application server instance.

Now that you've installed and configured PointBase, continue with the database set up steps at the beginning of this document.