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]}}
]
————————————————
版权声明：本文为CSDN博主「qq_35987023」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_35987023/article/details/131723981