MyBatis处理jts-gis空间数据类型

原创已于 2025-01-08 19:05:07 修改 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java #spring boot #postgresql

于 2022-09-09 16:24:25 首次发布

Java 专栏收录该内容

6 篇文章

订阅专栏

这篇博客介绍了如何在Spring Boot应用中使用MyBatis和PostGIS进行空间数据类型（如点、多边形、多边形）的存储和处理。通过自定义的GeometryTypeHandler基类和针对MultiPolygon、Polygon的具体TypeHandler，实现了WKB与JTS Geometry对象之间的转换。配置了mybatis的type-handlers-package，确保TypeHandler能被扫描到，并提供了实体类与Mapper.xml的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

使用PostGis存储空间数据类型（点、多边形、多多边形等），使用java的jts库中的相应类型映射数据库中的字段。

依赖

使用spring-boot管理依赖版本，springboot版本：2.1.4.RELEASE

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.1</version>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

typehandler

Geometry 类型的通用tpyehandler

/**
 * 将WKB转换为JTS对应的Geometry几何类型，或反向转换
 */
public class AbstractGeometryTypeHandler<T extends Geometry> extends BaseTypeHandler<Geometry> {

    private static GeometryFactory geometryFactory;
    static {
        // 经度保留6为小数
        PrecisionModel precisionModel = new PrecisionModel(1000000);
        geometryFactory = new GeometryFactory(precisionModel, 4326);
    }
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Geometry geometry, JdbcType jdbcType) throws SQLException {
        PGobject object = new PGobject();
        object.setType("geometry");
        WKBWriter wkbWriter = new WKBWriter();
        byte[] bytes = wkbWriter.write(geometry);
        object.setValue(WKBWriter.toHex(bytes));
        preparedStatement.setObject(i, object);
    }

    @Override
    public Geometry getNullableResult(ResultSet resultSet, String s) throws SQLException {
        Object object = resultSet.getObject(s);
        return getGeometry(object);
    }

    @Override
    public Geometry getNullableResult(ResultSet resultSet, int i) throws SQLException {
        Object object = resultSet.getObject(i);
        return getGeometry(object);
    }

    @Override
    public Geometry getNullableResult(CallableStatement callableStatement, int i) throws SQLException {

        Object object = callableStatement.getObject(i);
        return getGeometry(object);
    }

    private Geometry getGeometry(Object object) {
        if (ObjectUtils.isEmpty(object)) {
            return null;
        }
        String source = object.toString();
        byte[] bytes = WKBReader.hexToBytes(source);
        WKBReader reader = new WKBReader(geometryFactory);
        try {
            Geometry geometry = reader.read(bytes);
            return geometry;
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

}

MultiPolygon类型的typehandler

@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(value = MultiPolygon.class)
public class MultiPolygonTypeHandler extends AbstractGeometryTypeHandler<MultiPolygon> {
}

Polygon类型的typehandler

@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(value = Polygon.class)
public class PolygonTypeHandler extends AbstractGeometryTypeHandler<Polygon> {
}

springboot配置

mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  # 自动扫描 typehandler
  type-handlers-package: com.example.demo.typehandler

实体类与mepper.xml示例

public class Area {
    private String name;
    private Polygon area;

    // getter/setter
    ...
}

<resultMap id="BaseResultMap" type="com.example.demo.entity.Area">
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <result column="area" jdbcType="OTHER" property="area"/>
</resultMap>

<insert id="insert">
    insert into area(name, area) values (#{name}, #{area})
</insert>

补充 MySQL typehandler

public class AbstractGeometryTypeHandler <T extends Geometry> extends BaseTypeHandler<Geometry> {
    private static GeometryFactory geometryFactory;

    static {
        // 经度保留6为小数
        PrecisionModel precisionModel = new PrecisionModel(1000000);
        geometryFactory = new GeometryFactory(precisionModel, 4326);
    }

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

    @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 bytes
     * @return
     */
    private Geometry convertToGeo(byte[] bytes) {
        if (bytes == null) {
            return null;
        }
        try {
            byte[] geomBytes = ByteBuffer.allocate(bytes.length - 4).order(ByteOrder.LITTLE_ENDIAN)
                    .put(bytes, 4, bytes.length - 4).array();
            Geometry geometry = new WKBReader(geometryFactory).read(geomBytes);
            return geometry;
        } catch (Exception e) {
            throw new  RuntimeException("解析Geometry数据失败", e);
        }
    }

    /**
     * geo转bytes
     * @param geometry
     * @return
     * @throws IOException
     */
    private byte[] convertToBytes(Geometry geometry) {
        WKBWriter wkbWriter = new WKBWriter(2, ByteOrderValues.LITTLE_ENDIAN, false);
        byte[] geometryBytes = wkbWriter.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;
    }
}