Chapter 12 - Getting Started with Object-Relational Mapping

Chapter 12 - Getting Started with Object-Relational Mapping

Connecting your database to the working environment facilitates bridging between object model, data model and relational database. This chapter shows you how to configure the database connection, automatically download the database driver files and describes the supported database driver for connecting database in the working environment.

In this chapter:

Database Configuration
Automatic Downloading Driver Files
Supported Database
Supported JDBC Drivers
Supported .NET Drivers

Database Configuration

In order to bridge between object model, data model and relational database, you have to configure the database connection to ensure the environment.

To configure the database connection:

From the menu, click Modeling > ORM > Database Configuration...


Figure 12.1 - To open the Database Configuration

For other SDE:

SDE	Method
SDE for JBuilder	From the menu, click Tools > Modeling > ORM > Database Configuration...
SDE for NetBeans	From the menu, click Modeling > ORM > Database Configuration...
SDE for IntelliJ IDEA	From the menu, click Modeling > ORM > Database Configuration...
SDE for JDeveloper	From the menu, click Model > ORM > Database Configuration...
SDE for WebLogic Workshop	From the menu, click Modeling > ORM > Database Configuration...

Table 12.1

The Database Configuration dialog box is displayed.

Select the Language for the project to be developed from the drop-down menu. By default, Java language is selected.


Figure 12.2 - Select the generated programming language

Database Configuration for Java Project

For Java project development, continue the following steps for configuring the database connection.

Place a check mark beside the desired database for configuration.


Figure 12.3 - Database setting for Java

Enter the database setting.

For the Driver file, click button to specify the Driver file. The Driver file can be specified either by Download, Update or Browse. For more information, refer to the description of Specifying JDBC Driver File, .NET Driver and Adapter File section.

For the Driver, select the JDBC Driver from the drop-down menu. The driver description will be shown in the Database Driver Description pane.

You can press to modify the Driver class and Dialect manually.

For the Connection URL, enter the information required for the JDBC Driver to connect to the database.
For the User, enter the valid username who has the access right to connect to the database.
For the Password, enter the corresponding password for the user to connect to the database.
For the Engine, select the type of engine used in generating the MySQL database.

The Engine option in the Database Setting is only provided when configuring MySQL database for Java project.

Click the Test Connection button to test whether the database can be connected.


Figure 12.4 - Test Connection button

If the database can be connected, you will be prompted by a dialog box showing Connect Successful. Otherwise, a Connection Exception Dialog Box will be prompted.


Figure 12.5 - The connection successful/failure message

Configuring Multiple Database Settings

Multiple database configurations can be set up in the same project environment. To configure multiple database settings, simply place check marks beside the desired databases and specify the configuration for each database.

Generally, only one database is used for an application. As multiple database configurations are supported, only one database configuration will be applied as the default database connection for the generation of code and database.

You can set the default database connection in one of the two ways:

Right-click on the desired database, select Set as default.


Figure 12.6 - Set the database type as default

Select the desired database, click Set as default button on the Database Setting.


Figure 12.7 - Set the database setting as default

Database Configuration for .Net Project

For .Net project development, continue the following steps for configuring the database connection.

Place a check mark beside the desired database for configuration.


Figure 12.8 - Database Configuration for .NET

Enter the database setting.

For the Driver file, click button to specify the Driver file. The .NET Driver file can be specified either by Download, Update or Browse. For more information, refer to the description of Specifying JDBC Driver File, .NET Driver and Adapter File section.

For the Adapter file, click button to specify the Adapter file. The Adapter file can be specified either by Download, Update or Browse. For more information, refer to the description of Specifying JDBC Driver File, .NET Driver and Adapter File section.

For the Connection String, enter the information required for the .NET Driver to connect to the database.

For the Driver, select the .NET Driver from the drop-down menu. The driver's description will be shown in the Database Driver Description pane.

You can press button to modify the Driver class and Dialect manually.

Click the Test Connection button to test whether the database can be connected.


Figure 12.9 - Test Connection button

If the database can be connected, you will be prompted by a dialog box showing Connect Successful. Otherwise, a Connection Exception Dialog Box will be prompted.


Figure 12.10 - The connection successful/failure message

Configuring Multiple Database Settings

You can set the default database connection in one of the two ways:

Right-click on the desired database, select Set as default.


Figure 12.11 - Set the default database type

Select the desired database, click Set as default button on the Database Setting.


Figure 12.12 - Set the database setting as default

Specifying JDBC Driver File, .NET Driver File and Adapter File

In order to connect the database successfully, JDBC driver file must be specified for Java project. Meanwhile, .NET driver file and adapter file must be specified for .NET project. You are provided with three ways to specify the driver files. They are selecting the suitable driver file manually, downloading driver files automatically and updating the driver files automatically.

Automatic download of JDBC drivers, .NET drivers and Adapter files are supported for database connection in Java and .NET project development respectively. The drivers downloaded automatically are stored in the %VP_Suite_Installation_Directory%/ormlib/driver directory.

When configuring the database connection for any new projects, SDE automatically checks if the desired driver file exists in the driver directory, the detected driver file will be specified in the database configuration automatically.

To specify the driver file, click on the button, either click Download, Update or Browse... from the drop-down menu.


Figure 12.13 - The Driver options

Download

If Download is clicked, the download of the desired driver file for the desired database proceeds automatically. For more information on downloading driver file automatically, refer to the description of Automatic Downloading Driver File section.

Update

If Update is clicked, the update on the driver file proceeds automatically if there is an update version for the desired database.

Browse

If Browse is clicked, a File Chooser is shown, specify the location of the driver file.

Update is only available if the driver file is automatically downloaded and stored in the %VP_Suite_Installation_Directory%/ormlib/driver directory in advance.

Automatic Downloading Driver files

As the automatic download of the driver files for connecting the database is supported, it reduces the effort to find the desired driver file from the Internet manually.

Automatic Downloading JDBC Driver

The following steps illustrate the automatic download of JDBC Driver for MySQL database as an example:

Click on the button, click Download from the drop-down menu.


Figure 12.14 - Select download driver

A Download Database Driver dialog box is shown allowing the proxy setting. To enable proxy for the Internet connection, check the Use proxy option, and then fill in the information for proxy setting.


Figure 12.15 - Update Database Driver dialog

The Download dialog box is shown indicating the download progress.


Figure 12.16 - Download dialog

Click Close when the download is completed.


Figure 12.17 - Download complete with the message

The driver file is shown on the Driver file of the Database Setting after download is done.


Figure 12.18 - The new database driver is ready

After downloaded the driver file, <<MySQL Connector/J 3.1.10>> shown on the Driver file indicates that the JDBC driver file is downloaded with the specified version number.

Automatic Downloading .NET Driver and Adapter File

The following steps illustrate the automatic download of .NET Driver and Adapter for MySQL database as an example:

Click on the button, click Download both Driver from the drop-down menu to download the driver and adapter files at the same time.


Figure 12.19 - Select download driver and adaptor

The Driver file and Adapter file can be downloaded separately by selecting Download from its drop-down menu respectively.

Driver file
Adapter file

Table 12.2

A Download Database Driver dialog box is shown allowing the proxy setting. To enable proxy for the Internet connection, check the Use proxy option, and then fill in the information for proxy setting.


Figure 12.20 - Proxy setting for Download driver

The Download dialog box is shown indicating the download progress.


Figure 12.21 - Download dialog

Click Close when the download is complete.


Figure 12.22 - Download complete message show in Download dialog

The driver file and adapter file are shown on the Driver file and Adapter file of the Database Setting after download is done.


Figure 12.23 - The new driver are ready

After downloaded the driver file, <<MySQL Connector/NET 1.0.4>> shown on the Driver file indicates that the .NET driver file is downloaded with the specified version number.

After downloaded the adapter file, <<MySQL Connector/J 3.1.10>> shown on the Adapter file indicates that the adapter driver file is downloaded with the specified version number.

Supported Database, JDBC Drivers and .NET Drivers

SDE provides an environment for visual modeling the developing system. By connecting to the relational database, the mapping between models and relational database can be automated. The most common relational database are supported, including Oracle, DB2, Microsoft SQL Server, Sybase Adaptive Server Enterprise, Sybase SQL Anywhere, MySQL, HSQLDB, Cloudscape/Derby and PostgreSQL. Their relative JDBC Drivers and .NET Drivers are listed in the following tables.

In order to connect to any of the supported database, the relative JDBC and .NET Drivers are required for configuring the database connection. All of the required JDBC and .Net Drivers will not be bundled with SDE. You can get the driver files by the automatic download facility provided, or download the driver file manually. For more information on how to get the JDBC and .NET drivers manually, refer to Appendix D - JDBC and .NET Drivers.

Table shows the Supported Database and their relative JDBC Drivers.

Database Name	JDBC Drivers Name
Cloudscape/Derby 10	Cloudscape/Derby (Embedded), Cloudscape/Derby (Server)
DB2 7/8	DB2 (AS/400 Toolbox for Java JDBC Driver) DB2 (App Driver) DB2 (Net Driver)
HSQLDB 1.61-1.8	HSQLDB (In-process) HSQLDB (Server)
IBM Informix	IBM Informix (Client) IBM Informix (Server)
MS SQL Server 2000	MS SQL Server (DataDirect SequeLink Driver) MS SQL Server (JSQL Driver) MS SQL Server (JTURBO Driver) MS SQL Server (Microsoft Driver) MS SQL Server (WebLogic Connect Driver) MS SQL Server (WebSphere Connect Driver) MS SQL Server (jTDS Driver)
MySQL 3/4	MySQL (Connector/J Driver)
Oracle	Oracle (DataDirect SequeLink Driver)
Oracle 8i	Oracle8i (THIN JDBC Driver)
Oracle 9i	Oracle9i
PostgreSQL	PostgreSQL
Sybase Adaptive Server Enterprise 12.5	Sybase Adaptive Server Enterprise (jConnect Driver) Sybase Adaptive Server Enterprise (JDS Driver)
Sybase SQL Anywhere 9	Sybase SQL Anywhere (jConnect Driver)

Table 12.3

Table shows the Supported Database and their relative .NET Drivers.

Database Name	.NET Drivers Name
DB2 7/8	DB2 (DB2 UDB for iSeries .NET Data Provider)
MS SQL Server 2000	MS SQL Server
MySQL 3/4	MySQL (MySQL Connector/Net 1.0)
Oracle	Oracle (.NET Framework Data Provider) Oracle (Oracle Data Provider for .NET)
PostgreSQL	Postgre (Npgsql)

Table 12.4

Supporting Multiple Database

As multiple databases are supported and you are allowed to configure multiple database settings, there may be differences in the data type supported by these databases.

Assigning Data Types from Multiple Database

You are allowed to specify the data type of the column in the database by using the drop-down menu of Type in the Column Specification dialog box. By default, a list of pre-defined data types which is general to all databases is provided.


Figure 12.24 - Pre-defined data types

You are also allowed to assign a data type which is database-specific.

Place a check mark beside the desired database in the Database Configuration dialog box.


Figure 12.25 - Select database

The database-specific data types will be automatically added to the list.

Select the database-specific data types from the drop-down menu. For example, data type, tinyint is specific to MySQL database.


Figure 12.26 - Database-specific data types

If you have checked multiple databases in the Database Configuration dialog box, all data types supported by these databases will be added as an option for the drop-down menu.

Porting to Other Database

The generated persistent code, which maps to relational database tables, is capable of manipulating different databases. By default, the persistent code works with the database configured as default connection. On the other hand, you are allowed to port the persistent code to work with other databases by adding a few lines of code.

JDBCSettings setting = new JDBCSettings();
JDBCConnectionSetting connectionSetting = setting.getDefaultJDBCConnectionSetting(JDBCSettings_Constant);

The JDBCSettings_Constant is the constant for JDBC settings. Modify it by the corresponding constant value stated in the following table.

Table shows the constant for JDBC Settings for different database.

Database	Constant for JDBC Settings
MySQL	JDBCSettings.DBTYPE_MY_SQL
HSQLDB	JDBCSettings.DBTYPE_HSQL
MY SQL Server	JDBCSettings.DBTYPE_MS_SQL
Oracle	JDBCSettings.DBTYPE_ORACLE
Sybase ASE	JDBCSettings.DBTYPE_SYBASE
Sybase SQL Anywhere	JDBCSettings.DBTYPE_SYBASE_ANYWHERE

Table 12.5

Porting to Database is only supported in Java project.

Example:


Figure 12.27 - Example for using JDBCSettings constant

After adding these lines of code, the default database connection will be replaced by the database specified by the JDBCSettings_Constant.

Displaying Data Type based on Default Database

As SDE provides a visual data modeling of the database depicted by the Entity Relationship Diagram (ERD), you are allowed to enable and disable the display of data type for columns of the entities in the ERD. Since the default database is configured in the working environment, the data type will be displayed according to the data type supported by the default database.

To display the data type for columns of entities in the ERD:

Right-click on the background of the ERD, select Show Column Types.


Figure 12.28 - To show the column types

The data type for columns is displayed.


Figure 12.29 - Entity with column types

If the default database connection is changed, the data types for all columns will be changed with respect to the new database connection automatically.

Example:

There is an entity, Customer in the ERD. Modify the default database connection from MySQL to Oracle, the data types will be changed automatically.


Figure 12.30 - Modify the data type

Visual Paradigm International Limited
Website:	www.visual-paradigm.com
E-mail:	support@visual-paradigm.com