EclipseUML 2007 reverses of Oracle 10g

This article is about how to reverse an Oracle 10g database inside Eclipse and use EclipseUML 2007 Free Edition in order to create a UML 2.1 model.

You first need to install:

  • EclipseUML Free Edition 2007
  • WTP 2.0
  • Dali

You then need an Oracle 10 database and the java connector: ojdbc14.jar

The Toplink persistence file can be downloaded at:

The following tutorial includes 4 steps:

  1. create an Oracle Database using the script
  2. Connect the Database
  3. Generate java classes and entities
  4. Reverse JPA project

1. We are going to use the following database script to create the Oracle Database:

drop table webstock.INVENTAIRE;

drop table webstock.ARTICLE;

drop table webstock.FOURNISSEUR;

drop table webstock.EMPLOYE;

drop table webstock.CLIENT;

drop table webstock.WEBSTOCKACCESS;

drop table webstock.CATEGORIE;

CREATE TABLE webstock.WEBSTOCKACCESS(USERID VARCHAR(5) NOT NULL PRIMARY KEY, NOMUSER VARCHAR(15) NOT NULL,

MOTDEPASSE VARCHAR(15),

CONSTRAINT SYS_STOCKACCESS UNIQUE(NOMUSER)

);

CREATE TABLE webstock.EMPLOYE (EMPLOYEID VARCHAR (5) NOT NULL PRIMARY KEY, USERID VARCHAR (5) NOT NULL,

EMPLOYENOM VARCHAR (15) NOT NULL, EMPLOYEPRENOM VARCHAR (10) NOT NULL, ADRESSE VARCHAR (50),

SALAIRE DECIMAL (10,2) NOT NULL,

CONSTRAINT FK_EMPLOYE

FOREIGN KEY (USERID)

REFERENCES webstock.WEBSTOCKACCESS (USERID)

);

CREATE TABLE webstock.FOURNISSEUR (FOURNISSEURID VARCHAR (5) NOT NULL PRIMARY KEY, USERID VARCHAR (5) NOT NULL,

FOURNISSEURNOM VARCHAR (15) NOT NULL, FOURNISSEURPRENOM VARCHAR (15) NOT NULL, ADRESSE VARCHAR (50),

LIMITECREDIT DECIMAL (10,2) NOT NULL,

CONSTRAINT FK_FOURNISSEUR

FOREIGN KEY(USERID)

REFERENCES webstock.WEBSTOCKACCESS(USERID));

CREATE TABLE webstock.CLIENT (CLIENTID VARCHAR(5) NOT NULL PRIMARY KEY, USERID VARCHAR (5), CLIENTNOM VARCHAR (15),

CLIENTPRENOM VARCHAR(10), ADRESSE VARCHAR (50), TEL VARCHAR(10), COMMENTS VARCHAR (25),

CONSTRAINT FK_CLIENT FOREIGN KEY (USERID) REFERENCES webstock.WEBSTOCKACCESS (USERID));

CREATE TABLE webstock.CATEGORIE (ARTICLECATEGORIEID VARCHAR (5) NOT NULL PRIMARY KEY,

ARTICLECATEGORIE VARCHAR (20) NOT NULL);

CREATE TABLE webstock.ARTICLE (ARTICLEID VARCHAR (5) NOT NULL PRIMARY KEY,

NOMARTICLE VARCHAR (30) NOT NULL,ARTICLECATEGORIEID VARCHAR (5),

FOURNISSEURID VARCHAR (5), DESCRIPTION VARCHAR (50),

POIDS DECIMAL (10,2), IMAGE_URL VARCHAR (50),

CONSTRAINT FK_ARTICLE FOREIGN KEY (FOURNISSEURID) REFERENCES webstock.FOURNISSEUR (FOURNISSEURID),

CONSTRAINT FK_CATEGORIE FOREIGN KEY(ARTICLECATEGORIEID) REFERENCES webstock.CATEGORIE(ARTICLECATEGORIEID));

CREATE TABLE webstock.INVENTAIRE (NUMEROINVENTAIRE NUMBER NOT NULL PRIMARY KEY, ARTICLEID VARCHAR (5),

QUANTITE INT NOT NULL, PRIX DECIMAL (8,2),

RAYON VARCHAR (2) NOT NULL, REGION VARCHAR (15) NOT NULL,

CONSTRAINT FK_INVENTAIRE FOREIGN KEY (ARTICLEID) REFERENCES webstock.ARTICLE(ARTICLEID));

CREATE TABLE webstock.COMMANDE (COMMANDEID INT NOT NULL PRIMARY KEY, CLIENTID VARCHAR(5), DATECOMMANDE DATE NOT NULL, MONTANT DECIMAL (10,2),

CONSTRAINT FK_COMMANDE FOREIGN KEY (CLIENTID) REFERENCES webstock.CLIENT (CLIENTID));

2. How toconnect the Database

You need to switch to JPA Development perspective.

Go to Window > Open Perspective > Other > JPA Development

You switch to the following perspective including three views (JPA Structure, Data Source Explorer and JPA details)

Connect Eclipse with the Oracle 10g database

Select the Data Source Explorer and click on Database > New

Select SQL Model-JDBC Connection and click on the Next button

Enter the Database nameand click on the Next button

Select a driver

Select the Oracle 10 driver click on the Add button, and finally click on OK.

SelectOracle Thin Driverand click on the OK button

Add the jar driver

Click on the Edit Jar/Zip button

We needed to change:

  • server by Host
  • db by the name of the database

Here are the new properties

Click on the OK button

Test the connection. « Ping succeeded »should be displayed. Click on the Finish button.

SelectOracle in the Data Source Explorer

Click on oracle to open the contextual menu > connect

Visualize Oracle 10g in the database source explorer.

  1. Generate the java code from your Database

You need to create a new JPA Project.

Click in the Package explorer to open the contextual menu > New project > JPA > JPA Project and click on the Next button.

Enter the name of the project, then select the configuration and click on the Next button

SelectConfigure User Librairies…

Add the TopLink jar file by selecting the toplink-essentials.jar file and click on the OK button

Select JPA Project preferences as below and click on the finish button.

The following project has been created

We are going to generate java classes from the Oracle 10g database

Right clickon the Webstock projectJPA Tools >Generate Entities…

Select the Package name where the entities will be generated and click on the finish button.

You get the following project in the package explorer

Many classes are with errors because classes doesn’t existe in the persistence.xml. We need to synchronize the database with the java java code.Click on the persistence.xml file and select JPA Tools > Synchronize Classes

You now get a clean java code project from your Oracle 10g database.

The persistence.xml files contains all the mapping information.

  1. Reverse the Oracle Database inside EclipseUML 2007

Select the project in the Package Explorer to open the contextual menu > UML > Create/Update UML Model

Select the project to reverse and click on the Next button.

Select Annotations (EJB3) in order to activate the annotation reverse and click on the Finish button.

When you reverse a project, you first display the package level reverse and package dependencies. Double click on the entities package inside the UML Editor in order to see UML information at class level.

Select the association and click on the OK button in order to display all class diagram associations

Below is the Class diagram including Entity.

Please note that you can change the stereotypes and deploy on Hibernate, spring or add webservices from the same UML model coming from Oracle 10g, or oracle 9, 8 or any other database.