How to configure spring-boot project to work with inmemory spatial database for tests?

Question

Here is my config now. I want to use hibernate spatial to work with postgis in production.

spring:
  profiles: production

  datasource:
    platform: postgres
    url: jdbc:postgresql://192.168.99.100:5432/dragon
    username: dragon
    password: dragon

  database:
    driverClassName: org.postgresql.Driver

  jpa:
    database: POSTGRESQL
    database-platform: org.hibernate.spatial.dialect.postgis.PostgisDialect
    show-sql: true
    hibernate:
      ddl-auto: update

---

spring:
  profiles: development
  datasource: SpatialInMemoryDb

  jpa:
    database-platform: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
    hibernate:
      ddl-auto: create-drop

For tests all found is h2gis project.

public class SpatialInMemoryDb extends SingleConnectionDataSource{



    public SpatialInMemoryDb() {
        setDriverClassName("org.h2.Driver");
        setUrl("jdbc:g2:mem:test");
        setSuppressClose(true);
    }

    @Override
    public Connection getConnection() throws SQLException {
        System.out.println("************");
        Connection connection =  super.getConnection();
        try (Statement st = connection.createStatement()) {
            // Import spatial functions, domains and drivers
            // If you are using a file database, you have to do only that once.
            CreateSpatialExtension.initSpatialExtension(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

Not sure that it will work with geodbdialect or postgisdialect, altough it seems very close to postgisdialect.

Anyway can someone recommend some easy solution?

Answer 1

Combining GeoDBDialect with h2gis library works fine in H2. I can store and load com.vividsolutions.jts.geom.Polygon with no problem.

I'm using Hibernate 5.2 + org.hibernate:hibernate-spatial:1.2.4

Hibernate dialect: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect

Column type: geometry.

H2 database should be initialized as described in the h2gis documentation (https://github.com/orbisgis/h2gis). These should be one of the first sql, when you initialize the database.

CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load";
CALL H2GIS_SPATIAL();

(H2GISFunctions should be on the classpath.)

Answer 2

Just to make things easier for anyone else who may be trying to get all this to work @Mateusz Stefek answer is the correct approach to take. Below is all you need to ensure postgis works with your hibernate models and h2 db for your unit test cases. Take note below will not work with hibernate 4 so your best bet is to upgrade to version 5. Take note in hibernate 5 improved naming strategy doesnt work anymore if that phases you out you may have a look at other stackoverflow solutions: ImprovedNamingStrategy no longer working in Hibernate 5

Ensure you have the following dependencies

maven repos for hibernate spatial + h2gis

<repository>
    <id>OSGEO GeoTools repo</id>
    <url>http://download.osgeo.org/webdav/geotools</url>
</repository>

<repository>
   <id>Hibernate Spatial repo</id>
   <url>http://www.hibernatespatial.org/repository</url>
</repository>

maven dependencies

<dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-spatial</artifactId>
   <version>5.3.7.Final</version>
</dependency>

<dependency>
   <groupId>org.orbisgis</groupId>
   <artifactId>h2gis-functions</artifactId>
   <version>1.3.0</version>
   <scope>test</scope>
</dependency>

Hibernate JPA model

import com.vividsolutions.jts.geom.Polygon;

/**
 * setting columnDefintion = "geometry(Polygon,4326)" will not work as h2gis 
 * expects default type geometry not an explicit defintion of the actual type * point 
 * polygon, multipolygon etc
 */
@Column(name = "region_boundary", nullable = false, columnDefinition = "geometry")
private Polygon regionBoundary;

Below ensures spring boot can serialize our postgis geometry data from postgres when ever we call our REST API endpoints

import com.bedatadriven.jackson.datatype.jts.JtsModule;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class JacksonConfig {

    @Bean
    public JtsModule jtsModule() {
        return new JtsModule();
    }
}

If you use flyway you can enable it to run in your test script to ensure the folowing below gets executed on to your h2 db

your test application.properties file

flyway.url=jdbc:h2:mem:test;MODE=PostgreSQL;INIT=RUNSCRIPT FROM 'classpath:your_flyway_init.sql'

contents of your_flyway_init.sql script

CREATE SCHEMA IF NOT EXISTS "{your_schema_if_applicable}";

CREATE ALIAS IF NOT EXISTS H2GIS_SPATIAL FOR "org.h2gis.functions.factory.H2GISFunctions.load";
CALL H2GIS_SPATIAL();

Ensure your test application.properties file hibernate dialet points to GeoDBDialect

spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.h2geodb.GeoDBDialect