正文
简介今天看了最新版本的《Java开发手册》，发现了一个有趣的事：【强制】代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
情景介绍#
当前，我们的项目分页采用的是自定义的Mybatis的plugin，也就是自己写的一个分页组件（ps:感觉现成的不好用，遂自己写）
分页组件代码：
/** * @author :breakpoint/赵立刚 * @date : 2019/11/10 */ @Slf4j @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})}) public class PagePlugin extends AbstractPlugin implements Interceptor { // 数据库的方言 private static String dialect = ""; // id的正则表达式 private static String pageSqlId = ""; @Override public Object intercept(Invocation invocation) throws Throwable { Object target = invocation.getTarget(); Method method = invocation.getMethod(); if (target instanceof RoutingStatementHandler) { RoutingStatementHandler statementHandler = (RoutingStatementHandler) target; BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate"); MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement"); // 专门匹配需要分页的查询 有的查询是不需要分页的 if (mappedStatement.getId().matches(pageSqlId)) { log.info("开始分页查询 start"); BoundSql boundSql = delegate.getBoundSql(); Object parameterObject = boundSql.getParameterObject(); if (parameterObject == null) { throw new NullPointerException("parameterObject error"); } else { Connection connection = (Connection) invocation.getArgs()[0]; String sql = boundSql.getSql(); String countSql = "select count(0) from (" + sql + ") myCount"; log.info("总数sql 语句:{}", countSql); PreparedStatement countStmt = connection.prepareStatement(countSql); BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject); setParameters(countStmt, mappedStatement, countBS, parameterObject); ResultSet rs = countStmt.executeQuery(); int count = 0; if (rs.next()) { count = rs.getInt(1); } rs.close(); countStmt.close(); PageInfo pageInfo = null; // 设置结果的返回值 if (parameterObject instanceof PageInfo) { pageInfo = (PageInfo) parameterObject; pageInfo.setPageTotalByTotalCount(count); } else if (parameterObject instanceof Map) { Map<String, Object> map = (Map<String, Object>) parameterObject; pageInfo = (PageInfo) map.get("pageInfo"); if (pageInfo == null) pageInfo = new PageInfo(); pageInfo.setPageTotalByTotalCount(count); } else { Field pageField = ReflectHelper.getFieldByFieldName(parameterObject, "pageInfo"); if (pageField != null) { pageInfo = (PageInfo) ReflectHelper.getValueByFieldName(parameterObject, "pageInfo"); if (pageInfo == null) pageInfo = new PageInfo(); pageInfo.setPageTotalByTotalCount(count); ReflectHelper.setValueByFieldName(parameterObject, "pageInfo", pageInfo); } else { throw new NoSuchFieldException(parameterObject.getClass().getName()); } } // 具有结果集合 if (count > 0) { setHaveData(true); } else { setHaveData(false); } String pageSql = generatePageSql(sql, pageInfo); log.info("pageSql={}", pageSql); ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); } } } log.info("target={}", target); log.info("method={}", method); return invocation.proceed(); } /** * 生成分页的sql * * @param sql * @param page * @return */ private String generatePageSql(String sql, PageInfo page) { if (page != null && (dialect != null || !dialect.equals(""))) { StringBuffer pageSql = new StringBuffer(); if ("mysql".equals(dialect)) { pageSql.append(sql); pageSql.append(" limit " + page.getDataStart() + "," + page.getPageSize()); } else if ("oracle".equals(dialect)) { pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from ("); pageSql.append(sql); pageSql.append(") tmp_tb where ROWNUM<="); pageSql.append(page.getDataEnd()); pageSql.append(") where row_id>="); pageSql.append(page.getDataStart()); } return pageSql.toString(); } else { return sql; } } /** * 设置参数操作 * * @param ps * @param mappedStatement * @param boundSql * @param parameterObject * @throws SQLException */ private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException { ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId()); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); if (parameterMappings != null) { Configuration configuration = mappedStatement.getConfiguration(); TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject); for (int i = 0; i < parameterMappings.size(); i++) { ParameterMapping parameterMapping = parameterMappings.get(i); if (parameterMapping.getMode() != ParameterMode.OUT) { Object value; String propertyName = parameterMapping.getProperty(); PropertyTokenizer prop = new PropertyTokenizer(propertyName); if (parameterObject == null) { value = null; } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { value = parameterObject; } else if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) { value = boundSql.getAdditionalParameter(prop.getName()); if (value != null) { value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length())); } } else { value = metaObject == null ? null : metaObject.getValue(propertyName); } TypeHandler typeHandler = parameterMapping.getTypeHandler(); if (typeHandler == null) { throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId()); } typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType()); } } } } @Override public Object plugin(Object o) { return Plugin.wrap(o, this); } /** * 设置属性 * * @param properties */ @Override public void setProperties(Properties properties) { dialect = properties.getProperty("dialect"); if (dialect == null || dialect.equals("")) { try { throw new PropertyException("dialect property is not found!"); } catch (PropertyException e) { e.printStackTrace(); } } pageSqlId = properties.getProperty("pageSqlId"); if (pageSqlId == null || pageSqlId.equals("")) { try { throw new PropertyException("pageSqlId property is not found!"); } catch (PropertyException e) { e.printStackTrace(); } } } }
我们看到，当我们结果集是0的时候，我设置了一个参数
// 具有结果集合 if (count > 0) { setHaveData(true); } else { setHaveData(false); }
这个方法在我们的抽象类里面：
package com.block.plugins; /** * @author :breakpoint/赵立刚 * @date : 2020/04/25 */ public abstract class AbstractPlugin { private static ThreadLocal<Boolean> haveData = new ThreadLocal<>(); protected Boolean isHaveData() { Boolean aBoolean = haveData.get(); haveData.set(null); return aBoolean; } protected void setHaveData(Boolean have) { haveData.set(have); } }
从上面的代码，我们可以看到，我们定义了一个ThreadLocal ，保证了在同一个请求上获取到我们的数据。
在另一个拦截器里面实现我们的业务逻辑
/** * @author :breakpoint/赵立刚 * @date : 2020/04/25 */ @Slf4j @Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class})}) public class QueryResultPlugin extends AbstractPlugin implements Interceptor { // id的正则表达式 private static String pageSqlId = ""; @Override public Object intercept(Invocation invocation) throws Throwable { Boolean haveData = isHaveData(); if (null == haveData || haveData) { log.info("执行 返回执行结果"); return invocation.proceed(); } else { // 没有执行 直接返回 log.info("没有执行 直接返回"); return new ArrayList<>(16); } } @Override public Object plugin(Object target) { // 返回代理的对象 return Plugin.wrap(target, this); //return target; } @Override public void setProperties(Properties properties) { pageSqlId = properties.getProperty("pageSqlId"); if (pageSqlId == null || pageSqlId.equals("")) { try { throw new PropertyException("pageSqlId property is not found!"); } catch (PropertyException e) { e.printStackTrace(); } } } }
这样我们就实现了他所说的情况！！！！！
运行结果#
