SpringRoo and DB ReverseEngineering Part 1

SpringRoo Article 1

Ever since I started trying to do websites in Java I wanted code generation tools. However they were impossible to find back then and I got disheartened. However SpringRoo is a tool which has restored my enthusiasm.

It is a program which essentially generates Java code suitable for websites of various kinds - but essentially they have to be database backed applications using the Spring Framework.

There are quite a few beginners examples but I am going to show you one of the features I am exploring now - Incremental Database Reverse Engineering.

SpringRoo was created for Java developers people who want to build web applications from scratch in the minimum amount of time. It lets you quickly define a project, connect to a database, create some tables, fields, and relationships between tables. It then lets you create a web front end for those tables which perform a simple CRUD system - "Create Read Update Delete". It has a fair number of bells and whistles including automated test generation and using GWT as the web front end technology. Unfortunately you then need to do a fair amount of work so long as you are familiar with the Spring Framework.

By default it uses Hibernate as an Object-Relational mapping tool to talk to the database and that is what I am going to focus on here.

The script

So what do I get when I run SpringRoo? Fancy GUI? Well, if you want you can run it from inside their own version of Eclipse. But that isn't my sort of thing. I just run it from the command line and get a simple shell.

alex:~/projects$ mkdir new_project
alex:~/projects$ cd new_project/
alex:~/projects/new_project$ roo-dev
    ____  ____  ____  
   / __ \/ __ \/ __ \ 
  / /_/ / / / / / / / 
 / _, _/ /_/ / /_/ /  
/_/ |_|\____/\____/    1.1.2.BUILD-SNAPSHOT [rev 51f7164]

Welcome to Spring Roo. For assistance press TAB or type "hint" then hit ENTER.

So what can I do? I can ask for a hint.

roo> hint
Welcome to Roo! We hope you enjoy your stay!

Before you can use many features of Roo, you need to start a new project.

To do this, type 'project' (without the quotes) and then hit TAB.

Enter a --topLevelPackage like 'com.mycompany.projectname' (no quotes).
When you've finished completing your --topLevelPackage, press ENTER.
Your new project will then be created in the current working directory.

Note that Roo frequently allows the use of TAB, so press TAB regularly.
Once your project is created, type 'hint' and ENTER for the next suggestion.
You're also welcome to visit http://forum.springframework.org for Roo help.

The point behind this is that roo tries to be context sensitive. It attempts to only give you commands that are suitable for your situation. Currently I haven't started my project so that is what it tells me about. I'm going to ask it to create a project, and tell it the top level java package.

roo> project --topLevelPackage uk.co.owal.drupalreader
10a2cac Created /home/alex/projects/new_project/pom.xml
df4da11 Created SRC_MAIN_RESOURCES/META-INF/spring/applicationContext.xml
53126d9 Created SRC_MAIN_RESOURCES/log4j.properties
uk.co.owal.drupalreader roo> 

Fine. What next? Well I already have a database I want it to connect to rather than creating its own. I'm going to tell it that I want to use Hibernate as the object persistence tool and tell it how to connect to my database.

uk.co.owal.drupalreader roo> persistence setup --provider HIBERNATE --database MYSQL --databaseName drupal_dbname --userName dbusername
7c0af79 Updated ROOT/pom.xml [Removed redundant artifacts]
f26f0d7 Updated SRC_MAIN_RESOURCES/META-INF/spring/applicationContext.xml
59845e7 Created SRC_MAIN_RESOURCES/META-INF/persistence.xml
c654d7e Created SRC_MAIN_RESOURCES/META-INF/spring/database.properties
c8498bc Updated ROOT/pom.xml [Added dependencies mysql:mysql-connector-java:5.1.13, org.hibernate:hibernate-core:3.6.0.Final, org.hibernate:hibernate-entitymanager:3.6.0.Final, org.hibernate.javax.persistence:hibernate-jpa-2.0-api:1.0.0.Final, org.hibernate:hibernate-validator:4.1.0.Final, javax.validation:validation-api:1.0.0.GA, cglib:cglib-nodep:2.2, javax.transaction:jta:1.1, org.springframework:spring-jdbc:${spring.version}, org.springframework:spring-orm:${spring.version}, commons-pool:commons-pool:1.5.4, commons-dbcp:commons-dbcp:1.3]
0a6f724 Updated ROOT/pom.xml [Added repository https://repository.jboss.org/nexus/content/repositories/releases]
Please enter your database details in src/main/resources/META-INF/spring/database.properties.
uk.co.owal.drupalreader roo> 

The first time you do this it will fall over and complain that it doesn't have a MySQL JDBC driver. Follow the on screen instructions to get one. There used to be one bundled with Spring but now they are trying to be more database agnostic.

Now it may be possible to add in all the database connection details from the command line but personally I go and change the file "database.properties" like it says in src/main/resources/META-INF/spring/database.properties

Now, I'm going to do something which may or may not be necessary. i haven't figured it out yet. Basically we are going to ask the system to connect to the database and decide how to represent all the columns in SpringRoo's variante of database types. (This is not identical to Hibernate types).

uk.co.owal.drupalreader roo> database introspect --schema drupal_dbname

If you are like me you will probably get a stack trace telling you that you got the connection details wrong, but eventually you will see some xml zip past you... This is basically a variant of the database DDL - Data Definition Language.

Here is a small section of my example database.

    <table name="twitter_account">
        <column name="twitter_uid" primaryKey="true" required="true" size="20" type="3,DECIMAL UNSIGNED"/>
        <column name="uid" primaryKey="false" required="true" size="20" type="-5,BIGINT UNSIGNED"/>
        <column name="host" primaryKey="false" required="false" size="255" type="12,VARCHAR"/>
        <column name="screen_name" primaryKey="false" required="false" size="255" type="12,VARCHAR"/>
        <column name="password" primaryKey="false" required="false" size="64" type="12,VARCHAR"/>
        <column name="oauth_token" primaryKey="false" required="false" size="64" type="12,VARCHAR"/>
        <column name="oauth_token_secret" primaryKey="false" required="false" size="64" type="12,VARCHAR"/>
        <column name="name" primaryKey="false" required="true" size="64" type="12,VARCHAR"/>
        <column name="description" primaryKey="false" required="false" size="255" type="12,VARCHAR"/>
        <column name="location" primaryKey="false" required="false" size="255" type="12,VARCHAR"/>
        <column name="followers_count" primaryKey="false" required="true" size="10" type="4,INT"/>
        <column name="friends_count" primaryKey="false" required="true" size="10" type="4,INT"/>
        <column name="statuses_count" primaryKey="false" required="true" size="10" type="4,INT"/>
        <column name="favourites_count" primaryKey="false" required="true" size="10" type="4,INT"/>
        <column name="url" primaryKey="false" required="false" size="255" type="12,VARCHAR"/>
        <column name="profile_image_url" primaryKey="false" required="false" size="255" type="12,VARCHAR"/>
        <column name="protected" primaryKey="false" required="true" size="10" type="4,INT UNSIGNED"/>
        <column name="profile_background_color" primaryKey="false" required="true" size="6" type="12,VARCHAR"/>
        <column name="profile_text_color" primaryKey="false" required="true" size="6" type="12,VARCHAR"/>
        <column name="profile_link_color" primaryKey="false" required="true" size="6" type="12,VARCHAR"/>
        <column name="profile_sidebar_fill_color" primaryKey="false" required="true" size="6" type="12,VARCHAR"/>
        <column name="profile_sidebar_border_color" primaryKey="false" required="true" size="6" type="12,VARCHAR"/>
        <column name="profile_background_image_url" primaryKey="false" required="false" size="255" type="12,VARCHAR"/>
        <column name="profile_background_tile" primaryKey="false" required="true" size="10" type="4,INT UNSIGNED"/>
        <column name="verified" primaryKey="false" required="true" size="10" type="4,INT UNSIGNED"/>
        <column name="created_at" primaryKey="false" required="true" size="64" type="12,VARCHAR"/>
        <column name="created_time" primaryKey="false" required="true" size="10" type="4,INT"/>
        <column name="utc_offset" primaryKey="false" required="true" size="10" type="4,INT"/>
        <column name="import" primaryKey="false" required="true" size="10" type="4,INT UNSIGNED"/>
        <column name="last_refresh" primaryKey="false" required="true" size="10" type="4,INT"/>
        <column name="is_global" primaryKey="false" required="true" size="10" type="4,INT UNSIGNED"/>
        <unique name="PRIMARY">
            <unique-column name="twitter_uid"/>
        <index name="screen_name">
            <index-column name="screen_name"/>

Along with many other tables in my database it has found one called "twitter_account", determined the columns in the table, decided what the types are of each column. It has spotted that "twitter_uid" is the primary key and that we have an index on screen_name. (As an aside screen_name can't be the primary key because you can change twitter screen_names, but not user ids)

But that isn't the fancy bit.

uk.co.owal.drupalreader roo> database reverse engineer --schema drupal_dbname --package ~.domain

The "~" above is shorthand for "the current package" and so we are asking it to create hibernate code for our database putting it in the package "uk.co.owal.drupalreader.domain"

I get lots of lines like this

67b5996 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/CacheUpdate_Roo_DbManaged.aj
d6a0fb9 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/Vocabulary_Roo_DbManaged.aj
ebfba82 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/OauthCommonContext_Roo_DbManaged.aj
507120b Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/VocabularyNodeTypes_Roo_Entity.aj
2bd41fb Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/TermRelation_Roo_Configurable.aj
4d6c323 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/TermData_Roo_Entity.aj
9c80fb6 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/CacheBlock_Roo_Entity.aj
049c3f4 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/History_Roo_DbManaged.aj
0d08f53 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/FilterFormats_Roo_Entity.aj
b08cd86 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/Blocks_Roo_DbManaged.aj
1ef18ac Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/Comments_Roo_Entity.aj
4138175 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/Actions_Roo_Entity.aj
1badaee Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/Node_Roo_ToString.aj
9d74f88 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/Authmap_Roo_Configurable.aj
d4324b0 Created SRC_MAIN_JAVA/uk/co/owal/drupalreader/domain/Flood_Roo_DbManaged.aj
uk.co.owal.drupalreader roo> 

But what did it do? Let's have a look at what was created.
It has created several files for each table. eg In a different shell outside of roo:

alex:~/projects/new_project$ cd src/main/java/uk/co/owal/drupalreader/domain/
alex:~/projects/new_project$ ls TwitterAccount*

TwitterAccount.java                 TwitterAccount_Roo_DbManaged.aj  TwitterAccount_Roo_ToString.aj
TwitterAccount_Roo_Configurable.aj  TwitterAccount_Roo_Entity.aj

Let's go straight for the most obvious one TwitterAccount.java

package uk.co.owal.drupalreader.domain;

import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.entity.RooEntity;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.tostring.RooToString;

@RooEntity(versionField = "", table = "twitter_account")
@RooDbManaged(automaticallyDelete = true)
public class TwitterAccount {

That's short. I don't claim to have AspectJ terminology perfect but basically different requirements for the TwitterAccount object have been put in different AspectJ files. The next most obvious is File: TwitterAccount_Roo_DbManaged.aj
It basically tells the system what the columns are in the table corresponding to this object.

// You may push code into the target .java compilation unit if you wish to edit any member(s).

package uk.co.owal.drupalreader.domain;

import java.lang.Integer;
import java.lang.Long;
import java.lang.String;
import javax.persistence.Column;
import javax.validation.constraints.NotNull;

privileged aspect TwitterAccount_Roo_DbManaged {
    @Column(name = "uid", columnDefinition = "BIGINT UNSIGNED")
    private Long TwitterAccount.uid;
    @Column(name = "host", columnDefinition = "VARCHAR", length = 255)
    private String TwitterAccount.host;
    @Column(name = "screen_name", columnDefinition = "VARCHAR", length = 255)
    private String TwitterAccount.screenName;
    @Column(name = "password", columnDefinition = "VARCHAR", length = 64)
    private String TwitterAccount.password;
    @Column(name = "oauth_token", columnDefinition = "VARCHAR", length = 64)
    private String TwitterAccount.oauthToken;
    @Column(name = "oauth_token_secret", columnDefinition = "VARCHAR", length = 64)
    private String TwitterAccount.oauthTokenSecret;
    @Column(name = "name", columnDefinition = "VARCHAR", length = 64)
    private String TwitterAccount.name;

And so on. But that is not all - we have trivial getters and setters too....

    public Long TwitterAccount.getUid() {
        return this.uid;
    public void TwitterAccount.setUid(Long uid) {
        this.uid = uid;
    public String TwitterAccount.getHost() {
        return this.host;
    public void TwitterAccount.setHost(String host) {
        this.host = host;
    public String TwitterAccount.getScreenName() {
        return this.screenName;
    public void TwitterAccount.setScreenName(String screenName) {
        this.screenName = screenName;

This primarily means that the user of this class doesn't need to know the underlying database types - they can use normal Java types.

The next file is a useful "toString" method for the object. File: TwitterAccount_Roo_ToString.aj

// You may push code into the target .java compilation unit if you wish to edit any member(s).

package uk.co.owal.drupalreader.domain;

import java.lang.String;

privileged aspect TwitterAccount_Roo_ToString {
    public String TwitterAccount.toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("Uid: ").append(getUid()).append(", ");
        sb.append("Host: ").append(getHost()).append(", ");
        sb.append("ScreenName: ").append(getScreenName()).append(", ");
        sb.append("Password: ").append(getPassword()).append(", ");
        sb.append("OauthToken: ").append(getOauthToken()).append(", ");
        sb.append("OauthTokenSecret: ").append(getOauthTokenSecret()).append(", ");
        sb.append("Name: ").append(getName()).append(", ");
        sb.append("Description: ").append(getDescription()).append(", ");
        sb.append("Location: ").append(getLocation()).append(", ");
        sb.append("FollowersCount: ").append(getFollowersCount()).append(", ");
        sb.append("FriendsCount: ").append(getFriendsCount()).append(", ");
        sb.append("StatusesCount: ").append(getStatusesCount()).append(", ");
        sb.append("FavouritesCount: ").append(getFavouritesCount()).append(", ");
        sb.append("Url: ").append(getUrl()).append(", ");
        sb.append("ProfileImageUrl: ").append(getProfileImageUrl()).append(", ");
        sb.append("Protected1: ").append(getProtected1()).append(", ");
        sb.append("ProfileBackgroundColor: ").append(getProfileBackgroundColor()).append(", ");
        sb.append("ProfileTextColor: ").append(getProfileTextColor()).append(", ");
        sb.append("ProfileLinkColor: ").append(getProfileLinkColor()).append(", ");
        sb.append("ProfileSidebarFillColor: ").append(getProfileSidebarFillColor()).append(", ");
        sb.append("ProfileSidebarBorderColor: ").append(getProfileSidebarBorderColor()).append(", ");
        sb.append("ProfileBackgroundImageUrl: ").append(getProfileBackgroundImageUrl()).append(", ");
        sb.append("ProfileBackgroundTile: ").append(getProfileBackgroundTile()).append(", ");
        sb.append("Verified: ").append(getVerified()).append(", ");
        sb.append("CreatedAt: ").append(getCreatedAt()).append(", ");
        sb.append("CreatedTime: ").append(getCreatedTime()).append(", ");
        sb.append("UtcOffset: ").append(getUtcOffset()).append(", ");
        sb.append("Import1: ").append(getImport1()).append(", ");
        sb.append("LastRefresh: ").append(getLastRefresh()).append(", ");
        sb.append("IsGlobal: ").append(getIsGlobal());
        return sb.toString();

The last file to worry about is the TwitterAccount_Roo_Entity.aj This provides methods relating to the javax.persistence JPA control of this object.

That's enough for today.