在前面分别讲解了通过mybatis执行简单的增删改，多表联合查询，那么自然不能缺少存储过程调用，而且还带分页功能。
注意：表结构参见上篇讲解联合查询的表。
一、查询某班级以及该班级下面所有学生的记录
上面这个查询可以用sql语句表示为：
select c.class_id,c.class_name,s.s_id,s.s_name
from Class c left join ClassStudent cs
on c.class_id = cs.class_id
left join Student s
on cs.student_id = s.s_id
where c.class_id = 1
查询出来的结果是：
那么如果这个班级里面学生超级多，要通过分页查询，并且用存储过程该怎么通过mybatis调用了？？？
二、在sql2008r2里面新建一个分页查询的存储过程，如下
create proc [dbo].[SP_PAGE_GetClassStudent]
( @ClassID int, --班级编码
@PageSize int, --每页显示多少条
@PageIndex int , --查询第多少页 @PageCount int output, --总页数输出
@RecordCount int output --总记录数输出
)
as
begin
declare @startIndex int
declare @endIndex int
declare @sqlString varchar(4000)
declare @QueryPageSql nvarchar(4000)
declare @QueryCountString nvarchar(4000) --1、拼接sql语句
SET @sqlString ='(select *,ROW_NUMBER() over (order by t.s_id) as rowId
from ( select c.class_id,c.class_name,s.s_id,s.s_name
from Class c left join ClassStudent cs
on c.class_id = cs.class_id
left join Student s
on cs.student_id = s.s_id
where c.class_id = '+str(@ClassID)+' )t) temp' --2、计算总页数，总记录数
SET @QueryCountString = 'select @RecordCount=count(1),@PageCount=CEILING((COUNT(1)+0.0)/'
+ CAST(@PageSize AS NVARCHAR)+') from ' +@SqlString ; --PRINT(@QueryCountString) EXEC SP_EXECUTESQL @QueryCountString,N'@RecordCount int output,@PageCount int output',
@RecordCount output,
@PageCount output --3、判断输入的当前页数大于实际总页数,则把实际总页数赋值给当前页数
IF @PageIndex > CEILING((@RecordCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@RecordCount+0.0)/@PageSize)
END SET @startIndex=(@PageIndex-1)*@PageSize+1
SET @endIndex=@PageIndex*@PageSize --4、执行分页查询 SET @QueryPageSql ='select * from ' + @SqlString + ' where rowId between ' + str(@startIndex) + ' and ' + str(@endIndex); --PRINT(@QueryPageSql) EXEC (@QueryPageSql) end
不难看出，这个存储过程一共有5个参数，3个输入参数，2个输出参数，我们通过sql2008r2自带的工具执行这个存储过程，如下：
点击“确定”会在sql查询窗体中出现如下sql语句块：
USE [mybatisDB]
GO DECLARE @return_value int,
@PageCount int,
@RecordCount int EXEC @return_value = [dbo].[SP_PAGE_GetClassStudent]
@ClassID = 1,
@PageSize = 2,
@PageIndex = 5,
@PageCount = @PageCount OUTPUT,
@RecordCount = @RecordCount OUTPUT SELECT @PageCount as N'@PageCount',
@RecordCount as N'@RecordCount' SELECT 'Return Value' = @return_value GO
仔细一看，怎么多出了一个输出参数“@return_value”了，可是在设计存储过程的时候只有2个输出参数，此处先埋下伏笔。
三、在原先项目下新建com.mybatis.proc这个包，在包里面建一个procMapper.xml的映射文件，之后需要在该项目的mybatis的配置文件conf.xml下面对这个procMapper.xml文件进行注册，具体是：
<!-- 注册映射文件 -->
<mappers>
<!-- 通过xml方式映射 -->
<mapper resource="com/mybatis/proc/procMapper.xml"/>
</mappers>
四、填充procMapper.xml，编写测试代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.proc.procMapper"> <!-- 1、测试错误，待解决 -->
<!--
### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: '@P0' 附近有语法错误。
### The error may exist in com/mybatis/proc/procMapper.xml
### The error may involve com.mybatis.proc.procMapper.getCSPMap
### The error occurred while setting parameters
### SQL: CALL SP_PAGE_GetClassStudent(?,?,?,?,?)
-->
<!--
<select id="getClassStudentsProc" statementType="CALLABLE" parameterMap="getCSPMap" resultType="com.mybatis.bean.StudentClassView">
CALL SP_PAGE_GetClassStudent(?,?,?,?,?)
</select>
<parameterMap type="java.util.Map" id="getCSPMap">
<parameter property="ClassID" mode="IN" jdbcType="INTEGER" />
<parameter property="PageSize" mode="IN" jdbcType="INTEGER" />
<parameter property="PageIndex" mode="IN" jdbcType="INTEGER" />
<parameter property="PageCount" mode="OUT" jdbcType="INTEGER" />
<parameter property="RecordCount" mode="OUT" jdbcType="INTEGER" />
</parameterMap>
--> <!--2、测试通过,返回数据有冗余 -->
<!--
<select id="getClassStudentsProc" statementType="CALLABLE" resultType="com.mybatis.bean.StudentClassView">
<![CDATA[
{#{rval,mode=OUT,jdbcType=INTEGER} = CALL SP_PAGE_GetClassStudent(
#{ClassID,mode=IN,jdbcType=INTEGER},
#{PageSize,mode=IN,jdbcType=INTEGER},
#{PageIndex,mode=IN,jdbcType=INTEGER},
#{PageCount,mode=OUT,jdbcType=INTEGER},
#{RecordCount,mode=OUT,jdbcType=INTEGER}
)} ]]>
</select>
--> <!-- 3、测试通过，一对多格式 -->
<select id="getClassStudentsProc" statementType="CALLABLE" resultMap="cvs2Map">
<![CDATA[
{#{rval,mode=OUT,jdbcType=INTEGER} = CALL SP_PAGE_GetClassStudent(
#{ClassID,mode=IN,jdbcType=INTEGER},
#{PageSize,mode=IN,jdbcType=INTEGER},
#{PageIndex,mode=IN,jdbcType=INTEGER},
#{PageCount,mode=OUT,jdbcType=INTEGER},
#{RecordCount,mode=OUT,jdbcType=INTEGER}
)} ]]>
</select> <resultMap type="com.mybatis.bean.StudentClassView2" id="cvs2Map">
<id property="class_id" column="class_id" />
<result property="class_name" column="class_name" />
<result property="rowId" column="rowId" /> <collection property="students" ofType="com.mybatis.bean.Student">
<id property="studentid" column="s_id"/>
<result property="studentname" column="s_name"/>
</collection>
</resultMap> </mapper>
我最开始的写法是按照注释里面的那种方式，也就是第1种，可是在执行的时候却报错“ '@P0' 附近有语法错误。 ”，查了n久，不知道是那里问题，如是将sql追踪器打开，检测到该存储过程在数据库中的执行代码如下：
怎么这里第一个参数就是输出参数，与之前在sql编辑器里面一样的现象，看来这种写法是不行的，可是我找不到解决方案，如果谁发现了希望能告诉我，在这里谢谢。
如是按照另一种方式，执行却ok了。测试代码如下：
先定义一个实体类接收存储过程返回的结果
package com.mybatis.bean;
public class StudentClassView {
private int s_id;
private String s_name;
private int class_id;
private String class_name;
private int rowId;
//private List<Student> students; 如果启用这个属性，则将s_id，s_name注释掉，将类名称修改为StudentClassView2
public int getRowId() {
return rowId;
}
public void setRowId(int rowId) {
this.rowId = rowId;
}
public int getS_id() {
return s_id;
}
public void setS_id(int s_id) {
this.s_id = s_id;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this.s_name = s_name;
}
public int getClass_id() {
return class_id;
}
public void setClass_id(int class_id) {
this.class_id = class_id;
}
public String getClass_name() {
return class_name;
}
public void setClass_name(String class_name) {
this.class_name = class_name;
}
@Override
public String toString() {
return "studentclass2 [s_id=" + s_id + ", s_name=" + s_name
+ ", class_id=" + class_id + ", class_name=" + class_name
+ ", rowId=" + rowId + "]";
}
}
具体测试代码
package com.mybatis.proc; import java.util.*; import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory; import com.mybatis.bean.Student;
import com.mybatis.bean.StudentClassView;
import com.mybatis.bean.StudentClassView2;
import com.mybatis.util.MybatisUtils; public class testproc {
public static void main(String[] args) { SqlSessionFactory factory = MybatisUtils.getFactory();
SqlSession session = null;
try {
session = factory.openSession(true); Map<String, Integer> paramMap = new HashMap<>();
paramMap.put("ClassID", 1);
paramMap.put("PageSize", 2);
paramMap.put("PageIndex", 5); String statement = "com.mybatis.proc.procMapper.getClassStudentsProc"; //1、数据冗余
// List<StudentClassView> scv = session.selectList(statement, paramMap);
// for(int i = 0;i<scv.size();i++){
// System.out.println(scv.get(i));
// } //2、一对多展现
StudentClassView2 scv2 = session.selectOne(statement, paramMap);
List<Student> students = scv2.getStudents();
System.out.println("排序字段："+scv2.getRowId() +"；班级编号："+scv2.getClass_id()+"；班级名称："+scv2.getClass_name());
for(int i = 0;i<students.size();i++){
System.out.println(students.get(i));
} Integer pageCount = paramMap.get("PageCount");
Integer recordCount = paramMap.get("RecordCount"); System.out.println("总页数："+pageCount);
System.out.println("总记录数："+recordCount); } catch (Exception e) {
e.printStackTrace();
}finally{
session.close();
} }
}
在控制台中输出的结果如下：
到此测试成功。
五、需要注意的问题
1、存储过程的output参数，只能通过传入的map获取
2、存储过程的return结果需要使用 参数x=call procName(?,?...)的第一个参数接收，需要指定对应的mode为OUT类型
3、<select id="getClassStudentsProc" statementType="CALLABLE" resultType="com.mybatis.bean.StudentClassView">
resultType是映射数据查询出来的记录，存储过程必须标明类型为CALLABLE
4、<![CDATA[ sql语句 ]]> 作用是避免不必要的麻烦(如<，>，&，'，" 会被xml转义成<，>，&，'，")。
5、resultType="com.mybatis.bean.StudentClassView"会决定测试代码中session.selectList(statement, paramMap)的返回类型，是一一对应的关系。
6、<select id="getClassStudentsProc" statementType="CALLABLE" resultMap="cvs2Map">
如果这里使用resultMap，那么返回的结果可以自定义，同样与session.selectOne(statement, paramMap)返回类型一致。