MyBatis總結(三十三)--MyBatis調用帶遊标的存儲過程

本文内容來自尚矽谷http://www.gulixueyuan.com/course/43/task/1235/show的任務86與任務85

1建立JUnit

/**
	 * oracle分頁：
	 * 		借助rownum：行号；子查詢；
	 * 存儲過程包裝分頁邏輯
	 * @throws IOException 
	 */
	@Test
	public void testProcedure() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			OraclePage page = new OraclePage();
			page.setStart(1);
			page.setEnd(5);
			mapper.getPageByProcedure(page);
			
			System.out.println("總記錄數："+page.getCount());
			System.out.println("查出的資料："+page.getEmps().size());
			System.out.println("查出的資料："+page.getEmps());
		}finally{
			openSession.close();
		}
		
	}
	
           

2dao

package com.atguigu.mybatis.dao;

import java.util.List;

import com.atguigu.mybatis.bean.Employee;
import com.atguigu.mybatis.bean.OraclePage;

public interface EmployeeMapper {
	
	public Employee getEmpById(Integer id);
	
	public List<Employee> getEmps();
	
	public Long addEmp(Employee employee);
	
	public void getPageByProcedure(OraclePage page);
}
           

3bean

package com.atguigu.mybatis.bean;

import java.util.List;

/**
 * 封裝分頁查詢資料
 * @author lfy
 *
 */
public class OraclePage {
	
	private int start;
	private int end;
	private int count;
	private List<Employee> emps;
	
	public int getStart() {
		return start;
	}
	public void setStart(int start) {
		this.start = start;
	}
	public int getEnd() {
		return end;
	}
	public void setEnd(int end) {
		this.end = end;
	}
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}
	public List<Employee> getEmps() {
		return emps;
	}
	public void setEmps(List<Employee> emps) {
		this.emps = emps;
	}
	
	

}
           

4xml

#{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=PageEmp}中mode代表在存儲過程中的參數類型為輸出參數，jdbcType：代表着是遊标類型，javaType:設定傳回的結果集類型為ResultSet，resultMap：結果集的映射關系處理      

<!-- public void getPageByProcedure();
	1、使用select标簽定義調用存儲過程
	2、statementType="CALLABLE":表示要調用存儲過程
	3、{call procedure_name(params)}
	-->
	<select id="getPageByProcedure" statementType="CALLABLE" databaseId="oracle">
		{call hello_test(
			#{start,mode=IN,jdbcType=INTEGER},
			#{end,mode=IN,jdbcType=INTEGER},
			#{count,mode=OUT,jdbcType=INTEGER},
			#{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=PageEmp}
		)}
	</select>
	<resultMap type="com.atguigu.mybatis.bean.Employee" id="PageEmp">
		<id column="EMPLOYEE_ID" property="id"/>
		<result column="LAST_NAME" property="email"/>
		<result column="EMAIL" property="email"/>
	</resultMap>
           

5oracle中建立帶遊标的存儲過程