mybatis调用oracle11g存储过程/函数

pom依赖

oracle数据库版本为 11g，jdk1.8

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.6</version>
</dependency>
<dependency>
	<groupId>com.oracle.database.jdbc</groupId>
	<artifactId>ojdbc8</artifactId>
	<version>21.3.0.0</version>
</dependency>
<dependency>
	<groupId>com.oracle.database.nls</groupId>
	<artifactId>orai18n</artifactId>
	<version>21.3.0.0</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.1</version>
    <scope>test</scope>
</dependency>
<!-- JSON -->
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.12.3</version>
</dependency>

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.29</version>
</dependency>
<!--log4j 适配包。即面向 slf4j 调用 API，然后它内部调用适配包，最后适配包调用 log4j-->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.7.29</version>
</dependency>

log4j.properties

在resources目录下创建log4j.properties日志文件配置

#指定输出日志信息的级别与输出的位置
#日志级别，包括 TRACE < DEBUG < INFO < WARN < ERROR .
#console 表示下面的控制台，logFile 表示下面的日志文件.
log4j.rootLogger=DEBUG,console,logFile
#表示Logger不会在父Logger的appender里输出，默认为true
log4j.additivity.org.apache=true
#控制台
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.ImmediateFlush=true
log4j.appender.console.Target=System.err
#日志输出的格式
log4j.appender.console.layout.ConversionPattern=%5p [%t] (%c:%L) %d{yyyy-MM-dd HH:mm:ss,SSS} ---- %m%n
#日志文件
log4j.appender.logFile=org.apache.log4j.FileAppender
log4j.appender.logFile.Threshold=DEBUG
log4j.appender.logFile.ImmediateFlush=true
log4j.appender.logFile.Append=true
#日志文件存放的路径
log4j.appender.logFile.File=E:/logs_wmx/log.log4j
log4j.appender.logFile.layout=org.apache.log4j.PatternLayout
#日志输出的格式
log4j.appender.logFile.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n

jdbc.properties

在resources目录下创建jdbc.properties数据源配置文件

# mysql
jdbc.user=root
jdbc.password=root
jdbc.url=jdbc:mysql://localhost:3306/ry?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true
jdbc.driver=com.mysql.cj.jdbc.Driver
# oracle
oracle.jdbc.user=system
oracle.jdbc.password=system
oracle.jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
oracle.jdbc.driver=oracle.jdbc.driver.OracleDriver

entity实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Dept {
    private Integer deptNo;
    private String dName;
    private String loc;
}

Mapper接口

public interface DeptMapper {
    // 调用oracle存储过程返回单个结果集
    void findDeptNameById(Map<String,Object> map);

    // 调用oracle存储过程返回多个结果集
    void findAllDeptProcedure(Map<String,Object> map);
}

mybatis-config.xml

resources目录下新建mybatis-config.xml mybaits的全局配置文件

<?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>
    <!-- 加载类路径下数据源配置信息   -->
    <properties resource="jdbc.properties"/>
    <!-- 全局参数 -->
    <settings>
        <!-- 打印 SQL 语句 -->
        <setting name="logImpl" value="STDOUT_LOGGING" />
        <!-- 使用驼峰命名法转换字段。 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
   
    <!-- 全局设置别名 -->
    <typeAliases>
        <typeAlias type="cn.zysheep.entity.Dept" alias="dept"/>
    </typeAliases>
     <!-- 配置数据源,可以根据环境配置多个数据源 -->
    <environments default="pro">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
        <environment id="pro">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${oracle.jdbc.driver}"/>
                <property name="url" value="${oracle.jdbc.url}"/>
                <property name="username" value="${oracle.jdbc.user}"/>
                <property name="password" value="${oracle.jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/DeptMapper.xml"/>
    </mappers>
</configuration>

DeptMapper.xml

resources/mapper目录下创建DeptMapper.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="cn.zysheep.mapper.DeptMapper">
    <resultMap id="deptResultMap" type="dept">
        <id column="deptno" property="deptNo"/>
        <result column="dname" property="dName"/>
        <result column="loc" property="loc"/>
    </resultMap>
    <!-- 调用oracle存储过程返回单个结果集 -->
    <!--
        statementType="CALLABLE" :表明调用的是存储过程;
        parameterType="java.util.Map" :参数是一个map,所以在传参时需要传入一个map集合.(我尝试了其他的类型,比如int,不行!这点跟mysql不一样);
    -->
    <select id="findDeptNameById" statementType="CALLABLE" parameterType="map">
        <!--
        1.传入传出参数要注明mode=IN/OUT
        2.并要注明jdbcType（在网上可以查询mybatis支持哪些jdbcType类型）,
        3.返回参数要注明对应的resultMap
        4.注意:这里deptNo,d_Name都是参数map集合的key值.
        -->
       <![CDATA[ 
       {call  FindAllDeptById(#{deptNo,mode=IN,jdbcType=INTEGER},#{d_Name,mode=OUT,jdbcType=VARCHAR})} 
       ]]>
    </select>

    <!-- 调用oracle存储过程返回多个结果集
        1. call语句左右的大括号可以去掉，但网上的好像很多都有写。
        2. call语句与左右大括号间不能有空格或换行等，会报错（java.sql.SQLException: 出现不支持的 SQL92 标记: 1:）
    -->
    <select id="findAllDeptProcedure" statementType="CALLABLE">
        call find_all_dept(#{deptList, mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=deptResultMap})
    </select>
</mapper>

1、使用mybaits调用oracle存储过程,Mapper配置文件标签可以使用任意<update><insert><delete><select>，但是为了语义建议使用<select>标签
2、在使用mybatis 时我们sql是写在xml 映射文件中，如果写的sql中有一些特殊的字符的话，在解析xml文件的时候会被转义，但我们不希望他被转义，所以我们要使用<![CDATA[ ]]>来解决。这是XML语法。在CDATA内部的所有内容都会被解析器忽略。

oracle中的存储过程

FindAllDeptById()

create procedure FindAllDeptById(v_deptno in  integer, v_dname out String) is
begin
  select dname into v_dname  from dept where deptno = v_deptno;
end FindAllDeptById;

find_all_dept()

create procedure find_all_dept(v_dept out sys_refcursor) is
begin
  open v_dept for select * from dept;
end find_all_dept;

1. sys_refcursor 系统游标返回结果集
2. 使用动态游标返回结果集
   1、在包中创建动态游标

   CREATE OR REPLACE PACKAGE JZ_ORAOLEDB AS
      TYPE m_refcur IS REF CURSOR;
   END jz_oraoledb;
   

   2、使用动态游标

   create procedure find_all_dept(v_dept out jz_oraoledb.m_refcur) is
   begin
   open v_dept for select * from dept;
   end find_all_dept;
   

测试类DeptMapperTest

public class DeptMapperTest {
	
    String resource = "mybatis-config.xml";
    InputStream inputStream;

    {
        try {
        	// 读取配置mybaits全局配置文件
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
	// 构建SqlSessionFactory工厂
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    /**
     * 测试: 调用oracle存储过程返回单个结果集
     */
    @Test
    public void testFindDeptNameById() {
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
            HashMap<String, Object> map = new HashMap<>();
            map.put("deptNo",2);
            mapper.findDeptNameById(map);
			// 那怎么取得返回的内容呢，其实只要存储过程执行后map里就有值了
            System.out.println(map.get("d_Name"));
        }
    }

    /**
     * 测试: 调用oracle存储过程返回多个结果集
     */
    @Test
    public void testFindAllDeptProcedure() {
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
            Map<String, Object> param = new HashMap<String, Object>();
            mapper.findAllDeptProcedure(param);
            System.out.println((List<Dept>) param.get("deptList"));
        }
    }
}