Mybatis TypeHandler 解析转换 MySQL Geometry

Mybatis TypeHandler 解析转换 MySQL Geometry

• MySQL数据中存储Geometry类，存的是WKB格式，前4位是SRID值，所以在解析MySQL的Geometry类数据时，需要处理掉前4位二进制数，再后续的byte[]数组转为java Geometry类
• MySQL插入Geometry类型数据，直接插入WKB和WKT，MySQL是不识别的，会报错 com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field
  • MySQL 插入 Geometry类型数据，需要用到 ST_GeomFromText函数, 例如 insert into User(geom) values (ST_GeomFromText('POINT (1 2)'))

方式一

import lombok.SneakyThrows;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.PrecisionModel;
import org.locationtech.jts.io.InputStreamInStream;
import org.locationtech.jts.io.WKBReader;

import java.io.ByteArrayInputStream;
import java.nio.ByteBuffer;
import java.sql.*;


public class MysqlGeometryTypeHandler extends BaseTypeHandler<Geometry> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Geometry parameter, JdbcType jdbcType) throws SQLException {
        Connection conn = ps.getConnection();

        String wkt = parameter.toText();
        String sql = "SELECT ST_GeomFromText(?)";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, wkt);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Object obj = rs.getObject(1);
                ps.setObject(i, obj);
            } else {
                throw new SQLException("Failed to convert Geometry to database type");
            }
        }
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, String columnName) throws SQLException {
        final Object object = rs.getObject(columnName);
        return getGeometry(object);
    }

    @SneakyThrows
    private Geometry getGeometry(Object object) {
        ByteArrayInputStream inputStream = new ByteArrayInputStream((byte[]) object);
        byte[] sridBytes = new byte[4];
        inputStream.read(sridBytes);
        int srid = ByteBuffer.wrap(sridBytes).getInt();
        WKBReader wkbReader = new WKBReader(new GeometryFactory(new PrecisionModel(), srid));
        return wkbReader.read(new InputStreamInStream(inputStream));
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        final Object object = rs.getObject(columnIndex);
        return getGeometry(object);
    }

    @Override
    public Geometry getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        final Object object = cs.getObject(columnIndex);
        return getGeometry(object);
    }
}

  <resultMap id="BaseResultMap" type="cn.x.mybatis.po.UserPO">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="nickname" jdbcType="VARCHAR" property="nickname" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="age" jdbcType="VARCHAR" property="age" />
    <result column="phone" jdbcType="VARCHAR" property="phone" />
    <result column="addr" jdbcType="INTEGER" property="addr" />
    <result column="geom" jdbcType="OTHER" property="geom" typeHandler="cn.x.gis.mybatis.MysqlGeometryTypeHandler"/>
  </resultMap>

  <insert id="insertSelective" parameterType="cn.x.mybatis.po.UserPO">
    insert into sys_user2
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="username != null">
        username,
      </if>
      <if test="password != null">
        password,
      </if>
      <if test="geom != null">
        geom,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="username != null">
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="geom != null">
        #{geom,jdbcType=OTHER,typeHandler=cn.x.gis.mybatis.MysqlGeometryTypeHandler},
      </if>
    </trim>
  </insert>

    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            mapper.insertSelective(new UserPO() {{
                setUsername("test" + System.currentTimeMillis());
                setPassword("123456");
                setGeom(GeomUtils.createPoint(12, 12));
            }});
            session.commit();

            List<UserPO> user = mapper.getUser();
            System.out.println(GisJacksonUtils.toJson(user));
        }
    }

结果:

[
    {"id":1,"username":"test","password":"123","nickname":"nick","name":"张三","age":99,"phone":"18888888888","addr":"最高人民法院","geom":{"type":"Point","coordinates":[1.0,2.0]}},
    {"id":2,"username":"test1689315606183","password":"123456","nickname":"","name":"","age":0,"phone":"","addr":"","geom":{"type":"Point","coordinates":[12.0,12.0]}},
]

方式二

public class MysqlGeometryTypeHandler2 extends BaseTypeHandler<Geometry> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Geometry parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter.toText());
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, String columnName) throws SQLException {
        final Object object = rs.getObject(columnName);
        return getGeometry(object);
    }

    @SneakyThrows
    private Geometry getGeometry(Object object) {
        ByteArrayInputStream inputStream = new ByteArrayInputStream((byte[]) object);
        byte[] sridBytes = new byte[4];
        inputStream.read(sridBytes);
        int srid = ByteBuffer.wrap(sridBytes).getInt();
        WKBReader wkbReader = new WKBReader(new GeometryFactory(new PrecisionModel(), srid));
        return wkbReader.read(new InputStreamInStream(inputStream));
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        final Object object = rs.getObject(columnIndex);
        return getGeometry(object);
    }

    @Override
    public Geometry getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        final Object object = cs.getObject(columnIndex);
        return getGeometry(object);
    }
}

<insert id="insertSelective2" parameterType="cn.cjf.mybatis.po.UserPO">
    insert into sys_user2
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="username != null">
        username,
      </if>
      <if test="password != null">
        password,
      </if>
      <if test="geom != null">
        geom,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="username != null">
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="password != null">
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="geom != null">
        ST_GeomFromText(#{geom,jdbcType=OTHER,typeHandler=cn.cjf.gis.mybatis.MysqlGeometryTypeHandler2}),
      </if>
    </trim>
  </insert>

    @Test
    public void test3() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        try (SqlSession session = sqlSessionFactory.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            mapper.insertSelective2(new UserPO() {{
                setUsername("test" + System.currentTimeMillis());
                setPassword("123456");
                setGeom(GeomUtils.createPoint(12, 12));
            }});
            session.commit();

            List<UserPO> user = mapper.getUser();
            System.out.println(GisJacksonUtils.toJson(user));
        }
    }

结果

[
    {"id":1,"username":"test","password":"123","nickname":"nick","name":"张三","age":99,"phone":"18888888888","addr":"最高人民法院","geom":{"type":"Point","coordinates":[1.0,2.0]}},
    {"id":8,"username":"test1689317869524","password":"123456","nickname":"","name":"","age":0,"phone":"","addr":"","geom":{"type":"Point","coordinates":[12.0,12.0]}}
]