Queries

Introduction

The purpose of this section is to explain the concept of query objects, as used in the Wizard.

A query is meant provide the same kind of functionality as a database view. The wizard breaks down a query into columns. The nodes on the left show a database query and its associated columns. Each node in the tree has a tab associated with it, which can be used to view, and alter the information displayed.

Creating A Query

Please ensure that the Wizard is connected to the database, and that database scanning is on.

When a "Queries" node is selected in the tree, a "Queries View" tab appears in the right pane of the wizard. Below is an example.

• Enter your SQL select, without any JDBC ? parameters. Parameters are added in later, when creating a query class.
• Make sure that your query is a select. The code generator depends on this.
• The database objects your query refers to should be fully qualified to include the schema and/or database name.
• If your query performs some database updates too (not advised), please make sure your driver can cope with rollback.

When the create button is pressed, the query is executed, this validates it.

Editing A Query

Once a query is created, it is possible to change the SQL, changes appear in the expanded tree. New columns, however, just appear, they do not have special markers.

• Deleted columns need to be manually deleted (select the column in the tree, then press the delete button in the tab displayed).
• Changed columns may need to be synchronized: select the column in the tree, then press the synchronize button.

Once a query has been changed, and the columns updated to reflect this, all the classes should be checked. This ensures they pick up column changes. All class methods should also be checked to ensure parameters do not refer to old columns.

Creating Query Classes

When the "JDBC2 Classes" node is clicked, the tab below is displayed. This tab allows a new Query class to be created.

The Database class generated contains the methods which use JDBC to select the data, and to navigate the ResultSet.

The Data class generated contains data for one ResultSet.

If the Serializable data class checkbox is checked, the generated data class implements java.io.Serializable.

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 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. You may elect to use scrolling resultset methods too.

Be sure to call close() in the generated JDBC class. In JDBC2, prepared statements can be reused. Hence the wizard does not close them automatically.

Editing A Query Class

When a specific query node is selected in the tree, the following set of tabs is displayed:

Class Info Tab

Once a class has been created, we can drill down the tree to its node. Selecting a class' node brings up a tabbed pane, whose first tab is illustrated below

The class information tab allows us to change general class information.

• Enable code generation automatically selects this class for code generation. We could deselect this checkbox, if for some reason the class should not be generated at the moment.
• The database class name allows the name of the database access class to be changed. indicates that some javadoc has been entered for this class description.
• The database package allows the database access class' package name to be specified. indicates that no javadoc has been entered for this class description.
• The data class name can be changed to a more suitable name, if wanted.
• The data class' package name can be specified here.
• The data class is generated with a copy constructor if this box is checked. A copy constructor is useful if you intend to extend the data class.

  A copy constructor is used to create a copy of a class. For example:

  AClass aClass = new AClass();
  aClass.setName("One");
  AClass cClass = new AClass(aClass);
  aClass.setName("Two");
  System.out.println(aClass.getName() + " " + cClass.getName());

  The output is "Two One"

  After cClass is made from aClass, any changes to aClass do not change the contents of cClass. In effect cClass is a snapshot copy of aClass.

• If the Serializable checkbox is checked, the generated data class implements java.io.Serializable.
• If any changes were made to this tab, the SAVE button should be pressed.

Now we should review the table columns the classes are to encapsulate. For example, which are used to get data, insert row and update row calls on the query's resultset.

Columns Tab, Data Class

The columns tab shows all the columns the database object has to offer. In effect it allows us to see which columns are mapped to java properties of the generated data class, and what SQL operations we wish to perform on that column.

JDBC2 allows resultsets to be updatable. The wizard allows inividual columns to be earmarked for Select, Update, and Insert. The code generated in the database class can have methods to select, insert and update the ResultSet (JDBC2Query Settings tab sets the concurrency to read only or read/write). Usually the query must only select from one table for this to work.

Columns which have an access of  "--- None ---" will not be present in the data class.

By double clicking on any cell, a property editor dialog for that data class member pops up.

The Database Class

The following SQL methods have been created:

SQL Method In Detail

When a SQL Method is selected, and the "Edit..." button is pressed, a method edtior pops up.

The method is given a name. This name will generate a method to execute the query in the database class.

The SQL is completed here. Notice how we have completed the SQL here by entering "AND EMPNO = ?".

Parameters are added, one per the "?" present in the SQL, and in the same order. The parameter editor appears below.

Typically, you would provide a useful name (the default is not very useful). Then select the dbms type of the parameter. At this point all other combos reflect the default settings for this data type. The precision and scale should be entered. And that should be it.

• The name of the parameter is entered. This name is used when generating the JDBC access class for you.
• "Set Fields From" lists all the properties of the data class. Selecting an item in this combo sets the other fields in this dialog to those of the selected data class property.
• The database type of the parameter is selected.
• The scale and precision are not used at present, but should be entered, so that future versions of the JDatabase wizard can make use of this.

  The precision is the total number of digits for numbers and characters for strings.

  The scale is only useful for decimal numbers. It specifies how many decimal digits the number can have.

• The Java type is the parameter's data type you will use when calling the generated method.
• The get/set op is the operation that the code generator will use to set the parameter in the PreparedStatement.
• The Set NULL type is the java.sql.Types constant to use, should the passed parameter value be null.
• Press the OK button when all details are correct.

JDBC2 Query Settings

By default the generated code is read only on the resultset. That is to say, no insert, update or delete methods are generated.

• Select Statement Concurrency specifies the generated PreparedStatements' concurrency, and whether resultset insert/update and delete operations are allowed.
• The Scroll Type specifies the generated PreparedStatements' scroll type.
• finalize() can be generated to close all the statements in the database class. As stated previously, your code should really make sure that it closes statements (close() closes all open statements). This option is only provided as a last resort, the JVM may take a long time before finalizing the class.

Advanced Settings

The advanced settings tab provides additional control over the generated classes. The wizard generates two classes.

• The database class performs the calls to JDBC. It returns data class objects (from the next() method), or uses data class objects as parameters (to insert/update data).
• The data class is a data bean, representing the data for one database row.

For the data class the following settings are available:

• Data class superclass is used to generate DataClass extends package.superclass.
• Data class subclass is used by the database class next(). the subclass extends the data class. When supplying this class, the constructor of your subclass should be without parameters: new package.subclass() should compile.

For the database class, the following settings are available:

• Database Class Superclass is used to generate DatabaseClass extends package.superclass.
• Generate next() method controls the data type returned by the next() method of the database class. It can be Object, the Data Class type, the Data Class subclass type, or the data class superclass type.

Using The Generated Code

Using query classes is pretty much the same as other JDBC2 classes generated by the wizard. The only difference being that no insert/update or delete methods are available.

Insert/update and delete is only possible using the generated resultset methods: insertRow(), updateRow() and deleteRow().

Using JDBC2 classes is described here.