当前位置:首页 > 开发 > 编程语言 > mybatis,Ibatis > 正文

Mybatis基础操作之存储过程

发表于: 2013-12-27   作者:53873039oycg   来源:转载   浏览:
摘要:     mybatis调用存储过程非常的方便,下面开始学习Mybatis/Ibatis 2如何调用Oracle的存储过程。先学习一个简单输出文本的例子:     CREATE OR REPLACE PROCEDURE proc_out(yes IN VARCHAR2,fly OUT VARCHAR2) AS begin d

    mybatis调用存储过程非常的方便,下面开始学习Mybatis/Ibatis 2如何调用Oracle的存储过程。先学习一个简单输出文本的例子:

   

CREATE OR REPLACE PROCEDURE proc_out(yes IN VARCHAR2,fly OUT VARCHAR2) AS
 begin
  dbms_output.put_line(yes);
  fly:='return something';
 end proc_out;

  

   Mybatis调用配置如下:

   

<select id="testOutput" statementType="CALLABLE" parameterType="hashmap">  
  <![CDATA[ {call proc_out(#{yes, mode=IN, jdbcType=VARCHAR}, #{gog, mode=OUT, jdbcType=VARCHAR})}]]>
	</select>

    测试方法如下:

   

public void TestOutputCallable(SqlSessionFactory ssf2) {
		SqlSession sqlSession = ssf2.openSession();
		Map params = new HashMap();
		// 调用存储过程的传递的参数名可以不和定义存储过程的参数名保持一致,只要保证它们的顺序是一致的即可。
		params.put("yes", "china");
		sqlSession.selectOne("test.testOutput", params);
		String result = (String) params.get("gog");
		System.out.println(result);
	}

    Ibatis配置如下:

   

<parameterMap id="stringOutMap" class="java.util.Map">
		<!-- mode参数用来设置是传入参数还是返回参数 -->
		<parameter property="yes" javaType="String" jdbcType="VARCHAR"
			mode="IN" />
		<parameter property="fly" javaType="String" jdbcType="VARCHAR"
			mode="OUT" />
	</parameterMap>

	<procedure id="testOutput" parameterMap="stringOutMap">
       <![CDATA[{call proc_out(?,?)}]]>
	</procedure>

    测试方法为:

   

public void TestOutputCallable(SqlMapClient sqlMapper) throws SQLException {
		Map  mapIn = new HashMap();
		mapIn.put("yes", "china");
		mapIn.put("fly", "");
		sqlMapper.queryForObject("test.testOutput",mapIn);
		String result=(String) mapIn.get("fly");
		System.out.println(result);
	}

    Ps,Ibatis每次输入都要新建一个Map来指定输入参数和输出参数,用起来特不爽,还是Mybatis好用

   

     下面开始使用表了, 首先新建表Emp:

   

-- Create table
create table EMP
(
  EMPNO    NUMBER not null,
  ENAME    VARCHAR2(30) not null,
  JOB      VARCHAR2(15),
  MGR      NUMBER,
  HIREDATE DATE,
  SALE     NUMBER,
  COMM     NUMBER,
  DEPTNO   NUMBER
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table EMP
  add primary key (EMPNO)
 

   再新建JavaBean

  

package bean;

import java.util.Date;

public class EmpBean {
	@Override
	public String toString() {
		return "EmpBean [empNo=" + empNo + ", ename=" + ename + ", job=" + job
				+ ", mrg=" + mrg + ", hireDate=" + hireDate.toLocaleString()
				+ ", sale=" + sale + ", comm=" + comm + ", depNo=" + depNo
				+ "]";
	}

	private long empNo;
	private String ename;
	private String job;
	private long mrg;
	private Date hireDate;
	private long sale;
	private long comm=0;
	private long depNo;

	public long getEmpNo() {
		return empNo;
	}

	public void setEmpNo(long empNo) {
		this.empNo = empNo;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public long getMrg() {
		return mrg;
	}

	public void setMrg(long mrg) {
		this.mrg = mrg;
	}

	public Date getHireDate() {
		return hireDate;
	}

	public void setHireDate(Date hireDate) {
		this.hireDate = hireDate;
	}

	public long getSale() {
		return sale;
	}

	public void setSale(long sale) {
		this.sale = sale;
	}

	public long getComm() {
		return comm;
	}

	public void setComm(long comm) {
		this.comm = comm;
	}

	public long getDepNo() {
		return depNo;
	}

	public void setDepNo(long depNo) {
		this.depNo = depNo;
	}

}

 

    先来一个根据主键得到Emp记录的例子,对应的存储过程为:

   

create or replace procedure getEmpById(V_USERID IN NUMBER,
                                       V_CURSOR OUT SYS_REFCURSOR) is
begin
  OPEN V_CURSOR FOR
    SELECT * from emp WHERE empno = V_USERID;
end getEmpById;

 

    Mybatis配置如下:

   

<resultMap id="enameMap" type="bean.EmpBean">
		<result column="EMPNO" property="empNo" />
		<result column="ENAME" property="ename" />
		<result column="JOB" property="job" />
		<result column="MGR" property="mrg" />
		<result column="HIREDATE" property="hireDate" />
		<result column="SALE" property="sale" />
		<result column="COMM" property="comm" />
		<result column="DEPTNO" property="depNo" />
</resultMap>

<update id="selectEmpById" statementType="CALLABLE"
		parameterType="map">     
          <![CDATA[
		  call getEmpById(#{userid,mode=IN,jdbcType=DECIMAL},
		                 #{emp,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=enameMap})
	    ]]>
	</update>

   测试方法为:

   

public void TestGetEmpByIdCallable(SqlSessionFactory ssf2) {
		SqlSession sqlSession = ssf2.openSession();
		Map params = new HashMap();
		// 调用存储过程的传递的参数名可以不和定义存储过程的参数名保持一致,只要保证它们的顺序是一致的即可。
		params.put("userid", 7769);
		sqlSession.selectOne("test.selectEmpById", params);
		List<EmpBean> list = (List<EmpBean>) params.get("emp");
		System.out.println(list.size() + "---" + list.get(0).toString());
	}

   Ibatis配置为:

  

<parameterMap id="empListParam" class="java.util.Map">
	   <parameter property="userid" javaType="Long" jdbcType="NUMBER"
			mode="IN" />
		<parameter property="result" jdbcType="ORACLECURSOR"
			javaType="java.sql.ResultSet" mode="OUT" />
	</parameterMap>
	
	<procedure id="selectEmpById" parameterMap="empListParam" resultClass="bean.EmpBean">
       <![CDATA[{call getEmpById(?,?)}]]>
	</procedure>

    Ibatis测试方法为:

   

public void TestEmpByIdCallable(SqlMapClient sqlMapper) throws SQLException {
		Map t=new HashMap();
		t.put("userid",7782L);
		List<EmpBean> list = sqlMapper.queryForList("test.selectEmpById",t);
		System.out.println(list.size()+"----="+list.get(0).toString());
	}

   下面学习使用存储过程进行普通的增删改查操作,首先是新增,新建存储过程

  

create or replace procedure addEmp(e_no    in long,
                                   e_name  in varchar2,
                                   e_job   in varchar2,
                                   e_mgr   in long,
                                   e_date  in date,
                                   e_sale  in long,
                                   e_comm  in long,
                                   e_depno in long,
                                   message out varchar2) is
begin
  insert into emp
    (EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO)
  VALUES
    (e_no, e_name, e_job, e_mgr, e_date, e_sale, e_comm, e_depno);
  message := '插入用户表成功';
  commit;
EXCEPTION
  WHEN OTHERS THEN
    message := '插入用户表失败';
end addEmp;

   Mybatis配置如下:

  

<insert id="addEmp" statementType="CALLABLE">
		{call
		addEmp(#{empNo},#{ename},#{job},#{mrg},#{hireDate},#{sale},#{comm},#{depNo},#{message,
		mode=OUT,javaType=string,jdbcType=VARCHAR})}
	</insert>

    测试方法为:

  

public void TestAddEmpCallable(SqlSessionFactory ssf2) {
		SqlSession sqlSession = ssf2.openSession();
		Map params = new HashMap();
		params.put("empNo", 123);
		params.put("ename", "testadd");
		params.put("job", "testjob");
		params.put("mrg", 123);
		params.put("hireDate", new Date());
		params.put("sale", 8000);
		params.put("comm", 1);
		params.put("depNo", 20);
		sqlSession.selectOne("test.addEmp", params);
		String result = (String) params.get("message");
		System.out.println(result);
	}

    Ibatis配置为:

   

<parameterMap id="empInfoMap" class="java.util.Map">
	  <parameter property="empNo" jdbcType="NUMBER" javaType="java.lang.Long"
		mode="IN" />
	  <parameter property="ename" jdbcType="VARCHAR" javaType="java.lang.String"
		mode="IN" />
	  <parameter property="job" jdbcType="VARCHAR" javaType="java.lang.String"
		mode="IN" />
	  <parameter property="mrg" jdbcType="NUMBER" javaType="java.lang.Long"
		mode="IN" />
	  <parameter property="hireDate" jdbcType="DATE" javaType="java.util.Date"
		mode="IN" />
	  <parameter property="sale" jdbcType="NUMBER" javaType="java.lang.Long"
		mode="IN" />
	  <parameter property="comm" jdbcType="NUMBER" javaType="java.lang.Long"
		mode="IN" />
	  <parameter property="depNo" jdbcType="NUMBER" javaType="java.lang.Long"
		mode="IN" />
	  <parameter property="message" jdbcType="VARCHAR" javaType="java.lang.String"
		mode="OUT" />
   </parameterMap>
	
	<procedure id="addEmp" parameterMap="empInfoMap">
       <![CDATA[{call addEmp(?,?,?,?,?,?,?,?,?)}]]>
	</procedure>

    对应的测试方法为:

   

public void addEmpCallable(SqlMapClient sqlMapper)throws SQLException{
		Map params = new HashMap();
		params.put("empNo", 124L);
		params.put("ename", "testadd");
		params.put("job", "testjob");
		params.put("mrg", 124L);
		params.put("hireDate", new Date());
		params.put("sale", 8000L);
		params.put("comm", 2L);
		params.put("depNo", 20L);
		params.put("message","");
		sqlMapper.update("test.addEmp",params);
		System.out.println(params.get("message"));
	}

    下面是修改,存储过程如下:

   

create or replace procedure updateEmp(e_no    in long,
                                       e_name  in varchar2,
                                       e_job   in varchar2,
                                       e_mgr   in long,
                                       e_date  in date,
                                       e_sale  in long,
                                       e_comm  in long,
                                       e_depno in long,
                                       message out varchar2) is
begin
  update emp u
     set ENAME     = e_name,
         JOB      = e_job,
         MGR      = e_mgr,
         HIREDATE  = e_date,
         SALE=e_sale,
         COMM=e_comm,
         DEPTNO=e_depno
   where EMPNO = e_no;
  message := '更新用户表成功';
  commit;
EXCEPTION
  WHEN OTHERS THEN
    message := '更新用户表失败';
end updateEmp;

   Mybatis匹配为:

  

<update id="updateEmp" statementType="CALLABLE">
		{call
		updateEmp(#{empNo},#{ename},#{job},#{mrg},#{hireDate},#{sale},#{comm},#{depNo},#{message,
		mode=OUT,javaType=string,jdbcType=VARCHAR})}
	</update>

    测试方法为:

   

public void TestUpdateEmpCallable(SqlSessionFactory ssf2) {
		SqlSession sqlSession = ssf2.openSession();
		Map params = new HashMap();
		params.put("empNo", 123);
		params.put("ename", "testupdate");
		params.put("job", "testjob2");
		params.put("mrg", 123);
		params.put("hireDate", new Date());
		params.put("sale", 8500);
		params.put("comm", 1);
		params.put("depNo", 20);
		sqlSession.selectOne("test.updateEmp", params);
		String result = (String) params.get("message");
		System.out.println(result);
	}

    Ibatis配置为:

   

<procedure id="updateEmp" parameterMap="empInfoMap">
       <![CDATA[{call updateEmp(?,?,?,?,?,?,?,?,?)}]]>
	</procedure>

    对应的测试方法为:

  

public void updateEmpCallable(SqlMapClient sqlMapper)throws SQLException{
		Map params = new HashMap();
		params.put("empNo", 124L);
		params.put("ename", "testupdate");
		params.put("job", "testjob");
		params.put("mrg", 125L);
		params.put("hireDate", new Date());
		params.put("sale", 8500L);
		params.put("comm", 3L);
		params.put("depNo", 20L);
		params.put("message","");
		sqlMapper.update("test.updateEmp",params);
		System.out.println(params.get("message"));
	}

   下面是查询操作,存储过程为:

   

create or replace package JUV is
  TYPE CUR_GETEMP IS REF CURSOR;
end JUV;

create or replace procedure getAllEmp(empList out JUV.CUR_GETEMP)
as
begin
open empList for select * from emp;
end getAllEmp;

   Mybatis配置为:

  

<select id="getAllEmp" statementType="CALLABLE">
		<![CDATA[{call getAllEmp(#{empList,mode=OUT,javaType=java.sql.ResultSet,jdbcType=CURSOR,resultMap=enameMap})}]]>
	</select>

    测试方法为:

   

public void TestGetAllEmpsCallable(SqlSessionFactory ssf2) {
		SqlSession sqlSession = ssf2.openSession();
		Map params = new HashMap();
		sqlSession.selectOne("test.getAllEmp", params);
		List<EmpBean> beanList = (List<EmpBean>) params.get("empList");
		for (EmpBean empBean : beanList) {
			System.out.println(empBean);
		}
	}

     Ibatis配置为:

    

<procedure id="getEmps" parameterMap="searchParam" resultClass="bean.EmpBean">
       <![CDATA[{call getAllEmp(?)}]]>
	</procedure>

    对应的测试方法为:

   

public void TestEmpListsCallable(SqlMapClient sqlMapper) throws SQLException {
		List<EmpBean> list = sqlMapper.queryForList("test.getEmps");
		for (EmpBean empBean : list) {
			System.out.println(empBean);
		}
	}

    最后是删除操作,存储过程为:

   

create or replace procedure delEmp(u_id in varchar2, message out varchar2) is
begin
  delete emp where EMPNO = u_id;
  message := '删除用户表成功';
  commit;
EXCEPTION
  WHEN OTHERS THEN
    message := '删除用户表失败';
end delEmp;

   Mybatis配置为:

  

<delete id="delEmp" statementType="CALLABLE">
		{call delEmp(#{id},#{message,
		mode=OUT,javaType=string,jdbcType=VARCHAR})}
	</delete>

   测试方法为:

   

public void TestDelEmpCallable(SqlSessionFactory ssf2) {
		SqlSession sqlSession = ssf2.openSession();
		Map params = new HashMap();
		params.put("id", 7844);
		sqlSession.selectOne("test.delEmp", params);
		String result = (String) params.get("message");
		System.out.println(result);
	}

   Ibatis配置为:

   

<parameterMap id="delEmpMap" class="java.util.Map">
	  <parameter property="empNo" jdbcType="NUMBER" javaType="java.lang.Long"
		mode="IN" />
	  <parameter property="message" jdbcType="VARCHAR" javaType="java.lang.String"
		mode="OUT" />
   </parameterMap>
   
	<procedure id="delEmp" parameterMap="delEmpMap">
       <![CDATA[{call delEmp(?,?)}]]>
	</procedure>

    对应的测试方法为:

   

public void delEmpCallable(SqlMapClient sqlMapper)throws SQLException{
		Map params = new HashMap();
		params.put("empNo", 124L);
		params.put("message","");
		sqlMapper.update("test.delEmp",params);
		System.out.println(params.get("message"));
	}

    全文完。

     题外话:

     在测试的过程中,发现Mybatis在Number类型记录不存在时候自动返回0,Ibatis直接报错,不知道是不是Ibatis配置出错了,另外,个人感觉Ibatis调用存储过程应该有更简洁的方法,欢迎各位指教,写的不好的地方,请多包涵,另外,本文中的例子网上也要,个人只是把他们整合在一起。

 

 

 

 

Mybatis基础操作之存储过程

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
看了下mybatis源码,有调用存储过程的例子,整理下。 参数形式: create procedure sptest.adder(in
  在前面分别讲解了通过mybatis执行简单的增删改,多表联合查询,那么自然不能缺少存储过程调用,
Mybatis前身是Ibatis,Ibatis是apache的一个顶级开源项目。2010年迁移到Google code,更名为Mybatis
话说很久很久以前,大约是我国解放很久很久以后,有位叫兔子的童鞋对存储过程很感兴趣,苦于无师,
Mybatis深入之初始化过程 一:简介 这篇开始是根据Mybatis源码来对Mybatis进行更深入的学习、当然、
“皇上,还记得当年大三时认识的存储过程吗?” “朕记得,就是。。。” 今天咱们练练存储过程。如
Sqlserver返回结果集的存储过程调用方式: 一、存储过程: create procedure selectCount @num int
存储过程在小公司用的不多,但是如果业务比较复杂或者性能要求比较苛刻的时候存储过程就派上用场了
DB2存储过程-基础详解 学习 DB2 9.5 SQL Procedural Language,包括变量、条件、处理程序声明、控制
学习 DB2 9.5 SQL Procedural Language,包括变量、条件、处理程序声明、控制流和迭代语句以及错误
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号