Rapid J2EEв„ў Development: An Adaptive Foundation for Enterprise Applications

To illustrate the virtues of Hibernate and Middlegen, we first install a suitable RDBMS and create a database for which a persistence layer can be generated.

The first step is to install an RDBMS, and in this case, we use the services of MySQL.

Introducing MySQL

MySQL is a small, efficient database and is freely available for most platforms. MySQL offers several types of the product, including MySQL Pro, a commercial version intended for use in production systems, and MySQL Standard, which is available free under the GNU Public License (GPL). Both versions of MySQL offer identical functionality, the only differences being in the terms and conditions of the license.

The upcoming example uses version 4.0 of MySQL Standard. MySQL binaries are available for download from http://dev.mysql.com/downloads/mysql/4.0.html.

Comprehensive installation instructions, along with the MySQL manual, can be found at http://dev.mysql.com/doc/mysql/en/Installing.html.

Access to a MySQL database from Java requires the MySQL Connector/J JDBC driver. This is a Type 4 driver, and like MySQL Standard, is free to download. The latest version can be pulled down from http://www.mysql.com/products/connector/j.

From this download, we get mysql-connector-java-3.0.11-stable-bin.jar, which must be placed on the classpath of any Java application requiring access to a MySQL database.

For any additional information regarding MySQL, refer to the main product Web site at http://www.mysql.com.

Once the database has been installed, the next step is to build up a suitable database schema for use in the example.

Creating a Database Schema

To generate the persistence layer from the database, we need a suitable schema from which to work. For the example, I have put together an entity-relationship (ER) diagram containing four entities: Customer, Account, Purchase_Order, and Item.

The ER diagram in Figure 7-1 defines the relationships between the entities.

From the ER diagram, the following relationships are apparent:

• Each Customer entity can have exactly one Account.

• Each Account can have many Purchase_Order entities.

• A Purchase_Order is comprised of many Item entities.

• A one-to-one relationship has been defined between Item and Product.

To save time creating the schema from the diagram, the data modeling features of a modeling tool were used to generate the data definition language (DDL) statements from the ER diagram. The example uses Borland's Together ControlCenter to produce the DDL.

Note

Unfortunately, the generated scripts weren't exactly what I was after, making it necessary to tinker with the output. I therefore claim this to be an example of passive, not active, code generation.

The final version of the database script is shown in Listing 7-1.

Listing 7-1. Database Script customer-mysql.sql

DROP TABLE IF EXISTS Customer; DROP TABLE IF EXISTS Account; DROP TABLE IF EXISTS Purchase_Order; DROP TABLE IF EXISTS Item; DROP TABLE IF EXISTS Product; CREATE TABLE Customer ( customer_id mediumint(7) NOT NULL, name varchar(127), PRIMARY KEY (customer_id) ) TYPE=INNODB; CREATE TABLE Account ( account_id mediumint(7) NOT NULL, customer_id mediumint(7) NOT NULL, balance mediumint(7), invoice_period mediumint(7), PRIMARY KEY (account_id), INDEX customer_idx(customer_id), FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) ) TYPE=INNODB; CREATE TABLE Purchase_Order ( order_id mediumint(7) NOT NULL, account_id mediumint(7) NOT NULL, delivery_date date, PRIMARY KEY (order_id), INDEX account_idx(account_id), FOREIGN KEY (account_id) REFERENCES Account(account_id), ) TYPE=INNODB; CREATE TABLE Product ( product_id mediumint(7) NOT NULL, name varchar(127), description text, PRIMARY KEY (product_id) ) TYPE=INNODB; CREATE TABLE Item ( item_id mediumint(7) NOT NULL, order_id mediumint(7) NOT NULL, product_id mediumint(7) NOT NULL, quantity mediumint(7), unit_price mediumint(7), PRIMARY KEY (item_id, order_id), INDEX order_idx(order_id), INDEX product_idx(product_id), FOREIGN KEY (order_id) REFERENCES Purchase_Order(order_id), FOREIGN KEY (product_id) REFERENCES Product(product_id) ) TYPE=INNODB;

The changes made were around declaring the type of the tables to be InnoDB by adding the line TYPE=INNODB. Without this specification, MySQL discards the relationship information imposed by the foreign key constraints. Another option to changing the scripts would have been to configure MySQL to use InnoDB table types by default.

Looking at the Account table, a relationship is expressed through the customer_id foreign key to the Customer table. It is a MySQL condition that to be able to define a foreign key, an index must exist for the key. Hence, the script declares INDEX customer_idx (customer_id). This creates the index necessary to allow the relationship to be defined between the Account and Customer entities.

Running the Database Script

To create the database schema, we must first create a database and run the script. Start MySQL using the instructions appropriate for your particular platformfor example, mysqld --console.

MySQL comes with a command-line application for interacting with the database engine. Issue mysql from a command shell to start the application, enter the following commands to create a database called customer, and run the script.

mysql> create database customer; mysql> use customer mysql> source customer-mysql.sql

Issuing show tables at the mysql prompt lists the newly created tables for the schema.

With the database established and the schema created, we can now put both Hibernate and Middlegen through their paces.