mybatis+postgrelsql 实现实体类中含geometry、polygon、point等字段的转化
-
背景:
项目需要存储包含地理字段的业务信息,架构为mybais+pg,传统的写pg函数sql可以实现,但是太麻烦,且mybatisplus的基本用法就失效了,需要扩展进行geometry类型字段自动转化来简化开发。
-
思路:
1.利用mybatis类型转化器进行类型转化
2.扩展SimpleModule来反序列化字符串为Geometry类型,解决前端传进来的参数转化。
-
相关代码实现
1. 类型转化器
2. 反序列化
3. 注入容器
点击查看代码
package org.jeecg.modules.demo.typehandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKTReader;
import org.postgis.PGgeometry;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* mybatis 几何字段类型转换处理器 - geometry抽象类,由具体几何类型继承
*
* @Author ergui
* @Package:org.jeecg.modules.demo.typehandler
* @Project:zg-server
* @Date:2024/1/21 16:15
**/
@MappedJdbcTypes({JdbcType.OTHER})
@Slf4j
public class JTsGeometryTypeHandler<T extends Geometry> extends BaseTypeHandler<T> {
/**
* SRS-EPSG
*/
private static final int EPSG_CODE = 4490;
private final WKTReader wktReader = new WKTReader();
/**
* 插入 - 插入时设置参数类型
*
* @param preparedStatement
* @param i
* @param parameter GeoTools组织的几何类型实体
* @param jdbcType
* @throws SQLException
*/
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, T parameter, JdbcType jdbcType) throws SQLException {
// 获取要插入的自定义类型的(GeoTools的Geometry实体)实体,并进行类型检查
boolean typeCheck = parameter instanceof Geometry;
if (!typeCheck) {
preparedStatement.setObject(i, null);
log.error("MyBatis类型转换器插入参数非GeoTools的Geometry对象");
}
// 通过几何的WKT格式字符串构建PG的Geometry
PGgeometry pGgeometry = new PGgeometry(parameter.toText());
org.postgis.Geometry geometry = pGgeometry.getGeometry();
geometry.setSrid(EPSG_CODE);
preparedStatement.setObject(i, pGgeometry);
}
/**
* 获取 - 获取时转换回的自定义类型 - 根据列名获取
*
* @param resultSet
* @param columnName
* @return
* @throws SQLException
*/
@Override
public T getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
// 获取读取到的指定列存储的值并进行类型检查
Object result = resultSet.getObject(columnName);
boolean typeCheck = result instanceof PGgeometry;
if (!typeCheck) {
log.error("MyBatis类型转换器获取参数转自定义类型错误,列实际存储类型非org.postgis.PGgeometry,列实际存储类型");
return null;
}
// 检查并返回目标转换类型实例
return getResult((PGgeometry) result);
}
/**
* 获取 - 获取时转换回的自定义类型 - 根据索引位置获取
*
* @param resultSet
* @param columnIndex
* @return
* @throws SQLException
*/
@Override
public T getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException {
// 获取读取到的指定列存储的值并进行类型检查
Object result = resultSet.getObject(columnIndex);
boolean typeCheck = result instanceof PGgeometry;
if (!typeCheck) {
log.error("MyBatis类型转换器获取参数转自定义类型错误,列实际存储类型非org.postgis.PGgeometry,列实际存储类型");
return null;
}
// 检查并返回目标转换类型实例
return getResult((PGgeometry) result);
}
/**
* 获取 - 获取时转换回的自定义类型 - 根据存储过程获取
*
* @param callableStatement
* @param columnIndex
* @return
* @throws SQLException
*/
@Override
public T getNullableResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
// 获取读取到的指定列存储的值并进行类型检查
Object result = callableStatement.getObject(columnIndex);
boolean typeCheck = result instanceof PGgeometry;
if (!typeCheck) {
log.error("MyBatis类型转换器获取参数转自定义类型错误,列实际存储类型非org.postgis.PGgeometry,列实际存储类型");
return null;
}
// 检查并返回目标转换类型实例
return getResult((PGgeometry) result);
}
/**
* pgGeometry实例转GeoTools的Geometry实例
*
* @param pgGeometry
* @return
*/
private T getResult(PGgeometry pgGeometry) {
if (pgGeometry == null) {
return null;
}
// 替换掉pgWKT中关于SRID的字符串部分
// WKT-PG:"SRID=4326;POINT(118.88888888 36.6666666666)"
// WKT:"POINT(118.88888888 36.6666666666)"
String pgWkt = pgGeometry.toString();
String wkt = pgWkt.replace(String.format("SRID=%s;", EPSG_CODE), "");
// 通过WKT构建GeoTools的Geometry对象
try {
return (T) wktReader.read(wkt);
} catch (Exception e) {
log.error("解析WKT为GeoTools的Geometry对象失败,异常信息:" + e.toString());
return null;
}
}
// 从WKT中获取几何
private Geometry getGeometryFromWkt(String wkt) {
Geometry geometry = null;
try {
geometry = wktReader.read(wkt);
} catch (Exception e) {
e.printStackTrace();
}
if (geometry == null) {
return null;
}
return geometry;
}
// 从WKT中获取几何
private Geometry covertPgGeometry2JtsGeometry(PGgeometry pGgeometry) {
StringBuffer wkt = new StringBuffer();
pGgeometry.getGeometry().outerWKT(wkt);
Geometry geometry = null;
if (pGgeometry == null) {
return null;
} else {
try {
geometry = wktReader.read(wkt.toString());
} catch (ParseException e) {
throw new RuntimeException(e);
}
return geometry;
}
}
// 从WKT中获取几何
private PGgeometry covertJTsGeometry2PgGeometry(Geometry geometry) throws SQLException {
// 通过几何的WKT格式字符串构建PG的Geometry
PGgeometry pGgeometry = new PGgeometry(geometry.toText());
org.postgis.Geometry pgGeometry = pGgeometry.getGeometry();
pgGeometry.setSrid(EPSG_CODE);
return pGgeometry;
}
}
点击查看代码
package org.jeecg.modules.demo.typehandler.config;
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.JsonDeserializer;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;
import com.fasterxml.jackson.databind.module.SimpleModule;
import org.jeecg.modules.web.util.geoutils.GeometryDataCoverUtils;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.ParseException;
import java.io.IOException;
public class GeometryModule extends SimpleModule {
public GeometryModule() {
addDeserializer(Geometry.class, new WKTGeometryDeserializer());
addSerializer(Geometry.class, new WKTGeometrySerializer());
}
public static class WKTGeometryDeserializer extends JsonDeserializer<Geometry> {
private final org.locationtech.jts.io.WKTReader wktReader = new org.locationtech.jts.io.WKTReader();
@Override
public Geometry deserialize(JsonParser p, DeserializationContext ctxt) throws IOException {
String wkt = p.getText();
try {
return wktReader.read(wkt);
} catch (ParseException e) {
throw new RuntimeException(e);
}
}
}
public static class WKTGeometrySerializer extends JsonSerializer<Geometry> {
@Override
public void serialize(Geometry value, JsonGenerator gen, SerializerProvider serializers) throws IOException {
try {
gen.writeString(GeometryDataCoverUtils.geometry2Wkt(value));
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
点击查看代码
package org.jeecg.modules.demo.typehandler.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import com.fasterxml.jackson.databind.ObjectMapper;
import javax.annotation.PostConstruct;
@Configuration
public class JacksonConfig {
@Autowired
private ObjectMapper objectMapper;
@PostConstruct
public void init() {
objectMapper.registerModule(new GeometryModule());
}
}
点击查看代码
package org.jeecg.modules.demo.test.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import org.jeecg.modules.demo.typehandler.JTsGeometryTypeHandler;
import org.locationtech.jts.geom.Geometry;
/**
* @Author ergui
* @Package:org.jeecg.modules.demo.test.entity
* @Project:zg-server
* @name:GeomTest
* @Date:2024/1/22 10:10
* @Filename:GeomTest
**/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value = "测试DEMO对象", description = "测试DEMO")
@TableName(value = "yzz_test", autoResultMap = true)
public class GeomTest {
private String id;
@TableField(typeHandler = JTsGeometryTypeHandler.class)
private Geometry geom;
}
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)