微码：自定义typeHandler将包含经纬度对象插入到mysql的point类型的字段中

博客：https://www.emanjusaka.top

公众号：emanjusaka的编程栈

mysql 中的 point 类型在 java 中没有对应的类型匹配，需要我们自定义 typeHandler 去处理。

环境参数

• SpringBoot

• MybatisPlus

• mysql

代码实现

数据库表

 CREATE TABLE `user` (
   `id` bigint DEFAULT NULL,
   `location` point DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

实体类

User.java

 package top.emanjusaka.domain;
 ​
 import com.baomidou.mybatisplus.annotation.TableField;
 import com.baomidou.mybatisplus.annotation.TableId;
 import com.baomidou.mybatisplus.annotation.TableName;
 import lombok.Data;
 import top.emanjusaka.config.typeHandler.GeomPointTypeHandler;
 ​
 /**
  * @Author emanjusaka
  * @Date 2024/11/28 17:33
  * @Version 1.0
  */
 @Data
 @TableName(value = "user", autoResultMap = true)
 public class User {
     @TableId
     private Long id;
     @TableField(value = "location", typeHandler = GeomPointTypeHandler.class)
     private GeomPoint location;
 ​
 }
 ​

GeomPoint.java

 package top.emanjusaka.domain;
 ​
 import lombok.AllArgsConstructor;
 import lombok.Data;
 ​
 /**
  * @Author emanjusaka
  * @Date 2024/11/28 17:19
  * @Version 1.0
  */
 @Data
 @AllArgsConstructor
 public class GeomPoint {
     /**
      * 经度
      */
     private double lat;
     /**
      * 纬度
      */
     private double lng;
 ​
     public GeomPoint() {
     }
 ​
     public GeomPoint parse(String pointString) {
         String[] latLng = pointString.toLowerCase().replaceAll("point\\(", "").replaceAll("\\)", "")
                 .split(" ");
         setLat(Double.parseDouble(latLng[0]));
         setLng(Double.parseDouble(latLng[1]));
         return this;
     }
 ​
     @Override
     public String toString() {
         return "point(" + lat + " " + lng + ")";
     }
 }
 ​

配置文件

 mybatis-plus:
   type-handlers-package: top.emanjusaka.config.typeHandler

typeHandler

GeomPointTypeHandler.java

 package top.emanjusaka.config.typeHandler;
 ​
 import org.apache.ibatis.type.BaseTypeHandler;
 import org.apache.ibatis.type.JdbcType;
 import top.emanjusaka.domain.GeomPoint;
 ​
 import java.sql.CallableStatement;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 ​
 public class GeomPointTypeHandler extends BaseTypeHandler<GeomPoint> {
     @Override
     public void setNonNullParameter(PreparedStatement ps, int i, GeomPoint parameter, JdbcType jdbcType) throws SQLException {
         ps.setString(i, parameter.toString());
     }
 ​
     @Override
     public GeomPoint getNullableResult(ResultSet rs, String columnName) throws SQLException {
         String columnValue = rs.getString(columnName);
         return new GeomPoint().parse(columnValue);
     }
 ​
     @Override
     public GeomPoint getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
         String columnValue = rs.getString(columnIndex);
         return new GeomPoint().parse(columnValue);
     }
 ​
     @Override
     public GeomPoint getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
         String columnValue = cs.getString(columnIndex);
         return new GeomPoint().parse(columnValue);
     }
 }

Mapper

UserMapper.java

 package top.emanjusaka.mapper;
 ​
 import com.baomidou.mybatisplus.core.mapper.BaseMapper;
 import top.emanjusaka.domain.User;
 ​
 /**
  * @Author emanjusaka
  * @Date 2024/11/28 17:46
  * @Version 1.0
  */
 public interface UserMapper extends BaseMapper<User> {
     void savePoint(User user);
 ​
     User getUserById(Long id);
 ​
 }
 ​

UserMapper.xml

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <mapper namespace="top.emanjusaka.mapper.UserMapper">
     <resultMap id="pointResultMap" type="top.emanjusaka.domain.User">
         <id column="id" property="id"/>
         <result column="location" property="location"
                 typeHandler="top.emanjusaka.config.typeHandler.GeomPointTypeHandler"/>
     </resultMap>
     <insert id="savePoint" parameterType="top.emanjusaka.domain.User">
         insert into user(id,location)
         values(#{id},ST_GeomFromText(#{location,typeHandler=top.emanjusaka.config.typeHandler.GeomPointTypeHandler}))
     </insert>
     <select id="getUserById" parameterType="long" resultType="top.emanjusaka.domain.User">
         select id,ST_ASTEXT(location) location from user where id = #{id}
     </select>
 </mapper>
 ​

测试类

UserTest.java

 package top.emanjusaka.point;
 ​
 import org.junit.jupiter.api.Test;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 import org.springframework.boot.test.context.SpringBootTest;
 import top.emanjusaka.domain.GeomPoint;
 import top.emanjusaka.domain.User;
 import top.emanjusaka.mapper.UserMapper;
 ​
 import javax.annotation.Resource;
 ​
 /**
  * @Author emanjusaka
  * @Date 2024/11/28 17:41
  * @Version 1.0
  */
 @SpringBootTest
 public class UserTest {
     private static final Logger log = LoggerFactory.getLogger(UserTest.class);
     @Resource
     private UserMapper userMapper;
 ​
     @Test
     void testPoint() {
         User user = new User();
         user.setLocation(new GeomPoint(3.14, 5.15));
         userMapper.savePoint(user);
     }
 ​
     @Test
     void testSelectPoint() {
         User user = userMapper.getUserById(1L);
         System.out.println("point = " + user.getLocation().getLat());
     }
 }
 ​

测试结果

插入数据：

查询数据：