SpringBoot集成MyBatis操作PostGIS数据库

参考：

SpringBoot2.7.4（6）：集成MyBatis连接PostGIS数据库_postgis-jdbc_碰碰qaq的博客-CSDN博客

一、依赖

需要导入的依赖有：

<dependencies><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-webartifactId>dependency><dependency><groupId>org.mybatis.spring.bootgroupId><artifactId>mybatis-spring-boot-starterartifactId><version>3.0.0version>dependency><dependency><groupId>org.postgresqlgroupId><artifactId>postgresqlartifactId>dependency><dependency><groupId>org.projectlombokgroupId><artifactId>lombokartifactId><optional>trueoptional>dependency><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-testartifactId><scope>testscope>dependency><dependency><groupId>net.postgisgroupId><artifactId>postgis-jdbcartifactId><version>2.5.0version>dependency>
dependencies>

二、配置文件

spring:datasource:url: jdbc:postgresql://localhost:5432/shuiwenusername: postgrespassword: 123456driver-class-name: org.postgresql.Drivermybatis:mapper-locations: classpath:mapper/*.xmltype-aliases-package: com.example.entityconfiguration:# 开启驼峰命名map-underscore-to-camel-case: true# 控制台打印SQL语句log-impl: org.apache.ibatis.logging.stdout.StdOutImpltype-handlers-package: com.example.mybatis

三、项目结构

3.1 Mybatis转换类

package com.example.mybatis;import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.postgis.Geometry;
import org.postgis.PGgeometry;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;/*** @author King* @description: 映射Geometry类与数据库中的Geometry类型* @create 2023-04-01-12:47-*/
public abstract class AbstractGeometryTypeHandler extends BaseTypeHandler {// 数据到数据库的回调方法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();}
}

package com.example.mybatis;import org.apache.ibatis.type.MappedTypes;
import org.postgis.Point;/*** @author King* @description: 映射Point类与数据库中的Point类型* @create 2023-04-01-12:47-*/
@MappedTypes(Point.class)
public class PointTypeHandler extends AbstractGeometryTypeHandler {
}

3.2 实体类

package com.example.entity;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.postgis.Point;/*** @author King* @description:* @create 2023-04-01-12:49-*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Station {private Integer gid;private String district;private String address;private String basin;private String riversys;private String river;private String station;private String type;private Double lng;private Double lat;private Point geometry;
}

3.3 Mapper接口

package com.example.mapper;import com.example.entity.Station;
import org.apache.ibatis.annotations.Mapper;/*** @author King* @description:* @create 2023-04-01-12:51-*/
@Mapper
public interface StationMapper {// 通过监测站点名称查询监测站点的信息Station getInfoByStation(String station);
}

3.4 Mapper文件

DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.StationMapper"><resultMap id="station" type="com.example.entity.Station"><id column="gid" property="gid"/><result column="address" jdbcType="VARCHAR" property="address"/><result column="station" jdbcType="VARCHAR" property="station"/><result column="district" jdbcType="VARCHAR" property="district"/><result column="basin" jdbcType="VARCHAR" property="basin"/><result column="type" jdbcType="VARCHAR" property="type"/><result column="river" jdbcType="VARCHAR" property="river"/><result column="riversys" jdbcType="VARCHAR" property="riversys"/><result column="lat" jdbcType="DOUBLE" property="lat"/><result column="lng" jdbcType="DOUBLE" property="lng"/><result column="geom" jdbcType="OTHER" property="geometry"typeHandler="com.example.mybatis.PointTypeHandler"/>resultMap><select id="getInfoByStation" resultMap="station">select * from station where station = #{station}select>
mapper>

3.5 测试

package com.example;import com.example.entity.Station;
import com.example.mapper.StationMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;@SpringBootTest
class JtsApplicationTests {@Autowiredprivate StationMapper stationMapper;@Testvoid contextLoads() {Station st = stationMapper.getInfoByStation("石门");System.out.println(st.toString());}}

测试结果：

JDBC Connection [HikariProxyConnection@873002645 wrapping org.postgresql.jdbc.PgConnection@2b6a0ea9] will not be managed by Spring
==>  Preparing: select * from station where station = ?
==> Parameters: 石门(String)
<==    Columns: gid, district, address, basin, riversys, river, station, type, lng, lat, geom
<==        Row: 1, 河南省 南阳市, 南阳市西峡县城郊乡十亩地村, 长江, 丹江, 老灌河, 石门, 水库站, 111.475200000000001, 33.369799999999998, 0101000020E6100000CAC342AD69DE5B40C8073D9B55AF4040
<==      Total: 1Station(gid=1, district=河南省 南阳市, address=南阳市西峡县城郊乡十亩地村, basin=长江, riversys=丹江, river=老灌河, station=石门, type=水库站, lng=111.4752, lat=33.3698, geometry=SRID=4326;POINT(111.4752 33.3698))

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！