public interface PageSortInterface { Integer getPageNum(); Integer getPageSize(); String getSort(); String getOrder(); } public interface MyBaseMapper<T> extends Mapper<T>, MySqlMapper<T>,MyInsertListMapper<T> { } public interface UserMapper extends MyBaseMapper<User> { } springboot项目在有这三个接口的情况下,实现在sql层的分页(使用RowBounds方式)
时间: 2024-03-17 08:41:55 浏览: 21
可以通过以下步骤来实现在SQL层的分页:
1. 定义一个PageSort类,实现PageSortInterface接口,用于接收分页所需的参数pageNum、pageSize、sort和order。
```
public class PageSort implements PageSortInterface {
private Integer pageNum;
private Integer pageSize;
private String sort;
private String order;
public PageSort(Integer pageNum, Integer pageSize, String sort, String order) {
this.pageNum = pageNum;
this.pageSize = pageSize;
this.sort = sort;
this.order = order;
}
public Integer getPageNum() {
return pageNum;
}
public void setPageNum(Integer pageNum) {
this.pageNum = pageNum;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public String getSort() {
return sort;
}
public void setSort(String sort) {
this.sort = sort;
}
public String getOrder() {
return order;
}
public void setOrder(String order) {
this.order = order;
}
}
```
2. 在MyBaseMapper中添加一个方法,用于在SQL中实现分页查询:
```
public interface MyBaseMapper<T> extends Mapper<T>, MySqlMapper<T>, MyInsertListMapper<T> {
List<T> selectByPage(@Param("page") PageSort page, @Param("example") Example example, RowBounds rowBounds);
}
```
3. 在UserMapper中继承MyBaseMapper,并使用@SelectProvider注解指定SQL语句和对应的Provider类:
```
public interface UserMapper extends MyBaseMapper<User> {
@SelectProvider(type = UserSqlProvider.class, method = "selectByPage")
List<User> selectByPage(@Param("page") PageSort page, @Param("example") Example example);
}
```
4. 在UserSqlProvider类中编写selectByPage方法,用于拼接SQL语句:
```
public class UserSqlProvider {
public String selectByPage(@Param("page") PageSort page, @Param("example") Example example) {
String sql = "SELECT * FROM user";
if (example != null) {
sql += " WHERE " + example.getWhereClause();
}
if (StringUtils.isNotEmpty(page.getSort())) {
sql += " ORDER BY " + page.getSort() + " " + page.getOrder();
}
sql += " LIMIT #{page.pageNum}, #{page.pageSize}";
return sql;
}
}
```
5. 在Service层中调用UserMapper的selectByPage方法,传入PageSort和Example参数即可实现分页查询。
```
public List<User> selectUsers(Integer pageNum, Integer pageSize, String sort, String order) {
Example example = new Example(User.class);
example.createCriteria().andEqualTo("status", 1);
PageSort pageSort = new PageSort(pageNum, pageSize, sort, order);
RowBounds rowBounds = new RowBounds(pageNum, pageSize);
return userMapper.selectByPage(pageSort, example, rowBounds);
}
```
以上是使用RowBounds方式实现在SQL层的分页查询的方法。另外,也可以使用PageHelper插件来实现分页查询,该插件能够自动拦截Mapper接口的方法调用,并根据参数自动进行分页。