Mollusca Code

It seems like the latest Java update from Apple breaks the JDBC4 version of the postgresql database driver.

I currently have two Grails apps doing similar things. One (by accident or luck, your call) uses postgresql-604.jdbc3 as it’s database driver. The other one uses a different driver, namely postgres-604-jdbc4.

Today I updated my installed Java on the Mac using software update.

Suddenly one of the Grails apps was broken.

.... tries to work out what has gone wrong

I got a ClassNotFound Exception while loading the driver. So I tried to figure out where the two apps differ. It turns out that the only relevant difference between the two apps (database-driver-wise) is that one uses jdbc3 and the other jdbc4.

I’m not blaming anyone, but I suspect the update broke it.

So if anyone faces the same problem while running a Grails app, there you go: Replace jdbc4 with jdbc3!

And now part of the stacktrace for googleability:

Starting integration tests ...
Cannot load JDBC driver class 'org.postgresql.Driver'
java.lang.ClassNotFoundException: org.postgresql.Driver
    at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:316)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:280)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
    at org.codehaus.groovy.grails.cli.support.GrailsRootLoader.loadClass(GrailsRootLoader.java:45)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
    at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:374)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:164)

Addendum

And yes, I’m aware of the fact that the JVM on OSX is 1.5. But previously it used to work, so I figured this might still be of some use to some people. Maybe JDBC4 was a bad idea all along the way, however.

This is intended as a follow up to this artile: Grails + Spatial Data

We recently discovered (actually we were nudged in the right direction…) that one can completely do away with the manual hibernate mapping, by adding the following code to the domain class that contains spatial data:

class Event {
  String title
  String description
  Url url
  Point spatial

  static mapping = {
    columns {
      spatial type: org.hibernatespatial.GeometryUserType
    }
  }
}

I think it doesn’t get any sweeter than that.

I will probably not this a lot, but this one is necessary. I just received my copy of the book “Programming Groovy” by Venkat Subramaniam, read it through in a few days and I gotta say:

It’s awesome!

Very clear and concise explanations. Even MOPping is explained in very short and clear examples. I like that!

Calling stored procedures from Grails is not that hard to achieve. One needs to wade through the HQL documentation though, which I find slightly counter-intuitive. I’d rather see stuff like that on the grails page.

So, how do we call a stored procedure then?

What stored procedure?

Here’s the situation:

• The events from the last post are in the database now.
• The database has been spatially enabled according to the PostGIS plan.

We now want to find the closest events, to a certain point on the earth. Let’s assume we have successfully geocoded the point, so we have its coordinates in lat;long form.

The basic function that we want to call in plain SQL is:

select * from event e
  order by distance_sphere(
  Geometry('POINT(lat, long)'),e.location_id)

The distance_sphere() function calculates the distance of two points on the earth given in lat;long form. However the function takes two argumenty of type geometry, so we have to create one first.

Calling it from grails

To call this function from grails and get a nice an clean Collection of objects back (we don’t want to mess with ResultSet, do we?) we have to grab a hold of the current hibernate session. This is pretty easy: We just have it injected by grails:

Add the following line to the EventController class:

def sessionFactory

The sessionFactory object is now defined inside of this controller and will have its value injected by grails. Assuming that the function is called by some form using POST, the code for our controller function boils down to this:

def searchByLocation = {
  if (request.method == "POST"){
     def lat = params['lat']
     def long = params['long']

     def session = sessionFactory.currentSession

     def sql = "select * from " +
     "event e order by distance_sphere(setsrid" +
     "(geometry('POINT(${lat} ${long})'), 0)," +
     "e.location_id) limit 20;" 

     def results = session.createSQLQuery(sql).
       addEntity(Event.class).list()
  }
}

The setSRID() function from PostGIS has to be called, because by default grails in combination with hibernatespatial stores the points with the SRID value set to 0. This is no problem, as long as all coordinates you save are in the same reference system and you do not have to do any coordinate transformation. Otherwise you would have to handle that as well.

What’s nice about this method of calling the stored procedure through hibernate is that by calling addEntity() we can set the returned type and calling list() we get a nice and clean java collection.

That’s it so far…

Important note (This is new)

As pointed out by Matt, this code is vulnerable to SQL-Injection type attacks, if used directly like that in a setting where this is of any use to the attacker.

Please, with sugar on top, always sanitize form input data, before parsing it into a database query. In this case, this would have meant more code, but I should have added a note to the code. Mea culpa. To fix this simply make sure that the entered text looks exactly like float data and nothing else.

For a client/server application I’m currently developing, I chose grails (www.grails.org) as the framework to develop the web application and webservice part. Grails is a framework very similar in concepts to Ruby on Rails, which I personally like a lot for it’s convention over configuration attitude.

The main downside of grails, as far as I’m concerned is the huge amount of *.jar dependencies that it comes along with. This is, as long as applications do not stray from the standard data types of a web framework, very well hidden from the developer.

In this blog post I will outline a few points, on how to save geo-spatial points (no pun intended) using grails.

Read the rest of this entry

So this is it.

Grails + Hibernatespatial made me open a blog of my own.

Since grails in combination with spatial data is documented quite badly, I will try to fix this issue by posting my solution to the problem (Which basically boils down to a spring+hibernate problem). I hope this will make the string “grails hibernatespatial” google-able. At least all the solutions that pop up on google right now, are highly outdated or downright wrong XX(

The working solution will be up on monday, when I’m at work and have access to my code-base…