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..
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.
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.
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).
Create a new table with the following details:
CREATE TABLE "J3_DEMO"."PERSON"("EMAIL"
VARCHAR2(50) NOT NULL,
"FIRST_NAME" VARCHAR2(50), "LAST_NAME" VARCHAR2(50)
PRIMARY KEY("EMAIL"), UNIQUE("EMAIL"))
Microsoft SQL Server
CREATE TABLE [dbo].[Person] (
[Email] [varchar] (50) NOT NULL ,
[FirstName] [varchar] (50) NULL ,
[LastName] [varchar] (50) NULL ,
[Title] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
CREATE TABLE Person (
Email VARCHAR(50) NOT NULL,
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 )
EMAIL_OUT := EMAIL_IN;
LAST_NAME := 'MELASTNAME';
FIRST_NAME := 'MEFIRSTNAME';
Microsoft SQL Server
CREATE PROCEDURE spParm
@FirstName varchar(50) OUTPUT,
@LastName varchar(50) OUTPUT,
@EmailOut varchar(50) output
ASselect @FirstName = FirstName, @LastName = LastName, @EmailOut = Email from Person where Email = @EmailIn return 10
The MySQL version used (3.23.49) does not support stored procedures.
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).
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.
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
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
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.
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.
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.
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.
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.
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.
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.
We added a parameter with the following settings:
Note the parameter added in the snapshot above. The steps followed are the same as for the update method.
No additional settings are necessary for the insert method.
To use scrollable and updateable resultsets, the following settings are made. The SAVE button is pressed.
|To display the code generation options, the last entry in the tree on the left hand side of the window should be selected.|
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.
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.
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.
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.
The wizard generates two classes. A Data class and a 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