Reverse engineering – Spring-Roo (DBRE) and Microsoft SQL Server 2005

Spring 1.2.1 introduced us very powerful tool for the reverse engineering. Recently I’ve got occasion to try it out with MS SQL Server 2005 (Express) database – it didn’t go as smooth as expected, but after several hours spent online I’ve fixed some of the problems. Here is a quick tutorial showing what should be done to create CRUD for existing database, and below you can find some of the problems explained.

Note: Problems that pop up in lines 4 and 5 are described in the end of this post.

Step 1: setup SQL Server

We will use SQL Server 2005 Express in the following example. You need to have access to this server from your application, have user account and permissions to view/list tables in your schema. I’m using SQL Server authentication (alternatively you may use Windows Authentication) and database runs on the same machine as java.

Step 2: Create CRUD application using Spring-Roo

I assume you have basic knowledge about spring-roo, and have your environment configured. If not, please refer to Getting started with Roo or to one of the tutorials available online. You’ll need also Java JDK 6, Maven and some servlet container to deploy WAR file (Tomcat 6).

  1. Create directory for your application and cd to it
  2. Start Roo shell by typing roo
  3. Setup your application by executing the following commands. You can also copy them to the file and execute as script (script –file [path]). Change package names, db credentials etc. to the ones matching your environment.
    1. project --topLevelPackage net.mgorski.roo.reverse --java 6
    2. module create --moduleName core --topLevelPackage ~.core --packaging JAR --java 6
    3. persistence setup --provider HIBERNATE --database MSSQL --databaseName test--userName test--password test --hostName localhost
    4. database introspect --schema dbo --enableViews 
    5. database reverse engineer --schema dbo --package ~.domain --testAutomatically --enableViews 
    6. module focus --moduleName ~
    7. module create --moduleName mvc --topLevelPackage ~.mvc --packaging POM
    8. web mvc setup
    9. web mvc all --package ~
    10. logging setup --level INFO
    11. perform package
    12. #perform eclipse

Step 3: Run your application

Exit roo shell. Find persistence.xml in your project and change to validate (otherwise database may be overwritten when something goes wrong). Execute maven package and deploy your application to the container.
In theory everything should take less then half an hour – unfortunately generated schema is not perfect – in my case there were problems with encoding (database runs Latin1, Roo uses UTF-8), also table and column names do not match perfectly – all those things need some manual handling.

Problems / Errors

“JDBC driver not available for ‘net.sourceforge.jtds.jdbc.Driver'”

When you see this, it means that OSGI JTDS wrapper is not loaded.
Solution has been found on springsource forum here.

  1. Check version of the driver set in pom.xml for your module (artifact jtds, my version is 1.2.4)
  2. Download and extract the driver from here
  3. Start is as an OSGI service; in a Roo console type (of course correct the path first):
    osgi start --url file:///[PATH_TO_YOUR_FILE]/jtds-1.2.4.jar
  4. Driver should be running now, you can verify it with
    osgi ps

    This will display list of all started OSGIs.

“Unable to get information from SQL Server”

When you’re getting this message, check if SQL Server Browser service is started. (In SQL Express 2005 by default it is not!). More information can be found here here

Schema ‘…’ does not exist or does not have any tables.

If you cannot access your server, the best thing to do before blaming Hibernate configuration is to check if you can connect and browse the desired tables using the same user and driver. I suggest using Squirrel SQL with combination with exactly the same driver as your webapp will use (you can grab it from your maven repository – ~/.m2/repository/[PATH_TO_DRIVER]). When connected, check if you can see and select the tables you want to access. If not, check your server’s config – permissions, permissions group, and association between schemas and logins.

DBRE error –Invalid byte 2 of 2-byte UTF-8 sequence

Generated dbre.xml is in UTF-8, even when contains data of a different charset. So far no solution ( see ROO-3214 for details). To deal with it you need to dump schema to the file ( database introspect –schema dbo –enableViews –file dbre.xml on line 4 above ) and then manually convert it to UTF-8 – i.e. on Windows: Notepad++ or JEdit – first select correct encoding, then choose “Convert to UTF-8 w/o BOM”).

Leave a Reply