1. Overview

OneWebSQL™ for JDBC is a tool which generates OneWebSQL™ Java code directly from your database. It connects to the database, extracts information about the database structure, and generates Java classes representing database tables and views.

OneWebSQL™ for JDBC supports the following database engines:

2. How to perform reverse engineering

OneWebSQL™ for JDBC can be used as a stand-alone application or as a Maven plugin.

2.1. Stand-alone application

2.1.1. In Linux

To invoke OneWebSQL™ for JDBC as a stand-alone program in Linux, run the following command.

java -cp onewebsql-db2java-1.0.3.jar:PATH_TO_DRIVER_JAR com.onewebsql.db2java.DB2Java \
     -jdbc_url DATABASE_URL \
     -user USER \
     -password PASSWORD \
     -schema SCHEMA1 SCHEMA2 \
     -schema_package SCHEMA1 package1 -schema_package SCHEMA2 package2 \
     -java_output_dir target/generated-sources/onewebsql  \
     -java_default_package com.yourdomain.example_package \
     -dictionaries_dir example  \
     -jdbc_driver JDBC_DRIVER  \
     -onewebsql_runtime onewebsql-runtime-1.0.3.jar

The command-line options are discussed in detail in a subsequent section.

2.1.2. In Windows

To invoke OneWebSQL™ for JDBC as a stand-alone program in Windows, run the following command.

java -cp "onewebsql-db2java-1.0.3.jar;PATH_TO_DRIVER_JAR" com.onewebsql.db2java.DB2Java ^
   -jdbc_url DATABASE_URL ^
   -user USER ^ 
   -password PASSWORD ^
   -schema SCHEMA1 SCHEMA2 ^ 
   -schema_package SCHEMA1 package1 -schema_package SCHEMA2 package2 ^
   -java_output_dir target\generated-sources\onewebsql ^
   -java_default_package com.yourdomain.example_package ^
   -dictionaries_dir example ^
   -jdbc_driver JDBC_DRIVER ^
   -onewebsql_runtime onewebsql-runtime-1.0.3.jar

The command-line options are discussed in detail in the next section.

2.1.3. Command-line options

The stand-alone application takes the following options:

2.1.3.1. Schema map file

Instead of specifying the schema-package mapping in the command-line, you can define it in a file. The file is a Java properties file. The format is as follows:

schema.<schema-name>=<package-name>

Every key consists of a prefix schema., followed by the schema name. The value is the name of the package for tables or views in the schema.

2.2. Maven plugin

You can use the Maven plugin version of OneWebSQL™ for JDBC. First, add the plugin to the project's pom.xml file.

<plugin>
  <groupId>com.onewebsql</groupId>
  <artifactId>maven-onewebsql-db2java-plugin</artifactId>
  <version>1.0.3</version>
  ...
</plugin>

Add the database's JDBC driver jar as a dependency for the plugin.

<plugin>
  ...
  <dependencies>
    <dependency>
      <groupId>JDBC_DRIVER_GROUP_ID</groupId>
      <artifactId>JDBC_DRIVER_ARTIFACT_ID</artifactId>
      <version>JDBC_DRIVER_VERSION</version>
    </dependency>
  </dependencies>
  ...
</plugin>

Configure the generation process. An example of a configuration is shown below.

<plugin>
  ...
  <configuration>
    <jdbc_url>DATABASE_URL</jdbc_url>
    <connection_properties>
      <property>
        <name>user</name>
        <value>USER</value>
      </property>
      <property>
        <name>password</name>
        <value>PASSWORD</value>
      </property>
    </connection_properties>
    <schemas>
      <param>SCHEMA1</param>
      <param>SCHEMA2</param>
    </schemas>
    <schema_package>
      <SCHEMA1>package1</SCHEMA1>
      <SCHEMA2>package2</SCHEMA2>
    </schema_package>
    <java_default_package>com.yourdomain.example_package</java_default_package>
    <jdbc_driver>JDBC_DRIVER</jdbc_driver>
  </configuration>
</plugin>

Options that must be configured:

3. What information is read

3.1. Tables

For every table, OneWebSQL™ for JDBC reads:

3.2. Views

For every view, OneWebSQL™ for JDBC reads the:

You can define primary and alternative keys for a view in the model properties file. See the section "Customizing code generation with model properties file".

3.3. References

For every reference (foreign key) in a database, OneWebSQL™ for JDBC reads the:

Every reference can be:

By default, OneWebSQL™ sets the reference multiplicity to one-to-many. It sets the multiplicity to one-to-one if the foreign key has unique constraint as well, like in this diagram:

You can change the multiplicity OneWebSQL™ generates in model properties file. See the section "Customizing code generation with model properties file".

Note
Some databases engines (like MyISAM engine of MySQL database) do not store foreign key information. For those databases OneWebSQL™ for JDBC will not read the references.

3.3.1. Multiple references between two tables

If there are multiple references between two tables, you have to give names to roles in references.

OneWebSQL™ for JDBC uses generic role names (Role1, Role2, ...) for multiple references it finds. You can change the names in the model properties file (see section Customizing code generation with model properties file).

4. Customizing code generation with model properties file

Some information cannot be read from a database (such as view keys) and sometimes you may want to change the values returned by a database (for example Java class for a certain column). You can give the information to OneWebSQL™ for JDBC in a model properties file.

If the model properties file exists, OneWebSQL™ for JDBC reads properties defined in the file and modifies generated code accordingly. If the file does not exist, the OneWebSQL™ for JDBC generates a template for the model properties file. All properties in the file are commented. All you have to do is uncomment the property you're interested in and change its value.

The model properties file consists of three sections:

4.1. Tables

For every table you can modify the:

4.1.1. Java™ package

In the model properties file, you can change the Java package for a table. This will override the package set in the generation setup.

To set the package for a table public.book, in the model properties file add the property:

table.public.book.package=com.onewebsql.books

The general pattern is:

table.<table-name>.package=<package-name>

4.1.2. Sequences

Tables with a single-column integer primary key (i.e., a key which can be translated to the type java.lang.Byte, java.lang.Short, java.lang.Integer, or java.lang.Long) are handled in a special way. If the value of the key is not provided, then insert methods in the DAO will read the value of the key from a sequence. By default, the name of this sequence is <table_name>_seq. You may change the name of this sequence by setting the appropriate property.

To set the sequence for a table public.book in the model properties file, add the property:

table.public.book.sequence=book_sequence

The general pattern is:

table.<table-name>.sequence=<sequence-name>

4.1.3. Disabling auto-increment for the primary key

You can switch off auto-increment for the primary key value. To do this, set an appropriate property to false.

To switch off auto-increment for the primary key value for a table public.book, in the model properties file add the property:

table.public.book.generate_get_next_id=false

The general pattern is:

table.<table-name>.generate_get_next_id=true|false

4.2. Views

4.2.1. Primary keys in views

You can add a new primary key to the view in the model properties file. For example, to add to a view public.book_with_author a primary key which uses the column id, add the property:

view.public.book_with_author.key.pk=id

Alternately, to create in view public.book_with_author a multicolumn key which uses the id and author columns, add the property:

view.public.book_with_author.key.pk=id,author

The general syntax is:

view.<view-name>.key.pk=<list_of_columns>

4.2.2. Alternative keys in views

You can add a new alternative key to the view in the model properties file. For example, to add to view public.book and a new alternative key named 1 which uses the isbn column, add the property:

view.public.book.key.ak.1=isbn

Here is an additional example. If you want to create a multicolumn key which uses the id and author columns, type:

view.public.book.key.ak.2=id,author

The general syntax is:

view.<view-name>.key.ak.<key-name>=<list_of_columns>

Note
The names of all alternative keys of one view have to be different.

4.2.3. Java package

In the model properties file, you can change the Java package for a view. This will override the package set in the generation setup. To set the package for a view public.book_with_author, set the property:

view.public.book_with_author.package=com.onewebsql.books

The general pattern is

view.<view-name>.package=<package-name>

4.3. Columns

For every column, in either a table or a view, you can change the Java class and the JDBC type of the column. You can also hide column in the toString() method.

4.3.1. Change JDBC type for a column

OneWebSQL™ talks to a database using database's JDBC driver. To every column it assigns the JDBC type returned by the driver. You can change the JDBC type assigned to a column, like this:

table.public.book.id.jdbcType=VARCHAR

The general pattern is

table.<table-name>.<column-name>.jdbcType=<jdbc-type>

The <jdbc-type> is the name of java.sql.Types constant representing the desired type. Constants you can use:

4.3.2. Change Java class for a column

You can change the Java class which is to be assigned to a column. To change the Java class of a column id in table public.book, add the property:

table.public.book.id.column.class=java.lang.Boolean

The general pattern for table columns is:

table.<table-name>.<column-name>.column.class=<java-type>

The general pattern for view columns is:

view.<view-name>.<column-name>.column.class=<java-type>

The <java-type> value can be one of the following:

You must use the fully qualified name of the type, exactly as it is shown above.

The values are retrieved from the database by an appropriate ResultSet method, such as getBoolean(int) for the java.lang.Boolean type. Make sure that the corresponding ResultSet method works with an underlying database type.

4.3.3. Using Booleans in a database with no Boolean types

This feature is most useful when the database does not have Boolean datatypes (as in Oracle) but the attribute is a logical value. Set the column.class property to java.lang.Boolean.

table.public.book.id.column.class=java.lang.Boolean

Database values and their corresponding Java Boolean values are shown in the table below:

Database type Database value Java value
CHAR / VARCHAR "0" false
CHAR / VARCHAR "1" true
CHAR / VARCHAR other unspecified
BIT / TINYINT / SMALLINT/ INTEGER / BIGINT 0 false
BIT / TINYINT / SMALLINT/ INTEGER / BIGINT 1 true
BIT / TINYINT / SMALLINT/ INTEGER / BIGINT other unspecified

4.3.4. Hide column in toString() method

OneWebSQL™ generates a Java bean for every table and view in the database schema. By default the bean's toString() method prints all non-LOB columns. Sometimes this behavior is not desirable, most often for security reasons. For example, your column may contain the hash of a user's password. If you don't want a certain column to be used in the toString() method, then set the dont_generate_to_string property to true.

For column password in a table public.author, set the property:

table.public.author.password.dont_generate_to_string=true

The general syntax for a table column is

table.<table-name>.<column-name>.dont_generate_to_string=true|false

For column password in view public.book_with_author, set the property:

view.public.book_with_author.password.dont_generate_to_string=true

The general syntax for a view column is

view.<view-name>.<column-name>.dont_generate_to_string=true|false

4.4. References

4.4.1. Multiplicity of references

A reference in OneWebSQL™ can be:

Most references generated by OneWebSQL™ for JDBC are one-to-many. The one-to-one reference is generated only when the foreign key has unique constraint and the unique constraint is read from a database.

You may change the generated multiplicity by setting the multiplicity property. To set the multiplicity of reference between tables public.author, with primary key id and public.book, with foreign key author_id to 1..1, add the property:

reference.table1.public.author.id.table2.public.book.author_id.multiplicity=1..1

To set the multiplicity of reference between tables public.author, with primary key name, last_name and public.book, with foreign key author_name,author_last_name to 1..1, add the property:

reference.table1.public.author.name.last_name.table2.public.book.author_name.author_last_name.multiplicity=1..1

The general syntax is:

reference.table1.<parent-table-name>.<pk-columns>.table2.<child-table-name>.<fk-columns>.multiplicity=1..1|1..*

4.4.2. Self-references

It is possible that there are self-references in the database, that is, references from a table to itself. For example, you may have a category tree such as the one shown below:

Self-references work the same way as other relationships between tables. However, you must give names to the roles in the relationship. OneWebSQL™ for JDBC will generate some generic role names, but you will want to change them to meaningful names. Set the properties like this:

reference.table1.public.category.id.table2.public.category.parent_category_id.parentRole=parent_category
reference.table1.public.category.id.table2.public.category.parent_category_id.childRole=subcategory

The general syntax is:

reference.table1.<parent-table-name>.<pk-columns>.table2.<child-table-name>.<fk-columns>.parentRole=<role-name>
reference.table1.<parent-table-name>.<pk-columns>.table2.<child-table-name>.<fk-columns>.childRole=<role-name>

4.4.3. Multiple references between two tables

If you have more than one relationship between two tables, you must name the roles in the relationship. Roles are required for multiple relationships between tables. OneWebSQL™ for JDBC will generates some generic role names, but you will want to change them to meaningful names.

To set role name to reference between tables public.author, with primary key id and public.book, with foreign key author_id and to reference between tables public.author, with primary key id and public.book, with foreign key translator_id, add properties:

reference.table1.public.author.id.table2.public.book.author_id.parentRole=author
reference.table1.public.author.id.table2.public.book.author_id.childRole=authored_book
reference.table1.public.author.id.table2.public.book.translator_id.parentRole=translator
reference.table1.public.author.id.table2.public.book.translator_id.childRole=translated_book

The general syntax is:

reference.table1.<parent-table-name>.<pk-columns>.table2.<child-table-name>.<fk-columns>.parentRole=<role-name>
reference.table1.<parent-table-name>.<pk-columns>.table2.<child-table-name>.<fk-columns>.childRole=<role-name>

5. Supported databases

The supported databases are:

6. Known issues and limitations

6.1. MySQL

6.1.1. Foreign keys

Foreign keys can only be read for InnoDB tables. The MyISAM engine does not store foreign keys even if they are used in CREATE TABLE command and, thus, they cannot be read from the database.

The InnoDB foreign keys have to be created using the FOREIGN KEYS clause. If the foreign key is created using REFERENCES clause, it cannot be read.

6.1.2. Sequences

MySQL does not have sequences. You cannot read the name of the sequence associated with a table. OneWebSQL™ reads the id values from an auxiliary table tech_max_id. You have to create this table when you create the database structure. Here is some sample code which shows you how to create the table. The code can also be found in the distribution archive in the file sql/mysql-create-table-tech_max_id.sql.

create table tech_max_id (
 table_name varchar(255) primary key not null,
 next_id int not null
);

6.2. PostgreSQL

6.2.1. Sequences

OneWebSQL™ can read the name of the sequence associated with a table if the column is created as serial type. If the sequence is associated as default nextval('xx') then sequence name cannot be read.

create table table_1 (
	id int primary key default nextval('db2java_sequence')"
)

6.3. Oracle

Oracle has its own proprietary values regarding types of columns. For example, INTEGER in CREATE TABLE is returned as NUMBER and java.math.BigDecimal in Java. You can use model properties file to override the generated values.

6.3.1. Sequences

You cannot read the name of the sequence associated with a table.

6.4. DB2

6.4.1. Sequences

You cannot read the name of the sequence associated with a table.

7. Large objects (BLOBs/CLOBs)

For performance reasons, the fields corresponding to binary objects are not generated in Java beans. If a table contains binary objects, then appropriate access methods are generated in DAOs. This is why you cannot have a BLOB/CLOB column which is NOT NULL. Otherwise, you will not be able to insert a Java™ bean into the database.

OneWebSQL™ for JDBC supports two kinds of binary objects: binary objects (BLOBs) and character objects (CLOBs). For each kind of object, a different set of access methods is generated. For details about the generated methods, see the DAO Guide.

7.1. PostgreSQL

PostgreSQL has two different methods of storing binary data:

BYTEA datatype - the binary data is stored directly in the table. Large Object feature (LO feature) - the binary data is stored in a separate table, and the original table contains special value of type OID.

The BYTEA columns are mapped to byte[] columns by OneWebSQL™ for JDBC, and are stored in Java bean. OID columns are treated as BLOB columns. See also the section on Large objects (BLOBs/CLOBs). OneWebSQL™ for JDBC does not, by default, delete OID objects when it deletes the corresponding table. If you want this to happen, you should create a trigger which deletes the binary data when the corresponding table row is deleted.

Here is some sample code showing how to do this:

create trigger <trigger_name>
after delete or update on <table_name>
for each row execute procedure delete_linked_blobs();

The body of the function delete_linked_blobs() is as follows:

create or replace function delete_linked_blobs() returns trigger as $delete_linked_blobs$
begin
    if tg_op = 'DELETE' then
        perform lo_unlink(old.content);
    elsif tg_op='UPDATE' and (old.content != new.content) then
        perform lo_unlink(old.content);
    end if;
    return old;
end;
$delete_linked_blobs$ language plpgsql;

The code can also be found in the distribution archive in file sql/postgresql-create-function-delete_linked_blobs.