1. Introduction

This document shows you how to model databases in ERwin for use with OneWebSQL™ ERwin2Java. ERwin 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 ERwin modeling tool with OneWebSQL™ ERwin2Java makes your code easy to maintain. Integration of OneWebSQL™ ERwin2Java with ERwin 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 ERwin

2.1. ERwin file formats

ERwin is able to store models in several formats:

2.2. 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. Overview of modeling process

  1. Start with a new Blank Physical Model.

  2. Right click on the model and choose Properties...

  3. Open the Definition tab. Enter the target DBMS and default Java package. The following database types are supported:
Database Code to enter in Definition tab
IBM DB2 DB2
Microsoft SQL Server MSSQL
Oracle Oracle
PostgreSQL PostgreSQL
MySQL MySQL
HSQLDB HSQLDB

  1. Add the tables and relationships between tables.
  2. Add views and sequences.
  3. Save the model as an XML file.

  4. Generate the Java code.

3.2. Tables

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

  1. In the toolbar, select the Table icon. This creates a new table.

  2. Right click on the new table and choose Table Properties... from the context menu. Here you can change the name of the table:

  3. Use the Comment tab to add any extra information about the table.
  4. Right click on the table and choose Column Properties... from the context menu.

  5. Here, you can add and edit columns.

3.2.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.2.2. Primary keys

If you want a column to be part of a table's primary key, check the Primary Key checkbox.

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 Comment tab for the table in question, add:

has_no_pk=true

Note about the generated Java code
The Java code for tables will contain methods for retrieving rows using primary key columns.

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.2.3. Alternative keys

  1. Right click on the table and choose the Index Properties... option from the context menu.

  2. In the toolbar, click the New icon to create a new index.

  3. Add the required columns.

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 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. Relationships between tables

To create relationships between tables, select the Non-identifying relationship icon in the toolbar.

Click and drag the mouse to draw a line between related tables. First click anywhere inside the first table. Then, while holding the mouse button down, draw a line to the second table. Release the mouse once the cursor is inside the second table.

You should enter a proper name for the relation in the Foreign Key Constraint Name column.

3.4. 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 View table icon from the toolbar, then click in the table area to create the view.

  2. Select View/Materialized relationship from the toolbar, and then connect the view with its related tables.

  3. Right click on the view and choose View Properties... from the context menu:

  4. Select the columns for your view:

  5. Or, you can write the view definition by hand in the User Defined SQL tab:

3.4.1. Primary keys in views

  1. You can add a new primary key to the view in the Comment 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
    
    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.4.2. Alternative keys in views

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

key.name=name

Additional Example: If you want to create a multicolumn key named unique which uses the id and name columns, type:

key.unique=id,name

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 Comment or Definition for an object. Properties can be set for:

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 Definition 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 view. This will override the package property set in the model. In the Comment 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 Comment tab for the table or view, type

sequence=<name_of_sequence>

Make sure that the sequence exists in the database.

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 Comment tab for the table or view. Type:

dont_generate_get_next_id=true

4.3. Change Java class for a column

You can change the Java class which is assigned to a column. In the Comment tab 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 Comment 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™ ERwin2Java 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 Comment 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™ ERwin2Java 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.

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™ ERwin2Java type-checking. If your database schema changes, OneWebSQL™ ERwin2Java will not detect changes in database-stored functions.

7. Inheritance

OneWebSQL™ ERwin2Java 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

At the moment OneWebSQL™ ERwin2Java 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.2. Inheritance

OneWebSQL™ ERwin2Java does not support Java object inheritance.

8.3. NOT NULL Large Object fields

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