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


      • #4
        More on overrinding Roo Ids

        Mike,

        These are great helpers...

        Could you please provide an example on how to change the "annotation in the the Java class..." It is exactly where a I have trouble.


        Yes, I would really appreciate you send me the referred code to [email protected]


        Thx-Thx

        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