mybatis 照射oracle spatial JGeometry 类型并调用存储过程

mybatis 映射oracle spatial JGeometry 类型并调用存储过程。
有个需求是经纬度坐标字符串通过 http请求，后台调用一个 oracle存储过程.

因为调用oracle库，并且需要用到spatial特性的geometry类型的字段，所以就用了oracle自己的类库。
oracle.spatial.geometry.JGeometry。

我这里涉及到4个jar包。
sdoapi.jar
sdotopo.jar
sdoutl.jar
sdonm.jar

jar包一般需要从oracle官网下载，如果有服务器端，一般在${ORACLE_HOME}md/jlib 目录可以找到。

如果mybatis 想要映射到JGeometry类型，需要将设置类型成STRUCT.
必须实现mybatis提供的接口。参考下面的博客。
http://blog.****.net/zhaowenbo168/article/details/46943509

上面博客中有个问题，就是如果你用到某些连接池，mybatis调用 TypeHandler的时候，包装的连接不能强制转成 OracleConnection ，需要判断连接池中的连接是否是OracleConnection的包装，如果是需要拆包，然后利用原始连接构造STRUCT对象。看下面代码setParameter方法。

import java.sql.*;

import oracle.jdbc.driver.OracleConnection;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.apache.ibatis.type.TypeHandler;

@MappedTypes({JGeometry.class})
@MappedJdbcTypes({JdbcType.STRUCT})//这两个Mapped也可不需要
public class SdoGeometryTypeHandler implements TypeHandler<JGeometry> {

    @Override
    public void setParameter(PreparedStatement ps, int i, JGeometry parameter,
                             JdbcType jdbcType) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn = ps.getConnection();
        OracleConnection oracleConnection= null;
        if(conn.isWrapperFor(OracleConnection.class)){
            oracleConnection = conn.unwrap(OracleConnection.class);
            STRUCT dbObject = JGeometry.store(parameter, oracleConnection);
            ps.setObject(i, dbObject);
        }
        else{
            STRUCT dbObject = JGeometry.store(parameter, conn);
            ps.setObject(i, dbObject);
        }

    }

    @Override
    public JGeometry getResult(ResultSet rs, String columnName)
            throws SQLException {
        // TODO Auto-generated method stub
        STRUCT st = (STRUCT) rs.getObject(columnName);
        if (st != null) {
            return JGeometry.load(st);
        }
        return null;
    }

    @Override
    public JGeometry getResult(ResultSet rs, int columnIndex)
            throws SQLException {
        // TODO Auto-generated method stub
        STRUCT st = (STRUCT) rs.getObject(columnIndex);
        if (st != null) {
            return JGeometry.load(st);
        }
        return null;
    }

    @Override
    public JGeometry getResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        // TODO Auto-generated method stub
        STRUCT st = (STRUCT) cs.getObject(columnIndex);
        if (st != null) {
            return JGeometry.load(st);
        }
        return null;
    }

}

接下来就是配置xml文件了。mybatisconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 配置映射类的别名 -->
    <typeAliases>

        <typeAlias alias="Usermodel" type="com.xxx.model.Usermodel"/>
        <typeAlias type="oracle.spatial.geometry.JGeometry" alias="JGeometry" />
        <typeAlias type="com.xxx.model.GeometryTest" alias="Geometry"/>

    </typeAliases>

    <typeHandlers>
        <typeHandler handler="com.xxx.config.SdoGeometryTypeHandler" javaType="JGeometry"/>
    </typeHandlers>
    <!-- 配置Mapper文件的路径 -->
    <mappers>
        <mapper resource="conf/mybatis-config.xml"/>
    </mappers>
</configuration>

mybatis-config.xml: 下面select id =calcost 就是调用存储过程，mapping接口中定义参数是HashMap,包括输入参数，输出参数。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.navinfo.smap_tollgate.mapper.TollMapping">
    <!--  查询单条记录  -->


    <select id="usermodelByKey" parameterType="String" resultType="Usermodel">

select a.userid,a.accesskey,a.expiration  from ni_user a where to_date(a.expiration,'YYYYmmDDhh24MISS') - SYSDATE >0 AND A.accesskey = #{key}
    </select>

    <select id="calcost" parameterType="java.util.HashMap" statementType="CALLABLE">

            {call PRO_SHAPE_MATCH.PRO_TOLL_ENTRY(#{geom,mode=IN,javaType=JGeometry,jdbcType=STRUCT},
            #{s_x,mode=IN,jdbcType=DOUBLE},
            #{s_y,mode=IN,jdbcType=DOUBLE},
            #{e_x,mode=IN,jdbcType=DOUBLE},
            #{e_y,mode=IN,jdbcType=DOUBLE},
            #{buf_dis,mode=IN,jdbcType=INTEGER},
            #{cost,mode=OUT,jdbcType=DOUBLE})}

    </select>

    <select id="callProceTest" parameterType="java.util.HashMap" statementType="CALLABLE">

        {call NAVI_SHAPE_MATCH.TEST_OUT(#{x,mode=IN,jdbcType=VARCHAR},#{y,mode=OUT,jdbcType=VARCHAR})}

    </select>

    <resultMap id="defaultResultMap" type="Geometry">
        <result property="link_pid" column="link_pid" />
        <result property="geometry" column="geometry" javaType="JGeometry" jdbcType="STRUCT" />
    </resultMap>

    <select id="callGeoTest" resultMap="defaultResultMap">

        select link_pid,geometry from rd_link_test where rownum=1

    </select>

    <insert id="insert" parameterType="Geometry">
        INSERT INTO rd_link_test(link_pid, geometry) VALUES(#{link_pid}, #{geometry,javaType=JGeometry,jdbcType=STRUCT})
    </insert>

</mapper>

mapping接口：

public interface TollMapping {

     Usermodel usermodelByKey(String key);

     void calTollcost(Map<String,Object> map);

     void callProceTest(Map<String,Object> map);

     GeometryTest callGeoTest();

     void insert(GeometryTest gt);

}

服务调用：

@Service
public class TollService {

    @Autowired
    TollMapping tollMapping;

 private static final int gtypeLine = 2002;

    private static final int gtypePoint = 2001;

    private static final int srid = 8307;

    private static final int[] elementInfo= new int[]{1,2,1};

    private JGeometry getGeomLineByLineString(double[] ordinArray){

        return new JGeometry(gtypeLine,srid,elementInfo,ordinArray);
    }

private double getCostByMap(double[] ordinArray,double s_x,double s_y,double e_x,double e_y){
        Map<String,Object> gMap = new HashMap<>();
        gMap.put("geom",getGeomLineByLineString(ordinArray));
        gMap.put("s_x",s_x);
        gMap.put("s_y",s_y);
        gMap.put("e_x",e_x);
        gMap.put("e_y",e_y);
        gMap.put("buf_dis",80);
        return getToll(gMap);
    }



    private double getToll(Map<String,Object> map){
//        try {
//            return getTollCostFeeJbdc(map);
//        }
//        catch (Exception e){
//            e.printStackTrace();
//            return 0;
//        }
        tollMapping.calcost(map);
        return (double)map.get("cost");
    }
}

调用calcost后，输出参数得到的值，自然映射到map中。

下面是jdbc方式调用geometry相关的存储过程。

private double getTollJbdc(Map<String,Object> map) throws SQLException, ClassNotFoundException {

        String url = "jdbc:oracle:thin:@" + "192.168.1.120" + ":" + 1521 + ":" + "orcl";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(url, "test", "1");

        String procedure = "{call PRO_SHAPE_MATCH.PRO_TOLL_ENTRY(?,?,?,?,?,?,?)}";

        CallableStatement cstmt = conn.prepareCall(procedure);

        OracleConnection oc = (OracleConnection) cstmt.getConnection();

        STRUCT struct = JGeometry.store((JGeometry) map.get("geom"),oc);
        double tocost =0;
        try {
            cstmt.setObject(1, struct);

            cstmt.setDouble(2, (double) map.get("s_x"));
            cstmt.setDouble(3, (double) map.get("s_y"));
            cstmt.setDouble(4, (double) map.get("e_x"));
            cstmt.setDouble(5, (double) map.get("e_y"));
            cstmt.setInt(6,(int)map.get("buf_dis"));
            cstmt.registerOutParameter(7, Types.DOUBLE);

            cstmt.executeUpdate();

            tocost = cstmt.getDouble(7);
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            cstmt.close();
            conn.close();
        }

        return tocost;

    }

mybatis 照射oracle spatial JGeometry 类型并调用存储过程

相关推荐