Announcement Announcement Module
Collapse
No announcement yet.
Another way to reverse engineer from a database Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Another way to reverse engineer from a database

    There's already a couple of threads that discuss or provide ways to reverse-engineer Roo entities from an existing database, for example:We're all waiting with bated breath for ROO-435 to be finished, but until then, here's another approach that worked for me, and which scales to large numbers of tables (230, in my case):
    1. Use Apache Torque to generate the schema of your database as a database-neutral XML file. Disclaimer: I haven't actually done this step*, but I'm sure it's feasible.
    2. Use Apache Torque's generator module to generate the Java files that would normally be created by Roo's "entity" and "field" commands. You can run this directly or as I prefer, via Maven using the "torque:om" goal; see the plugin configuration below. Of course Torque can't generate Roo-style JPA entities by default, you have to create your own Velocity templates in src\main\torque\templates\om, as follows:
      1. Control.vm - can be blank but must exist
      2. ControlBase.vm - the Velocity template that Torque invokes once per schema (see my example below)
      3. JPAEntity.vm - the template for your JPA entities (see my example below); this file can be called whatever you like, as long as you update ControlBase.vm accordingly.
    3. Copy the generated files from target\generated-sources\torque to the appropriate source package.
    4. Run Roo to trigger the generation of the AspectJ ITDs. This even works in a multi-module Maven project, despite Roo's purported lack of support for same. You just need to ensure that the Maven module in which you run Roo has an explicit literal <groupId> (e.g. "com.example", not ${project.groupId}) rather than simply inheriting this from the parent POM, otherwise Roo's MavenProjectMetadataProvider throws an NPE.
    That's it! From then on, if you make changes to your database schema, you need to:
    1. Regenerate (or update, in our case) the Torque schema XML,
    2. Re-run the Torque generator,
    3. Re-copy the affected file(s) to the relevant source package (being careful not to lose any hand-written code you've added to your entities since then)
    4. Re-run Roo to manage/delete the ITDs as necessary.
    Here's the Maven Torque plugin configuration (you could bind it to Maven's generate-sources phase if you wanted to):

    Code:
    <plugin>
                    <groupId>org.apache.torque</groupId>
                    <artifactId>torque-maven-plugin</artifactId>
                    <version>3.3</version>
                    <dependencies>
                        <dependency>
                            <groupId>ant</groupId>
                            <artifactId>ant</artifactId>
                            <!-- 1.7.0 can't handle spaces in file paths -->
                            <version>1.6.5</version>
                        </dependency>
                        <dependency>
                            <!-- Required for Velocity logging -->
                            <groupId>logkit</groupId>
                            <artifactId>logkit</artifactId>
                            <version>2.0</version>
                        </dependency>
                    </dependencies>
                    <configuration>
                        <enableJava5Features>true</enableJava5Features>
                        <schemaDir>${basedir}/src/main/resources</schemaDir>
                        <!-- Ignored; it's just to keep the Torque plugin happy -->
                        <targetDatabase>oracle</targetDatabase>
                        <targetPackage>com.example.domain</targetPackage>
                        <!-- Whether to load templates from the classpath -->
                        <useClasspath>false</useClasspath>
                    </configuration>
                </plugin>
    Here's ControlBase.vm, which uses the table name from Torque as the JPA class name (you can use whatever convention you like as long as you update JPAEntity.vm accordingly; the compiler will show you any places you missed):

    Code:
    #set ($package = "com.example.domain")
    #set ($path = $strings.getPackageAsPath($package))
    $files.mkdir("$outputDirectory/$path")
    #foreach ($database in $dataModels)
      ## Collect the foreign keys that we want to be bidirectional in Java
      #set ($bidirectionalFKs = [])
      #foreach ($table in $database.Tables)
        #foreach ($foreignKey in $table.getForeignKeys())
          #if ("$!foreignKey.getOption('bidirectional')" == "true")
             #if ($bidirectionalFKs.add($foreignKey))
               ## Blank statement; the add operation above is the important bit
             #end
          #end
        #end
      #end
      #foreach ($table in $database.Tables)
        #set ($path = "${strings.getPackageAsPath($package)}${table.getName()}.java")
        #if ($table.getDescription().startsWith("Not used"))
          $table.getName() is not used - skipping
        #elseif ($table.getPrimaryKey().size() == 0)
          $table.getName() has no PKs - skipping
        #elseif ($table.getPrimaryKey().size() > 1)
          $table.getName() has multiple PKs - skipping
        #else$generator.parse("om/JPAEntity.vm", $path, "table", $table)#end
      #end
    #end
    JPAEntity.vm will appear below (because of the 10k character forum limit).

    These templates do a few nice things such as respecting any bidirectional or one-to-one foreign keys that are annotated in the schema XML as follows:

    Code:
    <table name="Passport" ...
        ...
        <foreign-key foreignTable="Person" name="FK_Person_Passport">
            <reference local="PersonFK" foreign="PersonID"/>
            <option key="bidirectional" value="true"/>
            <option key="multiplicity" value="one-to-one"/>
        </foreign-key>
        ...
    </table>
    Enjoy, and let me know how it works out for you.

    * We already had this file for our app, as it's useful for so many other purposes; for example, you can:
    • check it into version control to manage schema changes,
    • use it with DDLUtils to create test databases,
    • transform it using XSLT into a human-readable data dictionary
    We also use it as input to a home-grown database upgrade tool that generates db-specific upgrade scripts based on the old and new Torque schemas.

  • #2
    ... and here's JPAEntity.vm

    Here's JPAEntity.vm, with a few extra niceties that you can remove if you like:
    • our Torque schema uses a table-level Torque-style option called "data-type" to indicate the type of data contained therein, with "system" data being read-only,
    • our tables don't have a version number field, so the template suppresses it,
    • our columns use the Torque "javaName" attribute to store a more developer-friendly name for certain columns (the actual Torque ORM tool uses it for something totally different); you could use a Torque "option" tag for this instead,
    • our CHAR(1) columns have a Torque-style "flag" option that indicates whether they store a Y/N boolean or a genuine CHAR(1) value, which affects whether the associated Java field is a boolean/Boolean or a char/Character,
    • the template adds JSR-303 (Bean Validation) annotations to all String fields based on their length and nullability in the database.
    Code:
    ## This Velocity template generates the JPA entity class.
    ## The Roo annotations below will ensure that getters, setters, CRUD methods,
    ## etc. will be generated and maintained by Roo when it is run.
    ##
    ## All database objects like $table and $column are from the Torque package
    ## org.apache.torque.engine.database.model
    ##
    ## A macro that lowercases the first letter of the given string
    #macro (lowercaseFirst $input)
      #set ($firstLetter = $input.substring(0, 1).toLowerCase())
      #set ($rest = $input.substring(1))
    $firstLetter$rest#end
    package ${package};
    
    import java.math.BigDecimal;
    import java.util.ArrayList;
    import java.util.Collection;
    import java.util.Date;
    import java.util.List;
    
    import javax.persistence.CascadeType;
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.ManyToOne;
    import javax.persistence.OneToMany;
    import javax.persistence.OneToOne;
    import javax.persistence.Table;
    
    import javax.validation.constraints.NotNull;
    import javax.validation.constraints.Size;
    
    import org.springframework.roo.addon.entity.RooEntity;
    import org.springframework.roo.addon.javabean.RooJavaBean;
    import org.springframework.roo.addon.tostring.RooToString;
    
    /**
     * $!table.description.
     *
     * This class contains a combination of (a) code generated from the database
     * schema XML and (b) hand-written
     * business logic. If you regenerate this class from the schema (e.g. after
     * modifying that file), make sure you preserve the hand-written business logic.
     * By the same token, if you are adding business logic to a JPA class, make sure
     * you write a unit test for it, no matter how trivial, to guard against the
     * kind of accidental deletion described above. This approach ensures that the
     * schema XML remains the authoritative definition of the database schema while
     * still allowing us to have a rich domain model.
     */
    @Entity
    #if ("$!{table.getOption('data-type')}" == "system")
      #set ($createSetters = "(settersByDefault = false)")
      #set ($readOnly = ',
        flushMethod = "", mergeMethod = "", persistMethod = "", removeMethod = ""')
    #end
    ## Get this table's ID column, if any (not all tables have one)
    #if ($table.getPrimaryKey().size() == 1)
    @RooEntity(identifierColumn = "${table.getPrimaryKey().get(0).getName()}", versionField = ""$!readOnly)
    #else
    @RooEntity(versionField = "")
    #end
    ## Disable setter methods for system data tables
    @RooJavaBean$!createSetters
    @RooToString
    @Table(name = "${table.getName()}")
    public class ${table.getName()} {
    #foreach ($column in $table.Columns)
        #if (!$column.isPrimaryKey() && !$column.getDescription().startsWith("Not used"))
            ## ------- Add a blank line plus any @NotNull and @Size annotations ----
            
            #if ($column.description)
        // $!column.description
            #end
            #if ($column.getJavaNative() == "String")
                #set ($length = ", length = $column.getSize()")
            #end
        @Column(name = "$column.getName()"$!length)
            ## Generate the field name by lowercasing the first letter of the column
            #set ($fieldName = "#lowercaseFirst($column.getName())")
            #if (!$column.getJavaName().equalsIgnoreCase($column.getName()))
              ## This column has a more commonly used name
              #set ($fieldName = "#lowercaseFirst($column.getJavaName())")
            #end
            #if ($column.isForeignKey())
                  ## ------- FK column -----------------------------------------------
                #if ("$!column.getForeignKey().getOption('multiplicity')" == "one-to-one")
        @OneToOne
                #else
        @ManyToOne
                #end
                #set ($lastIndex = $fieldName.length() - 2)
        private $column.getForeignKey().getForeignTableName() $fieldName.substring(0, $lastIndex);
            #else
                  ## ------- Normal (non-ID, non-FK) column --------------------------
                ## ------- Work out which Java type to use for this field
                #set ($javaType = $column.getJavaNative())
                #if ($javaType == "String" && $column.getSize() == 1)
                    ## It's a one-character field; could be a flag or a character
                    #if ("$!column.getOption('flag')" == "false")
                        #if ($column.isNotNull())
                            #set ($javaType = "char")
                        #else
                            #set ($javaType = "Character")
                        #end
                    #else
                        #if ($column.isNotNull())
                            #set ($javaType = "boolean")
                        #else
                            #set ($javaType = "Boolean")
                        #end
                    #end                    
                #end
                #if ($javaType == "BigDecimal" && !$column.getScale())
                    #set ($tooBigForInt = $column.getPrecision().length() > 1)
                    #if ($column.isNotNull())
                        #if ($tooBigForInt)
                            #set ($javaType = "long")
                        #else
                            #set ($javaType = "int")
                        #end
                    #else
                        #if ($tooBigForInt)
                            #set ($javaType = "Long")
                        #else
                            #set ($javaType = "Integer")
                        #end
                    #end
                #end
                ## ------- Work out the default value, if any ----------------------
                #set ($defaultValue = "")
                #if ($column.DefaultValue && !$column.DefaultValue.equalsIgnoreCase("NULL"))
                  #if ($javaType == "BigDecimal")
                    #set ($defaultValue = " = new BigDecimal($column.DefaultValue)")
                  #elseif ($javaType == "boolean")
                    #if ($column.DefaultValue == "Y")
                      #set ($defaultValue = " = true")
                    #elseif ($column.DefaultValue == "N")
                      #set ($defaultValue = " = false")
                    #end
                  #elseif ($javaType == "Boolean")
                    #if ($column.DefaultValue == "Y")
                      #set ($defaultValue = " = Boolean.TRUE")
                    #elseif ($column.DefaultValue == "N")
                      #set ($defaultValue = " = Boolean.FALSE")
                    #end
                  #elseif ($javaType == "Byte")
                    #set ($defaultValue = " = new Byte((byte) $column.DefaultValue)")
                  #elseif ($javaType == "Character" || $javaType == "char")
                    #set ($defaultValue = " = '$column.DefaultValue'")
                  #elseif ($javaType == "int" || $javaType == "Integer")
                    #set ($defaultValue = " = $column.DefaultValue")
                  #elseif ($javaType == "long" || $javaType == "Long")
                    #set ($defaultValue = " = $column.DefaultValue")
                  #elseif ($javaType == "Short")
                    #set ($defaultValue = ' = new Short("$column.DefaultValue")')
                  #elseif ($javaType == "String")
                    #set ($defaultValue = ' = "$column.DefaultValue"')
                  #elseif (!$column.isPrimitive() && $javaType != "String")
                    #set ($defaultValue = " = new ${javaType}($column.DefaultValue)")
                  #end
                #end
                ## ------- Annotations (non-FK fields) -----------------------------
                #if ($column.isNotNull() && $javaType != "boolean"
                  && $javaType != "char" && $javaType != "int"
                  && $javaType != "long" && $javaType != "short")
        @NotNull
                #end
                #if ($javaType == "String")
                  #set ($min = "")
                  #if ($column.isNotNull())
                    #set($min = "min = 1, ")
                  #end
        @Size(${min}max = $column.getSize())
                #end
                ## ------- Declaration (non-FK fields) -----------------------------
        private $javaType $fieldName$defaultValue;
            #end
        #end
    #end
    #foreach ($foreignKey in $bidirectionalFKs)
      #if ($foreignKey.getForeignTableName() == $table.getName())
      
        #set ($fieldName = "#lowercaseFirst($foreignKey.getTableName())")
        // Inverse side of bidirectional FK ${foreignKey.getTableName()}.${foreignKey.getLocalColumnNames()}
        #set ($mappedByColumn = ${foreignKey.getForeignColumns().get(0)})
        #set ($lastIndex = $mappedByColumn.length() - 2)
        #set ($mappedByUpper = $mappedByColumn.substring(0, $lastIndex))
        #set ($mappedBy = "#lowercaseFirst($mappedByUpper)")
        #if ("$!foreignKey.getOption('multiplicity')" == "one-to-one")
        @OneToOne(cascade = CascadeType.ALL, mappedBy = "$mappedBy")
        private ${foreignKey.getTableName()} ${fieldName};
        #else
        @OneToMany(cascade = CascadeType.ALL, mappedBy = "$mappedBy")
        private Collection<${foreignKey.getTableName()}> ${fieldName}s;
        #end
      #end
    #end
    }
    By all means tweak the template (or replace it entirely) according to what code you want generated.

    Comment

    Working...
    X