本文共 6668 字，大约阅读时间需要 22 分钟。

按照上一篇的内容，有一些缺陷的地方。 分页对象传什么都可以，但是返回的是list<HashMap<String,Object>>集合的话，分页方法就是无效了。

这里把我写的贴一下:

Controller:

// 带条件分页		Integer pageNo = null;		Integer pageSize = null;		if (request.getParameter("pageNo") != null) {			pageNo = Integer.parseInt((String) request.getParameter("pageNo"));		} else {			page.setPageNo(1);			pageNo = 1;		}		if (request.getParameter("pageSize") != null) {			pageSize = Integer.parseInt((String) request.getParameter("pageSize"));		} else {			pageSize = 30;		}		page.setPageNo(pageNo);		page.setPageSize(pageSize);		//根据条件查到的集合		List

获取前台传过来的页码和分页数，这样再set到page对象中，前台就可以显示正常的分页和页码了。

jsp页面：

jsp页面，没改过，就用他生成的就可以。 再说一下shiro的自定义标签：

<shiro:hasAnyPermissions ></shiro:hasAnyPermissions>这个标签的意思是有任一权限就可以

<shiro:hasAnyPermissions ></shiro:hasAnyPermissions>这个标签的意思是有某个权限

Controller:

@RequiresPermissions(value= {"contract:busMddkApplication:active","contract:busMddkApplication:edit","contract:busMddkApplication:add","contract:busMddkApplication:info"})@RequestMapping(value = "form")	protected String form(HttpServletRequest request, BusMddkApplicationVo busMddkApplicationVo2, Model model,			HttpServletResponse response, RedirectAttributes redirectAttributes) { }

上面的意思是有任一权限就可以进入这个方法。 这是说一下分页的问题,可以用他的page对象，会进入pageInterceptor的拦截器。感觉他查询总数的方法有点问题。贴一下原来的和我改动的代码。 //得到总记录数

common.persistence.interceptor.SQLHelper包下面，在PaginationInterceptor里边先调用 

page.setCount(SQLHelper.getCount(originalSql, null, mappedStatement, parameterObject, boundSql, log));

贴一下他原来的getCount()方法。

public static int getCount(final String sql, final Connection connection,    							final MappedStatement mappedStatement, final Object parameterObject,    							final BoundSql boundSql, Log log) throws SQLException {    	String dbName = Global.getConfig("jdbc.type");		final String countSql;		if("oracle".equals(dbName)){			countSql = "select count(1) from (" + sql + ") tmp_count";			//需要优化的sql		}else{			countSql = "select count(1) from (" + removeOrders(sql) + ") tmp_count";		}        Connection conn = connection;        PreparedStatement ps = null;        ResultSet rs = null;        try {        	if (log.isDebugEnabled()) {                log.debug("COUNT SQL: " + StringUtils.replaceEach(countSql, new String[]{"\n","\t"}, new String[]{" "," "}));            }        	if (conn == null){        		conn = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();            }        	ps = conn.prepareStatement(countSql);            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,                    boundSql.getParameterMappings(), parameterObject);            //解决MyBatis 分页foreach 参数失效 start			if (Reflections.getFieldValue(boundSql, "metaParameters") != null) {				MetaObject mo = (MetaObject) Reflections.getFieldValue(boundSql, "metaParameters");				Reflections.setFieldValue(countBS, "metaParameters", mo);			}			//解决MyBatis 分页foreach 参数失效 end             SQLHelper.setParameters(ps, mappedStatement, countBS, parameterObject);            rs = ps.executeQuery();            int count = 0;            if (rs.next()) {                count = rs.getInt(1);            }            return count;        } finally {            if (rs != null) {                rs.close();            }            if (ps != null) {            	ps.close();            }            if (conn != null) {            	conn.close();            }        }    }      /**      * 去除hql的orderBy子句。      * @param hql      * @return      */      @SuppressWarnings("unused")	private static String removeOrders(String qlString) {          Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);          Matcher m = p.matcher(qlString);          StringBuffer sb = new StringBuffer();          while (m.find()) {              m.appendReplacement(sb, "");          }        m.appendTail(sb);        return sb.toString();      }

 这样生成的sql就是正常的条件查询语句外面包了一层select count(1) from(原sql)。     但是在实际使用过程中，数据量比较大(百万级别),并且查询条件和参数很多的时候,这样会很慢。不如直接select count(1) from (***) 这样的快。     所以改写了他的getCount()方法,在需要优化的sql的where后边加上'needOptimization' = 'needOptimization'。

public static int getCount(final String sql, final Connection connection,    							final MappedStatement mappedStatement, final Object parameterObject,    							final BoundSql boundSql, Log log) throws SQLException {    	String dbName = Global.getConfig("jdbc.type");		final String countSql;		if("oracle".equals(dbName)){			countSql = "select count(1) from (" + sql + ") tmp_count";			//需要优化的sql		}else if(sql.contains("needOptimization")){			countSql = "select count(1) from "+ removeContent(sql);		}else{			countSql = "select count(1) from (" + removeOrders(sql) + ") tmp_count";		}        Connection conn = connection;        PreparedStatement ps = null;        ResultSet rs = null;        try {        	if (log.isDebugEnabled()) {                log.debug("COUNT SQL: " + StringUtils.replaceEach(countSql, new String[]{"\n","\t"}, new String[]{" "," "}));            }        	if (conn == null){        		conn = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();            }        	ps = conn.prepareStatement(countSql);            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,                    boundSql.getParameterMappings(), parameterObject);            //解决MyBatis 分页foreach 参数失效 start			if (Reflections.getFieldValue(boundSql, "metaParameters") != null) {				MetaObject mo = (MetaObject) Reflections.getFieldValue(boundSql, "metaParameters");				Reflections.setFieldValue(countBS, "metaParameters", mo);			}			//解决MyBatis 分页foreach 参数失效 end             SQLHelper.setParameters(ps, mappedStatement, countBS, parameterObject);            rs = ps.executeQuery();            int count = 0;            if (rs.next()) {                count = rs.getInt(1);            }            return count;        } finally {            if (rs != null) {                rs.close();            }            if (ps != null) {            	ps.close();            }            if (conn != null) {            	conn.close();            }        }    }     /**     * 将第一个from前的内容删除     * @Description:TODO     * @author:gmwang     * @time:2017年8月29日 上午10:16:28     */    private static String removeContent(String sql) {    	String sqlAfter = StringUtils.substringAfter(sql,"FROM");		return sqlAfter;	}

如果原来是select count(1) from ( select a.*,b.* from aa a  left join bb b on a.id = b.id ) 现在就改成了 select count(1) from aa a left join bb b on a.id = b.id 原来的sql执行24秒,现在大约是6秒。

转载地址：http://gfmlf.baihongyu.com/