SpringBoot2.7.4（6）：集成MyBatis连接PostGIS数据库

0 pom依赖

<!-- postgresql数据库驱动-->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
<!--            runtime 指的是直接在运行时所需要的包，而非在编译时等时候需要的包-->
<!--            <scope>runtime</scope>-->
        </dependency>
        <!-- 补充mybatis缺少的JDBC-Type如postGis中的geometry类型 -->
        <dependency>
            <groupId>net.postgis</groupId>
            <artifactId>postgis-jdbc</artifactId>
            <version>2.5.0</version>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

---

1 postgis geometry与mybatis转换配置抽象类

public abstract class AbstractGeometryTypeHandler<T extends Geometry> extends BaseTypeHandler<T> {

    // 数据到数据库的回调方法
    public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
        PGgeometry geometry = new PGgeometry();
        geometry.setGeometry(parameter);
        ps.setObject(i, geometry);
    }

    // 字符串类型转换为Java的Type类型的方法
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        PGgeometry pGgeometry = (PGgeometry) rs.getObject(columnName);
        if (pGgeometry == null) {
            return null;
        }
        return (T) pGgeometry.getGeometry();
    }

    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        PGgeometry pGgeometry = (PGgeometry) rs.getObject(columnIndex);
        if (pGgeometry == null) {
            return null;
        }
        return (T) pGgeometry.getGeometry();
    }

    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        PGgeometry pGgeometry = (PGgeometry) cs.getObject(columnIndex);
        if (pGgeometry == null) {
            return null;
        }
        return (T) pGgeometry.getGeometry();
    }
}

@MappedTypes(Point.class)
public class PointTypeHandler extends AbstractGeometryTypeHandler<Point> {
}

---

2 Entity

@Data // 使用了lombok
public class HnCors {
    private Integer gid;
    private String name;
    private String coor;
    private String proper;
    private Point geom;
    private String geomJson;
}

---

3 Mapper

@Repository
public interface IHnCorsMapper {

    HnCors selectByNameGeom(String name);

    HnCors selectByNameJson(String name);

    int updateByNameSelective(HnCors hnCors);

}

---

4 SQL

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.heny.wqx.mapper.gis.IHnCorsMapper">
    <!--jdbcType:数据库字段类型-->
    <resultMap id="BaseResultMap" type="com.heny.wqx.entity.gis.HnCors">
        <id column="gid" property="gid"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="coor" jdbcType="VARCHAR" property="coor"/>
        <result column="proper" jdbcType="VARCHAR" property="proper"/>
        <result column="geom" jdbcType="OTHER" property="geom"
                typeHandler="com.heny.wqx.config.postgis.PointTypeHandler"/>
    </resultMap>

    <!-- st_astext   st_asgeojson  -->
    <sql id="Base_Columns_Geom">
        gid, name, coor, proper, geom
    </sql>

    <sql id="Base_Columns_GeoJson">
        gid, name, coor, proper, st_asgeojson(geom) as geomJson
    </sql>

    <select id="selectByNameGeom" parameterType="java.lang.String" resultMap="BaseResultMap">
        select
        <include refid="Base_Columns_Geom"/>
        from hn_cors where name = #{name}
    </select>

    <select id="selectByNameJson" parameterType="java.lang.String" resultType="com.heny.wqx.entity.gis.HnCors">
        select
        <include refid="Base_Columns_GeoJson"/>
        from hn_cors where name = #{name}
    </select>

    <update id="updateByNameSelective" parameterType="com.heny.wqx.entity.gis.HnCors">
        update hn_cors
        <set>
            <if test="coor != null">
                coor = #{coor,jdbcType=VARCHAR},
            </if>
            <if test="proper != null">
                proper = #{proper,jdbcType=VARCHAR},
            </if>
        </set>
        where name = #{name,jdbcType=VARCHAR}
    </update>
</mapper>

---

5 测试

@Test
    void testPgType() {
        HnCors hnCors1 = iHnCorsMapper.selectByNameGeom("G0001");
        HnCors hnCors2 = iHnCorsMapper.selectByNameJson("G0001");
        log.info("碰碰-testPgType--geom:{}", hnCors1);
        log.info("碰碰-testPgType--geojson:{}", hnCors2);
    }

结果：