MyBatis总结(三十三)--MyBatis调用带游标的存储过程

妖狐艹你老母 2022-05-10 02:54 294阅读 0赞

本文内容来自尚硅谷[http://www.gulixueyuan.com/course/43/task/1235/show][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：结果集的映射关系处理

<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中创建带游标的存储过程 #

![70][]

[http_www.gulixueyuan.com_course_43_task_1235_show]: http://www.gulixueyuan.com/course/43/task/1235/show
[70]: /images/20220510/d57fe1bb25cc4270b3808c19f6aa3e7c.png