Java is my “Groundhog Day” language. It’s one that I use just infrequently enough to feel like I’m starting over every time a new requirement pops up. As a result of planning the system migration I discussed in my last post, I’ve been doing some work with it, which is my first sustained Java work since about 2006.
A migration of the type I’m looking at is really more of a re-write with a very detailed storyboard to work from. The system currently uses a home-grown approximation of ORM, so I’ve been tasked to look at using Hibernate in the target Java environment, with the goal of reducing the amount of code to be re-written and supported. Hibernate is, to put it mildly, not new. Nor is Hibernate Spatial, the spatial database extension to Hibernate. These tools are really just new to a team that has been totally immersed in a different tool set for the past few years. So I get to devise a way ahead.
Part of my tasking was to work with Hibernate Spatial, which converts HQL to native spatial SQL for your selected database dialect, which was PostGIS in my case. Using an HQL query editor, such as the one in NetBeans, you will see HQL such as this:
from Countries c where overlaps(c.polygonGeometry, (select envelope(d.polygonGeometry) from Countries d where d.countryIdint = 50)) = true
Correctly produces SQL such as this (assuming you have correctly created your class mappings):
select countries0_.country_id as col_0_0_ from public.countries countries0_ where st_overlaps(countries0_.polygon_geometry, (select st_envelope(countries1_.polygon_geometry) from public.countries countries1_ where countries1_.country_idint=50))=true
If we examine the source code of the PostGIS dialect, we can see that it extends the PostgreSQL dialect and adds various functions and types from PostGIS. We can also see that it doesn’t add nearly all of them. I tend to make heavy use of ST_AsGeoJSON in a lot of my applications, so I decided to follow the established pattern and extend the PostGIS dialect to include the functions I need. This turned out to be fairly trivial:
After this, I updated my hibernate.cfg.xml to use com.geomusings.dialect.PostgisDialectExtensions as its dialect and the following HQL:
select asgeojson(c.polygonGeometry) from Countries c where c.countryIdint = 50
Produced the following SQL:
select st_geojson(countries0_.polygon_geometry) from public.countries countries0_ where countries0_.country_idint=50
This indicates that the dialect is properly interpreting my new function and will (and did) properly execute the query from my application. Of course, I simply exposed some existing PostGIS functions here, but I am looking forward to do the same with some custom functions.
I alluded above to setting up mappings with Hibernate Spatial. I’ll address that topic in my next post.
While none of this technology is new, it’s been nice to orient myself to a different perspective and flex some long-atrophied muscles with this tool set.
