Mybatis调用存储过程

一、返回结果集

以MySql分页为例

1.存储过程

DROP PROCEDURE IF EXISTS sp_pager;
CREATE PROCEDURE sp_pager(
	IN currentPage int ,/*当前页*/
	IN pageSize int,/*每页的记录数*/
	OUT totalCount int /*总记录数*/
	-- IN tableName varchar(100), /*表名*/
	-- IN p_field varchar(300), /*查询的字段，逗号分隔*/
	-- IN p_where varchar(500),/*查询条件*/
	-- IN orderby varchar(300) /*排序*/
)
BEGIN
	SET @startIndex=(currentPage-1)*pageSize;
	SET @endIndex=pageSize;
	SET @strSql=CONCAT('select id,user_name,user_age,user_sex',' from ','tb_user',
	-- CASE IFNULL(p_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',p_where) END,
	-- CASE IFNULL(orderby,'') WHEN '' THEN '' ELSE CONCAT(' order by ',orderby) END,
	' limit ',@startIndex,',',@endIndex);
	/*预定义一个语句，并将它赋给stmtsql*/
	PREPARE stmtsql FROM @strSql;
	EXECUTE stmtsql;
	/*释放一个预定义语句的资源*/
	DEALLOCATE PREPARE stmtsql;

	SET @strCount=CONCAT('select count(*) into @Rows_Total from ','tb_user');
	PREPARE stmtcount FROM @strCount;
	EXECUTE stmtcount;
	DEALLOCATE PREPARE stmtcount;
	SET totalCount=@Rows_Total;
	/*计算总数也可以是下面这种方法*/
	-- SELECT COUNT(*) INTO totalCount FROM tb_user;
END

测试存储过程：

CALL sp_pager(1,20,@totalCount);
SELECT @totalCount;