广告投放网站平台,中铁建设门户网登录入口在哪,北京住房城乡建设厅网站,广告策划书撰写前言
在我们工作中想要实现将空间点位信息存储到数据库时,一般使用以下语句实现
INSERT INTO test-point ( point,text ) VALUES ( st_GeomFromText ( POINT(1 1) ),第1个点);update test-point set pointst_PointFromText(POINT(5 5)) where id 10;但是这样每次都要去编写新…前言
在我们工作中想要实现将空间点位信息存储到数据库时,一般使用以下语句实现
INSERT INTO test-point ( point,text ) VALUES ( st_GeomFromText ( POINT(1 1) ),第1个点);update test-point set pointst_PointFromText(POINT(5 5)) where id 10;但是这样每次都要去编写新增与编辑的sql语句非常麻烦,体现不出mybatisplus的便捷性,所以可以增加GeometryTypeHandler在mybatisplus生成新增编辑语句时将st_GeomFromText ()函数拼接到sql语句上,更加方便快捷
一.准备工作
首先项目添加依赖:
dependencygroupIdcom.vividsolutions/groupIdartifactIdjts/artifactIdversion1.13/version
/dependency在项目中加入GeometryTypeHandler
package com.sinosoft.springbootplus.lft.business.dispatch.map.common;import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.PrecisionModel;
import com.vividsolutions.jts.io.*;
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 java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;MappedTypes({String.class})
MappedJdbcTypes({JdbcType.OTHER})
Slf4j
public class GeometryTypeHandler extends BaseTypeHandlerString {Overridepublic void setNonNullParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType) throws SQLException {try{//String转GeometryGeometry geo new WKTReader(new GeometryFactory(new PrecisionModel())).read(s);// Geometry转WKBbyte[] geometryBytes new WKBWriter(2, ByteOrderValues.LITTLE_ENDIAN, false).write(geo);// 设置SRID为mysql默认的 0byte[] wkb new byte[geometryBytes.length4];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);}}Overridepublic 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;}Overridepublic 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;}Overridepublic 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);// 解析sridint 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 (),实现空间点位数据的录入和编辑了.
