Mybatis封装分页查询的java公用类
分页----对于数据量很大的查询中，是必不可少的。mybatis底层的分页sql语句由于需要我们自己去手动写。而实现分页显示的时候我们需要根据分页查询条件查询符合条件的总记录数和记录的详细情况。因此，若是不去实现封装一下的话，我们需要写两条SQL语句去实现它。一次用于查询记录数目。一次用于查询分页显示的详细记录。当项目中碰到很多需要分页的时候，我们便对于每一个Mapper.xml文件都需要去写两条SQL语句。极其麻烦，代码重用----必须重用。所以，一个公共方法的分页需求应运而生。
直接上分页公共代码，其实现的原理是使用了拦截器的拦截作用。拦截一类分页查询的请求。我们根据传进来的参数是否是需要interceptor()方法中拦截的参数，是的话则拦截，并执行相应的SQL追加，否则，不进行追加。直接放行。视作普通查询。
需要在Mybatis的配置文件中配置加载服务器的时候加载该公共类:mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties></properties>
<plugins>
<plugin interceptor="com.iboxpay.clear.filter.PaginationInterceptor"></plugin>
</plugins>
</configuration>
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* 分页拦截器
* @since 10.20.2014
*/
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PaginationInterceptor implements Interceptor {
private final Logger logger = LoggerFactory.getLogger(PaginationInterceptor.class);
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler,
DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);
// 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过下面的两次循环
// 可以分离出最原始的的目标类)
while (metaStatementHandler.hasGetter("h")) {
Object object = metaStatementHandler.getValue("h");
metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY,
DEFAULT_OBJECT_WRAPPER_FACTORY);
}
// 分离最后一个代理对象的目标类
while (metaStatementHandler.hasGetter("target")) {
Object object = metaStatementHandler.getValue("target");
metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY,
DEFAULT_OBJECT_WRAPPER_FACTORY);
}
MappedStatement mappedStatement = (MappedStatement)
metaStatementHandler.getValue("delegate.mappedStatement");
// 只重写需要分页的sql语句。通过MappedStatement的ID匹配，默认重写以Page结尾的
// MappedStatement的sql
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
Object parameterObject = boundSql.getParameterObject();
if(parameterObject instanceof PageParam){
if (parameterObject == null) {
throw new NullPointerException("parameterObject is null!");
} else {
PageParam page = (PageParam)parameterObject;
String sql = boundSql.getSql();
// 重写sql
String pageSql = buildPageSql(sql, page);
metaStatementHandler.setValue("delegate.boundSql.sql", pageSql);
metaStatementHandler.setValue("delegate.rowBounds.offset",
RowBounds.NO_ROW_OFFSET);
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
Connection connection = (Connection) invocation.getArgs()[0];
// 重设分页参数里的总页数等
setPageParameter(sql, connection, mappedStatement, boundSql, page);
}
}
// 将执行权交给下一个拦截器
return invocation.proceed();
}
private String buildPageSql(String sql, PageParam page) {
if (page != null) {
StringBuilder pageSql = new StringBuilder();
pageSql = buildPageSqlForOracle(sql, page);
return pageSql.toString();
} else {
return sql;
}
}
public StringBuilder buildPageSqlForOracle(String sql, PageParam page) {
StringBuilder pageSql = new StringBuilder(100);
String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize());
String endrow = String.valueOf(page.getCurrentPage() * page.getPageSize());
pageSql.append("select * from ( select temp.*, rownum row_id from ( ");
pageSql.append(sql);
pageSql.append(" ) temp where rownum <= ").append(endrow);
pageSql.append(") where row_id > ").append(beginrow);
return pageSql;
}
/**
* 从数据库里查询总的记录数并计算总页数，回写进分页参数<code>PageParam</code>,这样调用
* 者就可用通过 分页参数<code>PageParam</code>获得相关信息。
*
* @param sql
* @param connection
* @param mappedStatement
* @param boundSql
* @param page
* @throws SQLException
*/
private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement,
BoundSql boundSql, PageParam page) throws SQLException {
// 记录总记录数
String countSql = "select count(0) from (" + sql + ")";
PreparedStatement countStmt = null;
ResultSet rs = null;
try {
countStmt = connection.prepareStatement(countSql);
BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql,
boundSql.getParameterMappings(), boundSql.getParameterObject());
setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject());
rs = countStmt.executeQuery();
int totalCount = 0;
if (rs.next()) {
totalCount = rs.getInt(1);
}
page.setTotalCount(totalCount);
int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1);
page.setTotalPage(totalPage);
} catch (SQLException e) {
logger.error("exception", e);
} finally {
try {
rs.close();
} catch (SQLException e) {
logger.error("exception", e);
}
try {
countStmt.close();
} catch (SQLException e) {
logger.error("exception", e);
}
}
}
private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
Object parameterObject) throws SQLException {
ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler.setParameters(ps);
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties arg0) {
}
}
将日志的过滤模式调到DEBUG模式，控制台可以打印出SQL
使用上述方法处理的分页查询，其只需要一条SQL语句就可以(Mapper.xml文件的SQL)
<select id="selectChannelSettleByParam" parameterType="PageParam" resultMap="RS_CHANNELSETTLE">
<![CDATA[
select *
]]>
from channlsettle where 1=1
<if test="params.channelSettleModel.channelId != null and 1000 != params.channelSettleModel.channelId">AND CHANNL_ID=#{params.channelSettleModel.channelId}</if>
<if test="params.channelSettleModel.clearStartTime != null and '' != params.channelSettleModel.clearStartTime">
<![CDATA[
AND to_number(CLEAR_DATE) >= to_number(substr(#{params.channelSettleModel.clearStartTime},0,8))
]]>
</if>
<if test="params.channelSettleModel.clearEndTime != null and '' != params.channelSettleModel.clearEndTime">
<![CDATA[
AND to_number(CLEAR_DATE) <= to_number(substr(#{params.channelSettleModel.clearEndTime},0,8))
]]>
</if>
order by INSTDATE desc
</select>
控制台打印的SQL:
第一条:select count(0) from (select * from channlsettle where 1=1 AND CHANNL_ID=? AND to_number(CLEAR_DATE) >= to_number(substr(?,0,8)) AND to_number(CLEAR_DATE) <= to_number(substr(?,0,8)) order by INSTDATE desc)
第二条:select * from ( select temp.*, rownum row_id from ( select * from channlsettle where 1=1 AND CHANNL_ID=? AND to_number(CLEAR_DATE) >= to_number(substr(?,0,8)) AND to_number(CLEAR_DATE) <= to_number(substr(?,0,8)) order by INSTDATE desc ) temp where rownum <= 20) where row_id > 0
从而让公共类实现了我们需要在Mapper.xml配置文件中重复写入两条SQL的工作，以后没一个分页查询都可以使用。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
