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;
// 查询条件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.id;
var name = json.name;
var trueName = json.trueName;
var sex = json.sex;
var school = "";
if (json.school) {
school = json.school.name;
}
var grade = "";
if (json.grade) {
grade = json.grade.name;
}
var clazz = "";
if (json.clazz) {
clazz = json.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.getName();
String destFieldType = destField.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.setAccessible(true);
srcField[n].setAccessible(true);
Object srcValue = srcField[n].get(src);
Object destValue = destField.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);
}
}