Moving your spring-roo hibernate project from Hypersonic to Postgresql

I’ve always been an ORM hater. However, recently the decision to use ORM on a project I am working on was made for me. So I borrowed a pair of my pro-ORM’s colleague’s moccasins so I could walk a few miles in them. To throw me further out of my comfort zone, this project is being done in java, a language I am quite rusty in.

The first thing I learned about ORMs is that at least in java, you don’t pick your database right away. You start off using an in-memory database like Hypersonic until your data model is fleshed out a bit. Our project is not quite there yet, but we wanted to get familiar with the procedure for doing that. I made an attempt to use SQL Server Denali CTP1 as our database. However, that was proving difficult and another colleague suggested I use a database that might be more “java friendly.” I decided to go with my first true love Postgres.

Our ORM was hibernate. We were using the SpringSource Tool Suite or STS. This includes the development tool spring roo. Amongst many other things, roo manages your maven dependencies, and configures your hibernate persistance layer. These are the particular tasks I will concentrate on for this howto.

Installing postgres

How to do this on your particular operating system is beyond the scope of this document. I went with postgres 9.1 beta 1 just because. The lastest version of postgres 8 should be fine as well. In the examples below I am assuming your database server is running on localhost, and the user name and password you use to connect to it are both postgres. Never do this on anything besides your local laptop, and don’t even do that on your laptop if your postgres port (5432 TCP) is opened to the world.

Installing the driver

According to a comment on this stackoverflow answer, You have to use the jdbc3 postgres driver and not the jdbc4 version for hibernate to be able to generate the DDL to make the tables. My experiments have confirmed this. To install it, startup the roo shell. The roo shell can either be started from your operating system shell via roo.sh/roo.bat, or from inside the SpringSource IDE, which is a very polished eclipse distro. If you are starting up the roo shell from a terminal or command prompt, first cd to the folder where your project is located. The command to install the postgres jdbc driver is as follows:

roo> dependency add --groupId postgresql --artifactId postgresql --version 9.0-801.jdbc3
Updated ROOTpom.xml [added dependency postgresql:postgresql:9.0-801.jdbc3]
roo>

This will take care of fetching the dependency and editing the pom.xml for you. If you want to attempt with a different version of the postgres driver, browse the different versions on mvnrepository.

Switching the persistence layer

Persistence layer is hibernate speak for database. This makes sense, because data persists in your database, as opposed to ram, cache, etc which is all meant to be temporary. Switching persistence layers in hibernate requires editing two files, persistence.xml and database.info. Editing these two by hand would violate the don’t repeat yourself rule. Luckily we can edit both with one command in the roo shell.

roo> persistence setup --database POSTGRES --provider HIBERNATE --databaseName myproject --userName postgres --password postgres
Updated ROOTpom.xml [added dependency postgresql:postgresql:8.4-701.jdbc3]
roo>

One thing to be aware of here. The database name needs to be lower case. If you used uppercase characters, your app will not be able to connect to it.

Your persistance.xml file will now look like this:

And your database.properties will look like this:

One manual step

I have not figured out how to get hibernate to execute CREATE DATABASE if the database does not exist. So connect to postgres, and execute the following:

CREATE DATABASE myproject;

Conclusion

Hopefully, all you will need to do is debug your project as a web applicatin and the tables in your application get built. Happy coding!