mybatisplus集成geometry实现增改功能

系统管理员 2023-10-14 13:26 3阅读 0赞

## 前言 ##

在我们工作中想要实现将空间点位信息存储到数据库时,一般使用以下语句实现

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语句上,更加方便快捷

## 一.准备工作 ##

1.  首先项目添加依赖:

<dependency>
        <groupId>com.vividsolutions</groupId>
        <artifactId>jts</artifactId>
        <version>1.13</version>
    </dependency>

1.  在项目中加入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 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)  
如下图  
![在这里插入图片描述][fd2802f4e5cb4819b3cb5b2a7f527136.png]  
这样的话正常传入字段内容point(1 2),在新增和编辑时mybatisplus就会自动拼接st\_GeomFromText (),实现空间点位数据的录入和编辑了.

[fd2802f4e5cb4819b3cb5b2a7f527136.png]: https://img-blog.csdnimg.cn/fd2802f4e5cb4819b3cb5b2a7f527136.png