Announcement Announcement Module
Collapse
No announcement yet.
Overriding Entity Ids. How? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Overriding Entity Ids. How?

    I wonder if anyone there had succeed on overriding the default Roo Id entity generation.

    Sort of thing you have to do when you want to use Roo with an already established Db. That comes with their own id, foreign key etc.

    Hopefully someone can help on this

    Thx
    Last edited by delgad9; Jun 11th, 2010, 12:18 PM. Reason: Typo

  • #2
    Foreign key handling

    As far as handling existing foreign keys, that is simple: Create your tables in 'roo, then when you define your fields don't define the foreign key fields as you normally would. For example, say you have table A, consisting of Person Id-int, Name-string, Address-string, a foreign key - say 'person_type-int', and Zip-string. Your definitions to 'roo

    would be:
    field string name -class ~.domain.Person -type java.lang.string

    field string address -class ~.domain.Person....

    field reference -class ~.domain.Person -type ~.domain.PersonType

    field string zip -class ~.domain.Person -type

    When you define the reference field, roo looks at the referenced class and determines the correct data type. You simply tell roo it's a foreign key, and it's type is the same as the primary key of the referenced class.

    If you need some facility to automatically determine if a field is a foreign key, I have a stored procedure that does that automatically. Also, if you need to convert database style names to java style names, I have that too.
    Just let me know if you would like these, and I'll send them along.

    Regards,
    Mike Dolan
    [email protected]

    Comment


    • #3
      Existing key's

      Regarding your issue with primary keys, there are several potential issues, for example:
      I use a naming convention similar to 'table_name_id', and typically use an int. By default roo uses a bigint named id. To get around this, you simply define the ID field in your table generation script, similar to this:
      entity --class ~.domain.AccessLog --table access_log --identifierField accessLogId --identifierColumn access_log_id --identifierType java.lang.Integer --testAutomatically

      The --identifierField tells roo what you want to call it in Java,
      the --identifierColumn tells roo what your database calls it,
      and the --identifierType tells roo what kind it is.

      As mentioned in earlier posts, I have queries that generate all this for you, let me know if you would like them.

      The other potential problem is where you generate the key via some custom algorithm, other than an auto-increment or sequence. For this you would need to change the annotation in the Java class (which is really quick & simple), and this lets Hibernate know what you want to do. Hibernate has the capability of handling primary keys in many different ways.

      Regards,
      Mike Dolan
      [email protected]

      Comment


      • #5
        I have scripts for MySql and Postgress, but they are different. What database are you using?

        As far as the annotation, if you open a class – say Person.java, you will see annotations like the following:
        package com.ihealthus.domain;

        import javax.persistence.Entity;
        import org.springframework.roo.addon.javabean.RooJavaBean ;
        import org.springframework.roo.addon.tostring.RooToString ;
        import org.springframework.roo.addon.entity.RooEntity;
        import javax.persistence.Table;
        import javax.validation.constraints.NotNull;
        import javax.persistence.Column;
        import javax.validation.constraints.Size;

        @Entity
        @RooJavaBean
        @RooToString
        @RooEntity(identifierField = "dosageId", identifierColumn = "dosage_id", identifierType = Integer.class)
        @Table(name = "dosage")
        public class Dosage {

        @NotNull
        @Column(name = "dose_form")
        @Size(max = 3)
        private String doseForm;

        @NotNull
        @Column(name = "translation")
        @Size(max = 100)
        private String translation;
        }


        The entries such as @Entity, @RooJavaBean etc, are the annotations I was referring to. With Hibernate, you can configure using either annotations (preferred) or XML files.
        Hibernate, especially in conjunction with Spring allows you to use these simple annotations to tell it what you want.
        There is a good book, ‘Spring Persistence with Hibernate’ that explains a lot of this. The part in particular you are probably interested in is @Id and @Generated value.
        In the case of @GeneratedValue for example, you have several options, such as AUTO, wherein Hibernate chooses the key generation strategy predicated upon your database,
        IDENTITY, which stipulates a MySql or MS Sql Server type identity generation, SEQUENCE, which uses a Postgres or Oracle type of sequence, or TABLE which tells Hibernate to
        Use some specific table to generate the key. You can also use your own key generation function if you need to.

        Hope this helps,

        Regards,
        Mike Dolan

        Comment


        • #6
          Mike,

          MySql...

          Thx

          Comment


          • #7
            MySql scripts

            Here 'ya go....

            1. Create the stored procedures (ProperCase & CamelCase) first.

            2. Create your project - similar to ..
            'project --topLevelPackage com.ihealthus'

            3. Define your database - similar to ..

            /*** Be sure to change the database name server url userid & password to your names
            persistence setup --provider HIBERNATE --database MYSQL

            database properties set --key database.driverClassName --value com.mysql.jdbc.Driver
            database properties set --key database.url --value jdbc:mysql://192.168.1.109:3306/ihealthus
            database properties set --key database.username --value xxx
            database properties set --key database.password --value yyy

            4. Run the table script
            5. Run the column script
            6. Run the controller script
            7. Perform eclipse

            You now have a running program totally reverse engineered from an existing database.

            --------------------------------------------------
            -- Create function ProperCase
            --------------------------------------------------
            create function ProperCase ( str VARCHAR(128)) Returns varchar(128) CHARSET latin1
            BEGIN
            DECLARE c CHAR(1);
            DECLARE s VARCHAR(128);
            DECLARE i INT DEFAULT 1;
            DECLARE bool INT DEFAULT 1;
            DECLARE punct CHAR(18) DEFAULT ' ()[],.-_!@;:?/';
            SET s = LCASE( str );
            WHILE i <= LENGTH( str ) DO
            BEGIN
            SET c = SUBSTRING( s, i, 1 );
            IF LOCATE( c, punct ) > 0 THEN
            SET bool = 1;
            ELSEIF bool=1 THEN
            BEGIN
            IF c >= 'a' AND c <= 'z' THEN
            BEGIN
            SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
            SET bool = 0;
            END;
            ELSEIF c >= '0' AND c <= '9' THEN
            SET bool = 0;
            END IF;
            END;
            END IF;
            SET i = i+1;
            END;
            END WHILE;
            RETURN s;
            END;



            --------------------------------------------------
            -- Create function CamelCase
            --------------------------------------------------
            create function CamelCase ( str VARCHAR(128) ) Returns varchar(128) CHARSET utf8
            BEGIN
            DECLARE c CHAR(1);
            DECLARE s VARCHAR(128);
            DECLARE i INT DEFAULT 1;
            DECLARE bool INT DEFAULT 1;
            DECLARE punct CHAR(18) DEFAULT ' ()[],.-_!@;:?/';
            SET s = LCASE( str );
            WHILE i <= LENGTH( str ) DO
            BEGIN
            SET c = SUBSTRING( s, i, 1 );
            IF LOCATE( c, punct ) > 0 THEN
            SET bool = 1;
            ELSEIF bool=1 THEN
            BEGIN
            IF c >= 'a' AND c <= 'z' THEN
            BEGIN
            IF (i > 1) THEN
            SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
            ELSE
            SET s = CONCAT(LEFT(s,i-1), c,SUBSTRING(s,i+1));
            END IF;
            SET bool = 0;
            END;
            ELSEIF c >= '0' AND c <= '9' THEN
            SET bool = 0;
            END IF;
            END;
            END IF;
            SET i = i+1;
            END;
            END WHILE;
            RETURN s;
            END;



            /* This script will generate a roo script to create your entities, just run it, save the output and feed that to roo via the
            command 'script <whatever you called the output file>

            *** Important *** change the table_schema to reflect your schema as indicated below

            */

            Select concat(
            (
            SELECT
            CASE data_type
            WHEN 'tinyint' THEN 'java.lang.Byte'
            WHEN 'smallint' THEN 'java.lang.Short'
            WHEN 'int' THEN 'java.lang.Integer'
            WHEN 'bigint' THEN 'java.lang.Long'
            WHEN 'decimal' THEN 'java.lang.Double'
            WHEN 'double' THEN 'java.math.BigDecimal'
            WHEN 'float' THEN 'java.lang.Float'
            WHEN 'date' THEN 'java.util.Date'
            WHEN 'timestamp' THEN 'java.sql.Timestamp'
            WHEN 'datetime' THEN 'java.util.Date'
            WHEN 'bit' THEN 'java.lang.Boolean'
            WHEN 'blob' THEN 'java.lang.Blob'
            WHEN 'char' THEN 'java.lang.String'
            WHEN 'varchar' THEN 'java.lang.String'
            WHEN 'longtext' THEN 'java.lang.String'
            END as refType
            ),
            ' --testAutomatically'
            )
            AS '// roo_Entities'

            from information_schema.COLUMNS
            where table_schema ='ihealthus' -- <-************** Change 'ihealthus' to your schema name
            and column_key = 'PRI'
            order by table_name



            /* This script will generate a roo script to create your fields, just run it, save the output and feed that to roo via the
            command 'script <whatever you called the output file>

            *** Important *** change the table_schema to reflect your schema as indicated below
            *** You probably want to change the names of reserved words you will allow, as indicated below
            */

            Select CONCAT(
            COALESCE(
            (

            /** This figures out if the column is a reference field, and handles it accordingly */

            SELECT 'field reference'
            from DUAL
            where (
            SELECT COUNT(*)
            from information_schema.COLUMNS co,
            information_schema.KEY_COLUMN_USAGE kc
            where c.column_key = 'MUL'
            and c.table_schema ='ihealthus'
            and c.table_schema = co.table_schema
            and c.table_name = co.table_name
            and c.column_name = co.column_name
            and co.table_schema = kc.table_schema
            and co.table_name = kc.table_name
            and co.column_name = kc.column_name
            ) > 0
            ),

            /** If it isn't a reference field, treat it normally */
            (
            CASE c.data_type
            WHEN 'tinyint' THEN 'field number'
            WHEN 'smallint' THEN 'field number'
            WHEN 'int' THEN 'field number'
            WHEN 'bigint' THEN 'field number'
            WHEN 'decimal' THEN 'field number'
            WHEN 'double' THEN 'field number'
            WHEN 'float' THEN 'field number'
            WHEN 'date' THEN 'field date'
            WHEN 'timestamp' THEN 'field date'
            WHEN 'datetime' THEN 'field date'
            WHEN 'bit' THEN 'field boolean'
            WHEN 'blob' THEN 'field other'
            WHEN 'char' THEN 'field string'
            WHEN 'varchar' THEN 'field string'
            WHEN 'text' THEN 'field string'
            WHEN 'longtext' THEN 'field string'
            END
            )
            ),

            coalesce(
            (
            SELECT concat(' --type ~.domain.', replace(utils.ProperCase(kc.referenced_table_name) ,'_',''))
            from information_schema.COLUMNS co,
            information_schema.KEY_COLUMN_USAGE kc
            where c.column_key = 'MUL'
            and c.table_schema = 'ihealthus'
            and c.table_schema = co.table_schema
            and c.table_name = co.table_name
            and c.column_name = co.column_name
            and co.table_schema = kc.table_schema
            and co.table_name = kc.table_name
            and co.column_name = kc.column_name
            ),
            (
            CASE c.data_type
            WHEN 'tinyint' THEN ' --type java.lang.Byte'
            WHEN 'smallint' THEN ' --type java.lang.Short'
            WHEN 'int' THEN ' --type java.lang.Integer'
            WHEN 'bigint' THEN ' --type java.lang.Long'
            WHEN 'decimal' THEN ' --type java.math.BigDecimal'
            WHEN 'double' THEN ' --type java.lang.Double'
            WHEN 'float' THEN ' --type java.lang.Float'
            WHEN 'date' THEN ' --type java.util.Date'
            WHEN 'timestamp' THEN ' --type java.util.Date'
            WHEN 'datetime' THEN ' --type java.util.Date'
            WHEN 'bit' THEN ' --type java.lang.Boolean'
            WHEN 'blob' THEN ' --type java.lang.Blob'
            WHEN 'char' THEN ' --type java.lang.String'
            WHEN 'varchar' THEN ' --type java.lang.String'
            WHEN 'text' THEN ' --type java.lang.String'
            WHEN 'longtext' THEN ' --type java.lang.String'
            END
            )
            ),

            ' --fieldName ', replace(utils.camelCase(column_name),'_',''),
            ' --class ', replace(utils.ProperCase(table_name),'_',''),
            ' --column ', column_name
            ,
            (
            SELECT
            CASE is_nullable THEN 'YES' THEN ''
            ELSE ' --notNull '
            END
            ),

            -- sizeMax
            (
            SELECT
            CASE data_type
            WHEN 'char' THEN coalesce(concat(' --sizeMax ', character_maximum_length),' ')
            WHEN 'varchar' THEN coalesce(concat(' --sizeMax ', character_maximum_length),' ')
            ELSE ''
            END
            ),

            --digitsInteger
            (
            SELECT
            CASE data_type
            WHEN 'decimal' THEN concat(' --digitsInteger ', numeric_precision)
            ELSE ''
            END
            ),

            --digitsFraction
            (
            SELECT
            CASE data_type
            WHEN 'decimal' THEN concat(' --digitsFraction ', numeric_scale)
            ELSE ''
            END
            ),
            --comments
            (
            SELECT
            CASE column_comment
            WHEN '' THEN ''
            ELSE concat(' --comment ','"',trim(column_comment),'"')
            END
            ),

            --permitReservedWords
            (
            SELECT
            CASE column_name
            WHEN 'year' THEN ' --permitReservedWords' -- Change 'year', or add lines to permit reserved words you want to allow
            WHEN 'translation' THEN ' --permitReservedWords'
            ELSE ''
            END
            )

            )
            AS '//roo_fields'

            from information_schema.COLUMNS c
            where table_schema ='ihealthus' -- ********** Change 'ihealthus' to your schema name
            and column_name != 'version' -- roo generates this automatically when you create the table, so don't define it again

            /* Need to add logic to ensure it isn't a view! */

            and column_key != 'PRI'
            order by table_name,
            ordinal_position


            /* This script will generate a 'controllers' script, just run it, save the output, and feed that ro 'roo via the
            command 'script <whatever you called the output>

            *** Important *** change the table_schema to reflect your schema as indicated below

            */
            Select concat(
            'controller scaffold --class ~.web.', replace(utils.ProperCase(table_name),'_',''),
            'Controller',
            ' --entity ', replace(utils.ProperCase(table_name),'_','')
            )
            AS '// roo_Controllers'

            from information_schema.COLUMNS
            where table_schema ='ihealthus' -- ******Change 'ihealthus' to your schema name
            and column_key = 'PRI'
            order by table_name

            Comment


            • #8
              formatting

              Please forgive the formatting, the source has indentations etc. but the forum strips that all out.

              Regards,
              Mike Dolan

              Comment


              • #9
                ihealthus

                I see I missed noting a couple of other cases where you need to replace ihealthus with your database name. They are in the select portion that located reference fields. Just search for ihealthus and replace any occurrences with your database name.

                Sorry for the oversight,
                Mike Dolan

                Comment


                • #10
                  Missed another one IMPORTANT

                  I sent you the wrong script for table generation, I'll get the correct one out shortly.

                  Sorry

                  Comment


                  • #11
                    Correct table script

                    Here is the correct table script, sorry for the earlier misfire.

                    --entities
                    Select concat(
                    'entity --class ~.domain.', replace(utils.ProperCase(table_name),'_',''),
                    ' --table ', table_name,
                    ' --identifierField ', replace(utils.CamelCase(column_name),'_',''),
                    ' --identifierColumn ', column_name,
                    ' --identifierType ',
                    (
                    SELECT
                    CASE data_type
                    WHEN 'tinyint' THEN 'java.lang.Byte'
                    WHEN 'smallint' THEN 'java.lang.Short'
                    WHEN 'int' THEN 'java.lang.Integer'
                    WHEN 'bigint' THEN 'java.lang.Long'
                    WHEN 'decimal' THEN 'java.lang.Double'
                    WHEN 'double' THEN 'java.math.BigDecimal'
                    WHEN 'float' THEN 'java.lang.Float'
                    WHEN 'date' THEN 'java.util.Date'
                    WHEN 'timestamp' THEN 'java.util.Date'
                    WHEN 'datetime' THEN 'java.util.Date'
                    WHEN 'bit' THEN 'java.lang.Boolean'
                    WHEN 'blob' THEN 'java.lang.Blob'
                    WHEN 'char' THEN 'java.lang.String'
                    WHEN 'varchar' THEN 'java.lang.String'
                    WHEN 'longtext' THEN 'java.lang.String'
                    END as refType
                    ),
                    ' --testAutomatically'
                    )
                    AS '// roo_Entities'
                    from information_schema.COLUMNS
                    where table_schema ='ihealthus'
                    and column_key = 'PRI'
                    order by table_name

                    Comment


                    • #12
                      You can wrap the code in code tags and the forum should retain the tabs.

                      Comment

                      Working...
                      X