说明：本文整理拓展自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