MyBatis的简单易用的分页研究

aokunsang

浏览: 816052 次
性别:
来自: 北京

最近访客更多访客>>

xuchengzhong83

Crow00

chenxinba

beyond0851

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

mybatis

网上很多Mybatis的分页文档，但是感觉千篇一律，并且不完整，只查询出来分页后的List列表，未封装查询的总条数（还需要单独查询记录总数）。
本文借鉴iteye网友对记录条数的封装思路，做了一个完整版的分页查询。

参考：http://stamen.iteye.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);
	}