Josemar Furegatti de Abreu Silva: agosto 2013

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.

Josemar Furegatti de Abreu Silva

quinta-feira, 1 de agosto de 2013

Database to Grails Reverse Engineering