在我们工作中想要实现将空间点位信息存储到数据库时,一般使用以下语句实现
INSERT INTO `test-point` ( point,text ) VALUES ( st_GeomFromText ( 'POINT(1 1)' ),'第1个点');
update `test-point` set point=st_PointFromText('POINT(5 5)') where id =10;但是这样每次都要去编写新增与编辑的sql语句非常麻烦,体现不出mybatisplus的便捷性,所以可以增加GeometryTypeHandler在mybatisplus生成新增编辑语句时将st_GeomFromText ()函数拼接到sql语句上,更加方便快捷
一.准备工作
首先项目添加依赖:
<dependency>
<groupId>org.locationtech.jts</groupId>
<artifactId>jts-core</artifactId>
<version>${jts-core.version}</version>
</dependency>在项目中加入GeometryTypeHandler
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.PrecisionModel;
import org.locationtech.jts.io.*;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 空间数据类型处理器
*
* 参考：{@link https://blog.csdn.net/mcband/article/details/132099924}
*/
@MappedTypes({String.class})
@MappedJdbcTypes({JdbcType.OTHER})
@Slf4j
public class GeometryTypeHandler extends BaseTypeHandler<String> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {
try{
//String转Geometry
Geometry geo = new WKTReader(new GeometryFactory(new PrecisionModel())).read(s);
// Geometry转WKB
byte[] geometryBytes = new WKBWriter(2, ByteOrderValues.LITTLE_ENDIAN, false).write(geo);
// 设置SRID为mysql默认的 0
byte[] wkb = new byte[geometryBytes.length+4];
wkb[0] = wkb[1] = wkb[2] = wkb[3] = 0;
System.arraycopy(geometryBytes, 0, wkb, 4, geometryBytes.length);
preparedStatement.setBytes(i,wkb);
}catch (ParseException e){
log.error("坐标转换异常：【{}】",e.getMessage(),e);
}
}
@Override
public String getNullableResult(ResultSet resultSet, String s){
try(InputStream inputStream = resultSet.getBinaryStream(s)){
Geometry geo = getGeometryFromInputStream(inputStream);
if(geo != null){
return geo.toString();
}
}catch(Exception e){
log.error("坐标转换异常：【{}】",e.getMessage(),e);
}
return null;
}
@Override
public String getNullableResult(ResultSet resultSet, int i){
try(InputStream inputStream = resultSet.getBinaryStream(i)){
Geometry geo = getGeometryFromInputStream(inputStream);
if(geo != null){
return geo.toString();
}
}catch(Exception e){
log.error("坐标转换异常：【{}】",e.getMessage(),e);
}
return null;
}
@Override
public String getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return "";
}
/**
* 流 转 geometry
* */
private Geometry getGeometryFromInputStream(InputStream inputStream) throws Exception {
Geometry dbGeometry = null;
if (inputStream != null) {
// 二进制流转成字节数组
byte[] buffer = new byte[255];
int bytesRead;
ByteArrayOutputStream baos = new ByteArrayOutputStream();
while ((bytesRead = inputStream.read(buffer)) != -1) {
baos.write(buffer, 0, bytesRead);
}
// 得到字节数组
byte[] geometryAsBytes = baos.toByteArray();
// 字节数组小于5 异常
if (geometryAsBytes.length < 5) {
throw new RuntimeException("坐标异常");
}
//字节数组前4个字节表示srid 去掉
byte[] sridBytes = new byte[4];
System.arraycopy(geometryAsBytes, 0, sridBytes, 0, 4);
boolean bigEndian = (geometryAsBytes[4] == 0x00);
// 解析srid
int srid = 0;
if (bigEndian) {
for (byte sridByte : sridBytes) {
srid = (srid << 8) + (sridByte & 0xff);
}
} else {
for (int i = 0; i < sridBytes.length; i++) {
srid += (sridBytes[i] & 0xff) << (8 * i);
}
}
WKBReader wkbReader = new WKBReader();
// WKBReader 把字节数组转成geometry对象。
byte[] wkb = new byte[geometryAsBytes.length - 4];
System.arraycopy(geometryAsBytes, 4, wkb, 0, wkb.length);
dbGeometry = wkbReader.read(wkb);
dbGeometry.setSRID(srid);
}
return dbGeometry;
}
}二.使用
找到相对应的空间数据属性字段，加上下面的注解:
@TableField(typeHandler = GeometryTypeHandler.class)
这样的话正常传入字段内容point(1 2),在新增和编辑时mybatisplus就会自动拼接st_GeomFromText (),实现空间点位数据的录入和编辑了.
原文链接：https://blog.csdn.net/mcband/article/details/132099924
发表评论