How to insert into geo point column in spring data-jpa + mysql 8?

Question

My Environment

• mysql 8.0.25
• hibernate-core:5.4.32
• hibernate-spatial:5.4.32
• spring-boot2.5.4
• java 8

What I did

application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/database?serverTimezone=UTC&characterEncoding=UTF-8
    username: root
    password: password
  jpa:
    hibernate.ddl-auto: create
    generate-ddl: true
    database: mysql
    properties:
      hibernate.dialect: org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect

logging:
  level:
   org:
    hibernate:
      SQL: debug
      type: trace

Entity class

import com.example.mypackage.domain.BaseTimeEntity;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import org.springframework.data.geo.Point;

import javax.persistence.*;

@Getter
@NoArgsConstructor
@Entity
public class Party extends BaseTimeEntity { // BaseTimeEntity adds modifiedAt, createdAt columns

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(columnDefinition = "TEXT")
    private String title;

    @Column(columnDefinition = "POINT")
    private Point coordinate;

    @Builder
    public Party(Point coordinate, String title, String body) {
        this.coordinate = coordinate;
        this.title = title;
        this.body = body;
    }
}

Test

@SpringBootTest
class PartyRepositoryTest {

    @Autowired
    PartyRepository partyRepository;

    @Test
    public void register_party() {
        // Given
        Double x = 127.02558;
        Double y = 37.30160;
        Point coordinate = new Point(x, y);
        partyRepository.save(
            Party.builder()
                    .coordinate(coordinate)
                    .title("test title")
                    .build()
        );

        // When
        List<Party> partyList = partyRepository.findAll();

        // Then
        Party party = partyList.get(0);
        assertEquals(x, party.getCoordinate().getX());
        assertEquals(y, party.getCoordinate().getY());
    }

What I expected

insert row in 'party' table successfully

What actually happened

I got error. Log is as below.

insert into party (created_at, modified_at, body, coordinate, title) values (?, ?, ?, ?, ?)
binding parameter [1] as [TIMESTAMP] - [2021-09-12T14:45:31.018]
binding parameter [2] as [TIMESTAMP] - [2021-09-12T14:45:31.018]
binding parameter [3] as [VARCHAR] - []
binding parameter [4] as [VARBINARY] - [Point [x=127.025580, y=37.301600]]
binding parameter [5] as [VARCHAR] - [test title]
SQL Error: 1416, SQLState: 22001
Data truncation: Cannot get geometry object from data you send to the GEOMETRY field

Question

• Please Let me know what am I doing wrong?
• does hibernate-spatial supports mysql point?

Cabochon

Answer 1

You are using the wrong spatial type: org.springframework.data.geo.Point is not supported by Hibernate Spatial. Use either org.locationtech.jts.geom.* or org.geolatte.geom.* in your entity class and it should be fine.

Burdine

Answer 2

After a lot of research, that's how it worked for me:

• Language: Kotlin
• MySQL version: 8.0.23

build.gradle.kts

...
implementation("org.hibernate:hibernate-spatial:5.6.2.Final")
...

properties.yml

...
spring:
  jpa:
    properties:
      hibernate.dialect: org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect
    database-platform: org.hibernate.spatial.dialect.mysql.MySQL8SpatialDialect
...

entity:

package svns.mono.fad.springcore.data.entity

...
import org.locationtech.jts.geom.Point
...


@Entity
@Table(name = "issue")
data class IssueEntity(
    ...
    val location: Point,
    ...
)

Ichthyolite

Answer 3

1. add Point converter

import org.springframework.data.geo.Point;

import javax.persistence.AttributeConverter;
import java.nio.ByteBuffer;
import java.nio.ByteOrder;

public class PointConverter implements AttributeConverter<Point, byte[]> {
    @Override
    public byte[] convertToDatabaseColumn(Point point) {
        ByteBuffer wrap = ByteBuffer.allocate(25)
                .order(ByteOrder.LITTLE_ENDIAN);
        // mysql database default 0
        wrap.putInt(0);
        // LITTLE_ENDIAN
        wrap.put((byte) 1);
        // wkbType: MySQL 1 -> point
        wrap.putInt(1);
        // Y
        wrap.putDouble(point.getY());
        // X
        wrap.putDouble(point.getX());
        return wrap.array();
    }

    @Override
    public Point convertToEntityAttribute(byte[] bytes) {
        if (bytes == null) {
            return null;
        }
        ByteBuffer wrap = ByteBuffer.wrap(bytes).order(ByteOrder.LITTLE_ENDIAN);

        // SRID
        wrap.getInt();
        // endian
        wrap.get();
        // wkbType
        wrap.getInt();

        double y = wrap.getDouble();
        double x = wrap.getDouble();
        return new Point(x, y);
    }
}

2. add @Converter on Point field

@Entity
public class YourEntity {
    @Convert(converter = PointConverter.class)
    private Point yourCol;
}

Flodden