Databases And Types

Introduction

There are many vendors offering databases. Each dbms usually has some proprietary data types.

The JDBC driver can enumerate a dbms' data types, and propose which Java data types should be used for a given dbms type.

Some tables in databases may be of one type (VARCHAR for example), but are known to hold a more specific kind of data (NUMERIC for example).

The wizard generates Java code to interact with the database. In order for the wizard to be able to decide which Java types to use for each database entity type, the wizard uses a type map.

The JDatabase wizard is equipped to deal with these situations. This section describes how the JDatabase wizard can be used to finely control which Java data types to use for every object in the database, or for individual columns of a table.

The code generated will contain data conversion routines where necessary.

Database Tab

The database tab is the first tab seen when flipping to the database view.

In most circumstances, it is not necessary to change information on this tab. One exception we have come across is when using Microsoft Access databases (via the JDBC-ODBC bridge). We have disabled catalogs, because the database does not typically use catalog names in SQL queries.

The product name displays the name of the database product, as provided by the JDBC driver.
The product version displays the version of the database product, as reported by the JDBC driver.
The catalog term reports what term the database product uses to refer to database catalogs. A catalog is a JDBC term. Not all databases support catalogs.
The schema term reports what term the database product uses to refer to database schemas. A schema is a JDBC term. Not all databases support schemas. The above snapshot shows that Microsoft Access does not know of database schemas.
The identifier quote string is used in database queries. For example, should a table be called Big Customers, a select statement needs to put the table name in quotes to avoid the problem a space character can cause: select * from "Big Customers"
The catalog separator defines what character is used when using catalog names in queries. For example select * from Catalog.Schema.TableName The catalog separator is the ',' between the catalog and the schema.
Supports stored procedures specifies whether the DBMS supports stored procedures.
Uses schemas in queries is used for generated SQL: if you would like schemas to precede tables names in select statements, this option should be selected. For example, on Oracle databases, the generated SQL would be of the form "select * from SCOTT.EMP"
Uses blank schemas in queries. This option is used if your dbms supports schemas and catalogs. If you wish to use blank schemas in the generated SQL, select this option. For example MS SQL server 6.5 and 7.0 should set this option to getenrate select statements of the form: "select * from catalog..table".
Uses no schemas in queries causes the generated SQL not to contain schemas. For example, on Oracle databases, if you are connected as user scott, you may wish to select this option to generate SQL of the form "select * from EMP".
Uses catalogs in queries controls whether the wizard uses catalog names in SQL queries.
Catalog starts query is used if the database has schemas and catalogs. Either the schema appears first in SQL queries, or the catalog appears first.
Should any changes have been made to the tab, the save button must be pressed to save the changes.

Database Type Map

The database type map tab allows the user to override the default data mappings used when a new class is created. The wizard uses the type map to determine which conversion, if any needs to be generated in the source code.

Type Map Controls:

Use the "Type Name" combo control to select the database type
The "Get/Set Op." combo selects the ResultSet get/set operation for this type.
Using the "Procedure Get" combo, select the correct stored procedure get for parameters. This combo is necessary, because some ResultSet gets do not exist in the CallableStatement interface (for example getUnicodeStream is not available in the CallableStatement interface).
PreparedStatement setNull: when setting a column to NULL, JDBC requires a java.sql.Types constant to be specified. This combo allows the type to be set.
CallableStatement type: parameters need OUTPUT parameters and the NULL to be assigned a type. This combo box allows a default java.sql.Types constant to be specified.
The Java type is the type you would like to work with in your Java application. The wizard generates the code necessary to convert the data read in the JDBC call to the java type you select here.
By default types are Select, Insert and Update. This means columns of this type can be selected, inserted and updated. It is possible that some database types should be selected only (automatically incremented data types), or inserted but not updated, or not used at all. Complete control over the default operations is here. Please note that automatically updated data types, where detected by the wizard are set to select only, this can be changed here, should you disagree with this policy.
Once the currently viewed database type's mapping has been altered, the SAVE button should be pressed to save the changes for the currently displayed database type.

Database Entity Type Maps

As stated in the introduction, some database objects may be specified as of one type, when the data they contain is of another type. The JDatabase wizard can be made to convert between types for a given database object by using the class view tab.

We use the Property Editor to tell the JDatabase wizard what the "Property Type" is, what ResultSet.get...() and set to call. The database JDBC type is salso specified here.

The code generated will attempt to convert data types between the "get set op" and the "property type", should they be diferent.

The sql access (select, insert, update) can be selected here.

The advanced tab allows server side expressions to be entered for insert and update operations. The column access should include insert/update for the settings below to take effect.

Please note that no question mark (?) character may be entered here.

The expressions can be used when the database does not allow default column values to be set.

JDBC Types Supported

Version 1.0 of the JDatabase wizard supports the following JDBC types.

BIGINT
BINARY
BIT
CHAR
DATE
DECIMAL
DOUBLE
FLOAT
INTEGER
LONGVARBINARY
LONGVARCHAR
NUMERIC
OTHER
REAL
SMALLINT
TIME
TIMESTAMP
TINYINT
VARBINARY
VARCHAR

Generated Names

When creating a database class within the wizard, data class properties are created. For example in a PERSON table, FIRST_NAME, LAST_NAME, table columns have a corresponding property created for them.

When a class is created, the wizard can apply java styling to the column names. This is usually a good idea. Database objects names do not always make good java class property names. However, sometimes this may not be the preferred option.

When checked, the checkbox causes all new classes to use java styling. This means that data class properties fit in with the java naming conventions. For example a FIRST_NAME column becomes a FirstName data class property. If the checkbox is not checked, any new generated data classes will use FIRST_NAME as the java class property name.

This option only affects new classes created in the wizard. Any existing classes in the wizard retain their original property names.