Getting Started With JDBC2 Classes

Introduction

This section walks through the process of using the Wizard for the first time.

A quick introduction to the wizard is given by explaining how to use the Wizard to generate, and use Java classes which encapsulate a database table.

If your intended JDBC driver is not JDBC2 compliant, or the JDBC-ODBC Bridge, please follow this section instead.

We have come accross a varying level of JDBC 2.0 support in drivers which claim to be JDBC 2.0: Some allow updateable resultset operations, others only allow read only operations on ResultSet objects..


Sample Application

The documentation download contains a sample applications. 3 versions are included: Oracle, Microsoft SQL Server and MySQL.

The sample application opens a window. The file menu allows demonstration database operations to be carried out. The window displays the database operations. Click here to see a sample of the demonstration's display.

Example Databases

The examples use a demonstration database. If you have a database administrator, please seek authorization and assistance to set up the demonstration databases.

Follow these steps to create the database on Oracle 8i, Microsoft SQL Server or MySQL. It should be possible to reproduce the examples using other databases, should you wish to.

1. Create A Database / Schema

On Oracle the we need to create a new schema/user called "J3_DEMO". The tables and stored procedures are in this schema.

On Microsoft SQL Server we need to create a database called "J3Demo", owner is dbo. The tables and stored procedures are in this database (also known as a catalog).

On MySQL we need to create a database called "J3Demo". The tables and stored procedures are in this database (also known as a catalog).

2. Create A Table

Create a new table with the following details:

Oracle

Microsoft SQL Server

MySQL

3. Create A Stored Procedure

Create a new stored procedure with the following details:

Oracle should look something like this:

CREATE PROCEDURE "J3_DEMO"."SPPARM" (  EMAIL_IN IN VARCHAR,  
FIRST_NAME OUT VARCHAR,
LAST_NAME OUT VARCHAR,
EMAIL_OUT OUT VARCHAR )
IS
BEGIN
EMAIL_OUT := EMAIL_IN;
LAST_NAME := 'MELASTNAME';
FIRST_NAME := 'MEFIRSTNAME';
END;

Microsoft SQL Server

CREATE PROCEDURE spParm
@EmailIn varchar(50),
@FirstName varchar(50) OUTPUT,
@LastName varchar(50) OUTPUT,
@EmailOut varchar(50) output
AS
 select
     @FirstName = FirstName,
     @LastName = LastName,
     @EmailOut = Email
     from Person
     where Email = @EmailIn
     return 10
   

MySQL

The MySQL version used (3.23.49) does not support stored procedures.


Example code

The example code and project files are included in the online documentation zip file.

The samples create a swing frame, connect to the database and delete/insert/update and select records. A report of what has been done is displayed in the frame. Click here to see the sample output

j3.Main is the class to run.

j3.mssql.JDBCHandler for MSSQL contains the code which uses the JDatabase wizard generated classes. You will need to alter this class to connect to your database server (driver class, url, username, password).

j3.oracle.JDBCHandler for oracle contains the code which uses the JDatabase wizard generated classes. You will need to alter this class to connect to your database server (driver class, url, username, password).

j3.mysql.JDBCHandler for MySQL contains the code which uses the JDatabase wizard generated classes. You will need to alter this class to connect to your database server (driver class, url, username, password).


Starting The Wizard

JDatabase Wizard needs JVM 1.3 (or JVM 1.4) on the machine where it is run. The JDBC driver(s) should be JDBC 2 compliant. The code generated can be configured to run using JDK 1.1, 1.2 or 1.3, and JDBC 1.0 or 2.0.

If  the JDK 1.3 is installed as your default JVM, double clicking on the J3Wizard jar file should start the wizard.

 The Wizard can be started manually, by launching com.j3.swingUI.Main in the Jar file, for example:

C:\temp>java -cp C:/temp/J3Wizard.jar com.j3.swingUI.Main

The above would be used if you needed to setup your JDBC driver classpath separately. The classpath to your preferred JDBC driver jar or folder can be set from within the Wizard.

Startup Parameters

JDatabase Wizard can take a project file as a parameter, for example:

C:\temp>java -cp C:/temp/J3Wizard.jar com.j3.swingUI.Main C:\temp\MYPROJECT.j3

When the above command is issued, JDatabase Wizard starts up, and attempts to open the project file MYPROJECT.j3


Creating A Project

This option is only available in the "Full Evaluation Version" or the "Fully Licensed" version of the product. Other versions create a temporary project file.

When first running the Wizard, a project needs to be created.

In the File menu select "Open/New Project...", and enter the filename you would like your new JDatabase Wizard project to use. The file should end with a .j3


Connecting To The Database

Once the project file is open, we can connect to the database, using any drivers. If working with the example project file, please ensure your driver is the same as the example project file's driver. Different vendors have been found to layout databases in different ways.

  1. If the "Current Profile" dropdown has your JDBC driver, select it.
  2. Check the "JDBC Driver Class" is correct.
  3. Check the "JDBC Database URL", you should at least have to replace "<Host Name>" with your database server's name.
  4. Press the "Edit" button, next to the JDBC Driver Classpath
  1. Enter the username and password to connect to the database.
  2. Press the "Connect And Save" button.
  3. Press the toolbar button

Review Type Maps

Once a project file is created, it is possible to adjust how database types map to ResultSet get and set methods.

In most cases, the defaults are OK. For MySQL using the MySQL Connector/J 3.0.0 Beta, all text mappings need adjusting, as the driver does not support getCharacterStream() or setCharacterStream(). Later versions of the driver may well support character streams.

Below text has been set to AsciiStream. This is only an example, other settings (eg String) may prove more efficient/advisable.

For each database type, if you make a change, press the SAVE button.

In all eventualities, please refer to your JDBC driver's documentation to see which operations are supported, and which ones are best.


Creating A Class

To create a class, we need to drill down the database tree, to select the database object for which a class is to be created.  Select its "JDBC2 Classes" node.

MSSQL Server:

Oracle:

MySQL:

It is worth noting that several Java classes can be created for a given database object. So we could have one class which encapsulates only the Email and LastName. And have another class which encapsulates every column in the Person table.

The database wizard will generate two classes. On class holds a table's data (data class). The "Database Class" is used to insert, update, delete and select data, using the data class to pass data.

The above panel allows class names and packages to be specified. Please change the fields to:

Press the "Create" Button.

Configuring Individual Column Access

Below is the columns' view of the class. Defaults can be changed from here by double clicking on any entry in the table. In most cases only the access for a few columns will need  their access changed to specify which columns to use in the SQL select, update, insert statements, and hence which ones appear in the generated data class.

Now we are ready to create JDBC methods to select, update, insert and delete records in the table.

Creating Methods To Select, Insert, Update And Delete Records

Below is a snapshot of an example SQL Methods tab:

From this tab we can specify new database access methods, delete them, or modify existing methods. The next section walks through the creation of a Select method.

Select Method

The JDBC class generated will have a method of the form void "selectByEmail(String theEmail)", in order to specify this the Select Statement Editor is used.

Update Method

We added a parameter with the following settings:

Delete Methods

Note the parameter added in the snapshot above. The steps followed are the same as for the update method.

Insert Method

No additional settings are necessary for the insert method.

JDBC2 Settings

To use scrollable and updateable resultsets, the following settings are made. The SAVE button is pressed.


Code Generation

To display the code generation options, the last entry in the tree on the left hand side of the window should be selected.

 

Selecting The Classes To Generate

With the Code Generation node selected, the following tab set is displayed:

The classes to generate can be set by selecting available classes and pressing the correct arrow button.

Specifying The Code Style

This tab allows the style of the code generated to be specified. If any changes are made to the fields, the Save button should be pressed.

Setting the file locations

The files can be generated using absolute file paths, or paths relative to the project file. The root folder can be selected by pressing the "..." button, or by entering the location in the textfield.

Generating The Code

To generate code, the "Generate Code" button needs to be pressed. A status is displayed to show what classes are currently being generated, and if any problems have occurred.


Using The Generated Code

The wizard generates two classes. A Data class and a Database class.

The Database class

This link has the generated javadoc for the example PersonSQL class. The sample file JDBCHandler.java makes use of the generated classes.

To insert a new record, the PersonData class is created, fields set, then insert() is called on the PersonSQL class. Update and delete methods work in a similar way.

To select one or more records, the selectAll() is called on the Person class. Once selectAll() is called, the class has a ResultSet instance. The PersonSQL class contains methods to manipulate the resultset, the names are very similar to JDBC2's ResultSet method names. There are slight differences in the return values of some methods: PersonSQL.next() returns the record's data, or null, if all records have been read. absolute(1) returns the first record's data, or null if there are no records.

It is a good idea to call close() on the Database class once it is not needed anymore, this allows the JDBC driver to release any resources held on the database (ResultSet and PreparedStatement).


Copyright © 2002 J3 Limited, all rights reserved