Unit Testing of Spatial Queries

One reason why native SQL should be avoided is that it is not unit testable, so you never will get fast feedback if you break some query or the application model does no longer match the DB model. Another reason is, that your customers might already have a database vendor of their choice and you don’t want to force them to buy expensive licences from another one.

Even for spatial queries it is possible to use an abstraction layer and support multiple database vendors. Thanks to Hiberante Spatial or Eclipse Link extensions.

And if you use Hibernate Spatial in conjunction with GeoDB, a spatial extension of H2, which can run In-Memory, it is possible to execute spatial queries within unit tests. Which is a beautiful thing.

Here as an example an Entity with some spatial information (a location point) and a Repository based on JPA:

package at.nonblocking.geodb;

import com.vividsolutions.jts.geom.Point;

public class Airport {

  private long id;

  private String icaoCode;
  private String iataCode;
  private String name;

  @Type(type = "org.hibernate.spatial.GeometryType")
  private Point location;

  public Airport() {}

  public Airport(String icaoCode, String iataCode, String name, Point location) {
      this.icaoCode = icaoCode;
      this.iataCode = iataCode;
      this.name = name;
      this.location = location;

  //Getter and setters omitted
package at.nonblocking.geodb;

import com.vividsolutions.jts.geom.Geometry;

public class AirportRepositoryJPAImpl implements AirportRepository {

    private EntityManager entityManager;

    public Airport getById(long id) {
        return this.entityManager.find(Airport.class, id);

    public List<Airport> findWithin(Geometry shape) {
        return this.entityManager.createQuery(
            "from Airport where within(location, :shape) = true", 
            .setParameter("shape", shape)

Hibernate Spatial comes with several geometry types you have to annotate it with @Type(type = “org.hibernate.spatial.GeometryType”). Furthermore it adds some spatial functions to JPQL, such as within(), intersect() and so on.

To be able to write a unit tests against the Repository above, first add the following dependencies to your POM file:



You might have to add the following repository for GeoDB:


Next create the following Spring context to bootstrap GeoDB and Hibernate:

<bean id="airportRepository" class="at.nonblocking.geodb.AirportRepositoryJPAImpl"/>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="persistenceProviderClass" value="org.hibernate.ejb.HibernatePersistence"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    <property name="jpaPropertyMap">
            <entry key="hibernate.dialect" 
              value="org.hibernate.spatial.dialect.h2geodb.GeoDBDialect" />
            <entry key="hibernate.hbm2ddl.auto" value="update" />
            <entry key="hibernate.show_sql" value="true" />
            <entry key="hibernate.format_sql" value="true" />
    <property name="packagesToScan">

<bean id="dataSource" class="at.nonblocking.geodb.GeoDBInMemoryDataSource" />

<bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory"/>

The only difference to a normal Hibernate 4 setup is the dialect org.hibernate.spatial.dialect.h2geodb.GeoDBDialect.

For GeoDB it was necessary to create a custom data source, because all connections need to be initialized through GeoDB.init(). GeoDBInMemoryDataSource looks like this:

import geodb.GeoDB;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

public class GeoDBInMemoryDataSource extends SingleConnectionDataSource {

    public GeoDBInMemoryDataSource() {

    public Connection getConnection() throws SQLException {
        Connection conn = super.getConnection();
        return conn;

And now you can use Spring Test to write a JUnit test:

@TransactionConfiguration(defaultRollback = true)
public class InMemoryGeoDbSpatial2Test {

    private AirportRepository airportRepository;

    private EntityManager entityManager;

    public void insertTestData() {
        GeometryFactory geometryFactory = new GeometryFactory();

        Airport airportVienna = new Airport("VIE", "LOWW", "Wien Schwechat",
          geometryFactory.createPoint(new Coordinate(16.569613472222223, 48.11034788888889)));
        Airport airportPrague = new Airport("PRG", "LKPR", "Prague Ruzyne", 
          geometryFactory.createPoint(new Coordinate(14.26, 50.100833333333334)));


    public void testFindAirportsAroundVienna() {
        GeometricShapeFactory shapeFactory = new GeometricShapeFactory();
        shapeFactory.setCentre(new Coordinate(16.366667, 48.2));

        Geometry aroundViennaShape = shapeFactory.createCircle();

        List<Airport> airports = this.airportRepository.findWithin(aroundViennaShape);

        assertTrue(airports.size() == 1);
        assertEquals("Wien Schwechat", airports.get(0).getName());

And here the Hibernate SQL output:

        airport0_.id as id0_,
        airport0_.iataCode as iataCode0_,
        airport0_.icaoCode as icaoCode0_,
        airport0_.location as location0_,
        airport0_.name as name0_ 
        Airport airport0_ 
        ST_Within(airport0_.location, ?)=1

You can see, Hibernate Spatial translates the JPQL within() function to ST_Within. On Oracle it would be translated to MDSYS.OGC_WITHIN.

Have fun with unit testing spatial queries.

Automatically logging slow Hibernate queries

When you’re using Hibernate you can either set the property hibernate.show_sql to true or enable the logger org.hibernate.SQL to display the generated SQL statements. But the generated log output does not include the (named) parameter values or – rather more interesting – the execution time.

This is where jdbcdslog comes in, it enables logging at the JDBC level and has some quite nice features:

  • Simple configuration with Spring as a proxy data source that wraps the actual data source
  • Outputs the execution time
  • Comes with a special logger for “slow” statements with a configurable threshold time
  • “Inlines” the parameter values, hence you can simply copy the outputted SQL into your DB administration tool and execute it manually

Here the few steps necessary to enable a slow statement logger in your application:

Add the maven dependency:


Use the jdbcdslog proxy datasource in your Spring context:

<bean id="dataSource" class="org.jdbcdslog.ConnectionPoolDataSourceProxy">
    	<property name="targetDSDirect">
    	  <jee:jndi-lookup resource-ref="true" jndi-name="jdbc/myDatasource">

Add a property file jdbcdslog.properties to /src/main/resources with the following content:


With this properties all SQL statements that last longer than 500 ms are outputted to the logger org.jdbcdslog.SlowQueryLogger at info level.

When you’re using logback as logging facility you would enable the slow statements like below:

<logger name="org.jdbcdslog.SlowQueryLogger">
	<level value="info" />
	<appender-ref ref="STDOUT" />
	<appender-ref ref="FILE_SLOW_SQL" />

And now you’ll see all slow SQL statements in the console during development. Pretty cool, isn’t it?

Exposing Hibernate and Ehcache statistics through JMX

When monitoring an JEE application with Java Persistence it’s useful to expose some statistics via JMX. If you’re using the common combination of Spring, Hibernate and Ehcache as 2nd level cache, you can achieve that by adding a few lines to your Spring context.

Suppose that you have an EntityManagerFactory configuration with a singleton Ehcache such like this one:

 <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="persistenceUnitName" value="myPersistenceUnit"
  <property name="jpaVendorAdapter">
    <bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
      <property name="databasePlatform" value="org.hibernate.dialect.PostgreSQLDialect" />
  <property name="jpaProperties">
      <prop key="hibernate.generate_statistics">true</prop>
      <!-- Activate 2n level cache -->
      <prop key="javax.persistence.sharedCache.mode">ENABLE_SELECTIVE</prop>
      <prop key="hibernate.cache.use_second_level_cache">true</prop>
      <prop key="hibernate.cache.use_query_cache">true</prop>
      <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory</prop>				

Then you can expose the Hibernate and the Ehache statistics by adding this snippet to your configuration:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

	<context:mbean-server />

	<bean id="mbeanExporter" class="org.springframework.jmx.export.MBeanExporter">
		<property name="beans">
				<entry key="MyApp:name=hibernateStatisticsMBean" value-ref="hibernateStatisticsMBean" />
				<entry key="MyApp:name=ehCacheManagerMBean" value-ref="ehCacheManagerMBean" />

	<bean name="hibernateStatisticsMBean" class="org.hibernate.jmx.StatisticsService">
		<property name="sessionFactory" value="#{entityManagerFactory.getSessionFactory()}" />

	<bean name="ehCacheManagerMBean" class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean" >
		<property name="shared" value="true" />

The get the native Hibernate SessionFactory from the entityManagerFactory I’ve used Spring Expression Language support (line 19).

When you now launch jconsole or jvisualvm you should be able to see your beans in the MBeans section: