Software developers are typically rather laid back and "chill" people. However, when the conversation turns to programming languages, web frameworks, development tools etc. things can get a little heated. This can be especially true when it comes to their IDE of choice. I have seen some very polite and mellow people turn into a software developer incarnation of Bill O'Reily (Fox News) while defending their choice of IDE.

I am not here to argue the merits of one IDE or another, but rather to write about a little bit of experimentation that I had done with Eclipse recently. I was prompted to check into Eclipse's support of JPA after a former co-worker (and Netbeans fan) of mine raved about how he was able to reverse-engineer a rather large database into JPA annotated Entities in a few minutes. It is fair to say that Netbeans has made some big improvements lately, so I just wanted to see if Eclipse is keeping up. Being somewhat partial to Eclipse myself, I am glad to report that Eclipse is matching Netbeans stride for stride. (At least in this case)

The Eclipse project that supports the JPA technology is code-named "Dali" after the possibly hallucinogen induced painting "The Persistence of Memory" by the Spanish painter Salvador Dali. Eclipse's Dali project is actually a sub-project of the Eclipse Web Tools Platform (WTP) which has a bunch of stuff that helps out with various Java web development tasks. Anyway.....

• Ubuntu Linux 7.04
• Eclipse 3.3 Europa (Which has WTP pre-installed)
• MySQL 5.0
• MySQL Connector J JDBC Driver
• Glassfish App Server

In the spirit of April, and the NBA playoffs, I decided to go with an NBA themed database. It has the typical Many-To-Many situation with an intersection table as the middle man. The basic idea behind it is that a nba_player will play for one or many nba_teams in his career, and a nba_team must have many nba_team_members. I figured this would be a rigorous enough test of the Dali JPA reverse engineering capabilities. Below are two MySQL SQL scripts. The first creates the MySQL users, database and necessary grant permissions. The second creates the database tables. I have also shown the output you should see when you execute the scripts. Something that gave me a hard time is that for some funky reason the table names must be all in lower case. If tables names are in mixed case the relationships will not be discovered during the reverse engineering process. During my experimentation I discovered that the Middlegen docs give a warning about this, so I am guessing that the Eclipse DALI plugin uses Middlegen under the covers. The MySQL engine should also be INNODB.

Script to create the MySQL Schema, Users and Grants: create-demo-db.sql

# Create the actual database
CREATE DATABASE demo_app CHARACTER SET utf8 
COLLATE utf8_general_ci;

# Create the Admin User and grant privlidges
GRANT SELECT,
	INSERT,
	UPDATE,
	DELETE,
	CREATE,
	CREATE ROUTINE,
	ALTER ROUTINE,
	CREATE VIEW,
	DROP,
	EXECUTE,
	ALTER
	ON demo_app.* TO 'demo-admin'@'localhost' 
	IDENTIFIED BY 'mysql123';
	
# Create the User that will be used
# for webapp and grant privlidges
GRANT SELECT,
	INSERT,
	UPDATE,
	DELETE ON demo_app.* TO 'demo-web'@'localhost' 
	IDENTIFIED BY 'mysql123';
	
#Must flush in order to grants to be set	
flush privileges;
    

Script Output when executed.
(Use MySqls ’show databases’ command to verify)

Script to create the database tables: create-demo-db-tables.sql

DROP TABLE IF EXISTS demo_app.nba_team_member;
DROP TABLE IF EXISTS demo_app.nba_team;
DROP TABLE IF EXISTS demo_app.nba_player;
#Table for all NBA Franchises
CREATE TABLE nba_team (
  PK_NBA_TEAM_INTR_NO INT(10) NOT NULL 
            AUTO_INCREMENT PRIMARY KEY,
  TEAM_MASCOT VARCHAR (30) NOT NULL UNIQUE,
  TEAM_HOME_CITY VARCHAR (30) NOT NULL
) ENGINE=INNODB;

#Table for all NBA players	
CREATE TABLE nba_player (
  PK_NBA_PLYR_INTR_NO INT(10) NOT NULL 
            AUTO_INCREMENT PRIMARY KEY,
  NBA_PLYR_FST_NAME VARCHAR (30) NOT NULL,
  NBA_PLYR_LST_NAME VARCHAR (30) NOT NULL,
  NBA_PLYR_MID_NAME VARCHAR (40)
) ENGINE=INNODB;

#Intersection table to handle many to many relationship
#A team has many players
#A player can play for multiple teams during his career
CREATE TABLE nba_team_member (
 PK_NBA_TEAM_MEM_INTR_NO INT(10) NOT NULL 
              AUTO_INCREMENT PRIMARY KEY,
 FK_NBA_PLYR_INTR_NO INT(10) NOT NULL,
 FK_NBA_TEAM_INTR_NO INT(10) NOT NULL,
 PLYR_TEAM_START_DT  DATE,
 PLYR_TEAM_END_DT  DATE,
 INDEX (FK_NBA_PLYR_INTR_NO),
 INDEX (FK_NBA_TEAM_INTR_NO)
) ENGINE=INNODB;

ALTER TABLE nba_team_member ADD CONSTRAINT 
TEAM_MEM_PLYR_FK_CON FOREIGN KEY (FK_NBA_PLYR_INTR_NO) 
REFERENCES nba_player(PK_NBA_PLYR_INTR_NO) 
ON DELETE RESTRICT;

ALTER TABLE nba_team_member ADD CONSTRAINT 
TEAM_MEM_TEAM_FK_CON FOREIGN KEY (FK_NBA_TEAM_INTR_NO) 
REFERENCES nba_team(PK_NBA_TEAM_INTR_NO) 
ON DELETE RESTRICT;
    

Script Output when executed. (Use MySqls ’use databaseName’
and ’show tables’ commands as demonstrated )

Now that we have a MySQL Database created we need to make Eclipse aware of this database. Accomplishing this is a two part process.

The following screen shots and captions will guide you through the process

Window --> Open Perspective --> Database Development Perspective

Right Click on ’Databases’ tree node --> New

Choose ’Generic JDBC Connection’

Choose MySQL JDBC 5.0 Driver and click the Add button

Point Eclipse to MySQL JDBC Driver Path

Enter the JDBC Connection Properties and test connectivily

Now that Eclipse is connected to the MySQL database we can go ahead and create the JPA project. This is also a two step process.

The following screen shots and captions will guide you through the process

File --> New --> Project --> JPA Project

Click the New button in the Target Runtime
section to view this screen

Point Eclipse to Glassfish installation directory

Choose JPA Project options as shown

Continue through wizard and jump to JPA perspective

OK so here comes the really hard part....

The following screen shots and captions will guide you through the process

In JPA Perspective: Right Click on project --> JPA Tools --> Generate Entities

Choose the appropriate JDBC Datasource and Schema

Choose all 3 tables as shown and you are done.