SpringBoot + MyBatis-Plus 读取 PostGIS 数据
样例数据库配置
用户
用户名：postgres
密码：postgres
数据库和表
数据库：test
表
Table "public.point_test"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(100) | | |
geom | geometry(Point,4326) | | |
Indexes:
"point_test_pkey" PRIMARY KEY, btree (id)
引入依赖
<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/net.postgis/postgis-jdbc -->
<dependency>
<groupId>net.postgis</groupId>
<artifactId>postgis-jdbc</artifactId>
<version>2025.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.locationtech.jts/jts-core -->
<dependency>
<groupId>org.locationtech.jts</groupId>
<artifactId>jts-core</artifactId>
<version>1.20.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-spring-boot3-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
<version>3.5.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.38</version>
</dependency>
调整属性设置
修改 resources/application.properties 文件
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=postgres
配置 JDBC 驱动、连接 URL、用户名和密码，根据实际情况修改
定义地理数据的 TypeHandler
我们会以 GeoJSON 的格式，读取数据库中相应的地理数据。这需要一个转换类，把 GeoJSON 转成相应的地理信息
package org.example.testgis.handler;
import net.postgis.jdbc.PGgeometry;
import net.postgis.jdbc.geometry.Geometry;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@MappedTypes({String.class})
public class PgGeometryTypeHandler extends BaseTypeHandler<String> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
PGgeometry pGgeometry = new PGgeometry(parameter);
Geometry geometry = pGgeometry.getGeometry();
geometry.setSrid(4326);
ps.setObject(i, pGgeometry);
}
@Override
public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
String string = rs.getString(columnName);
return getResult(string);
}
@Override
public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String string = rs.getString(columnIndex);
return getResult(string);
}
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String string = cs.getString(columnIndex);
return getResult(string);
}
private String getResult(String string) throws SQLException {
PGgeometry pGgeometry = new PGgeometry(string);
String s = pGgeometry.toString();
return s.replace("SRID=4326;", "");
}
}
定义 TypeHandler 方法说明
步骤
- 继承自
BaseTypeHandler类 - 实现以下四个方法：
setNonNullParameter：设置非空参数到PreparedStatementgetNullableResult(ResultSet, String)：从ResultSet按列名获取值getNullableResult(ResultSet, int)：从ResultSet按列索引获取值getNullableResult(CallableStatement, int)：从CallableStatement获取值
- 注解
@MappedTypes：指定Java类型
测试所用的 Entity
package org.example.testgis.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.*;
import org.example.testgis.handler.PgGeometryTypeHandler;
@TableName(value ="point_test", autoResultMap = true)
@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@ToString
public class PointTest {
@TableId
private Long id;
private String name;
@TableField(typeHandler = PgGeometryTypeHandler.class)
private String geom;
@TableField(exist = false)
private String geoJson;
}
注意设置 autoResultMap = true 和 @TableField(typeHandler = PgGeometryTypeHandler.class)
定义 Mapper
package org.example.testgis.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.example.testgis.entity.PointTest;
@Mapper
public interface PointTestMapper extends BaseMapper<PointTest> {
static final String FIND_GEOJSON_SQL = "<script>"
+ "select st_asgeojson(geom) as geoJson from point_test "
+ "where id = #{id} "
+ "</script>";
@Select(FIND_GEOJSON_SQL)
PointTest findGeoJsonById(@Param("id") Long id);
}
业务接口及其实现
业务接口
package org.example.testgis.service;
import org.example.testgis.entity.PointTest;
import java.util.List;
public interface IPointTestService {
PointTest selectById(Long id);
List<PointTest> selectList(PointTest point);
int insertPointTest(PointTest point);
int updatePointTest(PointTest point);
PointTest selectGeomById(Long id);
PointTest findGeoJsonById(Long id);
}
业务实现
package org.example.testgis.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.example.testgis.entity.PointTest;
import org.example.testgis.mapper.PointTestMapper;
import org.example.testgis.service.IPointTestService;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class PointTestServiceImpl extends ServiceImpl<PointTestMapper, PointTest> implements IPointTestService {
@Override
public PointTest selectById(Long id) {
return baseMapper.selectById(id);
}
@Override
public List<PointTest> selectList(PointTest point) {
QueryWrapper<PointTest> queryWrapper = new QueryWrapper<PointTest>();
queryWrapper.select("id,name,geom,st_asgeojson(geom) as geoJson");
return this.getBaseMapper().selectList(queryWrapper);
}
@Override
public int insertPointTest(PointTest point) {
return baseMapper.insert(point);
}
@Override
public int updatePointTest(PointTest point) {
return baseMapper.updateById(point);
}
@Override
public PointTest selectGeomById(Long id) {
QueryWrapper<PointTest> queryWrapper = new QueryWrapper<PointTest>();
queryWrapper.select("geom","st_asgeojson(geom) as geoJson");
queryWrapper.eq("id", id);
return this.getBaseMapper().selectOne(queryWrapper);
}
@Override
public PointTest findGeoJsonById(Long id) {
return baseMapper.findGeoJsonById(id);
}
}
功能验证
这里只举个简单的例子，设计两个接口，一个 post 插入数据，一个 get 查询插入的数据
实现测试控制器
package org.example.testgis.controller;
import org.example.testgis.entity.PointTest;
import org.example.testgis.service.IPointTestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class HelloController {
@Autowired
private IPointTestService pointTestService;
@GetMapping("/findAll")
public String findAll() {
List<PointTest> pointList = pointTestService.selectList(null);
System.out.println(pointList);
return pointList.toString();
}
@PostMapping("/insert")
public String insert(PointTest pointTest) {
pointTestService.insertPointTest(pointTest);
return "done";
}
}
测试
用 /insert 插入点，然后用 /findAll 查看是否成功插入
[PointTest(id=1, name=地点1, geom=POINT(1 1), geoJson={"type":"Point","coordinates":[1,1]}), PointTest(id=2, name=地点2, geom=POINT(2 2), geoJson={"type":"Point","coordinates":[2,2]})]
完整项目代码
https://gitee.com/tsun-chi-wong/springboot-mybatisplus-crud-postgis-example
浙公网安备 33010602011771号