springMVC、myBatis的物理分页和高级查询

最新项目用到springMVC和mybatis，分页其实用一个RowBounds可以实现，但是高级查询不好封装， 经过反复测试，总算搞出来了，感觉封装的不是很好，有待优化和提高！

原理：利用mybatis自定义插件功能，自定义一个拦截器，拦截需要分页的sql，并想办法通过BoundSql对象进行处理，大致分8步：

1、获得BoundSql对象

2、获取原始的写在配置文件中的SQL

3、拦截到mapper中定义的执行查询方法中的参数

4、解析参数，获取高级查询参数信息

5、解析参数，获取查询限制条件

6、根据4、5中的参数拼装并重新生成SQL语句

7、将SQL设置回BoundSql对象中

8、完成。

拦截器：

package com.wtas.page.interceptor;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Map;import java.util.Properties;import java.util.Set;import javax.xml.bind.PropertyException;import org.apache.ibatis.executor.ErrorContext;import org.apache.ibatis.executor.ExecutorException;import org.apache.ibatis.executor.statement.BaseStatementHandler;import org.apache.ibatis.executor.statement.RoutingStatementHandler;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.mapping.ParameterMode;import org.apache.ibatis.plugin.Interceptor;import org.apache.ibatis.plugin.Intercepts;import org.apache.ibatis.plugin.Invocation;import org.apache.ibatis.plugin.Plugin;import org.apache.ibatis.plugin.Signature;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.reflection.property.PropertyTokenizer;import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.type.TypeHandler;import org.apache.ibatis.type.TypeHandlerRegistry;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.wtas.page.PageContext;import com.wtas.page.Pager;import com.wtas.page.Query;import com.wtas.utils.SystemUtil;/** * 查询分页拦截器，用户拦截SQL，并加上分页的参数和高级查询条件 *  * @author dendy *  */@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })public class PaginationInterceptor implements Interceptor {private final Logger logger = LoggerFactory.getLogger(PaginationInterceptor.class);private String dialect = "";// 暂时不需要这个参数，现在根据参数类型来判断是否是分页sql// private String pageMethodPattern = "";public Object intercept(Invocation ivk) throws Throwable {if (!(ivk.getTarget() instanceof RoutingStatementHandler)) {return ivk.proceed();}RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk.getTarget();BaseStatementHandler delegate = (BaseStatementHandler) SystemUtil.getValueByFieldName(statementHandler, "delegate");MappedStatement mappedStatement = (MappedStatement) SystemUtil.getValueByFieldName(delegate, "mappedStatement");// BoundSql封装了sql语句BoundSql boundSql = delegate.getBoundSql();// 获得查询对象Object parameterObject = boundSql.getParameterObject();// 根据参数类型判断是否是分页方法if (!(parameterObject instanceof Query)) {return ivk.proceed();}logger.debug(" beginning to intercept page SQL...");Connection connection = (Connection) ivk.getArgs()[0];String sql = boundSql.getSql();Query query = (Query) parameterObject;// 查询参数对象Pager pager = null;// 查询条件MapMap<String, Object> conditions = query.getQueryParams();pager = query.getPager();// 拼装查询条件if (conditions != null) {Set<String> keys = conditions.keySet();Object value = null;StringBuffer sb = new StringBuffer();boolean first = true;for (String key : keys) {value = conditions.get(key);if (first) {sb.append(" where ").append(key).append(value);first = !first;} else {sb.append(" and ").append(key).append(value);}}sql += sb.toString();}// 获取查询数来的总数目String countSql = "SELECT COUNT(0) FROM (" + sql + ") AS tmp ";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();// 设置总记录数pager.setTotalResult(count);// 设置总页数pager.setTotalPage((count + pager.getShowCount() - 1)/ pager.getShowCount());// 放到作用于PageContext.getInstance().set(pager);// 拼装查询参数String pageSql = generatePageSql(sql, pager);SystemUtil.setValueByFieldName(boundSql, "sql", pageSql);logger.debug("generated pageSql is : " + pageSql);return ivk.proceed();}/** * setting parameters *  * @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);}@SuppressWarnings("unchecked")TypeHandler<Object> typeHandler = (TypeHandler<Object>) 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());}}}}/** * 生成Sql语句 *  * @param sql * @param page * @return */private String generatePageSql(String sql, Pager page) {if (page != null && (dialect != null || !dialect.equals(""))) {StringBuffer pageSql = new StringBuffer();if ("mysql".equals(dialect)) {pageSql.append(sql);pageSql.append(" LIMIT " + page.getCurrentResult() + ","+ page.getShowCount());} else if ("oracle".equals(dialect)) {pageSql.append("SELECT * FROM (SELECT t.*,ROWNUM r FROM (");pageSql.append(sql);pageSql.append(") t WHERE r <= ");pageSql.append(page.getCurrentResult() + page.getShowCount());pageSql.append(") WHERE r >");pageSql.append(page.getCurrentResult());}return pageSql.toString();} else {return sql;}}public Object plugin(Object arg0) {return Plugin.wrap(arg0, this);}public void setProperties(Properties p) {dialect = p.getProperty("dialect");if (dialect == null || dialect.equals("")) {try {throw new PropertyException("dialect property is not found!");} catch (PropertyException e) {e.printStackTrace();}}// pageMethodPattern = p.getProperty("pageMethodPattern");if (dialect == null || dialect.equals("")) {try {throw new PropertyException("pageMethodPattern property is not found!");} catch (PropertyException e) {e.printStackTrace();}}}}

查询对象的封装：

1、map封装查询条件

2、pager对象封装查询限制条件，就是MySql中limit后的参数等附加信息

package com.wtas.page;/** * 分页描述信息 *  * @author dendy *  */public class Pager {// 每一页的显示条数private int showCount;// 总的页数private int totalPage;// 查询的数据总条数private int totalResult;// 当前页private int currentPage;// 从第几条开始获取数据@SuppressWarnings("unused")private int currentResult;public Pager() {this(1);}public Pager(int currentPage) {// 默认每页显示10条记录this(currentPage, 10);}public Pager(int currentPage, int showCount) {this.currentPage = currentPage;if (showCount > 0) {this.showCount = showCount;}// 错误处理if (this.currentPage < 1) {this.currentPage = 1;}}        //只列出关键的getter和setter……        public int getTotalPage() {// 分页算法，计算总页数return this.totalPage;}public int getCurrentResult() {// 计算从第几条获取数据return (currentPage - 1) * showCount;}}

package com.wtas.page;import java.util.Map;/** * 封装查询蚕食和查询条件 *  * @author dendy *   */public class Query {private Map<String, Object> queryParams;private Pager pager;public Map<String, Object> getQueryParams() {return queryParams;}public void setQueryParams(Map<String, Object> queryParams) {this.queryParams = queryParams;}//省略getter和setter}

控制层关键代码：

        /** * 分页时获取所有的学生 *  * @return */@RequestMapping("pageStus")@ResponseBodypublic List<User> pageAllStudents(HttpServletRequest req) {try {Query query = new Query();Pager pager = new Pager();Map<String, Object> queryParams = new HashMap<String, Object>();// 获取分页参数String showCount = req.getParameter("showCount");String currentPage = req.getParameter("currentPage");if (StringUtils.hasLength(showCount)) {pager.setShowCount(Integer.parseInt(showCount));}if (StringUtils.hasLength(currentPage)) {pager.setCurrentPage(Integer.parseInt(currentPage));}// 高级查询条件：学生真实姓名String trueNameForQuery = req.getParameter("trueNameForQuery");if (StringUtils.hasLength(trueNameForQuery)) {queryParams.put(" u.REAL_NAME like ", "'%" + trueNameForQuery+ "%'");}query.setPager(pager);query.setQueryParams(queryParams);List<User> users = userService.pageUsersByRole(query);// req.setAttribute("pager", PageContext.getInstance().get());return users;} catch (Exception e) {LOG.error("getAllStudents error : " + e.getMessage());}return null;}@RequestMapping("getPager")@ResponseBodypublic Pager getPager() {return PageContext.getInstance().get();}

dao中的方法：

        /** * 级联查询所有某一角色的用户信息，带分页 *  * @param roleValue * @param page * @return */List<User> pageUsers(Object query);

dao的Mappder.xml定义：

<select id="pageUsers" resultMap="userMapping" parameterType="hashMap">SELECT DISTINCT u.* FROM T_USER u LEFT JOIN T_REL_USER_ROLE ur ONu.id=ur.user_idLEFT JOIN T_ROLE r ON ur.role_id=r.id</select>

页面通过javascript来异常发送请求获取数据，关键代码：

/** * 处理分页 *  * @param curPage * @param id */function page(curPage, id) {if(curPage <= 0){curPage = 1;}var trueNameForQuery = $("#findByTrueNameInput").val().trim();var url = path + "/studygroup/pageStus.do";var thCss = "class='s-th-class'";var tdCss = "class='s-td-class'";$.ajax({type : "POST",url : url,dataType : "json",data : {"id" : id,"currentPage" : curPage,"trueNameForQuery" : trueNameForQuery },success : function(data) {var json = eval(data);var res = "<tr><th " + thCss + ">选择</th>"        + "<th " + thCss + ">用户名</th>"        + "<th " + thCss + ">真实姓名</th>"        + "<th " + thCss + ">性别</th>"        + "<th " + thCss + ">学校</th>"        + "<th " + thCss + ">年级</th>"        + "<th " + thCss + ">班级</th></tr>";for ( var i = 0; i < json.length; i++) {var userId = json[i].id;var name = json[i].name;var trueName = json[i].trueName;var sex = json[i].sex;var school = "";if (json[i].school) {school = json[i].school.name;}var grade = "";if (json[i].grade) {grade = json[i].grade.name;}var clazz = "";if (json[i].clazz) {clazz = json[i].clazz.name;}res += "<tr><td align='center' " + tdCss + "><input type='checkbox' value='" + userId + "' /></td>"     + "<td align='center' " + tdCss + ">" + (name || "") + "</td>"     + "<td align='center' " + tdCss + ">" + (trueName || "") + "</td>"     + "<td align='center' " + tdCss + ">" + (sex == 1 ? '女' : '男' || "") + "</td>" + "<td align='center' " + tdCss + ">" + school + "</td>" + "<td align='center' " + tdCss + ">" + grade + "</td>" + "<td align='center' " + tdCss + ">" + clazz + "</td>" + "</td></tr>";}$("#inviteStudentsTbl").html(res);// 每次加载完成都要刷新分页栏数据freshPager(id);}});}/** * 重新获取分页对象，刷新分页工具栏 */function freshPager(id){var url = path + "/studygroup/getPager.do";var studyGroupId = id;$.ajax({type : "POST",url : url,dataType : "json",success : function (data) {var pager = eval(data);var currentPage = pager.currentPage;//var currentResult = pager.currentResult;//var showCount = pager.showCount;var totalPage = pager.totalPage;//var totalResult = pager.totalResult;var prePage = currentPage - 1;var nextPage = currentPage + 1;if (prePage <= 0) {prePage = 1;}if (nextPage > totalPage) {nextPage = totalPage;}$("#topPageId").attr("href", "javascript:page(1, " + studyGroupId + ");");$("#prefixPageId").attr("href", "javascript:page(" + prePage + ", " + studyGroupId + ");");$("#nextPageId").attr("href", "javascript:page(" + nextPage + ", " + studyGroupId + ");");$("#endPageId").attr("href", "javascript:page(" + totalPage + ", " + studyGroupId + ");");$("#curPageId").html(currentPage);$("#totalPageId").html(totalPage);}});}/** * 按真实姓名搜索 */function findByTrueName() {page(1, studyGroupId);}

end.

————————————————————————————————————————————————

应网友需要，贴上SystemUtil的代码：

package com.common.utils;import java.lang.reflect.Field;import javax.servlet.http.HttpSession;import com.common.consts.SystemConst;import com.wtas.sys.domain.User;/** * 系统工具类，定义系统常用的工具方法 *  * @author dendy *  */public class SystemUtil {private SystemUtil() {}/** * 获取系统访问的相对路径，如:/WTAS *  * @return */public static String getContextPath() {return System.getProperty(SystemConst.SYSTEM_CONTEXT_PATH_KEY);}/** * 修改一个bean(源)中的属性值，该属性值从目标bean获取 *  * @param dest *            目标bean，其属性将被复制到源bean中 * @param src *            需要被修改属性的源bean * @param filtNullProps *            源bean的null属性是否覆盖目标的属性<li>true : 源bean中只有为null的属性才会被覆盖<li>false *            : 不管源bean的属性是否为null，均覆盖 * @throws IllegalArgumentException * @throws IllegalAccessException */public static void copyBean(Object dest, Object src, boolean filtNullProps)throws IllegalArgumentException, IllegalAccessException {if (dest.getClass() == src.getClass()) {// 目标bean的所有字段Field[] destField = dest.getClass().getDeclaredFields();// 源bean的所有字段Field[] srcField = src.getClass().getDeclaredFields();for (int i = 0; i < destField.length; i++) {String destFieldName = destField[i].getName();String destFieldType = destField[i].getGenericType().toString();for (int n = 0; n < srcField.length; n++) {String srcFieldName = srcField[n].getName();String srcFieldType = srcField[n].getGenericType().toString();// String srcTypeName =// srcField[n].getType().getSimpleName();if (destFieldName.equals(srcFieldName)&& destFieldType.equals(srcFieldType)) {destField[i].setAccessible(true);srcField[n].setAccessible(true);Object srcValue = srcField[n].get(src);Object destValue = destField[i].get(dest);if (filtNullProps) {// 源bean中的属性已经非空，则不覆盖if (srcValue == null) {srcField[n].set(src, destValue);}} else {srcField[n].set(dest, srcValue);}}}}}}/** * 根据字段的值获取该字段 *  * @param obj * @param fieldName * @return */public static Field getFieldByFieldName(Object obj, String fieldName) {for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {try {return superClass.getDeclaredField(fieldName);} catch (NoSuchFieldException e) {}}return null;}/** * 获取对象某一字段的值 *  * @param obj * @param fieldName * @return * @throws SecurityException * @throws NoSuchFieldException * @throws IllegalArgumentException * @throws IllegalAccessException */public static Object getValueByFieldName(Object obj, String fieldName)throws SecurityException, NoSuchFieldException,IllegalArgumentException, IllegalAccessException {Field field = getFieldByFieldName(obj, fieldName);Object value = null;if (field != null) {if (field.isAccessible()) {value = field.get(obj);} else {field.setAccessible(true);value = field.get(obj);field.setAccessible(false);}}return value;}/** * 向对象的某一字段上设置值 *  * @param obj * @param fieldName * @param value * @throws SecurityException * @throws NoSuchFieldException * @throws IllegalArgumentException * @throws IllegalAccessException */public static void setValueByFieldName(Object obj, String fieldName,Object value) throws SecurityException, NoSuchFieldException,IllegalArgumentException, IllegalAccessException {Field field = obj.getClass().getDeclaredField(fieldName);if (field.isAccessible()) {field.set(obj, value);} else {field.setAccessible(true);field.set(obj, value);field.setAccessible(false);}}/** * 从session中获取当前登录用户 *  * @param session * @return */public static User getLoginUser(HttpSession session) {return (User) session.getAttribute(SystemConst.USER_IN_SESSION);}/** * @Description 设置更新信息后的登录用户给session * @param user 登录用户 * @param session session */public static void setUser(User user, HttpSession session) {session.setAttribute(SystemConst.USER_IN_SESSION, user);}}