MyBatis的三种分页方式

RowBounds实现分页
RowBounds 是 MyBatis提供的（MyBatis自带的）用于分页的一个简单工具，它通过 Java 代码的方式来实现分页功能。使用 RowBounds 时，只需要在 Mapper 的方法中添加 RowBounds 参数即可。
```
List<YourModel> selectByRowBounds(YourExample example, RowBounds rowBounds);
```
然后，在调用 Mapper 方法时传入 RowBounds 对象，其中包含了偏移量（offset）和限制量（limit）。
```
int offset = 0; // 起始位置
int limit = 10; // 每页显示的数据条数
RowBounds rowBounds = new RowBounds(offset, limit);
List<YourModel> list = yourMapper.selectByRowBounds(example, rowBounds);
```
RowBounds 简单易用，但并不是一种高效的分页方式，因为它是通过在数据库返回全部结果之后，在内存中进行分页处理，这在处理大量数据时会非常低效。
物理分页插件实现分页
引入 PageHelper 依赖
```
<dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper-spring-boot-starter</artifactId><version>2.1.0</version>
</dependency>
```
使用 PageHelper
只需要在查询方法前调用 PageHelper.startPage 方法，传入页码和每页数量即可。
```
PageHelper.startPage(1, 10); // 页码，每页数量
List<YourModel> list = yourMapper.selectByExample(example);
```
PageHelper 会自动的对接下来的第一个 MyBatis 查询进行分页处理。它通过改写原 SQL 语句，在 SQL 执行层面实现分页，它能够实现真正意义上的物理分页，从而大大提高了分页的效率。

编写分页SQL实现分页

@Select("SELECT * FROM your_table LIMIT #{offset}, #{limit}")
List<YourModel> selectByPage(@Param("offset") int offset, @Param("limit") int limit);

这种基于 “limit offset,length” 的方式如果offset的值很大时，也会导致性能很差

结合mybatis的Interceptor实现分页

/*** 自定义分页注解*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Limit {/*** 当前页面* * @return*/int page() default 0;/*** 每页显示数量* * @return*/int pageSize() default 10;
}

/*** 自定义排序注解*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface OrderBy {/*** 表的别名*/String tableAlias() default "";/*** 排序字段*/String orderColumn() default "";/*** ASC/DESC 默认倒序* @return*/boolean isAsc() default false;}

定义基础切面处理类

/*** @ClassName BaseAspectAbstract* @Description 基础切面处理类，每一个Spring的切面都需要去继承此抽象类* @Date**/
public abstract class BaseAspectAbstract {private static TreeMap<Integer, SQLLanguage> CONTAINERS = new TreeMap<>();// 放入sql 切点、sql类型、sqlpublic void putSQL(JoinPoint point, SQLEnums sqlEnums, SQLLanguage sqlLanguage) {CONTAINERS.put(sqlEnums.getId(), sqlLanguage);// 获取方法里的参数Object parmas = point.getArgs()[0];Map map = (Map)parmas;map.put("SQL", getSQL());}public TreeMap<Integer, SQLLanguage> getSQL() {return CONTAINERS;}}

进行分页切面

@Component
@Aspect
@Order(3) //拼接sql时的顺序
public class LimitAspect extends BaseAspectAbstract {@Pointcut("@annotation(com.mybatis.interceptor.annotation.Limit)")public void limitCut() {}@Before("limitCut()")public void limit(JoinPoint point) {StringBuilder limitBuilder = new StringBuilder(" LIMIT ");MethodSignature methodSignature = (MethodSignature)point.getSignature();// 获得对应注解Limit limit = methodSignature.getMethod().getAnnotation(Limit.class);if (!StringUtils.isEmpty(limit)) {limitBuilder.append(limit.page()).append(",").append(limit.pageSize());putSQL(point, LIMIT, new SQLLanguage(limitBuilder.toString()));}}
}

进行排序切面

@Component
@Aspect
@Order(2)
public class OrderByAspect extends BaseAspectAbstract {// 切点：对注解中的特定注解进行拦截，进行增强@Pointcut("@annotation(com.mybatis.interceptor.annotation.OrderBy)")public void orderByCut() {}// 执行切点操作，将其进行增强，放入排序@Before("orderByCut()")public void orderBy(JoinPoint point) {StringBuilder orderByBuilder = new StringBuilder(" ORDER BY ");MethodSignature methodSignature = (MethodSignature)point.getSignature();// 获得对应注解OrderBy orderBy = methodSignature.getMethod().getAnnotation(OrderBy.class);if (!StringUtils.isEmpty(orderBy)) {String sort = orderBy.isAsc() ? " asc " : " desc";orderByBuilder.append(orderBy.orderColumn()).append(sort);putSQL(point, ORDERBY, new SQLLanguage(orderByBuilder.toString()));}}
}

枚举sql顺序

/*** sql类型枚举*/
public enum SQLEnums {/*** 数字越靠前 则拼接SQL语句越靠前执行，目前拼接顺序为* SELECT * FROM table GROUP BY ORDER BY xxx LIMIT 0, 10*/LIKE(1, "LIKE"), GROUPBY(2, "GROUP BY"), ORDERBY(3, "ORDER BY"), LIMIT(4, "LIMIT");private int id;private String condition;SQLEnums(int id, String condition) {this.id = id;this.condition = condition;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getCondition() {return condition;}public void setCondition(String condition) {this.condition = condition;}
}

执行sql增强的注解放置在serviceImpl里面

@RequestMapping("/nba")
public class PlayController {@Autowiredprivate PlayerService playerService;@RequestMapping("/player")public List<Player> getList(Map<String, Object> params) {List<Player> players = playerService.getList(params);return players;}
}

执行sql增强

@Service
public class PlayerServiceImpl implements PlayerService {@Autowiredprivate PlayerMapper playerMapper;// 加入自定义注解，方便切点进行增强@OrderBy(orderColumn = "height")@Limit()@Overridepublic List<Player> getList(Map<String, Object> params) {return playerMapper.getList(params);}
}

执行到这里会执行动态代理，然后执行sql拦截

执行sql拦截

@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}))
@Component
public class DataFilterInterceptor extends AbstractSqlParserHandler implements Interceptor {// 拦截器@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());MetaObject metaObject = SystemMetaObject.forObject(statementHandler);// SQLLanguage 解析sqlParser(metaObject);// 非查询操作MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {return invocation.proceed();}// 取出原始SQL 取出参数BoundSql boundSql = (BoundSql)metaObject.getValue("delegate.boundSql");String dataSql = boundSql.getSql();Object paramObj = boundSql.getParameterObject();Map map = (Map)paramObj;String sqlLanguage = getSQLLanguage(map);String sql = dataSql + sqlLanguage;// 重写sqlmetaObject.setValue("delegate.boundSql.sql", sql);return invocation.proceed();}// 插件@Overridepublic Object plugin(Object target) {if (target instanceof StatementHandler) {return Plugin.wrap(target, this);}return target;}@Overridepublic void setProperties(Properties properties) {}// 获取sql语句private String getSQLLanguage(Map<String, Object> map) {TreeMap<Integer, SQLLanguage> sqlMap = (TreeMap)map.get("SQL");StringBuilder sqlBuilder = new StringBuilder();for (Map.Entry treeMap : sqlMap.entrySet()) {SQLLanguage sql = (SQLLanguage)treeMap.getValue();if (null != sql) {sqlBuilder.append(sql);}}return sqlBuilder.toString();}
}

MyBatis的三种分页方式

最新新闻

热搜词