MyBatis，MySql存储过程分页查询

时间：2022-10-19 04:43:47

前言

如果涉及到同一台机器上不同库不同表的关联查询，可以通过 数据库名.表名 的形式来进行跨库查询，若不用插件，这时可以通过存储过程来完成查询。

编写存储过程

以学生信息查询为例，例如有两个库。
学生库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<Object>

public interface StuInfoDao {
public List<Object> queryStuByProc(StuInfoForm form);
}

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

  <resultMap type="java.lang.Integer" id="recordCounts">
    <result column="recordCounts" jdbcType="INTEGER" />
  </resultMap> 


  <resultMap id="dtoResultMap" type="com.fengdai.report.model.Certification">
    <result column="stu" jdbcType="VARCHAR" property="stu" />
    <result column="teacher" jdbcType="VARCHAR" property="teacher" />
  </resultMap>

  <select id="queryStuByProc" resultMap="dtoResultMap,recordCounts" statementType="CALLABLE" >
    {
        CALL  test_proc_paging(#{pageNum,jdbcType=INTEGER},
#{pageSize,jdbcType=INTEGER})
    }
  </select>

查询与返回结果的处理

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

form.setPageNum((form.getPageNum()-1)*form.getPageSize());
List<Object> result = certificationDao.queryStuByProc(form);
// 结果
List<Object> result = list.get(0);
// 总记录数
Integer size = ((ArrayList<Integer>)list.get(1)).get(1);

form中的pageNum定义了页号，pageSize定义的单页大小，因此在查询前需要将pageNum,pageSize转换为相应的分页查询参数。
例如 查第5页，每页10条，那么sql中的limit偏移就是 limit (5-1)*10,10

参考

[1]Prepared SQL Statement Syntax,
https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html
[2]Mybatis references Multiple ResultSets for Association,
http://www.mybatis.org/mybatis-3/sqlmap-xml.html
标签：

相关文章