多应用+插件架构,代码干净,二开方便,首家独创一键云编译技术,文档视频完善,免费商用码云13.8K 广告
## 翻页查询 无论是执行JDBC SQL,还是模板SQL,还是SQL文件,SQLManager都提供了翻页操作API,翻页操作需要用到俩个对象 PageRequest,用于翻页请求,PageResult 翻页执行结果 ``` PageRequest request = DefaultPageRequest.of(1,10); ``` 如上代码访问第一页,期望每页10个结果集 PageResult包含了总的个数,以及当前分页的内容,定义如下 ```java public interface PageResult<T> { public long getTotalRow(); public List<T> getList(); public long getTotalPage(); } ``` > 用户可以自由实现PageRequest和PageResult,默认是DefaultPageRequest和DefaultPageResult JDBC SQL翻页查询 ```java String sql = "select * from sys_user where department_id=?"; PageRequest request = DefaultPageRequest.of(1,10); SQLReady sqlReady = new SQLReady(sql,new Object[]{1}); PageResult pr = sqlManager.execute(sqlReady,UserEntity.class,request); DefaultPageResult pageResult = (DefaultPageResult)pr; ``` 模板SQL 翻页查询 ```java String sql = "select #{page('*')} from sys_user where department_id=#{id}"; PageRequest request = DefaultPageRequest.of(1,10); Map map = new HashMap<>(); map.put("id",1); PageResult pr = sqlManager.executePageQuery(sql,UserEntity.class,map,request); ``` 使用了函数page(),用于动态生成俩条sql,一条是输出count(1),求总数的,一条是输出page入参的值,用于查询结果 如果sql语句是group by,则还需要封装成子查询 ```java String sql = "select #{page('*')} from (select count(1) total, department_id from sys_user group by department_id ) a"; PageRequest request = DefaultPageRequest.of(1,10); PageResult<Map> pr = sqlManager.executePageQuery(sql,Map.class,null,request); ``` SQL文件翻页 SQL文件翻页同模板翻页SQL一样,提供一个page()函数,用于方便转化为count语句和查询语句 ```sql select #{page("*")} from sys_user a order by id asc ``` 或者使用pageTag ```sql select -- @pageTag(){ id,name,department_id,create_time --@ } from sys_user a order by id asc ``` java代码如下 ```JAVA SqlId selectById = SqlId.of("user","select"); PageRequest pageRequest = DefaultPageRequest.of(1,20); PageResult pageResult = sqlManager.pageQuery(selectById,UserEntity.class,new HashMap(),pageRequest); ``` 为了提高性能,BeetlSQL提供了pageIgnoreTag 标签函数,在求总数的时候忽略order by,或者其他任何sql片段 ```java select -- @pageTag(){ id,name,department_id,create_time --@ } from sys_user a -- @pageIgnoreTag(){ order by id asc -- @} ``` 更进一步,可以提供俩个SQL来分别作为求总数以及查询结果集,有时候也能优化查询 ```sql queryNewUser === select * from user order by id desc ; queryNewUser$count === select count(1) from user ``` 当翻页查询的时候,如果存在$count,则使用此sql片段作为求总数sql Java代码如下 ```JAVA SqlId selectById = SqlId.of("user","queryNewUser"); PageRequest pageRequest = DefaultPageRequest.of(1,20); PageResult pageResult = sqlManager.pageQuery(selectById,UserEntity.class,new HashMap(),pageRequest); ``` PageRequest对象有一个方法是isTotalRequired,因此可以避免每次都查询,比如,构造PageRequest传入 false ```java PageRequest pageRequest = DefaultPageRequest.of(1,20,false); ``` **再次强调**,page("*")或者pageTag 来生成俩条sql语句,并不是生成俩条翻页语句,如果你的查询语句有group by,那么,提供sql语句应该用子查询方式 ```sql queryByGroup === select #{page("*")} from (select * from user group by department ) ```