Using The generated JDBC1 Classes Code

This section starts by giving an overview of the generated code. The subsequent sections start by explaining how to use database table and view classes, then how to use stored procedure classes.

The wizard typically generates two classes. A Data class and a JDBC class.

• The Data class is used to pass individual column data between your application and the JDBC class.
• The JDBC class performs the database insert, update, delete, select or call for you. 
• The JDBC class is used to retrieve data from a select statement, or stored procedure resultset. One record at a time is retrieved by your application, using the next() method. The next() method returns a Data class instance to your application.
• The insert and update JDBC class methods take a Data class instance as a parameter. This parameter, supplied by your application, contains the data to insert or update the record.
• The delete method does not need to use a Data class instance.
• For stored procedures, the JDBC class contains the return value and any parameter values need to call the stored procedure (that is: Input, Output and Input/Output parameters).
• The stored procedure call does not need to use a Data class instance.

Table and View JDBC Classes are described here.

Stored procedure JDBC classes are described here.

Tables And Views

In order to make this section easier, a Customer table is taken as an example. .

The Data Class: CustomersData.java

The following columns were selected:

Note that the CustomerID column is only used in Select and Insert operations.

The data class is a very simple class. It has one member variable per column, if the column has been selected for Select, Update or Insert.

The values in the data class are accessed according the the JavaBean specification: using a get or set method. For example the CustomerID member is accessed using:

public String getCustomerID() ;

public void setCustomerID(String theCustomerID) throws NullPointerException;

Notice that the set throws a NullPointerException, this is because the DBMS has specified that this column is a NOT NULL column.

In addition to the bean get/set methods, a method to set all the members to null is also present:

/**
* Sets all properties to null
*/
public void clearProperties()

A copy constructor may have been generated, if this was selected.

The Database Class: Customers.java

The following SQL methods have been created:

The generated Database class is rather more complex, simply because it encapsulates all the necessary JDBC code and data conversion operations for you. However you do not need to concern yourself with the implementation, only how to use the class.

The class has methods generated for each select, update, insert and delete method created. 

Update, Insert and delete are treated in the same way: the method creates the PreparedStatement, sets the values, executes the statement, then closes the statement.

The select methods are treated slightly differently. Only one select method can be live at any one time. This is because you need to call next() to iterate over each record retrieved. Should another select method be called, the initial statement member and resultset are closed for you.

In JDBC it is important not to leave open statements and resultsets lying around. To this end two methods are provided to close them if they exist and are open.

There now follows a description of the methods which have been generated in our example:

Delete

public void deleteByCustomerID(Connection theConnection, String theCustomerID) throws SQLException

This method should be called to delete records in the customer table. Notice the parameters. It takes a JDBC Connection and the CustomerID String used in the SQL delete.

Insert

public void insertCustomer(Connection theConnection, CustomersData theInsertData) throws SQLException

Inserts one customer record in the database. A JDBC connection is expected, along with an instance of the generated data class. Prior to calling this method, you should set the members of theInsertData to the values you would like the new database record to have.

Select

public void selectByCustomerID(Connection theConnection, String theCustomerID) throws SQLException

This method should be called to initiate an SQL select. After this method is called, we should call next() to iterate over the records.

public CustomersData next() throws SQLException

This method is used to iterate over records of the current select statement. It returns an instance of the generated data class. When there are no more records to retrieve, the method closes the current resultset, statement and then returns null.

public void close()

Closes any open select statement and resultset, but does not throw an exception. 

public void closeSelectStatement() throws SQLException

Closes the resultset and statement, should they be open, in addition it may throw an SQLException, though that would only happen if some system error had occurred.

Update

public void updateByCustomerID(Connection theConnection, CustomersData theUpdateData, String theCustomerID) throws SQLException

This method updates the database table. Notice that the parameters are a JDBC connection, an instance of the generated data class. The data class should hold the data needed to update the database. The last parameter is used to specify which records we wish to update.

Stored Procedures

A stored procedure is  used as an example.

The snapshot above shows the stored procedure:

• The stored procedure returns a value.
• The stored procedure expects one parameter.
• The stored procedure generates a ResulSet whose members are shown in the "Data Class" tab.
• The JDBC class to generate is called SpGetPersonByEmail

The Data Class

The stored procedure generates a ResultSet. Therefore a data class was generated by the Wizard to hold the ResultSet values.

The values in the data class are accessed according the the JavaBean specification: using a get or set method. For example the Title  member is accessed using:

public String getTitle()

public void setTitle(String theTitle)

In addition to the bean get/set methods, a method to set all the members to null is also present:

/**
* Sets all properties to null
*/
public void clearProperties()

A copy constructor may have been generated, if this was selected.

The Database Class

The Database class contains properties for the stored procedure's parameters, return value.

Methods are generated to call the stored procedure, iterate over Results, and to release any JDBC resources held by the class.

Parameters

The Database class is used to hold the stored procedure parameter(s) and the return value. The properties in the Database class are accessed according the the JavaBean specification: using a get or set method. For example the Email member is accessed using:

public String getEmail()

public void setEmail(String theEmail)

In addition to the bean get/set methods, a method to set all the members to null is also present:

/**
* Sets all properties to null
*/
public void clearProperties()

Methods

Methods are generated to call the stored procedure, iterate over the result set and to close any JDBC resources used by this class.

In our example we have specified the method to call in order to execute the stored procedure.

Call

The wizard generates the following method your application must invoke to call the stored procedure:

public void callSpGetPersonByEmail(Connection theConnection) throws SQLException

If the stored procedure does not produce a ResultSet, this is all there is to calling stored procedures. After calling the method above, any return value and output parameter values are present and correct in the JDBC class.

The JDBC reference states that stored procedure out parameters should be retrieved after all results have been retrieved.

Results

Should the stored procedure generate results, the methods described here are present.

If the stored procedure produces a ResultSet. The wizard generates the following methods to deal with the results:

public SpGetPersonByEmailData next() throws SQLException

This method is used to iterate over the results. After the last result has been retrieved, the next call to this method causes the stored procedure return value, and any output parameter values to be retrieved.

public void closeCall() throws SQLException

Closes any open ResultSet and CallableStatetment objects. This may method may throw an SQLException, if any JDBC calls it makes throw an Exception.

public void close()

Closes any open ResultSet and CallableStatement. This method catches any JDBC exception, hence close() does not throw any exceptions.

Copyright © 2002 J3 Limited, all rights reserved.