Announcement Announcement Module
Collapse
No announcement yet.
I have created a tutorial for using Spring Roo with an existing database Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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

  • #2
    Nice writeup! This is certainly useful for Roo users.

    -Stefan

    Comment


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

      Comment


      • #4
        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:

        Code:
        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'

        Comment


        • #5
          roo orm

          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

          Comment


          • #6
            Create 'roo scripts automatically

            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
            [email protected]

            Comment


            • #7
              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.

              Comment


              • #8
                Does anyone have the script for Oracle database? Thanks!

                Comment


                • #9
                  Originally posted by xiongfeng17 View Post
                  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.

                  Comment

                  Working...
                  X