微信产品展示小程序/百度sem优化师

MyBatis 分页插件原理: 通过拦截器拦截指定方法重写响应SQL语句实现

1. 自定义分页插件

 

1.1. application.properties 添加 mybatis 属性

# Locations of MyBatis mapper files. 
mybatis.mapper-locations=classpath*:/mybatis/mapper/**.xml# Packages to search type aliases. (Package delimiters are ",; \t\n")
mybatis.type-aliases-package=com.answer.springboot.mybatis.web.entity# Location of MyBatis xml config file.
mybatis.config-location=classpath:/mybatis/config/mybatis-config.xml

 

1.2. mybatis-config.xml 配置文件

<configuration><!-- 引入 MyBatis 分页插件 --><plugins><plugin interceptor="com.answer.springboot.mybatis.web.util.paging.PagePlugin"><!-- 数据库方言 --><property name="dialect" value="mysql" /><!-- 拦截以 Page 结尾的所有方法 --><!--<property name="pageSqlId" value=".*(Page)$" />--><!-- 拦截以 find 开头的所有方法 --><property name="pageSqlId" value="^find.*" /><!-- 分页对象信息对应的key值 --><property name="pageParam" value="pageInfo" /></plugin></plugins>
</configuration>    

 

1.3. 分页拦截器类 PagePlugin

// type : 可拦截的类型： Executor、StatementHandler、ParameterHandler 和 ResultSetHandler
// 不同类型拦截器的顺序 Executor -> ParameterHandler -> StatementHandler ->ResultSetHandler
// 拦截 StatementHandler#prepare 方法, args 为方法 prepare  的参数
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class}) })
public class PagePlugin implements Interceptor {private static final Logger LOGGER = LoggerFactory.getLogger(PagePlugin.class);/** 数据库方言 */private static String dialect = "mysql";/** 拦截以 Page 结尾的所有方法 */private static String pageSqlId;/** 分页对象信息对应的key值 */private static String pageParam;@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();MetaObject metaObject = SystemMetaObject.forObject(statementHandler);// 获取查询接口映射的相关信息MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");BoundSql boundSql = statementHandler.getBoundSql();String sql = boundSql.getSql();// 获取方法的全限定名, 或者通过boundSql.getParameterObject()获取String methodAllName = mappedStatement.getId();// 截取方法名String methodName = methodAllName.substring(methodAllName.lastIndexOf(".") + 1, methodAllName.length());// 获取方法的类型SqlCommandType sqlCmdType = mappedStatement.getSqlCommandType();// 如果是查询SQL并且和 pageSqlId 匹配则进行拦截, 进行统一的分页处理if (SqlCommandType.SELECT == sqlCmdType && methodName.matches(pageSqlId)) {// 获取方法的参数信息Map paramsMap = (Map) statementHandler.getParameterHandler().getParameterObject();// 如果参数中包含分页对象信息, 则进行重写SQLif (paramsMap.containsKey(pageParam)) {// 分页信息实体类PageInfo pageInfo = (PageInfo) paramsMap.get(pageParam);// 当前页码int currentPage = pageInfo.getCurrentPage();// 每页显示记录数int pageSize = pageInfo.getPageSize();// 记录偏移量int offset = (currentPage - 1) * pageSize;// 重写SQL, 加入分页逻辑sql = sql.trim() + " LIMIT " + offset + "," + pageSize;LOGGER.info("SQL Command Type【{}】, method【{}】 need paing.", sqlCmdType, methodName);// 将重写完的分页SQL语句覆盖掉原有的SQL语句metaObject.setValue("delegate.boundSql.sql", sql);}}LOGGER.info("mybatis intercept sqlID: {}, sql: \n{}", methodName, sql);// 打印 SQL 执行时间long startTime = System.currentTimeMillis();Object object = invocation.proceed();LOGGER.info("{} execute time: {} ms.", methodName, System.currentTimeMillis() - startTime);return object;}/*** 获取代理对象* */@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}/*** 设置代理对象的参数** @param properties config(sql-map-config.xml)中配置的 plugin 对应的 property* */@Overridepublic void setProperties(Properties properties) {// 缺省时, 默认mysqldialect = properties.getProperty("dialect", "mysql");pageSqlId = properties.getProperty("pageSqlId");checkParam("pageSqlId", pageSqlId);pageParam = properties.getProperty("pageParam");checkParam("pageParam", pageParam);System.out.println("mybatis intercept dialect: " + dialect + ", pageSqlId: " + pageSqlId + ", pageParam: " + pageParam);}private void checkParam(String key, String value) {if (StringUtils.isEmpty(value)) {try {throw new PropertyException(key + " property is not found.");} catch (PropertyException e) {e.printStackTrace();}}}
}

 

1.4. 分页对象实体类 PageInfo

public class PageInfo {private int currentPage;private int pageSize;public PageInfo(int currentPage, int pageSize) {this.currentPage = currentPage;this.pageSize = pageSize;}public int getCurrentPage() {return currentPage;}public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}
}

 

1.5. mapper 文件 SQL 配置信息

<select id="findUsers" parameterType="Long" resultType="User">select *from smw_user
</select>

 

1.6. 实际项目中使用

@Controller
@RequestMapping("/user")
public class UserController {@RequestMapping("findUsers")@ResponseBodypublic AIResponse findUsers() {Map<String, Object> params = new HashMap<>();PageInfo pageInfo = new PageInfo(1, 5);// 引入 MyBatis 分页插件params.put("pageInfo", pageInfo);List<User> users = userService.findUsers(params);logger.info("users size: " + users.size());return AIResponse.success(users);}}

 

2. 使用 Github 开源分页插件

• Mybatis-PageHelper