最新项目用到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;
- // 查询条件Map
- Map<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")
- @ResponseBody
- public 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")
- @ResponseBody
- public Pager getPager() {
- return PageContext.getInstance().get();
- }
dao中的方法:
- /**
- * 级联查询所有某一角色的用户信息,带分页
- *
- * @param roleValue
- * @param page
- * @return
- */
- ist<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 ON
- u.id=ur.user_id
- LEFT 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);
- }
- }