MyBatis的简单易用的分页研究

ukula

　　网上很多Mybatis的分页文档，但是感觉千篇一律，并且不完整，只查询出来分页后的List列表，未封装查询的总条数（还需要单独查询记录总数）。
本文借鉴iteye网友对记录条数的封装思路，做了一个完整版的分页查询。
　　参考：http://stamen.iyunv.com/blog/1901576
　　使用的是和Hibernate一样方言的模式来实现各种数据库的分页，先看基础类Dialect.java：

/**
* @description 方言基类
* @author aokunsang
* @date 2013-8-23
*/
public abstract class Dialect {
protected final static String SELECT = "select";
protected final static String SELECT_DISTINCT = "select distinct";
public final static String COUNT_FIELD = "TOTAL";
/* 数据库类型 */
public static enum DatabaseType{
MYSQL,ORACLE,SQLSERVER
}
/**
* 获取分页SQL语句
* @param sql The SQL statement to base the limit query off of.
* @param offset Offset of the first row to be returned by the query (zero-based)
* @param limit Maximum number of rows to be returned by the query
* @return A new SQL statement with the LIMIT clause applied.
*/
public abstract String getLimitString(String sql, int offset, int limit);
/**
* 获取总条数的SQL语句[该方法通用]
* @param sql
* @return
*/
public String getCountString(String sql){
StringBuilder sb = new StringBuilder(sql.trim());
String querySqlLowered = sql.trim().toLowerCase();
int orderByIndex = querySqlLowered.toLowerCase().indexOf("order by");
if (orderByIndex > 0) {
sb.delete(orderByIndex, orderByIndex + sql.substring(orderByIndex).length());
}
String querySqlFrom = sb.toString().toLowerCase();
int fromIndex = querySqlFrom.indexOf("from");
if(fromIndex > 0){
sb.delete(0, fromIndex);
}
sb.insert(0, "select count(1) as " + COUNT_FIELD + " ");
return sb.toString();
}
}

　　再看OracleDialect.java，其他的数据库分页参考hibernate核心包的源码org.hibernate.dialect包名下的类(附图)。

/**
* @description Oracle方言
* @author aokunsang
* @date 2013-8-23
*/
public class OracleDialect extends Dialect {
/* (non-Javadoc)
* @see com.aokunsang.mybatis.study.util.Dialect#getLimitString(java.lang.String, int, int)
*/
@Override
public String getLimitString(String sql, int offset, int limit) {
sql = sql.trim();
boolean isForUpdate = false;
if(sql.toLowerCase().endsWith(" for update")){
sql = sql.substring( 0, sql.length()-11 );
isForUpdate = true;
}
StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
if (offset > 0) {  //不是第一页
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
}else {
pagingSelect.append("select * from ( ");
}
pagingSelect.append(sql);
if (offset > 0) {
pagingSelect.append(" ) row_ where rownum <= "+(limit+offset)+") where rownum_ > "+offset);
}else {
pagingSelect.append(" ) where rownum <= "+limit);
}
if(isForUpdate) {
pagingSelect.append( " for update" );
}
return pagingSelect.toString();
}
}

 Mybatis的分页拦截器基本和网上查询到的一样，没做什么修改，参考类PaginationInterceptor.java：

/**
* @description 分页拦截器
* @author aokunsang
* @date 2013-8-23
*/
@Intercepts(
{@Signature(
type = StatementHandler.class,
method = "prepare",
args = {Connection.class}
)
})
public class PaginationInterceptor implements Interceptor {
private static final Log logger = LogFactory.getLog(PaginationInterceptor.class);
private static final String DATABASE_TYPE_ID = "dialect";
private static final String PAGE_SQL_MATCH_ID = "pageSqlId";
private static final DatabaseType DEFAULT_DATA_SOURCE_TYPE = DatabaseType.valueOf("ORACLE");  //默认数据库类型
//必须以Page结尾的查询ID  [解译：配置需要拦截的Mapper.xml中的查询ID（正则匹配），如：<select id="xx"></select> 中的xx]
private static final String DEFAULT_PAGE_SQL_ID = ".*Page$";
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler,DEFAULT_OBJECT_FACTORY,DEFAULT_OBJECT_WRAPPER_FACTORY);
Connection conn = (Connection)invocation.getArgs()[0];
BoundSql boundSql = statementHandler.getBoundSql();
RowBounds rowBounds = (RowBounds)metaStatementHandler.getValue("delegate.rowBounds");
if(rowBounds == null || rowBounds == RowBounds.DEFAULT){
return invocation.proceed();
}
Configuration configuration = (Configuration)metaStatementHandler.getValue("delegate.configuration");
DatabaseType databaseType = null;
try {
databaseType = DatabaseType.valueOf(configuration.getVariables().getProperty(DATABASE_TYPE_ID).toUpperCase());
} catch (Exception e) {
databaseType = DEFAULT_DATA_SOURCE_TYPE;
logger.warn(String.format("在configurationProperties中未找到数据库类型设置,默认使用[%s]。", "ORACLE"));
}
String pageSqlId = "";
try {
pageSqlId = configuration.getVariables().getProperty(PAGE_SQL_MATCH_ID);
} catch (Exception e) {
pageSqlId = DEFAULT_PAGE_SQL_ID;
logger.warn(String.format("在configurationProperties中未找到SQL查询语句ID的匹配设置,默认使用[%s]。",DEFAULT_PAGE_SQL_ID));
}
Dialect dialect = null;
switch(databaseType){
case ORACLE:{
dialect = new OracleDialect();break;
}
case MYSQL:{}
}
MappedStatement mappedStatement = (MappedStatement)metaStatementHandler.getValue("delegate.mappedStatement");
if(mappedStatement.getId().matches(pageSqlId)){
String originalSql = boundSql.getSql();
//获取记录总行数，并将其绑定到当前线程中
MybatisPageHelper.buildPageCount(conn, mappedStatement, boundSql, dialect.getCountString(originalSql));
//设置分页
metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit()) );   
//采用物理分页后，就不需要mybatis的内存分页了，所以重置下面的两个参数
metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);   
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
// 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数
if(target instanceof StatementHandler){
return Plugin.wrap(target, this);
}else{
return target;
}
}
@Override
public void setProperties(Properties properties) {}
}

　　怎么保存和获取记录总条数的，是MybatisPageHelper.java类操作的：

/**
* @description Mybatis分页帮助类(主要是设置和获取记录总数)
* @author aokunsang
* @date 2013-8-26
*/
public final class MybatisPageHelper {
private static final Log logger = LogFactory.getLog(MybatisPageHelper.class);  
/**
* 保存计算总行数的值
*/  
private static ThreadLocal<Integer> totalRowCountHolder = new ThreadLocal<Integer>();
/**
* 获取记录总数
* @param conn
* @param mappedStatement
* @param boundSql
* @param countSql   查询记录总数SQL语句
* @return
*/
static void buildPageCount(Connection conn,MappedStatement mappedStatement,BoundSql boundSql,String countSql){
int count = 0;
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(countSql);
BoundSql bound_count_sql = new BoundSql(mappedStatement.getConfiguration(),
countSql,
boundSql.getParameterMappings(),
boundSql.getParameterObject());
ParameterHandler pmHandler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), bound_count_sql);
pmHandler.setParameters(ps);
rs = ps.executeQuery();
if(rs.next()){
count = rs.getInt(Dialect.COUNT_FIELD);
}
if (logger.isDebugEnabled()) {  
logger.debug("Total count: {"+count+"}");
}  
totalRowCountHolder.set(count);
} catch (SQLException e) {
e.printStackTrace();
} finally{
try {
rs.close();
} catch (SQLException e) { e.printStackTrace();}
try {
ps.close();
} catch (SQLException e) { e.printStackTrace();}
}
}
/**
* 获取当前线程对应的分页查询的总行数
* @return
*/  
public static int getTotalRowCount() {  
return totalRowCountHolder.get();  
}
/**
* 组装RowBounds
* @param pageNumber     当前页码
* @param pageSize       查询记录数
* @return
*/
public static RowBounds toRowBounds(int pageNumber,int pageSize){
int offset = (pageNumber-1) * pageSize;  //根据页码数和展示记录数，计算出偏移量
return new RowBounds(offset, pageSize);
}
} 
　　接下来看配置文件，因为使用的是Spring整合的Mybatis，并且没有使用Mybatis-config.xml，spring的配置文件如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"  
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   
xmlns:aop="http://www.springframework.org/schema/aop"  
xmlns:tx="http://www.springframework.org/schema/tx"  
xmlns:context="http://www.springframework.org/schema/context"  
xsi:schemaLocation="   
http://www.springframework.org/schema/beans   
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd   
http://www.springframework.org/schema/tx   
http://www.springframework.org/schema/tx/spring-tx-3.1.xsd  
http://www.springframework.org/schema/context   
http://www.springframework.org/schema/context/spring-context-3.1.xsd   
http://www.springframework.org/schema/aop   
http://www.springframework.org/schema/aop/spring-aop-3.1.xsd" default-autowire="byName">
<context:property-placeholder location="classpath:config/database.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${datasource.driverClassName}"></property>
<property name="jdbcUrl" value="${datasource.url}"></property>
<property name="user" value="${datasource.username}"></property>
<property name="password" value="${datasource.password}"></property>
<property name="acquireIncrement" value="${c3p0.acquireIncrement}"></property>
<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
<property name="minPoolSize" value="${c3p0.minPoolSize}"></property>
<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
<property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property>
<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"></property>
<property name="maxStatements" value="${c3p0.maxStatements}"></property>
<property name="numHelperThreads" value="${c3p0.numHelperThreads}"></property>
<property name="acquireRetryAttempts" value="${c3p0.acquireRetryAttempts}"></property>
<property name="breakAfterAcquireFailure" value="${c3p0.breakAfterAcquireFailure}"></property>
<property name="testConnectionOnCheckout" value="${c3p0.testConnectionOnCheckout}"></property>
</bean>
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="sqlSessionFacroty" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="mapperLocations" value="classpath*:com/aokunsang/mybatis/study/mapper/*.xml"></property>
<property name="typeAliasesPackage" value="com.aokunsang.mybatis.study.po"></property>
<property name="plugins" ref="pageInterceptor"></property>
<property name="configurationProperties">
<props>
<prop key="dialect">oracle</prop>
<prop key="pageSqlId">.*Page$</prop>
</props>
</property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.aokunsang.mybatis.study.mapper"></property>
</bean>
<!-- MyBatis的物理分页拦截器 -->
<bean id="pageInterceptor" class="com.aokunsang.mybatis.study.page.PaginationInterceptor"/>
<!-- 注解式事务管理 -->
<tx:annotation-driven transaction-manager="txManager"/>
<!-- 自动扫描组件，需要把controller去掉，否则影响事务管理 -->
<context:component-scan base-package="com.aokunsang.mybatis.study">
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
</beans>

　　具体使用方式，先来看看Mapper接口和xml配置文件：

/**
* @description Mapper接口
* @author aokunsang
* @date 2013-8-9
*/
public interface ProjectGenreMapper {
public List<TbProjectGenre> findPrjGenrelkPage(@Param("namelk") String namelk, RowBounds rowBounds);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aokunsang.mybatis.study.mapper.ProjectGenreMapper">
<select id="findPrjGenrelkPage" resultType="TbProjectGenre">
select * from tb_project_genre
<where>
<if test="namelk!=null">
NAME like '%'||#{namelk}||'%'
</if>
</where>
order by ID desc
</select>
</mapper>
　　再看Service实现类：

/**
* @description
* @author aokunsang
* @date 2013-8-9
*/
@Service
@Transactional
public class ProjectGenreServiceImpl implements IProjectGenreService {
@Autowired
private ProjectGenreMapper pgMapper;
@Override
public PaginationSupport<TbProjectGenre> findPrjGenrelkPage(String namelk,
int pageNumber, int pageSize) {
List<TbProjectGenre> list = pgMapper.findPrjGenrelkPage(namelk, MybatisPageHelper.toRowBounds(pageNumber, pageSize));
PaginationSupport<TbProjectGenre> pager = new PaginationSupport<TbProjectGenre>(list,pageNumber,pageSize);
pager.setTotalCount(MybatisPageHelper.getTotalRowCount());  //设置记录总数[这里是重点哦]
return pager;
}
}
　　最后看看单元测试：

/**
* @description
* @author aokunsang
* @date 2013-8-9
*/
//@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext-jdbc.xml"})
public class JunitTest extends AbstractJUnit4SpringContextTests{
static{
//动态加载log4j日志
PropertyConfigurator.configure(JunitTest.class.getClassLoader().getResource("log4j.properties"));
}
@Autowired
private IProjectGenreService pgService;
@Test
public void list(){
PaginationSupport<TbProjectGenre> pager = pgService.findPrjGenrelkPage("水", 1, 2);
System.out.println("------------>>>"+pager.getTotalCount()+"-------->>>"+pager.getItems().size());
}
}
　　另外需要注意的是，想查看SQL语句，经过我测试，把log4j日志设置成如下即可：

# Output pattern : date [thread] priority category - message
log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
#正式环境时候 console级别设置为WARN
log4j.appender.Console.Threshold=DEBUG
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p [%c] - %m%n
# Spring Stuff
log4j.logger.org.springframework=WARN
# 这个是Mapper接口的包名(最重要)
log4j.logger.com.aokunsang.mybatis.study.mapper=DEBUG   
log4j.logger.com.mchange.v2=WARN
log4j.logger.org.apache.ibatis=WARN
log4j.logger.org.mybatis.spring=WARN
　　再修改下，因为在service实现类中，所有的分页最后都会执行一段代码：

pager.setTotalCount(MybatisPageHelper.getTotalRowCount());  //设置记录总数
　　这里修改为Spring AOP拦截相关方法，自动设置添加记录总数，看这里：

/**
* @description
* @author aokunsang
* @date 2013-8-26
*/
@Component
@Aspect
public class TotalRowValueMount {
@AfterReturning(value="execution(* com.aokunsang.mybatis.study.service.impl.*.*(..))",returning="pager")
@SuppressWarnings("unchecked")
public void setTotalCount(JoinPoint jp, PaginationSupport pager){
if(pager!=null){  //如果返回值为PaginationSupport，则执行
pager.setTotalCount(MybatisPageHelper.getTotalRowCount());
}
}
}
　　然后需要在spring的配置文件中加上对aspectj注解的支持就行了。

<!-- 启动对@Asceptj注解的支持 -->
<aop:aspectj-autoproxy />
　　此时，Service实现类的方法改为如下即可：

@Override
public PaginationSupport<TbProjectGenre> findPrjGenrelkPage(String namelk,
int pageNumber, int pageSize) {
List<TbProjectGenre> list = pgMapper.findPrjGenrelkPage(namelk, MybatisPageHelper.toRowBounds(pageNumber, pageSize));
return new PaginationSupport<TbProjectGenre>(list,pageNumber,pageSize);
}