How to store map geometry object to MS-SQL geometry column in Java?

Question

I am trying to store a geometry object into my MS-SQL database which has a table with a geometry column. I get geometry in JSON format.

Here I got the latest MSSQL-JDBC version which had datatype 'com.microsoft.sqlserver.jdbc.Geometry'.
This datatype is available after including the required dependency in the maven pom.xml.

But when I mention one of my MS-SQL geometry column datatype as 'com.microsoft.sqlserver.jdbc.Geometry' in the java Entity class and run the application, it throws error as below:

> Exception encountered during context initialization -  cancelling refresh attempt:  
org.springframework.beans.factory.BeanCreationException:  
Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]:  
Invocation of init method failed; nested exception is  javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is  org.hibernate.MappingException: Could not determine type for: com.microsoft.sqlserver.jdbc.Geometry, at table: GeoTable, for columns:  
[org.hibernate.mapping.Column(request_point)]

Below is code sample,

Entity class
import com.microsoft.sqlserver.jdbc.Geometry;

@Column(name = "request_point", columnDefinition = "Geometry")
private Geometry request_point;

pom.xml:

     <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-entitymanager</artifactId>
            </exclusion>
            <exclusion>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-core</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
     <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <version>7.0.0.jre10</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-spatial</artifactId>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.2.10.Final</version>
    </dependency>

Below lines I have in my application.properties

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true

Sample Geometry string -

{\"spatialReference\": {\"latestWkid\": 3434,\"wkid\": 4353}, \"x\": -10538019.079024673,\"y\": 4720603.9173474545}

I don't understand why my geometry datatype is not getting loaded, let me know if i am missing anything or any other approach to do the same.

Any help will be appreciated.

Answer 1

Thanks Karel, now I am able to save the geometry object to database using JpaRepository.

code change done are mentioned below,

@Entity class
import com.vividsolutions.jts.geom.Geometry;

@JsonSerialize(using = GeometryToJsonSerializer.class)
@JsonDeserialize(using = JsonToGeometryDeserializer.class)
@Column(name = "request_point", columnDefinition = "Geometry")
private Geometry request_point;


// JsonDeserializer method 
public class JsonToGeometryDeserializer extends JsonDeserializer<Geometry> {
@Override
public Geometry deserialize(JsonParser jp, DeserializationContext ctxt)
        throws IOException, JsonProcessingException {

    try {
        String text = jp.getText();
        if (text == null || text.length() <= 0)
            return null;

        MapGeometry geo = GeometryEngine.jsonToGeometry(text);
        String geomWkt = GeometryEngine.geometryToWkt(geo.getGeometry(), 0);

          WKTReader wktR = new WKTReader();
          Geometry geom = wktR.read(geomWkt);
          geom.setSRID(geo.getSpatialReference().getID());
        return geom;

    } catch (Exception e) {
        return null;

    }
}

}

by using this JsonDeserializer I can convert json geometry to geometry object and successfully save to MsSQL database.

Now I am stuck with retrieving the geometry object from MsSQL database.

I tried the below sample to fetch the geometry column as string but getting error,

@Query(value = "select request_point.STAsText() request_point from tablename where locate_request_guid=1?", nativeQuery = true)
String findByGUID(String guid);

but same is working with jdbcTemplate

  @Transactional(readOnly=true)
public locate_requestJDBC findUserById(String GUID) {
    return jdbcTemplate.queryForObject(
        "select locate_request_guid,user_name,work_description,request_point.STAsText() request_point, request_polygon.STAsText() request_polygon from tablename where locate_request_guid=?",
        new Object[]{GUID}, new LocateRowMapper());
}

What is the correct way of doing it, any input will be helpful.

Answer 2

I guess the reason is that Hibernate knows nothing about the type com.microsoft.sqlserver.jdbc.Geometry.

I notice you've included hibernate-spatial as a dependency. Hibernate Spatial provides Geometry types that are database-independent. See the documentation