springboot微服务架构整合postgreSQL数据库——存储geometry类型数据
目录
说明:本文整理拓展自https://github.com/eyougo/mybatis-typehandlers-postgis/blob/master/READM
1、要求
java1.7或更高版本。
2、pom.xml文件
pom.xml文件(这里是第3个依赖,其它依赖请忽略)
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.5</version>
</dependency>
<dependency>
<!-- https://mvnrepository.com/artifact/org.postgis/postgis-jdbc -->
<groupId>org.postgis</groupId>
<artifactId>postgis-jdbc</artifactId>
<version>1.3.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.eyougo/mybatis-typehandlers-postgis -->
<dependency>
<groupId>com.eyougo</groupId>
<artifactId>mybatis-typehandlers-postgis</artifactId>
<version>1.0</version>
</dependency>
3、configuration配置(根据自己的项目选择其一)
(1)If you are using MyBatis alone, add the type handlers to your mybatis-config.xml as follow:
<typeHandlers>
<!-- ... -->
<typeHandler handler="com.eyougo.mybatis.postgis.type.PointTypeHandler" />
<typeHandler handler="com.eyougo.mybatis.postgis.type.PolygonTypeHandler" />
</typeHandlers>
(2)If you are using MyBatis with Spring, add the type handlers package to the Spring configuration as follow:
With XML Configuration
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- ... -->
<property name="typeAliasesPackage" value="com.eyougo.mybatis.postgis.type" />
</bean>
Or with Java configuration
@Bean
public SqlSessionFactory sqlSessionFactory(Configuration config) {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
// ...
factory.setTypeHandlersPackage("com.eyougo.mybatis.postgis.type");
return factory.getObject();
}
(3)If you are using MyBatis with Spring Boot, add the type handlers package to the configuration file as follow:
application.properties
mybatis.type-handlers-package = com.eyougo.mybatis.postgis.type
Or application.yml(本文项目使用这种配置方式)
mybatis: type-handlers-package: com.eyougo.mybatis.postgis.type
支持类型:
Type handler | PostGIS Geometry API type | Available version |
Available version | Available version | 1.0 |
Available version | Available version | 1.0 |
Available version | Available version | 1.0 |
Available version | Available version | 1.0 |
4、springboot中自定义类型(PostGIS)
父类:
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;
/**
* Created by mei on 31/08/2017.
*/
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);
}
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();
}
}
多边形子类:
import com.eyougo.mybatis.postgis.type.AbstractGeometryTypeHandler;
import org.apache.ibatis.type.MappedTypes;
import org.postgis.Polygon;
/**
* Created by mei on 31/08/2017.
*/
@MappedTypes(Polygon.class)
public class PolygonTypeHandler extends AbstractGeometryTypeHandler<Polygon> {
}
点子类:
import com.eyougo.mybatis.postgis.type.AbstractGeometryTypeHandler;
import org.apache.ibatis.type.MappedTypes;
import org.postgis.Point;
/**
* Created by mei on 31/08/2017.
*/
@MappedTypes(Point.class)
public class PointTypeHandler extends AbstractGeometryTypeHandler<Point> {
}
多点子类:
import com.eyougo.mybatis.postgis.type.AbstractGeometryTypeHandler;
import org.apache.ibatis.type.MappedTypes;
import org.postgis.MultiPoint;
/**
* Created by mei on 04/09/2017.
*/
@MappedTypes(MultiPoint.class)
public class MultiPointTypeHandler extends AbstractGeometryTypeHandler<MultiPoint> {
}
线子类:
import com.eyougo.mybatis.postgis.type.AbstractGeometryTypeHandler;
import org.apache.ibatis.type.MappedTypes;
import org.postgis.LineString;
/**
* Created by mei on 04/09/2017.
*/
@MappedTypes(LineString.class)
public class LineStringTypeHandler extends AbstractGeometryTypeHandler<LineString> {
}
或者共用一个Geometry类型(上面的类型具体一点,这个包含以上几种类型)
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;
/**
* Created by mei on 31/08/2017.
*/
public class GeometryTypeHandler<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);
}
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();
}
}
使用的时候,在数据库表xml文件中geometry字段添加相应的自定义类型即可,比如:
<result column="lon_lats" jdbcType="OTHER" property="lonLats" typeHandler="com...postgisType.GeometryTypeHandler"/>
程序中的转化工具类(把String类型的坐标转化成相应的类型:Point、Polygon、LineString):
import org.postgis.LineString;
import org.postgis.LinearRing;
import org.postgis.Point;
import org.postgis.Polygon;
/**
* 坐标点格式化工具类
*/
public class CorrdinateUtils {
/**
* 格式化Polygon类型:
* 将Vertexts字符串转换成Polygon类型
* @param vertexes 多边形围栏形状点(顺时针或逆时针): "double,double; double,double; ...;double,double"
* @return Polygon
*/
public static Polygon formatPolygon(String vertexes) throws Exception{
vertexes = CorrdinateUtils.stringUtils(vertexes);
String[] points = vertexes.split(";");
int length = points.length;
Point[] pointArray = new Point[length + 1];
for (int i = 0;i<length;i++){
String[] xy = points[i].split(",");
pointArray[i] = new Point(Double.parseDouble(xy[0]),Double.parseDouble(xy[1]));
}
//首尾两点一致,封闭形成多边形
String[] firstPoint = points[0].split(",");
pointArray[length] = new Point(Double.parseDouble(firstPoint[0]),Double.parseDouble(firstPoint[1]));
LinearRing linearRing = new LinearRing(pointArray);
Polygon polygon = new Polygon(new LinearRing[]{linearRing});
return polygon;
}
/**
* 格式化Point类型:
* 将Vertexts字符串转换成Point类型
* @param vertexes 点坐标:"double,double"
* @return Point
*/
public static Point formatPoint(String vertexes) throws Exception{
vertexes = CorrdinateUtils.stringUtils(vertexes);
String[] split = vertexes.split(",");
Point point = new Point(Double.parseDouble(split[0]), Double.parseDouble(split[1]));
return point;
}
/**
* 格式化LineString类型:
* 将Vertexts字符串转换成LineString类型
* @param vertexes 折线点: "double,double; double,double; ...;double,double"
* @return LineString
*/
public static LineString formatLineString(String vertexes) throws Exception{
vertexes = CorrdinateUtils.stringUtils(vertexes);
String[] points = vertexes.split(";");
int length = points.length;
Point[] pointArray = new Point[length];
for (int i = 0;i<length;i++){
String[] xys = points[i].split(",");
pointArray[i] = new Point(Double.parseDouble(xys[0]),Double.parseDouble(xys[1]));
}
LineString lineString = new LineString(pointArray);
return lineString;
}
//过滤""号
public static String stringUtils(String str){
return str.replace("\"","");
}
}
5、例子
在xml文件中写特殊的sql时,就不需要使用相应的转化函数了,例如:
查询包含点POINT(1 1)的多边形
原先类似这种:(st_within(point,polygon)为postGIS中的特殊用途函数,这里请暂时忽略)
String point = "POINT(1 1)";
//字符串参数,必须使用函数ST_PointFromText('POINT(1 1)')来转成geometry类型
//st_asText() :把geometry转成类似文本类型(字符串)
select fence_id,fence_name,st_asText(fence_corrdinate),shape from fence where st_within(ST_PointFromText(#{point}),fence_corrdinate);
现在就直接写这种,跟普通SQL一样。(若是继承了BaseServiceImpl<T>/BaseService<T>,则一般化的SQL,可直接调用父类中通用的增删改查方法,不用单独在xml中写)
select * from fence where st_within(#{point} ,fence_corrdinate);
转载自:https://blog.csdn.net/xxwl122/article/details/88952287