如何使用Spring Data JPA向MySQL插入地理位置数据

环境信息 #

• MySQL版本：5.7.17
• Spring Boot版本：2.5.6
• 引入的依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

实体类定义 #

@Entity
@Table(name = "USER_DETAILS")
@Getter
@Setter
@Data
public class UserDetails implements Serializable {

    private static final long serialVersionUID = 3937414011943770889L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Integer id;

    @Column(name = "USER_NAME")
    private String userName;

    @Column(name = "MOBILE_NO")
    private String mobileNo;

    @Column(name = "EMAIL_ID")
    private String emailId;

    @Column(name = "COORDINATE")
    private Point point;
}

业务代码 #

public Geometry wktToGeometry(String wellKnownText) throws ParseException {
    return new WKTReader().read(wellKnownText);
}

public void saveUser(String lat, String longitude) {
    try {
        final User user = getUserDetails();

        UserDetails userDetails = new UserDetails();
        userDetails.setUserName(user.getName());
        userDetails.setEmailId(user.getEmail());
        userDetails.setPoint( (Point) wktToGeometry("POINT (2 5)"));
        userDetailsRepository.save(userDetails);
    } catch (Exception e) {
        System.out.println("Something went wrong!\n" + e.getMessage());
    }
}

数据库表结构 #

CREATE TABLE USER_DETAILS (
      ID INT(10) NOT NULL AUTO_INCREMENT,
      USER_NAME VARCHAR(100) DEFAULT NULL,
      MOBILE_NO VARCHAR(10) DEFAULT NULL, 
      EMAIL_ID VARCHAR(100) DEFAULT NULL,
      COORDINATE POINT NOT NULL,
      PRIMARY KEY (ID),
      SPATIAL INDEX (COORDINATE)
    );

报错信息 #

Hibernate: insert into user_details (email_id, mobile_no, coordinate, user_name) values (?, ?, ?, ?)
[2m2023-02-10 11:15:19.905[0;39m [33m WARN[0;39m [35m11468[0;39m [2m---[0;39m [2m[nio-8080-exec-3][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m SQL Error: 1416, SQLState: 22001
[2m2023-02-10 11:15:19.906[0;39m [31mERROR[0;39m [35m11468[0;39m [2m---[0;39m [2m[nio-8080-exec-3][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper  [0;39m [2m:[0;39m Data truncation: Cannot get geometry object from data you send to the GEOMETRY field

1. 配置Hibernate空间方言 #

在application.properties或application.yml中添加MySQL 5.7对应的空间方言，让Hibernate正确识别并处理空间类型：

spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL57SpatialDialect

2. 修正实体类空间字段注解 #

给Point字段添加Hibernate空间类型注解，明确指定字段的映射规则：

import org.hibernate.annotations.Type;
import org.hibernate.spatial.GeometryType;
import com.vividsolutions.jts.geom.Point;

// ...其他原有注解

@Column(name = "COORDINATE", columnDefinition = "POINT")
@Type(type = "org.hibernate.spatial.GeometryType")
private Point point;

3. 确认JTS依赖完整性 #

hibernate-spatial依赖JTS（Java Topology Suite）库处理空间对象，Spring Boot 2.5.x搭配的版本会自动引入该依赖，若缺失可手动添加：

<dependency>
    <groupId>com.vividsolutions</groupId>
    <artifactId>jts</artifactId>
    <version>1.13</version>
</dependency>

4. 可选：直接构建Point对象（替代WKT解析） #

如果WKT文本解析存在隐藏问题，可直接通过JTS的GeometryFactory构造Point对象，避免解析环节出错：

import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.PrecisionModel;

// ...

public void saveUser(String lat, String longitude) {
    try {
        final User user = getUserDetails();
        // 4326是WGS84坐标系的EPSG编码，根据实际场景调整
        GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(), 4326);
        
        UserDetails userDetails = new UserDetails();
        userDetails.setUserName(user.getName());
        userDetails.setEmailId(user.getEmail());
        // JTS的Coordinate构造顺序为：x=经度，y=纬度
        userDetails.setPoint(geometryFactory.createPoint(new com.vividsolutions.jts.geom.Coordinate(5.0, 2.0)));
        userDetailsRepository.save(userDetails);
    } catch (Exception e) {
        System.out.println("Something went wrong!\n" + e.getMessage());
    }
}

5. 验证数据库连接URL #

确保数据库连接URL配置正确，MySQL 5.7无需额外空间参数，基础配置示例：

spring.datasource.url=jdbc:mysql://localhost:3306/your_db_name?useSSL=false&serverTimezone=UTC

内容的提问来源于stack exchange，提问作者Shiladittya Chakraborty