Mybatis调用数据库的存储过程和方法

定义一个存储过程：

create or replace procedure prg_add(p1 in number,p2 in number,p3 out number)
as
begin
  p3:=p1+p2;
end;

定义一个方法：

create or replace function fun_add(p1 number, p2 number)
return number
as
begin
  return p1+p2;
end;

java中的sql调用语句：

String sql="{call 存储过程名(?,?,?)}"

Register(3,Number);

String sql="{?=call 函数名(?,?)}"

jdbc.properties:

url=jdbc\:oracle\:thin\:@localhost\:1521\:orcl
driverClass=oracle.jdbc.OracleDriver
account=scott
password=tiger

mybatis.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>
	
	<properties resource="cn/et/mybatis/lesson01/jdbc.properties">
		
	</properties>

  <!-- 
  	配置连接数据库的环境   development开发环境
  -->
  <environments default="development">
    <environment id="development">
      <!-- 事务交给JDBC来管理   也就是通过 Connection的 commit和rollback管事事务 -->
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driverClass}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${account}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  
  <mappers>
  	<mapper resource="cn/et/mybaits/lesson02/func/call_mapper.xml" />
  </mappers>
</configuration>

call_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">
  
<!-- 
	接口映射
	namespace必需跟接口的全名一致
 -->
<mapper namespace="cn.et.mybaits.lesson02.func.CallMapper">
	<!--  statementType: STATEMENT就是搞sql语句     CALLABLE就是搞存储过程和函数-->
	<select id="callProcedure" parameterType="map"  statementType="CALLABLE">
		{
			call prg_add(
				#{p1,mode=IN,jdbcType=NUMERIC},
				#{p2,mode=IN,jdbcType=NUMERIC},
				#{p3,mode=OUT,jdbcType=NUMERIC}
			)
		}
	</select>
	<select id="callFunction" parameterType="map"  statementType="CALLABLE">
		{
			#{p3,mode=OUT,jdbcType=NUMERIC}=call fun_add(
				#{p1,mode=IN,jdbcType=NUMERIC},
				#{p2,mode=IN,jdbcType=NUMERIC}
			)
		}
	</select>
	
	
</mapper>

CallMapper接口：

package cn.et.mybaits.lesson02.func;

import java.util.Map;

public interface CallMapper {

	public void callProcedure(Map map);

	public void callFunction(Map map);
}

TestMabatis.java:

package cn.et.mybaits.lesson02.func;

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

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

public class TestMybatis {
	
	public static SqlSession getSession(){
		String resource = "/cn/et/mybaits/lesson02/mybatis.xml";
		InputStream inputStream = TestMybatis.class.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		//打开会话
		SqlSession session = sqlSessionFactory.openSession();
		return session;
	}

/*	public static void main(String[] args) {
		SqlSession session = getSession();
		CallMapper cm = session.getMapper(CallMapper.class);
		Map map = new HashMap();
		map.put("p1", 145);
		map.put("p2", 100);
		cm.callProcedure(map);
		System.out.println(map.get("p3"));
	}
	*/
	public static void main(String[] args) {
		SqlSession session = getSession();
		CallMapper cm = session.getMapper(CallMapper.class);
		Map map = new HashMap();
		map.put("p1", 1243);
		map.put("p2", 1000);
		cm.callFunction(map);
		System.out.println(map.get("p3"));
	}
}