|
|
Chapter 1 - Working with DB Visual ARCHITECT
DB Visual ARCHITECT (DB-VA) is an Object-Relational Mapping tool which supports building database application faster, better and cheaper. This chapter gives you an introduction to DB-VA, describes the key benefits of DB-VA and shows you how to connect your database and download the database driver files automatically to facilitate the working environment. The supported database drivers for connecting database to the DB-VA working environment are also described.
In this chapter:
Software applications are most likely to be developed with a database such that all data working with the application system can be retained, resulting in information and knowledge. Hence, database application is widely adopted by businesses of all sizes. In order to access and manipulate the relational database, a standard computer language, Structured Query Language (SQL) has to be used. SQL statements play an important role when developing database application.
Taking a trading system as an example, if the end-user wants to update a Sales Order record, the system has to retrieve the corresponding record from the Sales Order table and display to the end-user. After the end-user confirms the modification of data, the system has to update the record accordingly. It is noticeable that a database application requires a lot of coding for handling SQL statements so as to access and manipulate the database.
Hence, it is inevitable that developers spend almost 50% of development time for implementing the code with SQL statement. Moreover, mapping between the persistent code and database table is maintained throughout the development life cycle. Once there is a change in the structure of a database table, SQL statements which related to the modified table have to be re-written. Developers have to keep an eye on every change in the database schema.
DB Visual ARCHITECT (DB-VA) provides a solution to develop database application. DB-VA is an Object-Relational Mapping tool which provides an ease-to-use environment bridging between object model, data model and relational database. DB-VA not only provides you a visual modeling for both logical data design and physical data design, but also automates the mapping between object model and data model.
DB-VA generates not only Java and .NET persistent code, but also a cost-effective, reliable, scalable and high-performance object to relational mapping layer. The generated mapping layer includes the support of transaction, cache and other optimized feature. DB-VA increases the productivity and significantly reduces the risk of developing the mapping layer manually.
 |
|---|
| Figure 1.1 - Introduction of Database Visual Architect |
The overview of DB-VA
Overview Diagram
 |
|---|
| Figure 1.2 - The overview diagram of DB-VA |
DB-VA provides the following key features so as to help you simplify your development:
Traditionally developers spend a lot of effort in saving and loading objects between memory and database which makes the program complicated and difficult to maintain. DB-VA simplifies these tasks by generating a persistence layer between object and data models.
 |
|---|
| Figure 1.3 - Generate a Persistence Layer for communicate with database |
DB-VA generates object-relational mapping layer which incorporates prime features such as transaction support, pluggable cache layer, connection pool and customizable SQL statement. With this mapping layer, developers can keep away from mundane implementation work and focus on the business requirements.
DB-VA provides a true model driven platform for application development. DB-VA allows developer not only to start from creating the models by using class diagram or entity relationship diagram to generating the executable persistence layer from the models, but also to modify the entity-relational model which comes from reverse engineering an existing database, transform into object model and generate persistence layer. With the sophisticated model-code generator, the persistent model will be updated automatically according to any modification.
DB-VA supports a wide range of database, including Oracle, DB2, Cloudscape/Derby, Sybase Adaptive Server Enterprise, Sybase SQL Anywhere, Microsoft SQL Server, PostgreSQL, MySQL and more. DB-VA also promotes an easy migration between databases by enabling the same set of ORM Java objects to work with different databases and transforms the proprietary data type that suit the default database specified.
DB-VA allows you to reverse engineering an existing database through JDBC into the entity-relational model. Developers can transform the entity-relational model to object model and redesign the database for further development.
 |
|---|
| Figure 1.4 - Reverse and Forward Engineering for database |
DB-VA allows you to reverse engineering Java classes and Hibernate models into the persistent object model. Developers can transform the persistent object model to data model and redesign the models for further development.
DB-VA is not only a standalone application, but also integrated to the major Integrated Development Environments (IDEs), including Eclipse/WebSphere®, Borland JBuilder®, NetBeans/Sun™ ONE, IntelliJ IDEA™ Oracle JDeveloper, BEA WebLogic Workshop™and Visual Studio .NET, which results in streamlining the entire model-code-deploy software development process.
 |
|---|
| Figure 1.5 - The supported IDE |
DB-VA inherits the visual modeling environment from Visual Paradigm for UML, a well-known UML CASE Tool, it not only provides an intuitive inline editing for both object and data models, but also adopts the resource-centric interface for assisting frequently performed tasks.
 |
|---|
| Figure 1.6 - Consistence user interface |
As DB-VA acts as a 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:
 |
|---|
| Figure 1.7 - To open the Database Configuration dialog |
The Database Configuration dialog box is displayed.
 |
|---|
| Figure 1.8 - Select the programming language |
For Java project development, continue the following steps for configuring the database connection.
 |
|---|
| Figure 1.9 - Database Configuration for Java Project |
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 and Supporting Automatic Download of JDBC Driver sections.
For the Driver, select the JDBC 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.
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. |
The Engine option in the Database Setting is only provided when configuring MySQL database for Java project. |
 |
|---|
| Figure 1.10 - 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 1.11 - Connection successful/failure message | |
DB-VA supports setting up multiple database configurations 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 DB-VA supports multiple database configurations, 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:
 |
|---|
| Figure 1.12 - Set the default database type |
 |
|---|
| Figure 1.13 - Set the database configure as default |
In order to connect the database successfully, JDBC driver file must be specified for Java project. DB-VA provides 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.
DB-VA supports the automatic download of JDBC drivers for database connection in Java project development. The drivers downloaded automatically are stored in the %VP_Suite_Installation_Directory%/ormlib/driver directory.
When configuring the database connection for any new projects, DB-VA automatically checks if the desired driver file exists in the driver directory, DB-VA specifies the driver file 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 1.14 - Download JDBC Driver |
If Download is clicked, DB-VA automatically downloads the desired driver file for the desired database. For more information on downloading driver file automatically, refer to the description of Supporting Automatic Download of JDBC Driver section.
If Update is clicked, DB-VA automatically updates the driver file if there is an update version for the desired database.
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. |
As DB-VA supports the automatic download of the driver files for connecting the database, it reduces the effort to find the desired driver file from the Internet manually.
The following steps illustrate the automatic download of JDBC Driver for MySQL database as an example:
button, click Download from the drop-down menu. |
|---|
| Figure 1.15 - To download the driver |
 |
|---|
| Figure 1.16 - Configure the proxy setting |
The Download dialog box is shown indicating the download progress.
 |
|---|
| Figure 1.17 - The Download dialog show the download progress |
 |
|---|
| Figure 1.18 - The download complete message show in the download dialog |
The driver file is shown on the Driver file of the Database Setting after download is done.
 |
|---|
| Figure 1.19 - Database setting |
|
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 by DB-VA. |
For .Net project development, continue the following steps for configuring the database connection.
 |
|---|
| Figure 1.20 - Database Configuration for .Net Project |
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 .NET Driver and Adapter File and Supporting Automatic Download of .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 .NET Driver and Adapter File and Supporting Automatic Download of .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.
You can press button to modify the Driver class and Dialect manually.
|
|---|
| Figure 1.21 - 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 1.22 - Connection successful/failure message | |
DB-VA supports setting up multiple database configurations 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 DB-VA supports multiple database configurations, 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:
 |
|---|
| Figure 1.23 - Set the database type as default |
 |
|---|
| Figure 1.24 - set the database setting as default |
In order to connect the database successfully, .NET driver file and Adapter File must be specified for .NET project. DB-VA provides 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.
DB-VA supports the automatic download of .NET drivers and Adapter File for database connection in .NET project development. The drivers downloaded automatically are stored in the %VP_Suite_Installation_Directory%/ormlib/driver directory.
When configuring the database connection for any new projects, DB-VA automatically checks if the desired driver file exists in the driver directory, DB-VA specifies the driver file 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 1.25 - Download button |
If Download is clicked, DB-VA automatically downloads the desired driver file for the desired database. For more information on downloading driver file automatically, refer to the description of Supporting Automatic Download of .NET Driver and Adapter File section.
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. |
As DB-VA supports the automatic download of the driver files for connecting the database, it reduces the effort to find the desired driver file from the Internet manually.
The following steps illustrate the automatic download of .NET Driver and Adapter File for MySQL database as an example:
button, click Download both Driver from the drop-down menu to download the driver and adapter files at the same time. |
|---|
| Figure 1.26 - Database Configuration for .NET |
|
The Driver file and Adapter file can be downloaded separately by selecting Download from its drop-down menu respectively. |
| Driver file |  |
| Adapter file |  |
 |
|---|
| Figure 1.27 - Configure the proxy setting |
The Download dialog box is shown indicating the download progress.
 |
|---|
| Figure 1.28 - Download dialog show the download progress |
 |
|---|
| Figure 1.29 - The 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 1.30 - Database Setting |
|
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 by DB-VA. |
|
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 by DB-VA. |
DB-VA provides an environment for visual modeling the developing system. By connecting the relational database to DB-VA environment, DB-VA can automate the mapping between models and relational database. DB-VA supports the most common relational database, 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 DB-VA. You can get the driver files by using the automatic download facility provided, or download the driver file manually.
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 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) |
As DB-VA supports multiple databases and allows you to configure multiple database settings, there may be differences in the data type supported by these databases.
DB-VA allows you 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, DB-VA provides a list of pre-defined data types which is general to all databases.
 |
|---|
| Figure 1.31 - The pre-defined data types for all database |
DB-VA also allows you to assign a data type which is database-specific.
 |
|---|
| Figure 1.32 - Set the database type |
The database-specific data types will be automatically added to the list.
 |
|---|
| Figure 1.33 - The 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. |
As DB-VA 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 DB-VA environment is configured with a default database, 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:
 |
|---|
| Figure 1.34 - To show column types |
The data type for columns is displayed.
 |
|---|
| Figure 1.35 - 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. |
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 1.36 - Data type convert automatically |
|
|
|||||||