MybatisPlus 中使用 geometry 字段

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

/**
 * MyBatisPlus 针对 Geometry 字段的数据库读写处理
 *
 * @author GuoYingdong
 * @date 2023/04/03
 */
@Slf4j
@MappedJdbcTypes(value = JdbcType.JAVA_OBJECT, includeNullJdbcType = true)
@MappedTypes({Geometry.class})
public class GeometryTypeHandler extends BaseTypeHandler<Geometry> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Geometry geometry, JdbcType jdbcType) throws SQLException {
        preparedStatement.setObject(i, toPGobject(geometry));
    }

    private static Geometry toGeometry(String hexString) {
        try {
            byte[] d = WKBReader.hexToBytes(hexString);
            return new WKBReader().read(d);
        } catch (ParseException e) {
            log.error("几何字段解析失败。{}", hexString, e);
            return null;
        }
    }

    private String toPGobject(Geometry geometry) {
        if (geometry.getSRID() == 0) {
            geometry.setSRID(4326);
        }
        int dimension = dimension(geometry);
        String wkt = new WKTWriter(dimension).write(geometry);
        return "SRID=" + geometry.getSRID() + ";" + wkt;
    }


    public static int dimension(Geometry geo) {
        if (geo.getCoordinates() != null && geo.getCoordinates().length > 0) {
            return Objects.equals(NULL_ORDINATE, geo.getCoordinates()[0].getZ()) ? 2 : 3;
        } else {
            // TODO: 2023/2/3
            log.error("无法判断geometry是否包含Z值,将保存为3维");
            return 3;
        }
    }

    @Override
    public Geometry getNullableResult(ResultSet resultSet, String s) throws SQLException {
        if (resultSet.getObject(s) == null) {
            return null;
        }
        String string = ((PGobject) resultSet.getObject(s)).getValue();
        return toGeometry(string);
    }


    @Override
    public Geometry getNullableResult(ResultSet resultSet, int i) throws SQLException {
        if (resultSet.getObject(i) == null) {
            return null;
        }
        String string = ((PGobject) resultSet.getObject(i)).getValue();
        return toGeometry(string);
    }

    @Override
    public Geometry getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        if (callableStatement.getObject(i) == null) {
            return null;
        }
        String string = ((PGobject) callableStatement.getObject(i)).getValue();
        return toGeometry(string);
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 注意数据库配置要添加：?stringtype=unspecified，不然插入数据会报错
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://192.168.10.96:5432/aerial_plan_service?stringtype=unspecified

mybatis-plus:
  type-handlers-package: com.gear.common.config
  

1
2
3
4
5

<dependency>
    <groupId>org.n52.jackson</groupId>
    <artifactId>jackson-datatype-jts</artifactId>
    <version>1.2.9</version>
</dependency>

1
2
3

@JsonSerialize(using = GeometrySerializer.class)
@JsonDeserialize(using = GeometryDeserializer.class)
private Geometry geom;