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; = 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="" 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:

    select as id0_,
        airport0_.iataCode as iataCode0_,
        airport0_.icaoCode as icaoCode0_,
        airport0_.location as location0_, 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.

Leave a Reply

Your email address will not be published. Required fields are marked *