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;
}
