mybatis的mysql分页查询语句_MyBatis，MySql存储过程分页查询

前言

一般我们使用MyBatis时进行分页主要通过使用PageHelper这个插件。但某些时候，在某XXX可能会遇到跨库查询并分页显示的场景(虽然一般情况禁止使用存储过程)，这时就无法在使用PageHelper了。如果待查询的MySql不同库在一台机器上那么可以通过 数据库名.表名 的形式来进行跨库查询，这是可以通过存储过程来完成查询。

编写存储过程

以学生信息查询为例，例如有两个库。

学生库student(学生信息表 stu_info)

CREATE TABLE `stu_info` ( `id` varchar(64) NOT NULL, `name` varchar(64) NOT NULL COMMENT '学生姓名', `teacher_id` varchar(64) DEFAULT NULL COMMENT '教师id', PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

教师库teacher(教师信息表 teacher_info)

CREATE TABLE `teacher_info` ( `id` varchar(64) NOT NULL, `name` varchar(64) NOT NULL COMMENT '教师姓名', PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

其中每个学生有一个指导老师，每个指导老师可以指导多个学生。

先要查询出所有学生的名字及其指导教师。由于分页参数是调用方传入，因此分页参数通过占位符(?,?)来替代，并使用MySql Prepared SQL Statement[1]。最终存储过程如下。

CREATE PROCEDURE test_proc_paging( IN pageNum int, IN pageSize int ) BEGIN SET @pageSize = pageSize;

SET @pageNum = pageNum;

PREPARE s1 FROM "

select s.name as stu,t.name as teacher from student.stu_info s left join teacher.teacher_info t on s.teacher_id = t.id limit ?,?" EXECUTE s1 USING @pageNum,@pageSize; DEALLOCATE PREPARE s1; SELECT FOUND_ROWS() as recordCounts; END

PREPARE s1 … 定义prepared statement s1

EXECUTE s1 … 执行s1,并用参数替换占位符

DEALLOCATE PREPARE s1… 释放prepared statement

SELECT FOUND_ROWS() as recordCounts; 返回总记录数

最终调用存储过程会返回两个结果，一个是分页查询的结果，一个是总的结果数。

MyBatis调用存储过程

StuInfoDao定义为，其中StuInfoForm 包含了查询参数，即两个分页参数。注意由于调用的存储过程返回多个结果集，因此DAO中的查询语句返回结果为List

public interface StuInfoDao {

public List queryStuByProc(StuInfoForm form);

}

Mybatis mapper.xml 定义，由于返回结果包含两个结果集，因此定义了两个ResultMap，并在select查询中指定了两个ResultMap[2]。

{

CALL test_proc_paging(#{pageNum,jdbcType=INTEGER},

#{pageSize,jdbcType=INTEGER})

}

查询与返回结果的处理

若使用PageHelper提供的PageInfo对象来返回结果，那么通过如下的代码获取结果，及总记录数，然后填入PageInfo对象的成员中，最后返回给前端即可。

form.setPageNum((form.getPageNum()-1)*form.getPageSize());

List result = certificationDao.queryStuByProc(form);

// 结果

List result = list.get(0);

// 总记录数

Integer size = ((ArrayList)list.get(1)).get(1);

form中的pageNum定义了页号，pageSize定义的单页大小，因此在查询前需要将pageNum,pageSize转换为相应的分页查询参数。

例如 查第5页，每页10条，那么sql中的limit偏移就是 limit (5-1)*10,10

参考