下面就用一个例子来测试Mybatis调用存储过程,并进行分页的例子:
使用的是oracle数据库
1 存储过程代码如下:create or replace procedure test_page(
page_start in int,page_end in int,page_count out int,
page_emps out sys_refcursor)
as
begin
select count(*) into page_count from employees;
open page_emps for
select * from
(select rownum rn,e.* from employees e
where rownum <= page_end)
where rn >= page_start;
end test_page;
2 Page.javapackage com.lxj.bean;
import java.util.List;
public class Page {
// 起始
private Integer start;
// 结束
private Integer end;
// 总数
private Integer count;
// 数据库中查询出来的员工
private List emps;
public Integer getStart() {
return start;
}
public void setStart(Integer start) {
this.start = start;
}
public Integer getEnd() {
return end;
}
public void setEnd(Integer end) {
this.end = end;
}
public Integer getCount() {
return count;
}
public void setCount(Integer count) {
this.count = count;
}
public List getEmps() {
return emps;
}
public void setEmps(List emps) {
this.emps = emps;
}
@Override
public String toString() {
return "Page [start=" + start + ", end=" + end + ", count=" + count + ", emps=" + emps + "]";
}
}
3 Employee.javapackage com.lxj.bean;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
4 EmployeeMapper.javapackage com.lxj.mapper;
import java.util.List;
import com.lxj.bean.Employee;
import com.lxj.bean.Page;
public interface EmployeeMapper {
//根据Id获取员工信息
public Employee getEmpById(Integer id);
//分页获取员工信息
public void getEmpsByPage(Page page);
}
5 EmployeeMapper.xml<?xml version="1.0" encoding="UTF-8" ?>
mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select EMPLOYEE_ID id,LAST_NAME lastName,EMAIL email from employees
where EMPLOYEE_ID = #{id}
{call test_page(
#{start,mode=IN,jdbcType=INTEGER},
#{end,mode=IN,jdbcType=INTEGER},
#{count,mode=OUT,jdbcType=INTEGER},
#{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=TestPge}
)}
6 测试
查看一下数据库:
取出的是从15-20,共6条记录,总记录数是20,没有任何问题