Java调用Oracle存储过程和函数(Mybatis+JDBC方式)

Java调用Oracle存储过程和函数(Mybatis+JDBC方式)

先创建一个通用的存储过程和函数，用于测试，都是通过传入一个部门编号，返回部门信息，这里返回部门信息使用ref游标的方式完成，具体代码如下：

创建一个数据包，并添加一个存储过程和函数

数据包的包声明部分

create or replace package pack_dept is

  procedure pro_dept_no(dno number,list out sys_refcursor); 

  function fun_dept_no(dno number) return sys_refcursor;

end pack_dept;

数据包的包主题部分

create or replace package body pack_dept is

  procedure pro_dept_no(dno number,list out sys_refcursor) is
  begin
     open list for select deptno,dname,loc from dept where deptno = dno;
  end pro_dept_no;

  function fun_dept_no(dno number) return sys_refcursor is
     list sys_refcursor;
  begin
     open list for select deptno,dname,loc from dept where deptno=dno;
     return list;
  end fun_dept_no;

end pack_dept;

使用JDBC的方式完成普通SQL，存储过程和函数的调用

package com.zt.test;

import oracle.jdbc.OracleTypes;

import java.sql.*;

/**
 * Created by CDHong on 2018/4/12.
 */
public class TestProcedure {

    static{
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args)  throws SQLException{
        testFunction();
    }

    /**
      * 执行函数的方式
    **/
    public static void testFunction()throws SQLException{
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        Connection con = DriverManager.getConnection(url, "scott", "tiger");

        String sql = "{call ?:=pack_dept.fun_dept_no(?)}";
        CallableStatement pc = con.prepareCall(sql);
        pc.registerOutParameter(1, OracleTypes.CURSOR);
        pc.setInt(2,10);
        pc.execute();

        ResultSet rs = (ResultSet) pc.getObject(1);
        if(rs.next()){
            System.out.println(rs.getString("dname"));
        }
    }

    /**
      * 执行存储过程的方式
    **/
    public static void testProcedure()throws SQLException{
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        Connection con = DriverManager.getConnection(url, "scott", "tiger");

        String sql = "{call pack_dept.pro_dept_no(?,?)}";
        CallableStatement pc = con.prepareCall(sql);
        pc.setInt(1,10);
        pc.registerOutParameter(2, OracleTypes.CURSOR);
        pc.execute();

        ResultSet rs = (ResultSet) pc.getObject(2);
        if(rs.next()){
           System.out.println(rs.getString("dname"));
        }
    }

    /**
      * 执行SQL的普通方式
    **/
    public static void testJdbc()throws SQLException{
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        Connection con = DriverManager.getConnection(url, "scott", "tiger");
        String sql = "select deptno,dname,loc from dept where deptno= ? ";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,10);
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            String dname = rs.getString("dname");
            System.out.println(dname);
        }
        rs.close();
        ps.close();
        con.close();
    }
}

使用Mybatis的方式调用普通方法，存储过程和函数

Mapper接口，方法定义

package com.zt.mapper;

import com.zt.entity.Dept;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author CDHong
 * @since 2018-04-06
 */
public interface DeptMapper extends BaseMapper<Dept> {
    Dept findById(Integer dno);

    void callProcedure(Map map);

    void callFunction(Map map);

}

SQL XML映射实现

<?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.zt.mapper.DeptMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.zt.entity.Dept">
        <id column="DEPTNO" property="deptno" />
        <result column="DNAME" property="dname" />
        <result column="LOC" property="loc" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        DEPTNO AS deptno, DNAME AS dname, LOC AS loc
    </sql>
    <!--普通SQL调用实现-->
    <select id="findById" resultType="com.zt.entity.Dept">
        SELECT <include refid="Base_Column_List" /> from dept where deptno=#{deptno}
    </select>
    <!--存储过程调用实现-->
    <select id="callProcedure" parameterType="map" statementType="CALLABLE">
        {call pack_dept.pro_dept_no(
          #{dno,mode=IN,javaType=int,jdbcType=NUMERIC},
          #{list, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=com.zt.mapper.DeptMapper.BaseResultMap})
        }
    </select>
    <!--存储函数用实现-->
    <select id="callFunction" parameterType="map" statementType="CALLABLE">
        {call #{list,jdbcType=CURSOR, mode=OUT, javaType=ResultSet, resultMap=com.zt.mapper.DeptMapper.BaseResultMap} := pack_dept.fun_dept_no(#{dno})}
    </select>

</mapper>

Junit 集成测试

package junit;

import com.zt.entity.Dept;
import com.zt.mapper.DeptMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by CDHong on 2018/4/6.
 */
@ContextConfiguration("classpath:spring/spring-mybatis-plus.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class DeptTest {

    @Autowired private DeptMapper deptMapper;

    @Test
    public void testFindById(){
        Dept dept = deptMapper.findById(10);
        System.out.println(dept);
    }

    @Test
    public void testProcedure(){
        Map map = new HashMap<>();
        map.put("dno",10);
        map.put("list",null);
        deptMapper.callProcedure(map);

        List<Dept> list = (List<Dept>) map.get("list");
        System.out.println(list);
    }

    @Test
    public void testFunction(){
        Map map = new HashMap<>();
        map.put("dno",10);
        map.put("list",null);
        deptMapper.callFunction(map);

        Object list = map.get("list");
        System.out.println(list);
    }


}

运行DeBug打印信息

到此说明完毕。