使用MyBatis调用Oracle存储过程

2019独角兽企业重金招聘Python工程师标准>>>

一、Oracle存储过程

存储过程代码：

create or replace procedure TEST_DEMO_PROC(param_name    IN VARCHAR2, return_result OUT VARCHAR2) is
begin
  return_result := 'Hello ' || param_name;
end TEST_DEMO_PROC;
/

测试存储过程：

declare result varchar2(100);
begin
test_demo_proc('Kevin', result);
DBMS_OUTPUT.put_line(result);
end;
/

####二、ojdbc6的Maven Dependency配置

<dependency>
	<groupId>com.oracle</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>12.1.0.1-atlassian-hosted</version>
</dependency>

三、Oracle连接配置

Oracle数据库连接属性（需要根据自己的实际情况进行修改）：

db.driverClass=oracle.jdbc.OracleDriver
db.url=jdbc:oracle:thin:@localhost:1521/xe
db.username=system
db.password=password

数据源dataSource配置：

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 
    <property name="driverClassName" value="${db.driverClass}"/> 
    <property name="url" value="${db.url}"/> 
    <property name="username" value="${db.username}"/> 
    <property name="password" value="${db.password}"/> 
</bean> 

四、MyBatis的Mapper和Mapper对应的xml文件

Mapper文件:

/**
 * 存储过程测试Demo
 * 
 * @author Kevin
 * @date 2017-01-03
 */
public interface ProcedureDemoMapper {

	/**
	 * 测试方法
	 *
	 * @param param
	 */
	void testMethod(Map<String, Object> param);

}

Mapper对应的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.zkj.dal.mybatis.model.mapper.ProcedureDemoMapper">
    <parameterMap type="map" id="paramMap">
        <parameter property="param_name" jdbcType="VARCHAR" mode="IN"/>
        <parameter property="return_result" jdbcType="VARCHAR" mode="OUT"/>
    </parameterMap>

    <select id="testMethod" parameterMap="paramMap" statementType="CALLABLE">
        {call system.test_demo_proc(?, ?)}
    </select>

</mapper>

五、Mapper单元测试代码

package mybatis;

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

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.zkj.dal.mybatis.model.mapper.ProcedureDemoMapper;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:/mybatis.xml"})
public class ProcedureDemoMapperTest extends AbstractJUnit4SpringContextTests {

    private ProcedureDemoMapper mapper;

    @Before
    public void setUp() {
        mapper = applicationContext.getBean(ProcedureDemoMapper.class);
    }

    @Test
    public void testMethod() {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("param_name", "Kevin");

        mapper.testMethod(paramMap);
        System.out.println(paramMap.get("return_result"));
        // 输出结果为：Hello Kevin
    }

}

转载于:https://my.oschina.net/zhaokaiju/blog/818157