1. Introduction

This document shows you how to model databases in Oracle SQL Developer Data Modeler for use with OneWebSQL™ Oracle2Java. Oracle SQL Developer Data Modeler (Oracle Data Modeler) is a database modeling tool. It helps you define and maintain the structure of your database. The primary advantages of using a modeling tool are:

Combining the Oracle Data Modeler tool with OneWebSQL™ Oracle2Java makes your code easy to maintain. Integration of OneWebSQL™ Oracle2Java with Oracle Data Modeler enables you to keep your code and the database changes in synch, so that any database structure changes are updated immediately in the code.

2. Using Oracle Data Modeler

2.1. Which version should I use?

Oracle Data Modeler can be used in two ways:

  1. As a stand-alone application, or
  2. As a part of Oracle SQL Developer program (for version 3 and up).

Both the stand-alone Oracle Data Modeler Program and the Data Modeler Program integrated with Oracle SQL Developer use the same data model file format and can be used interchangeably with OneWebSQL.

2.2. Oracle Data Modeler files

Oracle Data Modeler saves model in a .dmd file. In addition to the .dmd file, Oracle Data Modeler stores data in a corresponding model directory. If your model file is model.dmd, then the model directory will be called model. If you copy your model, make sure you copy the model directory as well.

Note about OneWebSQL™ setup
When you specify a model in OneWebSQL™ Oracle2Java setup, make sure that the model directory is in the same directory as the .dmd file.

2.3. Naming conventions

All database names (tables, columns, views, etc.) should be in lower case, with underscores "_" separating words. OneWebSQL™ will then automatically map the database names to Java identifiers with proper Java naming conventions when you use it to generate your code.

Warning
Do not use non-ASCII characters, whitespace, or Java keywords in the names. Otherwise, the Java code generation will not work properly.

3. Basic modeling

3.1. Start Oracle Data Modeler

3.1.1. Stand-alone Oracle Data Modeler

Just start the program.

3.1.2. Oracle SQL Developer

  1. If you use Oracle SQL Developer, first start the program.

  2. Open Data Modeler Browser.

3.2. Overview of modeling process

  1. Open model properties. Right-click on the name of the model and select Properties...

  2. Go to Comment tab. Enter the target DBMS.

  3. Open a relational model.

  4. Add tables, views, and sequences.
  5. Save the design. Right click on the design you want to save and select Save design.

3.2.1. Supported databases and their codes

By default, Oracle Data Modeler supports the following databases (called Database Sites):

You can also add new Database sites using Oracle Data Modeler user interface.

OneWebSQL™ Oracle2Java supports the following databases:

Database Code to enter in Definition tab
IBM DB2 DB2
Microsoft SQL Server MSSQL
Oracle Oracle
PostgreSQL PostgreSQL
MySQL MySQL
HSQLDB HSQLDB

3.3. Tables

Your tables are translated into Java objects (see the Application Development Guide).

  1. In the toolbar, select the New Table icon. Click in the relational model area to create a new table.

  2. In the General tab fill in a name for the table.

  3. Open the Columns table to add table columns. Click the green button

  4. Fill in the name and data type of the column. The name value is used to generate the name of the corresponding Java attribute. The data type value is translated to the appropriate Java attribute type.

  5. Use the Comments tab to add any extra information about the table.

3.3.1. List of corresponding database and Java types

Database to Java mappings are shown in the table below.

Database type Java type
DECIMAL java.math.BigDecimal
NUMERIC java.math.BigDecimal
CHAR java.lang.String
VARCHAR java.lang.String
INTEGER java.lang.Integer
BIGINT java.lang.Long
REAL java.lang.Float
FLOAT java.lang.Double
DOUBLE java.lang.Double
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp

Note that database types are mapped to their object counterparts, so NULL values are handled correctly.

3.3.2. Primary keys

If you want a column to be part of a table's primary key, check the PK checkbox in the Column Properties tab.

It is possible to design a table with no primary key. Since this is useful only in rare situations, you need to confirm that this is really what you want. In the Comments tab for the table in question, add:

has_no_pk=true

Note about the generated Java code
The Java code for views with a primary key will have methods to retrieve view rows using the primary key column(s).

Do not change the order of primary key columns in the model
For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the primary key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

3.3.3. Alternative keys

  1. To add a new alternative key, go to the Unique Constraints tab.
  2. Click the Add icon.

  3. Provide a name for the alternative key.
  4. Then select columns of the alternative key.

Note about the generated Java code
The Java code for tables with alternative keys will contain methods for retrieving rows using those alternative key columns.

Do not change the order of key columns in the model
For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

3.4. Relationships between tables

To create relationships between tables, select the New FK Relation icon in the toolbar.

Click and drag the mouse to draw a line between related tables. First click anywhere inside the parent (= primary key) table. Then, while holding the mouse button down, draw a line to the child (= foreign key) table. Release the mouse once the cursor is inside the child table.

Note that foreign key column(s) are created in child table. By default, the name of the columns is <parent-table>_<parent-column>. This setting lets OneWebSQL™ generate meaningful method names in your code. For example, a method which returns a book identifier would be called getBookId().

3.4.1. One-to-one relationships

You may create a one-to-one relationship between two tables.

  1. Double-click on the line connecting the table to itself.
  2. Go to the Comments tab.
  3. Set the cardinality property to 1..1.

    cardinality=1..1
    

    By default, if you don't set the cardinality property, the relationship is many-to-one.

3.4.2. Self-references

You may create self-references, that is, references from a table to itself. For example, you may want to have a category tree like 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.

  1. Double-click on the line connecting the table to itself.
  2. Go to the Comments tab.
  3. Give names to roles by specifying the properties pkRole and fkRole.

3.4.3. Multiple relationships between 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.

  1. Double-click on the line connecting the two entities.
  2. Go to the Comments tab.
  3. Give names to roles by specifying the properties pkRole and fkRole.

3.5. Views

It's a good practice to create a new view for every query that is executed often. For example, if you want to display an HTML table that combines three different database tables, it's more efficient in the long run to create a new view for it than to build JOINs every time.

  1. To create a new view, select the New view icon in the toolbar.

  2. Type in the name of the view.

  3. Click the Query button to open the Query Builder window.

  4. Create the SQL query you want to associate with the view. Even if you want to list all the columns, do not use an asterisk *.

  5. Make sure the names of columns in the view are different. If necessary, specify aliases for the offending columns.

  6. In the view, make sure the name and data type values are correct.

  7. If the type of a column is missing (e.g., you are selecting an expression which is not a column), go to the Comments tab for the view and type column.<name-of-column>.dbtype=TYPE_OF_COLUMN.

3.5.1. Primary keys in views

  1. You can add a new primary key to the view in the Comments tab. For example, to add a new key named pk which uses the id column of the view, type:
    key.pk=id
    
    Alternately, to create a multicolumn key which uses the id and author columns, type:
    key.pk=id,author
    
    The general syntax is
    key.<name_of_key>=<list_of_columns>
    
  2. To specify that the new key is the primary key, type:
    PK=pk
    
    The general syntax for the primary key is
    PK=<name_of_key>
    
    Note that the name of the key is case-sensitive.

Note about the generated Java code
The Java code for views with a primary key will have methods to retrieve view rows using the primary key column(s).

Do not change the order of primary key columns in the model
For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the primary key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

3.5.2. Alternative keys in views

You can add a new alternative key to the view in the Comments tab. For example, to add a new key named ak which uses the isbn column of the view, type:

key.ak=isbn

You can create a multicolumn key which uses the id and author columns, for example, by typing:

key.ak2=id,author

The general syntax is

key.<name_of_key>=<list_of_columns>

Note about the generated Java code
The Java code for views with an alternative key will have methods to retrieve view rows via the alternative key column(s).

Do not change the order of key columns in the model
For multicolumn keys, the order of columns in the methods is the same as in the database model. Be very careful not to change the order of columns in the key by accident: the order of parameters in the generated method would change too. This is an incompatible change the compiler may not notice: the order of method parameters changes but the signature of the method stays the same.

4. Customizing the model with object properties

You can customize the Java code that will be generated by setting additional properties in the Comments for an object. Properties can be set for:

For each of these, you should first double-click the object you want to modify. This opens its properties window. Then, go to the Comments tab. You should specify properties in the Java properties format:

<key>=<value>

Comments start at the beginning of a line with the pound sign #.

# This is a comment
package=com.example # This is NOT a comment

4.1. Java package

You can specify that all Java classes generated for a certain model's tables and views should become part of a specific Java package. In the Comments tab for the model, type:

package=com.example

If you don't specify the package in the model, the default Java package will be used. For information on how to set the default Java package, see the Code Generation Guide.

You can also change the Java package setting for a table or a view. This will override the package property set in the model. In the Comments tab for the table or view, type:

package=com.example.mytable

4.2. Sequences

Tables and views 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. In the Comments tab for the table or view, type:

sequence=<name_of_sequence>

Make sure that the sequence exists in the database. To associate a sequence with a table, follow these steps.

  1. Double-click the table to open the Table Properties window.
  2. Go to the Columns tab.
  3. Select the primary key column and double-click it to open the Column Properties window.
  4. In the General tab, check the Auto Increment checkbox.

  5. Go to the Auto Increment tab. Fill in the name of the sequence and the physical data of the view.

4.2.1. Disabling auto-increment for the primary key

You can switch off auto-increment for the primary key value. To do this, go to the Comments tab for the table or view (do NOT open the Comments tab for the primary key column itself). Type:

dont_generate_get_next_id=true

4.3. Change Java class for a column

You can change the Java class which is be assigned to a column. In the Comments for the column, type:

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.1. 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. In the Comments tab, set the column.class property to java.lang.Boolean.

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.4. Hide column in toString() method

OneWebSQL™ Oracle2Java 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 in the Comments tab for the column, type:

dont_generate_to_string=true

5. 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™ Oracle2Java 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.

5.1. PostgreSQL

By default, Oracle Data Modeler does not support PostgreSQL database. However, if you add PostgreSQL Database site you should be aware of how OneWebSQL™ handles LOB columns in PostgreSQL.

PostgreSQL has two different methods of storing binary data:

  1. BYTEA datatype - the binary data is stored directly in the table.
  2. 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™ Oracle2Java, and are stored in the Java bean. OID columns are treated as BLOB columns (see previous BLOBs/CLOBs section). OneWebSQL™ Oracle2Java 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 the file sql/postgresql-create-function-delete_linked_blobs.

6. Using database functions

You may invoke both built-in and user-defined database functions in your queries. For details on how to invoke a function, see the Query Language Guide. However, queries which invoke database-stored functions do not use the full power of OneWebSQL™ Oracle2Java type-checking. If your database schema changes, OneWebSQL™ Oracle2Java will not detect changes in database-stored functions.

7. Inheritance

OneWebSQL™ Oracle2Java does not support table inheritance. There are some ways to model inheritance in a database:

The structure of the generated code will reflect the structure of your database. You will not have inheritance in your code. You can create views which represent objects of a certain class in the hierarchy. However, the view will not be part of the Java inheritance hierarchy.

8. Limitations

8.1. Supported databases

Currently, OneWebSQL™ Oracle2Java supports the following databases:

8.1.1. Microsoft SQL Server

Microsoft SQL Server does not have sequences. OneWebSQL™ reads the next id with the help of get_next_id() stored procedure. You have to create this procedure when you create the database structure. Here is some sample code which shows you how to create the procedure. The code can also be found in the distribution archive in the file sql/mssql-create-get_next_id.sql.

/*==============================================================*/
/* Procedure used to obtain id for new records                  */
/*==============================================================*/
CREATE PROCEDURE get_next_id @table_name VARCHAR(255), @next_id INTEGER
OUTPUT AS
    DECLARE next_id_crsr CURSOR
            FOR SELECT next_id FROM tech_max_id WHERE table_name =
@table_name FOR UPDATE OF next_id
    OPEN next_id_crsr
    FETCH next_id_crsr INTO @next_id
    IF 
FETCH_STATUS = 0 UPDATE tech_max_id SET next_id = next_id + 1 WHERE CURRENT OF next_id_crsr ELSE BEGIN SELECT @next_id = 1 INSERT INTO tech_max_id (table_name, next_id) VALUES (@table_name, 2) END CLOSE next_id_crsr DEALLOCATE next_id_crsr go

8.1.2. MySQL

MySQL does not have sequences. 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
);

8.1.3. PostgreSQL

If you want to use the LO feature in PostgreSQL, make sure you delete the corresponding objects (see previous section on Large Objects). The code can which shows how to do it can be found in the distribution archive in the file sql/postgresql-create-function-delete_linked_blobs.

8.2. Inheritance

OneWebSQL™ Oracle2Java does not support Java object inheritance.

8.3. NOT NULL Large Object fields

You cannot have NOT NULL Large Object fields in the database.