I have created a tutorial for using Spring Roo with an existing database

[tomchikoore]

All,

I have created a tutorial for using Spring Roo with an existing database. My method is very simple and is based on the suggestions that I have read on this forum and also my understanding of the Spring Roo architecture. I would only encourage those who are curious or are desperate to use this method. For the rest, especially those working on a serious serious project, I would recommend that you wait for the what Ben is working on to address the Jira issue ROO-453 (https://jira.springsource.org/browse/ROO-435). You can find my tutorial here:

http://tomchikoore.com/2010/03/19/tu...ting-database/

Tom

[Stefan Schmidt]

Nice writeup! This is certainly useful for Roo users.

-Stefan

[tomchikoore]

Thanks Stefan. I am anxiously waiting for the solution to Jira issue ROO-453. That will definitely be the way to go.

[jbbarquero]

I've done a similar query for DB2 that fits our needs.

The ELT and FIELD MySQL functions has a difficult translation to DB2. I've used CASE-WHEN and POSITION respectively.

For POSITION I only use the Types that we are used to use. Feel free to add the ones that you need (see DB2 documentation)

For the ones that lacks, I use a Spanish expression ('puaj') in order to make Roo to fail.

I am not a DB2 expert (and it seems I'm not a good programmer either), so you will find extra whitespaces and maybe some other errors.

WHERE TBNAME = 'TABLE' is what you need to change (maybe you need permissions to read SYSIBM.SYSCOLUMNS, but I have no problems with that)

Here it is:

select
	concat(
		concat(
			concat(
				concat(
					concat(
						concat(
							'field '
							,
							CASE 
								POSITION(
									RTRIM(COLTYPE),'INTEGERSMALLINTFLOATCHARVARCHARDECIMALDATETIMETIMESTMP',CODEUNITS16
								)
							WHEN 1 THEN 'number'
							WHEN 8 THEN 'number'
							WHEN 16 THEN 'number'
							WHEN 21 THEN 'string'
							WHEN 25 THEN 'string'
							WHEN 32 THEN 'number'
							WHEN 39 THEN 'date'
							WHEN 43 THEN 'date'
							WHEN 47 THEN 'date'
							ELSE 'puaj'
							END
						)
						,
						concat(' --fieldName ',LOWER(RTRIM(NAME)))
					)
					,
					COALESCE(NULLIF(COALESCE(NULLIF(NULLS,'N'),' --notNull '), 'Y'), '  ')
				)
				,
				CASE 
					POSITION(
						RTRIM(COLTYPE),'CHARVARCHAR',CODEUNITS16
					)
				WHEN 1 THEN concat(concat(' --sizeMax ',CHAR(LENGTH)),' ')
				WHEN 5 THEN concat(concat(' --sizeMax ',CHAR(LENGTH)),' ')
				ELSE ' '
				END
			)
			,
			CASE 
				POSITION(
					RTRIM(COLTYPE),'INTEGERSMALLINTFLOATCHARVARCHARDECIMALDATETIMETIMESTMP',CODEUNITS16
				)
			WHEN 1 THEN ' --type java.lang.Integer '
			WHEN 8 THEN ' --type java.lang.Integer '
			WHEN 16 THEN ' --type java.lang.Integer '
			WHEN 21 THEN ' --type java.lang.String '
			WHEN 25 THEN ' --type java.lang.String '
			WHEN 32 THEN ' --type java.lang.Float '
			WHEN 39 THEN ' --type java.util.Date '
			WHEN 43 THEN ' --type java.util.Date '
			WHEN 47 THEN ' --type java.util.Date '
			ELSE 'puaj'
			END
		)
		,
		concat(' --column ',RTRIM(NAME))
	)
from SYSIBM.SYSCOLUMNS cols 
WHERE 
	TBNAME = 'TABLE'
	AND TBCREATOR = 'GRUDES'

[Cristianmontes]

i'm working in postgres, then i need to change some things but i think it could help to many people.

SELECT
'field '
||
CASE
POSITION(RTRIM(udt_name) IN 'int4numericbpcharvarchardate')
WHEN 1 THEN 'number'
WHEN 5 THEN 'number'
WHEN 12 THEN 'string'
WHEN 18 THEN 'string'
WHEN 25 THEN 'date'
ELSE 'puaj'
END
||
' --fieldName ' || LOWER(RTRIM(column_name))
|| COALESCE(NULLIF(COALESCE(NULLIF(is_nullable,'NO'), ' --notNull '), 'YES'), ' ')
||
CASE
POSITION(RTRIM(udt_name) IN 'bpcharvarchar')
WHEN 1 THEN ' --sizeMax ' || character_maximum_length || ' '
WHEN 7 THEN ' --sizeMax ' || character_maximum_length || ' '
ELSE ' '
END
||
CASE
POSITION(RTRIM(udt_name) IN 'int4numericbpcharvarchardate')
WHEN 1 THEN ' --type java.lang.Integer '
WHEN 5 THEN ' --type java.lang.Float '
WHEN 12 THEN ' --type java.lang.String '
WHEN 18 THEN ' --type java.lang.String '
WHEN 25 THEN ' --type java.util.Date '
ELSE 'puaj'
END
|| ' --column ' || RTRIM(column_name)
|| ' --class ~.model.' || UPPER(SUBSTRING(RTRIM(c.table_name) from 1 for 1)) || LOWER(SUBSTRING(RTRIM(c.table_name) from 2 for LENGTH(RTRIM(c.table_name))))

FROM
information_schema.tables AS t
JOIN
information_schema.columns AS c ON
t.table_catalog=c.table_catalog AND
t.table_schema=c.table_schema AND
t.table_name=c.table_name
WHERE
TABLE_TYPE='BASE TABLE'
AND c.TABLE_SCHEMA='public' ;


"field number --fieldName id_opcion --notNull --type java.lang.Integer --column id_opcion --class ~.model.Opcion"
"field string --fieldName codigo --sizeMax 5 --type java.lang.String --column codigo --class ~.model.Opcion"
"field string --fieldName descripcion_corta --sizeMax 10 --type java.lang.String --column descripcion_corta --class ~.model.Opcion"
"field string --fieldName descripcion_larga --sizeMax 50 --type java.lang.String --column descripcion_larga --class ~.model.Opcion"
"field number --fieldName id_almacen --notNull --type java.lang.Integer --column id_almacen --class ~.model.Almacen"


SELECT
'entity --class ~.model.' || UPPER(SUBSTRING(RTRIM(tc.table_name) from 1 for 1)) || LOWER(SUBSTRING(RTRIM(tc.table_name) from 2 for LENGTH(RTRIM(tc.table_name))))
|| ' --identifierField ' || ccu.column_name || ' --identifierColumn ' || ccu.column_name || ' --table ' || UPPER(SUBSTRING(RTRIM(tc.table_name) from 1 for 1)) || LOWER(SUBSTRING(RTRIM(tc.table_name) from 2 for LENGTH(RTRIM(tc.table_name))))

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON
tc.CONSTRAINT_CATALOG=ccu.CONSTRAINT_CATALOG AND
tc.CONSTRAINT_SCHEMA=ccu.CONSTRAINT_SCHEMA AND
tc.CONSTRAINT_NAME=ccu.CONSTRAINT_NAME AND
tc.TABLE_CATALOG=ccu.TABLE_CATALOG AND
tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA AND
tc.TABLE_NAME=ccu.TABLE_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.CONSTRAINT_SCHEMA = 'public'


"entity --class ~.model.Almacen --identifierField id_almacen --identifierColumn id_almacen --table Almacen"
"entity --class ~.model.Categoria --identifierField id_categoria --identifierColumn id_categoria --table Categoria"
"entity --class ~.model.Comprobante --identifierField id_comprobante --identifierColumn id_comprobante --table Comprobante"
"entity --class ~.model.Detallemovimiento --identifierField id_detallemovimiento --identifierColumn id_detallemovimiento --table Detallemovimiento"

then i think i'm ready to begin with my job.

if you find some mistake please tell me.
regards
Cristian Montes

[MikeDolan]

I have generated stored procedures and queries to automatically create roo scripts from an existing database.

Currently they are available for both MySql and Postgres. They create everything, Tables, Columns, Controllers etc. and properly handle things like foreign keys. They also handle the naming changes automatically. For example, where Hibernate assumes column names to be similar to big_field_name, and your application refers to bigFieldName or BigTableName, the names are correctly converted, saving you time & errors. They also do things like automatically identify primary keys, and correctly create and name them as a part of the Table generation function.

If anyone wants them, just let me know and I will be happy to send them to you.

Also if anyone wants, I can create similar functionality for SqlServer.

Regards,
Mike Dolan
mike@dolanclan.com

[Andrew Swan]

N.B. it's ROO-435, not ROO-453.

In any case, here's another approach that will work with any type of database:

http://forum.springsource.org/showthread.php?t=90950

As the author I'm biased, but to me it looks simpler (at least if you know Velocity's VTL syntax) than the script approach described above.

[xiongfeng17]

Does anyone have the script for Oracle database? Thanks!

[Andrew Swan]

Does anyone have the script for Oracle database? Thanks!

You shouldn't need any of the workarounds described in this thread now that ROO-435 (DBRE) has been implemented. If you have any problems using DBRE, please search the forums and start a new thread if necessary.