Mybatis调用存储过程和函数

来源：互联网发布：java基础项目实战编辑：程序博客网时间：2024/06/03 15:34

1、现在PL/SQL Developer中定义存储过程和函数

存储过程

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

函数

create or replace function fun_add(p1 in number,p2 in number)return numberasbegin  return p1+p2;end;

2、mybatis-config.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><!-- mybatis的核心配置文件1.数据库的连接的信息（连接池） --><properties resource="oracle.properties"></properties><!-- 取别名 --><typeAliases><typeAlias type="cn.easytop.lesson02.Food" alias="food"/></typeAliases>  <environments default="development">    <environment id="development">    <!-- 事务管理器  默认使用jdbc事务 -->      <transactionManager type="JDBC"/>      <dataSource type="POOLED">        <property name="driver" value="${driverClass}"/>        <property name="url" value="${url}"/>        <property name="username" value="${username1}"/>        <property name="password" value="${password}"/>      </dataSource>    </environment>  </environments>  <mappers>  <mapper resource="cn/easytop/lesson02/proc/ProcMapper.xml"/>  </mappers>    </configuration>

3、定义存储过程和函数

<?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">  <!-- jdbc调用存储过程 {call 存储过程名(?,?)}函数{?=call 函数名(?,?)}--><mapper namespace="cn.easytop.lesson02.xml.FoodMapper"><!-- 存储过程注意:statementType="CALLABLE" 必须要声明的类型 --><select id="call_prg_add" resultType="java.util.Map" statementType="CALLABLE">{call prg_add(#{p1,mode=IN,jdbcType=NUMERIC},#{p2,mode=IN,jdbcType=NUMERIC},#{result,mode=OUT,jdbcType=NUMERIC})}</select><!-- 函数resultType="java.util.Map"  返回的结果类型注意:statementType="CALLABLE" 必须要声明的类型 --><select id="call_fun_add" resultType="java.util.Map" statementType="CALLABLE">{#{result,mode=OUT,jdbcType=NUMERIC}=call fun_add(#{p1,mode=IN,jdbcType=NUMERIC},#{p2,mode=IN,jdbcType=NUMERIC})}</select></mapper>

4、测试类

package cn.easytop.lesson02.proc;import java.io.IOException;import java.io.InputStream;import java.util.HashMap;import java.util.Map;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class TestProc {public static SqlSession getSession() throws IOException{String resource = "cn/easytop/lesson02/proc/mybatis.xml";InputStream inputStream = Resources.getResourceAsStream(resource);//工厂类SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession openSession = sqlSessionFactory.openSession();//session操作的是 指向sql语句的一个唯一表示符return openSession;}//调用存储过程@org.junit.Testpublic void testXmlInterface() throws IOException{SqlSession session=getSession();Map map=new HashMap();map.put("p1",1111);map.put("p2", 1212);//直接返回到map集合中String result=session.selectOne("call_prg_add",map);System.out.println(map.get("result"));}//调用函数@org.junit.Testpublic void testFunInterface() throws IOException{SqlSession session=getSession();Map map=new HashMap();map.put("p1",1111);map.put("p2", 1212);String result=session.selectOne("call_fun_add",map);//直接返回到map集合中System.out.println(map.get("result"));}}

阅读全文

0 0