-C H A P T E R

C H A P T E R 7

Tutorial--Section 2.1
Prepare Application to Access SQL Database

This chapter describes how to expand the application and prepare the Sun ONE Application Framework application to access a SQL Database.

Expand the existing application by adding a SQL-based model and a page to display that model's data.

Link the two application pages together so they show coordinated data.

Task 1: Accessing a SQL Database

Connect to the Sample Database

Note - The remainder of the tutorial assumes the presence of an RDBMS database which is used as a prerequisite for introducing you to some additional Sun ONE Application Framework features.

There is no requirement for an Sun ONE Application Framework application to access an RDBMS. Therefore, your actual applications might not access an RDBMS, but rather some other enterprise system that requires another form of preparation, setup, and connection.

The step that follows (starting the PointBase Network Server) uses a Sun ONE Studio tool that is not actually a part of the Sun ONE Application Framework toolset module. However, the sample database, the PointBase Network Server, and the tools to connect to it are included with all of the various versions of the Sun ONE Studio.

1. Select the menu option Tools -> PointBase Network Server -> Start Server from the Sun ONE Studio to start the PointBase Network Server (database server).

This figure shows the Tools -> PointBase Network Server -> Start Server menu options.

Caution - Depending upon how you installed the Sun ONE Studio and the Sun ONE Application Server, you might get an error message that prohibits you from starting the PointBase Network Server. If this happens, you just need to configure it first. If you were able to start the server, skip to the JDBC Datasources section.

2. Select Tools -> PointBase Network Server -> Configure.

This figure shows the Tools ->PointBase Network Server -> Configure menu options.

A dialog displays that prompts for a file storage location and a port number.

3. Accept the defaults by clicking OK.

This figure shows the Configure PointBase dialog prompting for a file storage location, and showing the Server port number.

You receive two dialogs warning that a file already exists, and asking if it is OK to overwrite them.

4. Click OK to overwrite both of them.

Your PointBase Network Server is ready to be started.

JDBC Datasources

You can create a JDBC Datasource using the Sun ONE Application Framework JDBC Datasource wizard.

However, by default, one was created for you that points to the PointBase sample database that ships with the Sun ONE Studio.

If you need to create additional JDBC Datasources for a different database other than the one used in the tutorial, use the following steps.

(Otherwise, read over to become familiar with this topic, or skip to the Tomcat (and other non-JNDI containers) SQL Connection Preparation section.)

1. Under the Sun ONE Application Framework Web application node (Jato Tutorial), expand the Settings & Configuration folder.

2. Expand the Design-Time Resources folder.

3. Right-click the JDBC Datasources node.

4. Select Add JDBC Dataource.

This figure shows the Sun ONE Application Framework Web application node (Jato Tutorial) and the Design-Time Resources -> JDBC Datasources node -> Add JDBC Datasource options.

The Define JDBC datasource panel displays.

This figure shows the Define datasource panel.

5. Enter the preferred datasource name in the New datasource name textbox.

6. In the Select connection combo box, select the appropriate JDBC connection.

If the connection you need does not exist, you must create one. This is performed by a tool that is outside the scope of the Sun ONE Application Framework tools. You need to select the Runtime tab in the Explorer window, expand the Databases node, right-click it, and select Add Connection. You might need to add a driver for your database before you can add a connection. See the Sun ONE Studio online help for more details.

This figure shows the Explorer window with the expanded Databases node, and the Add Connection option.

7. Click Finish.

A new JDBC Datasource node is created.

This figure shows the new JDBC Datasource node.

Note - JDBC Datasources are only needed at design-time when creating JDBC SQL Models (tables and stored procedures). The JDBC SQL Model wizard presents a selection of the datasources that have been created.

The JDBC Datasources are not involved in the runtime environment. You must configure your runtime container with the proper JNDI settings, unless you are using direct JDBC URLs to connect to databases.

Tomcat (and other non-JNDI containers) SQL Connection Preparation

Note - If you are using the Sun ONE Application Server to run your tutorial application, you can skip this step, because JNDI is supported.

If you are using the built-in Tomcat engine, or running the tutorial application in another servlet container that does not support JNDI, you need to make a few minor modifications to the application servlet base class (JatoTutorialAppServletBase) in your application.

1. Expand the Application Classes folder.

2. Expand the jatotutorial package folder.

3. Double-click the JatoTutorialAppServletBase class to open it.

There is a lot of commented-out event code in here with comments describing what you can do with the events. Ignore them as you do not need any of them for this tutorial application.

You need to add a static initializer to perform the following:

a. Instruct the Sun ONE Application Framework not to use JNDI lookups

b. Load the PointBase JDBC driver

c. Map the JDBC Datasource (jdbc/jdbc-pointbase) to the PointBase sample database's JDBC connection URL

The following code sample shows the code that needs to be added to the JatoTutorialAppServletBase class. Only the bold code needs to be added. Much of the code/comments from the JatoTutorialAppServletBase class has been omitted here.

public class JatoTutorialAppServletBase extends ApplicationServletBase

	static

		// Turn off JNDI lookup (turn on DriverManager use)

		SQLConnectionManagerBase.setUsingJNDI(false);

try

			// load the PointBase JDBC driver

			Class.forName("com.pointbase.jdbc.jdbcUniversalDriver");

		catch (ClassNotFoundException e)

			// if the driver is unavailable, an exception will be thrown

			e.printStackTrace();

	SQLConnectionManagerBase.addDataSourceMapping("jdbc/jdbc-pointbase",

														"jdbc:PointBase://localhost:9092/sample");

	} // static init

Your application will now use a JDBC URL directly to make a connection to the database instead of using the connection pooling via JNDI.

Important: If you value performance in your Web application, use JNDI for production.

If you are not going to be testing the tutorial in the Sun ONE Application Server, you need to copy the PointBase client library JAR file (pbserver42RE.jar) into your WEB-INF/lib directory.

You can get the PointBase client library from the following directory:

<studio-install-dir>/appserver7/pointbase/server/lib

If you are using a different database, you might need to place that database vendor's client library in your WEB-INF/lib directory or in your servlet container's lib/ext directory (somewhere in the classpath). Place it in the Web application's WEB-INF/lib directory.

This is not necessary for the Sun ONE Application Server because the PointBase libraries are already included in its classpath.