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

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

公众号：emanjusaka的编程栈
下面给出关键部分代码，完整代码请访问原文地址

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

环境参数

• SpringBoot
• MybatisPlus
• mysql

代码实现

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());
    }
}

测试结果

插入数据：

查询数据：

谦学于心，谷纳万物，静思致远，共筑收获之旅！
上面代码展示了关键部分，完整代码请访问原文地址