C H A P T E R 11

Using Databases

This chapter describes how to configure Java DataBase Connectivity (JDBC⁾software. Most of the information pertains to the PointBase Server 4.2 Restricted Edition (PointBase), which is used with many of the IDE examples and tutorials. General information about using other databases with the IDE is also included.

PointBase is the default database provided as part of the Java Studio Enterprise installation. PointBase can be found in the Sun Java System Application Server Standard Edition 7 2004Q2 installation subdirectory, for example, java-studio-install-dir\AppServer7\pointbase. Documentation for PointBase can be found at java-studio-install-dir\AppServer7\pointbase\docs

This release of Java Studio Enterprise software introduces enhancements in the integration of PointBase with the IDE and Application Server.

This chapter covers:

Setting Up PointBase Connectivity

Using Other JDBC Enabled Databases

Setting Up PointBase Connectivity

An enterprise application uses the JDBC API to interact with a database. Enterprise applications require pooling of database connections so that the business objects in the system can share database access. A JDBC data source lets you make connections to a database. A persistent manager is a component responsible for the persistence of the entity beans installed in the container. The connection pool, JDBC data source and JDBC persistent manager are referred to as connectivity resources.

Before you can deploy and execute an enterprise application that uses a database with Sun Java System Application Server, the database's JDBC driver must be enabled in the application server environment and the connectivity resources must be created.

When you use the Java Studio Enterprise 7 installer to install the IDE and Application Server, the following tasks are done for you:

The PointBase JDBC driver is installed and registered with Application Server. The connectivity resources are created as part of this configuration step.

Starting and establishing a connection to the PointBase database is done automatically when the database is accessed the first time. The following activities, which require database access, automatically start the PointBase database server:

Connect to database server using the PointBase JDBC driver node from the IDE's Database Explorer.

Connect to database server from a disconnected pointbase connection under the database explorer

Capture dbschema

Connect to database server from cmp wizard

Connect to database from Web Application Framework wizards.

Execute a J2EE application against an appserver instance having registered PointBase connection pools.

Note - Execution against the default application server instance may start the PointBase database server even though the executed application does not use PointBase. This happens because the default application server instance has a PointBase connection pool defined as part of the installer's configuration process for Application Server and IDE integration. During the automatic starting of Application Server, the existence of the connection pool resource causes the IDE to start the PointBase database server before it starts the application server instance. If you are not using the PointBase database at all in your applications and want to avoid this startup of the PointBase server, delete the connection pool resource from your application server instance.

To use the IDE examples when you have installed Sun Java System Application Server independently of your IDE installation, see To Use an External PointBase Installation With the IDE.

To Confirm the PointBase Resource Configuration

1. In the IDE's Runtime window, expand the Server Registry node and expand the Installed Servers node.

2. Expand the Sun Java System Application Server 7 node and expand the default admin server node (localhost:4848).

Your port number may vary.

3. Expand the default application server instance node, (MyServer(localhost:xxxx)).

The variable xxxx = the port number assigned to your application server instance.

4. Expand the registered resources for JDBC Connection Pools, JDBC Data Sources, and Persistent Mangers.

If the IDE installer was able to configure your PointBase connectivity resources for the Application Server, you see the following nodes:

JDBC Connection Pools: PointbasePool

JDBC DataSources: jdbc/jdbc-pointbase

Persistent Managers: jdo/PointbasePM

Segment of Runtime window showing the three registered JDBC nodes.

To Set PointBase JDBC Resources for the Examples

If you need to set the PointBase JDBC resources in the application server environment, use this procedure.

1. In the IDE's Runtime window, expand the Server Registry node and expand the Installed Servers node.

2. Locate your application server instance.

It is labeled app-server-name (app-server-host:app-server-port), for example, server1 (localhost:59161).

3. Right-click the application server instance node and choose Preconfigure PointBase JDBC Resources from the contextual menu.

A timer icon (such as an hour glass) appears. When the process is complete, the cursor reverts to its usual icon.

4. Expand the registered resources for JDBC Connection Pools, JDBC Data Sources, and Persistent Managers.

You should see the following nodes:

JDBC Connection Pools: PointbasePool

JDBC DataSources: jdbc/jdbc-pointbase

Persistent Managers: jdo/PointbasePM

Segment of Runtime window showing the three registered nodes.

To Use an External PointBase Installation With the IDE

If you have a PointBase installation external to the IDE, perform the following procedure to connect the IDE to that external PointBase installation. If you do not have access to a PointBase installation, you must install PointBase before using Java Studio Enterprise examples and tutorials. See the Sun ONE Application Server 7 Getting Started Guide for instructions on how to install PointBase.

1. Choose Tools > PointBase Network Server > Configure.

2. Use the Browse button to navigate to the PointBase installation directory.

3. Specify a different port number if necessary.

4. Click OK.

When asked whether to overwrite files sample.dbn and sample$1.wal, click No.

Using Other JDBC Enabled Databases

Sun Java Studio Enterprise 7 2004Q4 software installs and registers several other commonly used database drivers. The following are installed and appear under the Databases Explorer Drivers node:

Oracle database driver

Sybase database driver

Microsoft SQL database driver

IBM's DB2 database driver

To use one of these installed database drivers from the IDE, follow this procedure.

Using Database Drivers Installed by Java Studio Enterprise

1. In the IDE's Runtime window, expand the Databases node.

2. Expand the Drivers node and select the driver you want to use.

3. Right-click the selected driver and choose Connect Using from the contextual menu.

The New Database Connection dialog is displayed. Ensure that the correct driver is selected in the driver field.

4. Enter the JDBC URL in the Database URL field.

This URL typically begins with jdbc: and a short name for the driver, followed by another colon. The rest of the URL depends on the type of driver you are using. For example, you might need to specify a host and port. Alternatively, the name of a database file might suffice, or an ODBC DSN. To help you get started, existing drivers are provided with templates for the correct URL format. For example:

IBM's DB2 - jdbc:sun:db2://servername:portnumber;databaseName=DATABASENAME

Microsoft SQL Server - jdbc:sun:sqlserver://server_name:[portNumber]

Oracle- jdbc:sun:oracle://server_name[:portNumber][;SID=DATABASENAME]

Sybase - jdbc:sun:sybase://server_name[:portNumber]

Parameters in square brackets are optional. If in doubt, refer to the documentation for the driver you are using.

5. Provide the user name and password, if required by your database configuration.

6. Click the Remember Password checkbox to have the IDE remember your password.

The password is saved only for the current the IDE session. After restarting the IDE, you must type the password again. This feature can be turned on or off in the properties of each connected database in the Runtime window.

7. Click OK to connect the IDE to the database.

If the connection is successful, a new node appears on the same level as Drivers. The icon for a connected database shows complete. If the connection attempt fails, the IDE displays a dialog box indicating the connection is broken.

Using Database Drivers Not Installed by Java Studio Enterprise

To use database drivers other than those installed by Java Studio Enterprise from the IDE, follow this procedure.

1. In the IDE's Runtime window, expand the Databases node.

2. Right-click the Drivers node and choose Add Driver from the contextual menu.

The Add JDBC Driver dialog box is displayed.

3. Click the Add button to navigate to the directory where the driver .jar or .zip file is available and add the .jar or .zip file.

If the driver needs more than one .jar or .zip file, repeat this procedure to add all the required files.

4. Specify the Driver class for the driver.

Refer to the driver documentation for the driver class name.

5. Give this installation a Name and click OK.

A node representing this driver is displayed under the Drivers node.