Wednesday, May 21, 2008

How to migrate from hsqldb to h2

Recently from a udig IRC meeting I got a real appetite to try out the H2 database, since the google summer of code this year will bring the geotools module to have spatial indexing...

Well, in JGrass we had based all out-of-the-box database needs (non spatial) on Hsqldb, so I wanted to try the substitution. Well, since they are all based on jdbc, the thing has been fairly easy. Some interface extracted, some new plugin, and everything was fine. Not much to say about that.

But can you note something from the code below?


// start the server
String[] args = {"-tcp", "-tcpPort", String.valueOf(_port)};
tcpServer = Server.createTcpServer(args).start();
args = new String[]{"-web", "-webPort", String.valueOf(_port + 1)};
webServer = Server.createWebServer(args).start();

// connect
Class.forName(DRIVER);
url = "jdbc:h2:tcp://localhost:" + _port + "/" + database;
con = DriverManager.getConnection(url, user, _passwd);


With hsqldb I had a problem with not beeing able to access the database while JGrass being connected to it (which is nasty because of much development time lost). Also when bringing the application to client we usually had to ship also a db client like Squirrelsql to give a way to access the database.

H2 comes with a webserver instance with some nice application to browse the database tables and do queries:



Once JGrass is started I can access through the webbrowser my machine on the port I defined and I get the access login you can see in the picture above in the browser behind JGrass. JGrass in that case backs up the content of the annotationslayer in the H2 database booted together with JGrass.

Once entered, I get to see the following:


That is a great thing for keeping things under control while developing.
I think I will stay on H2 for a little while :)

No comments: