Tutorial for using Spring Roo with an existing database

March 19th, 2010

Recently, I selected Spring Roo as my RAD tool of choice.  I use it for quickly churning out ideas into prototypes. It is my Java solution for the functionality that I have admired in Ruby On Rails. However, Spring Roo is still in its infancy. Its is just over a year old and still on its arduos joruney to maturity. One of the inadequacies of Spring Roos that I ran into right off the bat was the inability of Roo to work with an existing database.  In other words, Roo does not currently allow developers to start with a database, reverse engineer the schema into an ORM and build apps on top.  The ability to do this is Roo’s currently most requested feature. It is documented here as a Jira issue.

A couple of workarounds have been suggested to get around this problem. One of the workaorunds that I considered  is using Eclipse to generate entities from table using a JPA project.  However, this approach does not create Roo-specific annotation such as the @RooEntity,@RooToString and @RooJavaBean, all of which will have to be coded by hand. Given the inadequacoes of all the approaches, I came up with a method that has been working for me. I must say, I suggest this method ONLY if you have a few tables (and for non-production work). I can see this method turning into a laborious process if you have many tables; if that is your situation, you are probably better served going the Eclipse JPA project route.

(NOTE:  Ben Alex has indicated that the next version of Spring Roo will address Jira issue “ROO-435″)

Here is the process for creating a Spring Roo project using an existing database (the assumption here is that the reader is familiar with the creation of Spring Roo projects. If not, please take this excellent tutorial by Ben Alex first):

We are going to create a Directory project that allows us to look up people using a Person object.

Step 1: Create a database named “directory”. Create a table named “Person”. Insert a few rows into the Person table. I am using MySQL for the database but any database of your choice can work with this tutorial.

$ mysql -h localhost -u su -p
mysql> create database directory;
mysql> use directory;
mysql> CREATE TABLE Person (person_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,last_name VARCHAR(45) NOT NULL,first_name VARCHAR(45) NOT NULL,date_of_birth DATE NOT NULL,created_date TIMESTAMP NOT NULL,PRIMARY KEY (`person_id`));
mysql> insert into person (last_name, first_name, date_of_birth, created_date) values ('Winnfield','Jules','1967-06-07',NOW());
mysql> insert into person (last_name, first_name, date_of_birth, created_date) values ('Vega','Vincent','1971-04-15',NOW());
mysql> insert into person (last_name, first_name, date_of_birth, created_date) values ('Wallace','Marsellus','1970-01-01',NOW());
mysql> insert into person (last_name, first_name, date_of_birth, created_date) values ('Wolfe','Winston','1960-07-04',NOW());


mysql> select * from Person;
+-----------+-----------+------------+---------------+---------------------+
| person_id | last_name | first_name | date_of_birth | created_date        |
+-----------+-----------+------------+---------------+---------------------+
|         1 | Winnfield | Jules      | 1967-06-07    | 2010-03-19 15:33:31 |
|         2 | Vega      | Vincent    | 1971-04-15    | 2010-03-19 15:35:00 |
|         3 | Wallace   | Marsellus  | 1970-01-01    | 2010-03-19 15:36:52 |
|         4 | Wolfe     | Winston    | 1960-07-04    | 2010-03-19 15:37:19 |
+-----------+-----------+------------+---------------+---------------------+
4 rows in set (0.09 sec)
mysql> select * from Person;
+-----------+-----------+------------+---------------+---------------------+
| person_id | last_name | first_name | date_of_birth | created_date        |
+-----------+-----------+------------+---------------+---------------------+
|         1 | Winnfield | Jules      | 1967-06-07    | 2010-03-19 15:33:31 |
|         2 | Vega      | Vincent    | 1971-04-15    | 2010-03-19 15:35:00 |
|         3 | Wallace   | Marsellus  | 1970-01-01    | 2010-03-19 15:36:52 |
|         4 | Wolfe     | Winston    | 1960-07-04    | 2010-03-19 15:37:19 |
+-----------+-----------+------------+---------------+---------------------+
4 rows in set (0.09 sec)

mysql> exit;

Step 2: Create an empty directory named “directory”, cd to it and fire up Roo.

$ mkdir directory
$ cd directory

$ roo
    ____  ____  ____
   / __ \/ __ \/ __ \
  / /_/ / / / / / / /
 / _, _/ /_/ / /_/ /
/_/ |_|\____/\____/    1.0.2.RELEASE [rev 638]

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

Step 3: Create the Roo project with a top level package of “com.tomchikoore.examples.directory”


roo> project --topLevelPackage com.tomchikoore.examples.directory
Created /Users/tom/Documents/workspace-sts-2.3.1.RELEASE/directory/pom.xml
Created SRC_MAIN_JAVA
Created SRC_MAIN_RESOURCES
Created SRC_TEST_JAVA
Created SRC_TEST_RESOURCES
Created SRC_MAIN_WEBAPP
Created SRC_MAIN_RESOURCES/META-INF/spring
Created SRC_MAIN_RESOURCES/META-INF/spring/applicationContext.xml
Created SRC_MAIN_RESOURCES/META-INF/spring/log4j.properties

Step 4: Install a JPA provider and database. Since I created the Person table in a MySQL database, I am going to specify MYSQL as my database.

roo> persistence setup --provider HIBERNATE --database MYSQL --databaseName directory --userName su
Created SRC_MAIN_RESOURCES/META-INF/persistence.xml
Created SRC_MAIN_RESOURCES/META-INF/spring/database.properties
please enter your database details in src/main/resources/database.properties
Managed SRC_MAIN_RESOURCES/META-INF/spring/applicationContext.xml
Managed ROOT/pom.xml

Step 5: Disable the auto database creation in the persistence.xml file

Roo sets up the persistence settings so that the database is created when the application run. Since your database already exists, turn this setting off by going to this file:

SRC_MAIN_RESOURCES/META-INF/persistence.xml

and comment this line:

<property name="hibernate.hbm2ddl.auto" value="create"/>

So that it looks like this:
<!-- <property name="hibernate.hbm2ddl.auto" value="create"/> -->

Save the file and close.

Step 6: Create the Person entity.  This is the Java object representation of a Person row entry in the database

IMPORTANT: The important thing to understand here is that Roo creates two fields by default, an identifier “id” field and a versioning “version” field.  The problem here is that our Person table has neither of those columns (I purposefully created the Person table without either field, especially the “id” field which I named “person_id” so that I can clearly make this point). In my case, I have a schema that has been in production for years and I do not want to start adding columns to our the table on because they are required by a RAD tool, that will turn out to be a bad decision once the next RAD tools comes along.

Therefore what I want to do is  instruct Roo NOT to create a default “id” and a “version” fields.   Unfortunately, I cannot instruct Roo not to create a “version” field  at this step, I can only do it at a later step. At this step, I can only instruct Roo not to create a default “id” field by instructing it to use a different field for an “id” field. So I am going to go ahead an instruct Roo to create a Person entity WITHOUT the default identifier field named “id” but instead use the “person_id” field as the identifier field:

roo> entity --class ~.model.Person --identifierField person_id --identifierColumn person_id  --table Person
Created SRC_MAIN_JAVA/com/tomchikoore/examples/directory/model
Created SRC_MAIN_JAVA/com/tomchikoore/examples/directory/model/Person.java
Created SRC_MAIN_JAVA/com/tomchikoore/examples/directory/model/Person_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/tomchikoore/examples/directory/model/Person_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/tomchikoore/examples/directory/model/Person_Roo_Configurable.aj

Step 7: Add entity fields

IMPORTANT:  This is the most important step when creating the entity because it involves reverse-engineering the database table schema into entity fields. This is the functionality that is currently lacking in Roo. For this step I am going to use a script that I found in the Spring Roo forums; I have made several enhancements to the script to to make it usable. The script introspects the databse schema and generates Roo entity fild creation commands.

The first step is to run the script to create the Roo entity field creation commands.  In a separate window log back in to MySQL and run the following script:

mysql> select concat('field ',
elt(
field(data_type,'int' ,'varchar','bit' ,'timestamp','datetime', 'date', 'tinyint'),
'number','string' ,'boolean','date','date','date','number'),' --fieldName ',column_name,if (is_nullable='NO',' --notNull ',' '),
if(character_maximum_length is not null,concat(' --sizeMax ',character_maximum_length,' '),''),
elt(
field(data_type,'int' ,'varchar','bit' ,'timestamp','datetime','date', 'tinyint'),
'--type java.lang.Integer','--type java.lang.String' ,'--type java.lang.Boolean','--type java.util.Date','--type java.util.Date','--type java.util.Date','--type java.lang.Integer')
) from information_schema.columns cols where table_name='Person' and table_schema='directory';
+-----------------------------------------------------------------------------------------------+
| field number --fieldName person_id --notNull --type java.lang.Integer                         |
| field string --fieldName last_name --notNull  --sizeMax 45 --type java.lang.String            |
| field string --fieldName first_name --notNull  --sizeMax 45 --type java.lang.String           |
| field date --fieldName date_of_birth --notNull --type java.util.Date                          |
| field date --fieldName created_date --notNull --type java.util.Date                           |
+-----------------------------------------------------------------------------------------------+

5 rows in set (0.02 sec)

The second step is to execute the entity field commands generated by the script in the step above at the Roo comand line (go back to the Roo command window):

IMPORTANT: DO NOT RUN THE FIRST COMMAND. That is because the “person_id” has been specified to be the identifier in Step 6. If you create it here again, you will get duplicate field errors.

roo> field number –fieldName person_id –notNull –type java.lang.Integer

roo> field string –fieldName last_name –notNull –sizeMax 45 –type java.lang.String

roo> field string –fieldName first_name –notNull –sizeMax 45 –type java.lang.String

roo> field date –fieldName date_of_birth –notNull –type java.util.Date

roo> field date –fieldName created_date –notNull –type java.util.Date


Step 8: Perform JUnit Integration test

roo> test integration

Step 9: Create the web tier

roo> controller scaffold ~.web.PersonController

Step 10: Add finders

roo> finder add –finderName findPeopleByLast_nameLike

roo> finder add –finderName findPeopleByFirst_nameLike

Step 11: Remove the default “version” field

In Step 6, I mentoned that Roo creates two default fields for each entrity, the idntifier “id’ field and the versioning “version” field. In step 6 we were able to instruct Roo to use the “person_id” field instead of the default “id” field for the default indentifier.  At this step we are going to instruct Roo not to use the “version” field.  To do this, we have to change the @RooEntity annotation in the Person.java class. In this example, the source for  the Person.java class is located at:

SRC_MAIN_JAVA/com/tomchikoore/examples/directory/model/Person.java

Open this source file and locate the following line (notice the identifier overrides that we set in Step 6):

@RooEntity(identifierField = “person_id”, identifierColumn = “person_id”, finders = { “findPeopleByLast_nameLike”, “findPeopleByFirst_nameLike” })

Add versionField = “”, such that the line looks as follows:

@RooEntity(identifierField = “person_id”, identifierColumn = “person_id”, versionField = “”, finders = { “findPeopleByLast_nameLike”, “findPeopleByFirst_nameLike” })

Save the file and close.

Step 12: Import the project into Eclipse

(I am going to use Eclipse/STS for the sole purpose of being  consistent with the original Spring Roo tutorials so that I don’t throw off novices)

roo> perform eclipse

After this command completes, open up Eclipse or STS and import (File -> Import -> Existing Projects into Workspace) the “directory” project.

Step 13: Run web aplication

Deploy the application war and go to http://localhost:8080/directory

When you click on “List all People”, you should see the following:

"List all People" results

"List all People" results

Thats it!!

  1. Stephane
    March 22nd, 2010 at 02:32
    Quote | #1

    I’m not sure but I think the id and version fields are mandated by Hibernate and not by Roo. That is, these two fields are here to stay and are not likely to be subjected to the whims of the next RAD tool.

  2. Stephane
    March 22nd, 2010 at 02:33
    Quote | #2

    A typo… fild

  3. Elio
    March 23rd, 2010 at 13:37
    Quote | #3

    Thank you so much! This was just what I needed right when I needed it….Cheers!

  4. tomchikoore
    March 23rd, 2010 at 20:12
    Quote | #4

    Stephane,

    You are right. Its a hibernate requirement that will change with choice of ORM component. I need to check with the Roo guys to see if Roo mandates this field or whether that changes if I use iBatis for example.

    Tom

  5. Nils
    March 24th, 2010 at 03:23
    Quote | #5

    Very nice! Thanks for the trick.
    I am really waiting for Jira issue ROO-453. But for now this is a good solution/workaround, well done!

  6. gonetil
    April 7th, 2010 at 12:33
    Quote | #6

    Excelent work, very clear and easy to follow. Even though I prefer waiting for this feature to be included into Roo, I really liked your post.

  7. Juan
    April 7th, 2010 at 16:12
    Quote | #7

    Hi Tom,

    I have a doubt. I have a database with 8 tables and some relationships among them (one to many, etc). I wondered if your approach would still work on this simple scenario, or if maybe this relationships could get messy and increment complexity.

    Thanks!

  8. tomchikoore
    April 7th, 2010 at 21:16
    Quote | #8

    I am looking forward to the support for reverse engineeing to fix Jira Issue ROO-453 as well.

  9. tomchikoore
    April 7th, 2010 at 21:24
    Quote | #9

    Juan,

    This method works well for very simple databases. For databases with moderately complex to complex relationships, this method becomes very tedious. In addition, in order to do it well, one needs a very good understanding of JPA. Therefore, I would suggest that you wait for the next version of Roo which should be out any day now. However, if you decide to use the method that I have illustrated and find a ways to generate a clean ORM, please share.

    Tom

  10. Palam Garg
    April 10th, 2010 at 03:31
    Quote | #10

    hi,
    I want to know is there any way to stop roo creating jspx pages. or to create custom view

    Thanks
    Palam

  11. Bruce
    May 19th, 2010 at 10:50
    Quote | #11

    A typo… fild

  12. epzee
    August 10th, 2010 at 12:34
    Quote | #12

    Great tip Tom! Step 5 also works if you change this:

    with this:

  13. epzee
    August 10th, 2010 at 18:02
    Quote | #13

    Great tip Tom! Step 5 also works if you change hibernate.hbm2ddl.auto property value from “create” to “update”

  14. Alex McLintock
    January 24th, 2011 at 05:33
    Quote | #15

    Hi Tom,

    have you looked at this again? I ask because there are still problems with SpringRoo and version fields when reverse engineering.

    I can, for example, reverse engineer a database with SpringRoo, but it falls over when generating a GWT web framework saying that it *needs* a version field.

    This is a pain when I have an existing schema I can’t easily change.

  15. tomchikoore
    January 24th, 2011 at 08:06
    Quote | #16

    I am not familiar with that error on GWT. I have not worked with the GWT web framework.

  16. BoB
    February 19th, 2011 at 14:17
    Quote | #17

    Hey.. Great job!!!!

  17. Dennis Gearon
    July 19th, 2011 at 22:01
    Quote | #18

    I think the best method would be inheritance, or ‘an element of’. I.E. name your NEW table user_in_roo, and have a field in it that is ‘id’, or whatever the original field primary key was. That is, until Roo gets gets introspection working.

    BTW, Java is a much better (in most ways) product line than PHP, more rigorous, and Roo is a much more powerful tool than many other similar tools (like Ruby on Rails and Symfony, CakePHP, et al). I’m most familiar with Symfony, I’ll admit.

    From what I’ve seen, Symfony/Doctrine always had problems with introspection on anthing but MySQL. It would work . . . on simple stuff. Personally, I expect Roo to do a better job, BUT, complex databases are hard to translate into ORMs. I think hooking into the existing database as I’ve described might be better, and using Eclipse JPA (with what little I’ve read about that).

    Another solution is to build an equivalent Roo version, export your database, reimport it into the new design, using SQL and POJ, (Plain Old Java/PHP). Now that I think about it, that’s what I did with my PHP project. It means, as usual in the database world, if you change your schema, be prepared to do a LOT of work.

  18. Peter G.
    September 12th, 2011 at 06:07
    Quote | #19

    Wow! Thank you for sharing, This helped me a lot on Spring Roo, I never thought that this is possible :)

  19. Hafsa
    September 29th, 2011 at 11:49
    Quote | #20
  20. Alan
    June 29th, 2012 at 13:00
    Quote | #21

    I was searching for Database Reverse Engineering in Spring Roo and I found this article.
    It’s a very good idea but nowadays we already have the reverse engineering implemented in Spring Roo.
    More details about on this link: http://blog.springsource.com/2010/10/27/spring-roo-1-1-0-is-released/

    To the author, thank you very much for sharing your experience with us.

  21. Marcel Bussien
    November 1st, 2012 at 07:08
    Quote | #22

    great stuff -> little correction the jpa was missing :
    entity jpa –class ~.model.Person –identifierField person_id –identifierColumn person_id –table Person