Build a Low Overhead Test Bed for Your JDBC Code

here’s a good chance that your enterprise applications interact with a database. Testing your application code against a database is important in making sure that your application works as expected. And while there are certainly plenty of ways to undertake that kind of testing, IBM offers one method that’s relatively easy. Unbeknownst to many, Rational Application Developer Version 6, an Eclipse-based IDE from IBM, ships with Cloudscape, a full featured database server with a small footprint.

Cloudscape got a lot of attention recently when IBM opened its source as Apache Derby. Cloudscape is ideal for testing your JDBC code as you’ll learn in this article. And while some purists?those who swear by command line, vi editor development?might beg to differ, the majority of developers tend to agree that use of an IDE can catalyze your development effort. If you are already familiar with the Eclipse IDE, then getting up and running with Rational Application Developer won’t be that big of a leap. You can download a trial version of Rational Application Developer 6 from http://www-128.ibm.com/developerworks/downloads/r/rad/. For this article, I will assume that you have some basic familiarity with Rational Application Developer. In addition to learning how to test your J2EE applications using Cloudscape, I will show you how to use a number of relational database tools innate in Rational Application Developer.

Creating a Cloudscape Database Sandbox
I’ll start off by showing you how to create a simple database and some underlying tables in Cloudscape via Rational Application Developer’s Data Perspective. In Application Developer, switch to the Data Perspective by going to Window> Open Perspective > Other… From the subsequent Select Perspective window, choose the “Data Perspective.”

From the Data Explorer view, right click and choose ‘New Connection’ from the context menu. You will be presented with a New Database Connection wizard. Specify a Connection Name. I specified CloudeSpaceCon. Also, specify that you want to “Choose a database manager and JDBC driver” and click Next.

Figure 1. Setting Up: There are quite a few parameters to enter on the New Database Connection screen. You can leave the user name and password fields blank for this sample application.

From the next wizard screen, in the ‘Select a database manager’ pane, make sure that a database manager type of Cloudscape V5.1 is chosen. Also, make sure that the JDBC driver of “Cloudscape Embedded JDBC driver” is specified. You also need to specify a disk location where your database will be located. I specified a location of c: empmytestdb (see Figure 1). Also, be sure the “Create the database if required” checkbox is checked. This instructs Rational Application Developer to create your database for you.

For an embedded Cloudscape database interaction, you can leave the User ID and Password fields in the ‘Specify user information’ section blank. You can test your connection to the Cloudscape database by clicking the Test Connection button. Application Developer will create the database on your behalf.

If everything went well in creating your Cloudscape database, you should receive a successful connection message. Go ahead and click Finish on that dialog. When asked to copy the database metadata to the project folder, select No. At this point, you should see an entry for CloudscapeCon in your Database Explorer view.

Creating a Database Table
Before creating your first table, create a Java Project in the Java perspective. To get to the Java Perspective, go to Window> Open Perspective> Java. Call your project TestProject.

Next, in the Data perspective’s Data Definition View, right click on the TestProject and from the context menu choose New>Database Definition. In the following Database Definition screen, specify a database name (I called mine TestDB) and specify a database vendor type of Cloudscape 5.1 (see Figure 2). After doing so, click Finish.


Figure 2. Defined: You need to create a definition of the database that is associated with the TestProject.
 
Figure 3. Name and Number: Create a simple table that includes social security numbers stored as integers.

Expand the TestProject project you just created and under it you should see an icon for TESTDB. Right-click the icon and choose New>Schema Definition. In the subsequent Schema Definition screen, specify a Schema name of MySchema and Click Finish.

Next, go back to the Data Definition view and expand the MySchema item you just created. Under MySchema item, you should see a Tables folder. Right-click on the Tables folder and select New>Table Definition.

In the Table Definition screen that follows, specify a table name of MyTable and click Next. To add columns to the database table, click the ‘Add Another’ button in the Table Columns screen that follows.

For illustration purposes, I’ll create a very simple table that holds social security numbers as well as the last name and first name of the social security number holder. To do this I need to specify a column name of SSN with a column type of INTEGER. Check the Key column checkmark to specify the column will be the primary key (see Figure 3).

Similarly, use the Add Another button to add columns for the LASTNAME and FIRSTNAME. For these columns, specify a column type of VARCHAR with a String length of 25. When you have created all three columns, click Finish.

In order for the creation of the schema and table to take effect, you need to deploy the changes to the database. Go ahead and right-click the TESTDB icon in the Data Definition view. From the context menu, choose the Deploy option.

From the subsequent Deploy wizard, click Next, bringing you to the Data Export Options screen. Choose the “Commit changes only upon success” option as well as the “Generate fully qualified names” option and click Next (see Figure 4).


Figure 4. Deploy: Specify your preferences for how you want the database to handle committing changes and naming.
 
Figure 5. Use Existing: Tell the application to connect to the database using the existing “CloudscapeCon” connection.

In the next database connection screen, make sure the “Use existing connection” checkbox is checked and that the existing connection of “CloudscapeCon” (i.e., the database connection you established earlier) is selected. Then click Finish (see Figure 5).

Seeding Your Database with Some Sample Data
The Data Definition view can be used to populate records into the database table you just created. Right-click on the Statements folder and select New>Insert Statement (see Figure 6). You will be asked to specify a name for your statement. Call your statement ‘PopulateTable’.


Figure 6. Insert Statement: Use the Data Definition view in Application Developer to tell the application how to populate data from records.
 
Figure 7. Add Table: Make sure to select the proper table name for your application from the dialog that appears after hitting the ‘Add Table’ button; then click OK.

In the view that appears next, right-click in the Tables area and select Add Table (see Figure 7). Make sure the table name of MYSCHEMA.MYTABLE is selected in the “Add Table” window that follows and click OK.

Next, in the Tables area, click the SSN, LASTNAME, and FIRSTNAME columns. Go ahead and use the wizard to specify values for the columns. I specified 111111111 for the SSN, ‘Washington’ for the LASTNAME, and ‘George’ for the FIRSTNAME column (see Figure 8).


Figure 8. Populate Table: Create a record with SSN, last name, and first name that will be used to populate your database.
 
Figure 9. Success: When your INSERT statements executes properly, Application Developer lets you know by displaying a Success message in the DB Output view.

You can view your associated DDL for your insert query in the SQL source area. You should see:

INSERT INTO    MYSCHEMA.MYTABLE   (      SSN,       LASTNAME,       FIRSTNAME   )   VALUES    (      111111111,       'Washington',       'George'   )

Press Ctrl+S for the statement you just created to be saved. To issue the SQL statement against the database, right-click on the on the PopulateTable statement under the Statements folder in the Data Definition view and select the Execute context option.

If the INSERT statement executes properly, you should see a successful message shown in the DB Output view (see Figure 9).

Go ahead and issue another INSERT statement against the database as you just did for the following values:

SSN FIRSTNAME LASTNAME
111111112 John Adams

Accessing Your Cloudscape Database Via JDBC
Now that you have created your Cloudscape database, a housing schema, and a table?seeded with sample data?within your database, you’re ready to finish off by writing a simple Java application that accesses that database. However, before doing so, you need to import the requisite JDBC driver for accessing the Cloudscape database.

In the Java perspective, right-click on the TestProject project and choose Properties from the context menu. In the Properties window, choose ‘Java Build Path’ in the left-hand pane and in the right-hand pane choose the Libraries tab and then the Add External JARs button.

Navigate to your Rational Application Developer installation directory and under it navigate to the runtimesase_v6cloudscapelib directory. In Windows the default installation directory would be:

C:Program FilesIBMRationalSDP6.0
untimesase_v6cloudscapelib

After navigating to the lib directory, select the file: db2j.jar and click Open. After adding the file db2j.jar to your build path, choose the OK button (see Figure 10).


Figure 10. Build: In preparation for testing your application, add the proper JAR file to your Java build path.
 
Figure 11. Working App: Testing the completed sample application outputs the data to the Console View.

At this point you are ready to create a new Java class. Creating classes in Rational Application Developer is outside the scope of this article. Instead I’ll concentrate on analyzing an existing class that accesses Cloudscape via JDBC. The class named TestClass is shown below:

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestClass {  public static void main(String[] args) {    try {      System.out.println("Testing Cloudscape DB connection");      Class.forName("com.ibm.db2j.jdbc.DB2jDriver");      String url = "jdbc:db2j:c://temp//mytestdb;create=false";      Connection cn =         DriverManager.getConnection(url,"","");      Statement st =         cn.createStatement();      ResultSet rs =         st.executeQuery("SELECT * FROM MYSCHEMA.MYTABLE");      System.out.println("SSN: LASTNAME, FIRSTNAME");      while (rs.next())      {        System.out.println(rs.getInt("SSN") + ": "         + rs.getString("LASTNAME") + ", "        + rs.getString("FIRSTNAME"));      } catch (ClassNotFoundException e) {      e.printStackTrace();    } catch (SQLException e) {      e.printStackTrace();   }  }}

Most of the code above should be quite familiar to you. Of particular importance and relevance to the Cloudscape database connection is the loading of the database driver via the line below. (Notice the use of the driver: com.ibm.db2j.jdbc.DB2jDriver, which is particular to connecting to Cloudscape.)

Class.forName("com.ibm.db2j.jdbc.DB2jDriver");

Next I need to specify the URL for connecting to my database:

String url = "jdbc:db2j:c://temp//mytestdb;create=false";

The URL takes the form:

jdbc:db2j:;create=false

In this URL, ‘database path’ refers to the path you specified earlier when you created your database connection (in my case c://temp//mytestdb).

To obtain a connection to your database use the call:

Connection cn =         DriverManager.getConnection(url,"","");

In the syntax above I am using the java.sql.DriverManager object’s getConnection method, which takes in the database URL I established earlier as my first argument followed by the user name and password. Rather than specify a user name and password (they are not required) to connect to the embedded Cloudscape database, I’ve provided my method with the value “” for both the user name and password.

Finally, I issue a SELECT statement against the database, namely:

SELECT * FROM MYSCHEMA.MYTABLE

This SELECT statement will retrieve all of the data from the MYSCHEMA.MYTABLE table.

At the end of the Java class, I cycle through the java.sql.ResultSet object using a while loop and print out my table contents:

while (rs.next()){     System.out.println(rs.getInt("SSN") + ": "      + rs.getString("LASTNAME") + ", "     + rs.getString("FIRSTNAME"));}

Finally you’re ready to give the application a test run. The output of the test run is shown in the Console View in Figure 11.

In this article, you learned how you can leverage the Cloudscape database (which ships with IBM Rational Application Developer Version 6.0) to act as a small footprint database that can test your JDBC code.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: