quinta-feira, 1 de agosto de 2013

Database to Grails Reverse Engineering

1. Introduction

This tool DatabaseToGrailsReverseEngineer  (databasetograilsreverseengineer.jar)  was designed to automatizate Grails Class building activity. DatabaseToGrailsReverseEngineer connect to target database ( Oracle, SQL Server, MySQL* and DB2 400* ) using JDBC driver, reads table specifications and generates Grails Class File .groovy.

* Under development

2. Basic explanation

2.1. What I would like when I did this

  • Suppose you would like to build Grails web CRUD applications based on a database table
  • So you think, everyting is defined and declared on database, columns, data-types, nullable coluns, primary key, alternate keys,etc
  • It would be fine if you could extract and build Grails Class Definition, based on database
  • It would be better if  you could ignore any table prefix
  • It would be better if you could automaticaly convert to Grails naming convension, ie "ClassName", "attributeName", etc
  • It would be better if you could automaticaly converte toString() method, always the table has a primary key defined as integer and there is another alternate key on table.

2.2. What do I need to run DatabaseToGrailsReverseEngineer ?

  • databasetograilsreverseengineer.jar
  • JSAP-2.0a.jar
  • JDBC driver to Oracle, SQL Server, MySQL

2.3. What are command line parameters?

C:\> java -cp .;databasetograilsreverseengineer.jar;JSAP-2.0a.jar  br.com.databasetograilsreverseengineer.ClassBuilder -h

DatabaseToGrailsReverseEngineer.ClassBuilder v1.0.00 allows you to build a Grails Class from a Database Table.
  [-h|--help]
        Print help message

  -d <db-type>
        JDBC database type ['orcl': Oracle, 'sqlserver': Microsoft SQLServer,
        'mysql': MySql, 'db2_400' : DB2/400].

  [-j <jdbc-driver>]
        JDBC driver class name. Defaults:
        - for Oracle   : "oracle.jdbc.OracleDriver'
        - for MySQ     : "com.mysql.jdbc.Driver"
        - for SQLServer: "com.microsoft.sqlserver.jdbc.SQLServerDriver
        - for DB2/400  : "com.ibm.as400.access.AS400JDBCDriver"

  -u <url>
        JDBC url for database connection. Examples:
        - for Oracle   : "jdbc:oracle:thin:127.0.0.1:1521:orcldb '
        - for MySQL    : "jdbc:mysql://127.0.0.1/mydb"
        - for SQLServer: "jdbc:sqlserver://127.0.0.1:1433;databaseName=sqldb;
        - for DB2/400  : ""

  -l <login>
        Database user to connect.

  -x <password>
        Database user password.

  -o <table-owner>
        Table Owner of database Schema.

  -t <table-name>
        Table Name.

  [-i <table-name-prefix-to-ignore>]
        Table Name prefix to igonre. Example: "-t MYOWNER.TAB_CUSTOMER_PERSON -i
        MY_OWNER.TAB_"  generates a class "CustomerPerson"

  [-f <file-name>]
        Force full path and file-name specification. Default is
        <ClassName>.groovy.

  [-p <grails-package-name>]
        Grails Package Name specification. Example:
        br.com.databasetograilsreverseengineer

  [-v <verbose>]
        Verbose level. [0:no-verbose, 1: low, 2:medium; 3:high]. (default: 0)

Usage: DatabaseToGrailsReverseEngineer.ClassBuilder [-h|--help] -d <db-type> [-j
 <jdbc-driver>] -u <url> -l <login> -x <password> -o <table-owner> -t <table-nam
e> [-i <table-name-prefix-to-ignore>] [-f <file-name>] [-p <grails-package-name>
] [-v <verbose>]

2.4. Let me see class generated

class Person {

Long   id
String fullName
Date   birthdayDate
int    numberOfChildren
Double avgAnnualIncome

static mapping = {
table 'tab_person'
columns{
id               column: 'person_id'
fullName         column: 'full_name'
birthdayDate     column: 'birthday_date'
numberOfChildren column: 'number_of_children'
avgAnnualIncome  column: 'avg_annual_income'
}
version false
}

static constraints = {
id               ()
fullName         (blank:false, unique:true, maxSize: 255)
birthdayDate     (nullable:true)
numberOfChildren (nullable:true)
avgAnnualIncome  (nullable:true, scale: 2)

}

String toString() {
return "${fullName}"
}

}

/*

  tab_person
    person_id          int(10) PRIMARY KEY[PK__tab_pers__543848DF628582E0]
    full_name          varchar(255) UNIQUE[ak_tab_person]
    birthday_date      date(0) NOT NULL
    number_of_children smallint(5) NOT NULL
    avg_annual_income  decimal(18,2) NOT NULL

 */

3. Examples

3.1. SQLServer database reverse engineering to Grails Class

Step#1. Supose you have a SQL SERVER table called "tab_person" like this:


CREATE TABLE [dbo].[tab_person](
 [person_id] [int] IDENTITY(1,1) NOT NULL,
 [full_name] [varchar](255) NOT NULL,
 [birthday_date] [date] NULL,
 [number_of_children] [smallint] NULL,
 [avg_annual_income] [decimal](18, 2) NULL,
 PRIMARY KEY CLUSTERED 
 (
   [person_id]
 )
)
GO 

ALTER TABLE [dbo].[tab_person]
ADD CONSTRAINT ak_tab_person UNIQUE ( [full_name] ); 
GO


Step#2. Supose you've downloaded DatabaseToGrailsReverseEngineer , JSAP-2.0a.jar requried library and SQL Server driver compatible with your server (sqljdbc4.jar

C:\Users\Josemar Silva\Google Drive\My Projects\database-to-grails-reverse-engin
eer\dist>dir
 O volume na unidade C é INMETRICS
 O Número de Série do Volume é FC43-483A

 Pasta de C:\Users\Josemar Silva\Google Drive\My Projects\database-to-grails-rev
erse-engineer\dist

01/08/2013  11:06    <DIR>          .
01/08/2013  11:06    <DIR>          ..
01/08/2013  09:38             9.885 databasetograilsreverseengineer.jar
22/01/2012  17:10            69.315 JSAP-2.0a.jar
17/01/2012  12:56           789.885 mysql-connector-java-5.1.18-bin.jar
13/04/2012  10:39         2.714.189 ojdbc6.jar
01/08/2013  10:50               827 run_me_by_sample.bat
24/07/2013  21:39           584.207 sqljdbc4.jar


Step#3. Now, you user DatabaseToGrailsReverseEngineer to generate Grails Class from SQL SERVER table 'tab_person', ignoring prefix "tab_", 

C:\> java -cp .;databasetograilsreverseengineer.jar;JSAP-2.0a.jar;sqljdbc4.jar br.com.databasetograilsreverseengineer.ClassBuilder -d sqlserver -u jdbc:sqlserver://127.0.0.1:1433;databaseName=grails -l grails -x grails -o dbo -t tab_person -i tab -v 0

C:\> dir Person_Sqlserver.groovy
01/08/2013  11:22               863 Person_Sqlserver.groovy


Step#4. Observe the result typing Grails class file generated <ClassName>.groovy

C:\> type Person.groovy

 2.2. Oracle database reverse engineering to Grails Class

Step#1. Supose you have a ORACLE table called "TAB_PERSON" like this:

CREATE TABLE TAB_PERSON 
(
  PERSON_ID NUMBER NOT NULL ENABLE, 
  FULL_NAME VARCHAR2(255) NOT NULL ENABLE, 
  BIRTHDAY_DATE DATE, 
  NUMBER_OF_CHILDREN NUMBER(*,0), 
  AVG_ANNUAL_INCOME NUMBER(18,2), 
  CONSTRAINT PK_PERSON PRIMARY KEY (PERSON_ID)
) ;

ALTER TABLE TAB_PERSON ADD 
CONSTRAINT AK_PERSON UNIQUE (FULL_NAME) ;


Step#2. Supose you've downloaded DatabaseToGrailsReverseEngineer , JSAP-2.0a.jar requried library and SQL Server driver compatible with your server (ojdbc6.jar

C:\Users\Josemar Silva\Google Drive\My Projects\database-to-grails-reverse-engin
eer\dist>dir
 O volume na unidade C é INMETRICS
 O Número de Série do Volume é FC43-483A

 Pasta de C:\Users\Josemar Silva\Google Drive\My Projects\database-to-grails-rev
erse-engineer\dist

01/08/2013  11:06    <DIR>          .
01/08/2013  11:06    <DIR>          ..
01/08/2013  09:38             9.885 databasetograilsreverseengineer.jar
22/01/2012  17:10            69.315 JSAP-2.0a.jar
17/01/2012  12:56           789.885 mysql-connector-java-5.1.18-bin.jar
13/04/2012  10:39         2.714.189 ojdbc6.jar
01/08/2013  10:50               827 run_me_by_sample.bat
24/07/2013  21:39           584.207 sqljdbc4.jar


Step#3. Now, you user DatabaseToGrailsReverseEngineer to generate Grails Class from ORACLE table 'TAB_PERSON', ignoring prefix "TAB_", 

C:\java -cp .;databasetograilsreverseengineer.jar;JSAP-2.0a.jar;ojdbc6.jar
 br.com.databasetograilsreverseengineer.ClassBuilder -d orcl -u jdbc:oracle:thin
:@10.10.11.13:1521:PROD -l GRAILS -x GRAILS -o GRAILS -t TAB_PERSON -i TAB_

C:\> dir Person.groovy
01/08/2013  11:33               716 Person.groovy




Step#4. Observe the result typing Grails class file generated <ClassName>.groovy

C:\> type Person.groovy


 3. Downloads

  • DatabaseToGrailsReverseEngineer  is available for download here!
  • (SQLSERVER  || ORACLE || MYSQL || DB2-400 ) JDBC drivers, strongly recommended you download from factory.

Nenhum comentário:

Postar um comentário