java 往 PostgreSQL 插入 geometry(P

需要转成 Geometry对象才可以

image.png

依赖：

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

java代码:

package org.jeecg.modules.config;

import cn.hutool.extra.spring.SpringUtil;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.ByteOrderValues;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKBReader;
import com.vividsolutions.jts.io.WKBWriter;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @version 1.0
 * @date 2023/3/24
 * @apiNote
 **/
@MappedTypes(Geometry.class)
public class GeometryTypeHandler extends BaseTypeHandler<Geometry> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Geometry parameter, JdbcType jdbcType) throws SQLException {
        try {
            ps.setBytes(i, convertToBytes(parameter));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return convertToGeo(rs.getBytes(columnName));
    }

    @Override
    public Geometry getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return convertToGeo(rs.getBytes(columnIndex));
    }

    @Override
    public Geometry getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return convertToGeo(cs.getBytes(columnIndex));
    }

    /**
     * bytes转Geo对象
     * @param geometryAsBytes
     * @return
     */
    private Geometry convertToGeo(byte[] geometryAsBytes) {
        Geometry dbGeometry = null;
        // 字节数组小于5，说明geometry有问题
        if (geometryAsBytes.length < 5) {
            return null;
        }

        //这里是取字节数组的前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 (int i = 0; i < sridBytes.length; i++) {
                srid = (srid << 8) + (sridBytes[i] & 0xff);
            }
        } else {
            for (int i = 0; i < sridBytes.length; i++) {
                srid += (sridBytes[i] & 0xff) << (8 * i);
            }
        }
        //use the JTS WKBReader for WKB parsing
        WKBReader wkbReader = new WKBReader();
        // 使用geotool的WKBReader 把字节数组转成geometry对象。
        byte[] wkb = new byte[geometryAsBytes.length - 4];
        System.arraycopy(geometryAsBytes, 4, wkb, 0, wkb.length);
        try {
            dbGeometry = wkbReader.read(wkb);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        dbGeometry.setSRID(srid);
        return dbGeometry;
    }

    /**
     * geo转bytes
     * @param geometry
     * @return
     * @throws IOException
     */
    private byte[] convertToBytes(Geometry geometry) throws IOException {
        byte[] geometryBytes = SpringUtil.getBean(WKBWriter.class).write(geometry);
        byte[] wkb = new byte[geometryBytes.length + 4];
        //设置SRID为4326
        ByteOrderValues.putInt(4326, wkb, ByteOrderValues.LITTLE_ENDIAN);
        System.arraycopy(geometryBytes, 0, wkb, 4, geometryBytes.length);
        return wkb;
    }

}

mysql插入语句:

INSERT INTO `sw_space` (the_geom , the_type , area_code ,create_time , is_delete)
VALUES (
           ST_GeomFromText(#{theGeom}),#{theType},#{areaCode},now(),#{isDelete}
       )

image.png