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.