Utilize JDeveloper and SchemaSpy to maintain database schema & document

Scenario
1. I need to change database schema frequently, and add comments for each column (it does not have any comments originally).

2. I have to deliver the up-to-date schema document to customer.

Solution

1. Use JDeveloper to maintain database schema and add comments for each column.

2. Use SchemaSpy to generate the up-to-date database schema [Concerning SchemaSpy, please refer to http://albert-myptc.blogspot.com/2010/12/schemaspy-quick-start.html]

Prerequisite

1. Download JDeveloper from http://www.oracle.com/technetwork/developer-tools/jdev/downloads/soft11-098086.html

2. Launch JDeveloper after you installed it

3. JDeveloper workspace

4. Setup database connection

5. Check the connection in Database Navigator

5.Create a new Project

6. Choose "Generic Project", and click OK

7. Click Finish

8. Project had been created

9. Create Database Diagram from project

Before change

I would like to add three columns to NIGT036, and add comments for each column.

Use JDeveloper to change database schema

1. Select specific table --> Column --> Add

2. Assign "Column Name", "Data Type", "Precision", and then click Apply

3. Check the result

4. Add comments for each column

5. Assign Comment and then click Apply

Generate the latest schema document via SchemaSpy

1. execute the command:

java -jar "C:\schemaSpy_5.0.0.jar" -dp "C:\ojdbc6.jar" -t orathin -db dev02 -s ap_tax -host 192.168.30.102 -port 1521 -u ap_tax -p taxtest -schemas AP_TAX -connprops "C:\schemaSpyGUI\properties\orathin.properties" -i "NIG.*" -charset UTF-8 -o "C:\schemaSpyGUI\output" -norows -hq

You can get more information about this command from http://schemaspy.sourceforge.net/

2. check the result

Java to UML via NetBeans and IBM Rational Software Architect(RSA)

Scenario

I have 80+ tables in my system, and need to deliver class diagram before deadline. Even worse, the schema keep changing. I need to find an automation approach to resolve this problem.

Solution

1. Create table in database

2. Use NetBeans to create entities and DAOs.

3. Import into RSA, and create entities/DAOs UML symbo from Java classes

(If schema change in the future, just repeat step2 and step3)

Process

Create database connection in NetBeans

1. Create connection

2. Add jdbc jar file and click OK

3. Provide connection information

4. Assign schema name

5. Done

Use code generation from NetBeans

1. New --> Entity Classes from Database...

2. select database connection, and select tables which we would like to do code generation

3. Assign Java package

4. Assign association fetch and collection type, then click Next

5. Generating

6. Entities had been generated

7. New --> JSF Pages from Entities Classes

8. Select entity classes, and click Next

9. assign packages

10. Generating DAOs...

11. DAO classes

12. Owing to the naming convention doesn't meet our need, so we need to do refactor

13. DAO classes generation process is done.

Java to UML via IBM Rational Software Architect(RSA)

1. File --> Project

2. Select Java Project in RSA

3. assign project name and click Next

4. Click Finish

5. Create dao and entity package

6. Change to Modeling perspective

7. Right click --> Transform --> New Configuration

8. Select Java to UML

9. Select source and target, then click Finish

10. Click Run

11. Generating...

12. Finish

SchemaSpy Quick Start

Introduction

SchemaSpy is a Java-based tool (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. It's also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints.

Running SchemaSpy

You run SchemaSpy from the command line: java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir

For further information, please check: http://schemaspy.sourceforge.net/

Execution Process

1. Download SchemaSpy jar file from http://sourceforge.net/projects/schemaspy/files/schemaspy/

2. Download JDBC jar file from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

3. execute a command:

java -jar "C:\schemaSpy_5.0.0.jar" -dp "C:\ojdbc6.jar" -t orathin -db dev02 -s ap_tax -host 192.168.30.102 -port 1521 -u ap_tax -p taxtest -schemas AP_TAX -connprops "C:\schemaSpyGUI\properties\orathin.properties" -charset UTF-8 -o "C:\schemaSpyGUI\output" -norows -hq

4. check the output

java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path

Scenario

As I run SchemaSpy and use this command line:

java -jar "C:\schemaSpy_5.0.0.jar" -dp "C:\ojdbc6.jar" -t ora -db dev02 -s ap_tax -host 192.168.30.102 -port 1521 -u ap_tax -p taxtest -schemas AP_TAX -connprops "C:\schemaSpyGUI\properties\ora.properties" -charset UTF-8 -o "C:\schemaSpyGUI\output" -norows -hq

I got this error message:

 Failed to load driver [oracle.jdbc.driver.OracleDriver] from classpath [file:/C:/ojdbc6.jar]  
 Make sure the reported library (.dll/.lib/.so) from the following line can be  
 found by your PATH (or LIB*PATH) environment variable  
 java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path  
 at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1734)  
 at java.lang.Runtime.loadLibrary0(Runtime.java:823)  
 at java.lang.System.loadLibrary(System.java:1028)  
 at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3535)  
 at java.security.AccessController.doPrivileged(Native Method)  
 at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3531)  
 at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:266)  
 at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:536)  
 at oracle.jdbc.driver.T2CConnection.(T2CConnection.java:162)  
 at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)  
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)  
 at net.sourceforge.schemaspy.SchemaAnalyzer.getConnection(SchemaAnalyzer.java:582)  
 at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:157)  
 at net.sourceforge.schemaspy.Main.main(Main.java:42) 

Root Cause

I assign wrong database type and connection property in command line.

Solution

Change database type from ora to orathin:

java -jar "C:\schemaSpy_5.0.0.jar" -dp "C:\ojdbc6.jar" -t orathin -db dev02 -s ap_tax -host 192.168.30.102 -port 1521 -u ap_tax -p taxtest -schemas AP_TAX -connprops "C:\schemaSpyGUI\properties\orathin.properties" -charset UTF-8 -o "C:\schemaSpyGUI\output" -norows -hq