mybatis基础操作之存储过程

mybatis基础操作之存储过程

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

<span>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;
</span>

Mybatis调用配置如下：

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

测试方法如下：

<span>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);
	}</span>

Ibatis配置如下：

<span><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></span>

测试方法为：

<span>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);
	}</span>

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

   

     下面开始使用表了， 首先新建表Emp：

<span>-- 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)
 
</span>

再新建JavaBean

<span>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;
	}

}
</span>

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

<span>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;
</span>

Mybatis配置如下：

<span><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></span>

测试方法为：

<span>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());
	}</span>

Ibatis配置为：

<span><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></span>

Ibatis测试方法为：

<span>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());
	}</span>