Mybatis-plus读取和保存Postgis geometry数据_mybatis 保存point

---

• Mybatis-plus读取和保存Postgis geometry数据 - 简书 (jianshu.com)

SpringBoot项目，数据库为PostgreSQL，集成了PostGIS，需要实现Geometry数据的类型转换问题。

1 Maven依赖

<dependency>
    <groupId>net.postgis</groupId>
    <artifactId>postgis-jdbc</artifactId>
    <version>2.5.0</version>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.6</version>
</dependency>
1
2
3
4
5
6
7
8
9
10

2 创建类型转换类GeometryTypeHandler

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgis.PGgeometry;

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

@MappedTypes({String.class})
public class MyGeometryTypeHandler extends BaseTypeHandler<String> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        PGgeometry pGgeometry = new PGgeometry(parameter);
        ps.setObject(i, pGgeometry);
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        PGgeometry pGgeometry = new PGgeometry(rs.getString(columnName));
        if (pGgeometry == null) {
            return null;
        }
        return pGgeometry.toString();
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        PGgeometry pGgeometry = new PGgeometry(rs.getString(columnIndex));
        if (pGgeometry == null) {
            return null;
        }
        return pGgeometry.toString();
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {

        PGgeometry pGgeometry = new PGgeometry(cs.getString(columnIndex));
        if (pGgeometry == null) {
            return null;
        }
        return pGgeometry.toString();
    }
}
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

3 在实体类配置typeHandler

@TableField(typeHandler = GeometryTypeHandler.class)
private String wkb;
1
2

4 踩坑

看到3部分的时候，相当于拦截器都已经实现好了，但是怎么验证呢？

自己试了半天，也没找到方法，一直报类型转换错误，然后就开始断点一步一步向下走，最后发现了蹊跷。

在断点走到GeometryBuilder.class类中，才发现关键代码。

public static Geometry geomFromString(String value, BinaryParser bp, boolean haveM) throws SQLException {
    value = value.trim();
    int srid = 0;
    if (value.startsWith("SRID=")) {
        String[] parts = splitSRID(value);
        value = parts[1].trim();
        srid = Geometry.parseSRID(Integer.parseInt(parts[0].substring(5)));
    }

    //关键代码，解析传过来的数据类型
    Object result;
    if (!value.startsWith("00") && !value.startsWith("01")) {
        if (value.endsWith("EMPTY")) {
            result = new GeometryCollection();
        } else if (value.startsWith("MULTIPOLYGON")) {
            result = new MultiPolygon(value, haveM);
        } else if (value.startsWith("MULTILINESTRING")) {
            result = new MultiLineString(value, haveM);
        } else if (value.startsWith("MULTIPOINT")) {
            result = new MultiPoint(value, haveM);
        } else if (value.startsWith("POLYGON")) {
            result = new Polygon(value, haveM);
        } else if (value.startsWith("LINESTRING")) {
            result = new LineString(value, haveM);
        } else if (value.startsWith("POINT")) {
            result = new Point(value, haveM);
        } else {
            if (!value.startsWith("GEOMETRYCOLLECTION")) {
                throw new SQLException("Unknown type: " + value);
            }

            result = new GeometryCollection(value, haveM);
        }
    } else {
        result = bp.parse(value);
    }

    if (srid != 0) {
        ((Geometry)result).srid = srid;
    }

    return (Geometry)result;
}
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

4.1 示例封装数据

前端，可以封装一个专门的GIS组件，传数据。

本质上可以是数据构造成如下格式：

let Point = 'POINT(138.22 39.22)';
1

后端，为方便测试，直接拼接个字符串。

String gps = 'POINT(138.22 39.22)';
//然后正常Mybatis的持久化即可。
1
2