mybatis框架的两种分页

mybatis有两种分页方法

1、内存分页，也就是假分页。本质是查出所有的数据然后根据游标的方式，截取需要的记录。如果数据量大，开销大和内存溢出。

使用方式：

利用自动生成的example类，加入mybatis的RowBounds类,在调用的接口中添加给类的参数

	@Override
	public List<UserVo> selectSelective(UserVo userVo) {
		
		List<UserVo> listVo = new ArrayList<UserVo>();
		UserPoExample example = new UserPoExample();
		/**
		 * 使用mybatis的RowBounds类，该类构造方法中要设置两个int类型参数
		 * 第一个是从该条记录开始，第二个是开始后查询的条数
		 */
		<strong>RowBounds rowBounds = new RowBounds(0, 5);</strong>
		Criteria criteria = example.createCriteria();
		criteria.andUsernameEqualTo("123");
		criteria.andRoleEqualTo(userVo.getRole());
		example.setOrderByClause("userId desc");//设置排序方式
		example.setStart(10);
		example.setLimit(10);
		UserPo userPo = new UserPo();
		userPo.setUsername("123");
		userPo.setRole(1);
		Page<UserVo> page = new Page<UserVo>();
		
		List<UserPo> listPo =null;
		try {
			//int countByExample = userPoMapper.countByExample(example);//按照条件查询总数
			//listPo = userPoMapper.selectBySelective(userPo,page);
			listPo = userPoMapper.selectByExample(example,<strong>rowBounds</strong>);
			
			for(UserPo po:listPo){
				UserVo vo = new UserVo();
				BeanUtils.copyProperties(po, vo);
				listVo.add(vo);
			}
		} catch (Exception e) {
			logger.error(e);
		}
		
		return listVo;
	}

第二中是，真正的物理分页

在自动生成的example对象中，加入两个成员变量start、和limit

public class UserPoExample {
	<strong>private int start;//设置分页开始
	
	private int limit;//设置分页的每页的数量</strong>
	
    public int getStart() {
		return start;
	}

	public void setStart(int start) {
		this.start = start;
	}

	public int getLimit() {
		return limit;
	}

	public void setLimit(int limit) {
		this.limit = limit;
	}

最后在对应的xml的方法中添加刚刚加入的条件，直接添加在自动生成的orderByClause后面

 <if test="orderByClause != null" >
      order by ${orderByClause}
    </if>
    
    <strong><if test="start !=0 or limit!=0">  
      limit #{start},#{limit}</if><span style="font-family: Arial, Helvetica, sans-serif;">  </span></strong>

通过日志可以看到是真正的分页查询

还有一种是使用分页拦截器实现的

首先在spring-dao的sqlsession工厂里面配置拦截器

<!-- sqlSessionFactory -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- 数据库连接池 -->
		<property name="dataSource" ref="dataSource" />
		
		<!-- 批量扫描别名 -->
		<property name="typeAliasesPackage" value="ssm.po" />  
		
		<!-- spring与mybatis整合不需要mybatis配置文件了,直接扫描mapper下的映射文件 -->
		<property name="mapperLocations" value="classpath:ssm/mapper/*.xml" />
		
		<!-- MybatisSpringPageInterceptor分页拦截器 -->
		<property name="plugins">  
        	<bean class="ssm.utils.MybatisSpringPageInterceptor"></bean>
    	</property>
    	 
	</bean>

拦截器代码：

package ssm.utils;

import java.lang.reflect.Field;
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 org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
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.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.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@Intercepts({ @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class }),
	@Signature(method = "query", type = Executor.class, args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })
public class MybatisSpringPageInterceptor implements Interceptor {
	private static final Logger log = LoggerFactory.getLogger(MybatisSpringPageInterceptor.class);

	public static final String MYSQL = "mysql";
	public static final String ORACLE = "oracle";

	protected String databaseType;// 数据库类型，不同的数据库有不同的分页方法

	@SuppressWarnings("rawtypes")
	protected ThreadLocal<Page> pageThreadLocal = new ThreadLocal<Page>();

	public String getDatabaseType() {
		return databaseType;
	}

	public void setDatabaseType(String databaseType) {
		if (!databaseType.equalsIgnoreCase(MYSQL) && !databaseType.equalsIgnoreCase(ORACLE)) {
			throw new PageNotSupportException("Page not support for the type of database, database type [" + databaseType + "]");
		}
		this.databaseType = databaseType;
	}

	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	@Override
	public void setProperties(Properties properties) {
		String databaseType = properties.getProperty("databaseType");
		if (databaseType != null) {
			setDatabaseType(databaseType);
		}
	}

	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Object intercept(Invocation invocation) throws Throwable {
		if (invocation.getTarget() instanceof StatementHandler) { // 控制SQL和查询总数的地方
			Page page = pageThreadLocal.get();
			if (page == null) { //不是分页查询
				return invocation.proceed();
			}

			RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
			StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate");
			BoundSql boundSql = delegate.getBoundSql();
			
			Connection connection = (Connection) invocation.getArgs()[0];
			prepareAndCheckDatabaseType(connection); // 准备数据库类型

			if (page.getTotalPage() > -1) {
				if (log.isTraceEnabled()) {
					log.trace("已经设置了总页数, 不需要再查询总数.");
				}
			} else {
				Object parameterObj = boundSql.getParameterObject();
				MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getFieldValue(delegate, "mappedStatement");
				queryTotalRecord(page, parameterObj, mappedStatement, connection);
			}

			String sql = boundSql.getSql();
			String pageSql = buildPageSql(page, sql);
			if (log.isDebugEnabled()) {
				log.debug("分页时, 生成分页pageSql: " + pageSql);
			}
			ReflectUtil.setFieldValue(boundSql, "sql", pageSql);

			return invocation.proceed();
		} else { // 查询结果的地方
			// 获取是否有分页Page对象
			Page<?> page = findPageObject(invocation.getArgs()[1]);
			if (page == null) {
				if (log.isTraceEnabled()) {
					log.trace("没有Page对象作为参数, 不是分页查询.");
				}
				return invocation.proceed();
			} else {
				if (log.isTraceEnabled()) {
					log.trace("检测到分页Page对象, 使用分页查询.");
				}
			}
			//设置真正的parameterObj
			invocation.getArgs()[1] = extractRealParameterObject(invocation.getArgs()[1]);

			pageThreadLocal.set(page);
			try {
				Object resultObj = invocation.proceed(); // Executor.query(..)
				if (resultObj instanceof List) {
					/* @SuppressWarnings({ "unchecked", "rawtypes" }) */
					page.setResults((List) resultObj);
				}
				return resultObj;
			} finally {
				pageThreadLocal.remove();
			}
		}
	}

	protected Page<?> findPageObject(Object parameterObj) {
		if (parameterObj instanceof Page<?>) {
			return (Page<?>) parameterObj;
		} else if (parameterObj instanceof Map) {
			for (Object val : ((Map<?, ?>) parameterObj).values()) {
				if (val instanceof Page<?>) {
					return (Page<?>) val;
				}
			}
		}
		return null;
	}

	/**
	 * <pre>
	 * 把真正的参数对象解析出来
	 * Spring会自动封装对个参数对象为Map<String, Object>对象
	 * 对于通过@Param指定key值参数我们不做处理，因为XML文件需要该KEY值
	 * 而对于没有@Param指定时，Spring会使用0,1作为主键
	 * 对于没有@Param指定名称的参数,一般XML文件会直接对真正的参数对象解析，
	 * 此时解析出真正的参数作为根对象
	 * </pre>
	 * @param parameterObj
	 * @return
	 */
	protected Object extractRealParameterObject(Object parameterObj) {
		if (parameterObj instanceof Map<?, ?>) {
			Map<?, ?> parameterMap = (Map<?, ?>) parameterObj;
			if (parameterMap.size() == 2) {
				boolean springMapWithNoParamName = true;
				for (Object key : parameterMap.keySet()) {
					if (!(key instanceof String)) {
						springMapWithNoParamName = false;
						break;
					}
					String keyStr = (String) key;
					if (!"0".equals(keyStr) && !"1".equals(keyStr)) {
						springMapWithNoParamName = false;
						break;
					}
				}
				if (springMapWithNoParamName) {
					for (Object value : parameterMap.values()) {
						if (!(value instanceof Page<?>)) {
							return value;
						}
					}
				}
			}
		}
		return parameterObj;
	}

	protected void prepareAndCheckDatabaseType(Connection connection) throws SQLException {
		if (databaseType == null) {
			String productName = connection.getMetaData().getDatabaseProductName();
			if (log.isTraceEnabled()) {
				log.trace("Database productName: " + productName);
			}
			productName = productName.toLowerCase();
			if (productName.indexOf(MYSQL) != -1) {
				databaseType = MYSQL;
			} else if (productName.indexOf(ORACLE) != -1) {
				databaseType = ORACLE;
			} else {
				throw new PageNotSupportException("Page not support for the type of database, database product name [" + productName + "]");
			}
			if (log.isInfoEnabled()) {
				log.info("自动检测到的数据库类型为: " + databaseType);
			}
		}
	}

	/**
	 * <pre>
	 * 生成分页SQL
	 * </pre>
	 * 
	 * @param page
	 * @param sql
	 * @return
	 */
	protected String buildPageSql(Page<?> page, String sql) {
		if (MYSQL.equalsIgnoreCase(databaseType)) {
			return buildMysqlPageSql(page, sql);
		} else if (ORACLE.equalsIgnoreCase(databaseType)) {
			return buildOraclePageSql(page, sql);
		}
		return sql;
	}

	/**
	 * <pre>
	 * 生成Mysql分页查询SQL
	 * </pre>
	 * 
	 * @param page
	 * @param sql
	 * @return
	 */
	protected String buildMysqlPageSql(Page<?> page, String sql) {
		// 计算第一条记录的位置，Mysql中记录的位置是从0开始的。
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		return new StringBuilder(sql).append(" limit ").append(offset).append(",").append(page.getPageSize()).toString();
	}

	/**
	 * <pre>
	 * 生成Oracle分页查询SQL
	 * </pre>
	 * 
	 * @param page
	 * @param sql
	 * @return
	 */
	protected String buildOraclePageSql(Page<?> page, String sql) {
		// 计算第一条记录的位置，Oracle分页是通过rownum进行的，而rownum是从1开始的
		int offset = (page.getPageNo() - 1) * page.getPageSize() + 1;
		StringBuilder sb = new StringBuilder(sql);
		sb.insert(0, "select u.*, rownum r from (").append(") u where rownum < ").append(offset + page.getPageSize());
		sb.insert(0, "select * from (").append(") where r >= ").append(offset);
		return sb.toString();
	}

	/**
	 * <pre>
	 * 查询总数
	 * </pre>
	 * 
	 * @param page
	 * @param parameterObject
	 * @param mappedStatement
	 * @param connection
	 * @throws SQLException
	 */
	protected void queryTotalRecord(Page<?> page, Object parameterObject, MappedStatement mappedStatement, Connection connection) throws SQLException {
		BoundSql boundSql = mappedStatement.getBoundSql(page);
		String sql = boundSql.getSql();
		String countSql = this.buildCountSql(sql);
		if (log.isDebugEnabled()) {
			log.debug("分页时, 生成countSql: " + countSql);
		}

		List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
		BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, parameterMappings, parameterObject);
		ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBoundSql);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = connection.prepareStatement(countSql);
			parameterHandler.setParameters(pstmt);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				long totalRecord = rs.getLong(1);
				page.setTotalRecord(totalRecord);
			}
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
					if (log.isWarnEnabled()) {
						log.warn("关闭ResultSet时异常.", e);
					}
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (Exception e) {
					if (log.isWarnEnabled()) {
						log.warn("关闭PreparedStatement时异常.", e);
					}
				}
		}
	}

	/**
	 * 根据原Sql语句获取对应的查询总记录数的Sql语句
	 * 
	 * @param sql
	 * @return
	 */
	protected String buildCountSql(String sql) {
		int index = sql.indexOf("from");
		return "select count(*) " + sql.substring(index);
	}

	/**
	 * 利用反射进行操作的一个工具类
	 * 
	 */
	private static class ReflectUtil {
		/**
		 * 利用反射获取指定对象的指定属性
		 * 
		 * @param obj 目标对象
		 * @param fieldName 目标属性
		 * @return 目标属性的值
		 */
		public static Object getFieldValue(Object obj, String fieldName) {
			Object result = null;
			Field field = ReflectUtil.getField(obj, fieldName);
			if (field != null) {
				field.setAccessible(true);
				try {
					result = field.get(obj);
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return result;
		}

		/**
		 * 利用反射获取指定对象里面的指定属性
		 * 
		 * @param obj 目标对象
		 * @param fieldName 目标属性
		 * @return 目标字段
		 */
		private static Field getField(Object obj, String fieldName) {
			Field field = null;
			for (Class<?> clazz = obj.getClass(); clazz != Object.class; clazz = clazz.getSuperclass()) {
				try {
					field = clazz.getDeclaredField(fieldName);
					break;
				} catch (NoSuchFieldException e) {
					// 这里不用做处理，子类没有该字段可能对应的父类有，都没有就返回null。
				}
			}
			return field;
		}

		/**
		 * 利用反射设置指定对象的指定属性为指定的值
		 * 
		 * @param obj 目标对象
		 * @param fieldName 目标属性
		 * @param fieldValue 目标值
		 */
		public static void setFieldValue(Object obj, String fieldName, String fieldValue) {
			Field field = ReflectUtil.getField(obj, fieldName);
			if (field != null) {
				try {
					field.setAccessible(true);
					field.set(obj, fieldValue);
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	public static class PageNotSupportException extends RuntimeException {

		/** serialVersionUID*/
		private static final long serialVersionUID = 1L;

		public PageNotSupportException() {
			super();
		}

		public PageNotSupportException(String message, Throwable cause) {
			super(message, cause);
		}

		public PageNotSupportException(String message) {
			super(message);
		}

		public PageNotSupportException(Throwable cause) {
			super(cause);
		}
	}

}

分页的Page对象：

package ssm.utils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Page<T> {

	public static final int DEFAULT_PAGE_SIZE = 10;

	protected int pageNo = 1; // 当前页, 默认为第1页
	protected int pageSize = DEFAULT_PAGE_SIZE; // 每页记录数
	protected long totalRecord = -1; // 总记录数, 默认为-1, 表示需要查询
	protected int totalPage = -1; // 总页数, 默认为-1, 表示需要计算

	protected List<T> results; // 当前页记录List形式
	
	public Map<String, Object> params = new HashMap<String, Object>();//设置页面传递的查询参数

	public Map<String, Object> getParams() {
		return params;
	}

	public void setParams(Map<String, Object> params) {
		this.params = params;
	}

	public int getPageNo() {
		return pageNo;
	}

	public void setPageNo(int pageNo) {
		this.pageNo = pageNo;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
		computeTotalPage();
	}

	public long getTotalRecord() {
		return totalRecord;
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalRecord(long totalRecord) {
		this.totalRecord = totalRecord;
		computeTotalPage();
	}

	protected void computeTotalPage() {
		if (getPageSize() > 0 && getTotalRecord() > -1) {
			this.totalPage = (int) (getTotalRecord() % getPageSize() == 0 ? getTotalRecord() / getPageSize() : getTotalRecord() / getPageSize() + 1);
		}
	}

	public List<T> getResults() {
		return results;
	}

	public void setResults(List<T> results) {
		this.results = results;
	}

	@Override
	public String toString() {
		StringBuilder builder = new StringBuilder().append("Page [pageNo=").append(pageNo).append(", pageSize=").append(pageSize)
				.append(", totalRecord=").append(totalRecord < 0 ? "null" : totalRecord).append(", totalPage=")
				.append(totalPage < 0 ? "null" : totalPage).append(", curPageObjects=").append(results == null ? "null" : results.size()).append("]");
		return builder.toString();
	}

}

ServiceImpl调用过程：最后查询出来的分页的信息

@Override
	public List<UserVo> selectSelective(UserVo userVo) {
		
		List<UserVo> listVo = new ArrayList<UserVo>();
//		UserPoExample example = new UserPoExample();
//		Criteria criteria = example.createCriteria();
//		criteria.andUsernameEqualTo(userVo.getUsername());
//		criteria.andRoleEqualTo(userVo.getRole());
//		example.setOrderByClause("userId desc");//设置排序方式
//		example.setStart(0);
//		example.setLimit(10);
		Page<UserVo> page = new Page<UserVo>();
		
		List<UserPo> listPo =null;
		try {
//			UserPo po1 = new UserPo();
//			po1.setUsername(userVo.getUsername());
//			po1.setRole(userVo.getRole());
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("username", userVo.getUsername());
			params.put("role", userVo.getRole());
			params.put("orderByClause","userId desc");
			page.setParams(params);
			
			listPo = userPoMapper.selectBySelective(page);
			
			for(UserPo po:listPo){
				UserVo vo = new UserVo();
				BeanUtils.copyProperties(po, vo);
				listVo.add(vo);
			}
			page.setResults(listVo);
		} catch (Exception e) {
			logger.error(e);
		}
		
		return listVo;
	}

对应的xml

<select id="selectBySelective" parameterType="ssm.utils.Page" resultType="ssm.po.UserPo">
    select * from tb_user
    <where>
        <if test="params.username != null and params.username !=""">
            userName = #{params.username}
            <!-- userName like "%${usesrname}%" -->
        </if>
        <if test="params.password != null and params.password !=""">
            and password = #{params.password}
        </if>
        <if test="params.sex != null and params.sex !=""">
            and sex = #{params.sex}
        </if>
        <if test="params.age != null and params.age !=""">
            and age = #{params.age}
        </if>
        <if test="params.email != null and params.email !=""">
            and email = #{params.email}
        </if>
        <if test="params.courseid != null and params.courseid !=""">
            and courseId = #{params.courseid}
        </if>
        <if test="params.role != null and params.role !=""">
            and role = #{params.role}
        </if>
        <if test="params.state != null and params.state !=""">
            and state = #{params.state}
        </if>
    </where>
        <if test="params.orderByClause != null and params.orderByClause !="""></if>
        	order by #{params.orderByClause}
      
  </select>