Mybatis分页查询避免两次查询同时返回总数和数据（一条SQL直接查询）

一、 场景&＃xff1a;

mysql分页查询一般都需要给前端返回一个总数&＃xff0c;便于前端人员计算总页数。以前的处理办法是查询两次数据库&＃xff0c;现在想提升下效率。

二、处理方法&＃xff1a;

SELECT SQL_CALC_FOUND_ROWS * FROM [table] WHERE ...... limit M, N;
SELECT FOUND_ROWS();


SQL_CALC_FOUND_ROWS 告诉MySQL将sql所处理的行数记录下来
FOUND_ROWS() 则取到了这个纪录。

三、controller层&＃xff1a;

public Page PageAfter(PageModel page, PeopleQuery query) {List list &＃61; service.PageAfter(page.getP(),page.getPs(),query);List people &＃61; (List) list.get(0); //数据集合Integer total &＃61; ((List) list.get(1)).get(0); //总量Page page1 &＃61; new Page();page1.setTotal(total);page1.setRecords(people);page1.setCurrent(page.getP());page1.setSearchCount(true);page1.setSize(page.getPs());page1.setPages(total/page1.getSize());return page1;}


四、service层&＃xff1a;

public List PageAfter(long p, long ps, PeopleQuery query) {return peopleMapper.PageAfter(p,ps,query);
}


五、mapper层&＃xff1a;

List PageAfter(&＃64;Param("p") Long p,&＃64;Param("ps") Long ps, &＃64;Param("query") PeopleQuery query);


六、xml代码&＃xff1a;


select SQL_CALC_FOUND_ROWSa.id,a.name,sum(b.type &＃61; &＃39;PEOPLE_VOICE&＃39; OR NULL) AS voice, sum(b.type &＃61; &＃39;PEOPLE_FACE&＃39; OR NULL) AS face,a.create_datefromdim_people aLEFT JOINdim_attach b ON b.bill_id &＃61; a.idwherea.flag&＃61;1and a.name like CONCAT(&＃39;%&＃39;,#{query.name},&＃39;%&＃39;)GROUP BYa.id,a.nameLIMIT #{p}, #{ps};SELECT FOUND_ROWS() as total;


注意&＃xff1a;在使用时须要在配置文件中&＃xff0c;设置容许sql进行多语句执行&＃xff1a;allowMultiQueries&＃61;true&＃xff0c;在sql的url上加上这个配置就能够了