文档章节

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

Dendy
 Dendy
发布于 2015/03/11 16:25
字数 2233
阅读 520
收藏 6

最新项目用到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
	 */
	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 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);
	}
}


本文转载自:http://blog.csdn.net/z_dendy/article/details/9063719

共有 人打赏支持
Dendy
粉丝 14
博文 253
码字总数 0
作品 0
成都
pagehelper/Mybatis-PageHelper

Mybatis分页插件 - PageHelper 如果你也在用Mybatis,建议尝试该分页插件,这一定是最方便使用的分页插件。 分页插件支持任何复杂的单表、多表分页,部分特殊情况请看重要提示。 想要使用分页...

java梦想家01
2015/08/03
0
0
稀饭下雪/CompanyManager

CompanyManager项目简介 该项目的搭建环境: 项目管理:maven 框架:spring、springmvc、mybatis、easyui 数据:mysql 主要用途:实现了用户管理、菜单管理、部门管理、理财管理、理财报表、...

稀饭下雪
2016/11/02
0
0
abel533/Mybatis_PageHelper

MyBatis 分页插件 - PageHelper English 如果你也在用 MyBatis,建议尝试该分页插件,这一定是最方便使用的分页插件。 分页插件支持任何复杂的单表、多表分页,部分特殊情况请看重要提示。 ...

abel533
2014/09/02
0
0
cc.z/resultbounds

resultbounds - MyBatis分页插件 该插件的可取之处 以简单的方式提供一个优雅的 MyBatis 物理分页功能,不需要修改代码就可以把默认的分页换成物理分页。目前该插件的实现经过几次重构,可读...

cc.z
2017/05/16
0
0
Mybatis分页和Spring的集成

写了一个Mybatis分页控件,在这记录一下使用方式。 在Maven中加入依赖: <dependencies> ... <dependency> <groupId>com.github.miemiedev</groupId> <artifactId>mybatis-paginator</artif......

miemiedev
2013/06/04
0
108

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Spring详解

Spring详解(一)------概述 目录 1、什么是 Spring ? 2、Spring 起源 3、Spring 特点 4、Spring 框架结构 5、Spring 框架特征 6、Spring 优点   本系列教程我们将对 Spring 进行详解的介绍...

DemonsI
20分钟前
0
0
CentOS7系统Nginx安装

1、下载nginx,官方网站https://nginx.org wget https://nginx.org/download/nginx-1.14.0.tar.gz 2、下载Nginx Sticky Module,官方网站https://bitbucket.org/nginx-goodies/nginx-sticky-......

m_lm
23分钟前
0
0
使用zTree树控件(二)

1:treeNode.checked用于判断是勾选还是取消勾选。(treeNode指的是节点) 2:treeObj.transformToArray(nodes)用于查询nodes节点下的所有子节点,json格式。(treeObj为数的id)...

uug
23分钟前
0
0
export, import 和 export default的区别

ES6的两个功能: export 和 import export 对外输出模块 import 引入(加载)进来一个模块 一、export => import 单个变量 export var name = "lishi" 在其他文件里引用 import {name} f...

Js_Mei
28分钟前
1
0
打造RecyclerView的n级列表

先上效果图: 1.该多级列表的优势: 支持无限级列表展开 基于一个recyclerView实现 可以自定义每一级item的样式,定制化更强 2.设计的思路 数据结构List<ItemBean>,ItemBean类中有变量List<...

WelliJohn
37分钟前
1
1

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部