MyBatis分页插件：使用很灵活，有全部插件源码和配置，同时有测试代码和日志（目前支持Oracle和MySQL）
来源：互联网 发布：植物转录因子数据库 编辑：程序博客网 时间：2024/06/05 02:01
1、概述：
经过努力写了个分页插件，目前支持MySQL和Oracle。它的使用应该是目前为止最简单的，对于PageHelper据原作者描述使用了ThreadLocal等技术，避免多线程的不一致，PageHelper实现较为复杂，其次也有其定义的startPage这样的开关代码,造成代码侵入，而没有屏蔽PageHelper导致代码污染。
我的插件十分简单易用，代码污染性极小，也无需考虑多线程的问题，反正都是一个事务中处理的，其次使用方法支持四种，十分灵活。
2、插件代码，配置和简易的POJO
我们先看看插件的代码：
package com.learn.mybatis.plugin;import java.beans.PropertyDescriptor;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Iterator;import java.util.Map;import java.util.Properties;import java.util.Set;import javax.transaction.NotSupportedException;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.SystemMetaObject;import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;import org.apache.ibatis.session.Configuration;/** * * @author ykzhen2015 */@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})public class PagingPlugin implements Interceptor { /** * 插件默认参数，可配置 */ private Integer defaultPage; //默认页码 private Integer defaultPageSize;//默认每页条数 private Boolean defaultUseFlag; //默认是否启用插件 private Boolean defaultCheckFlag; //默认是否检测页码参数 private Boolean defaultCleanOrderBy; //默认是否清除最后一个order by 后的语句 private static final String DB_TYPE_MYSQL = "mysql"; private static final String DB_TYPE_ORACLE = "oracle"; /** * 插件实现方法 */ @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler stmtHandler = (StatementHandler) getUnProxyObject(invocation.getTarget()); MetaObject metaStatementHandler = SystemMetaObject.forObject(stmtHandler); String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql"); MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); String dbType = this.getDataSourceType(mappedStatement); //不是select语句. if (!this.checkSelect(sql)) { return invocation.proceed(); } BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); Object parameterObject = boundSql.getParameterObject(); PageParams pageParams = getPageParamsForParamObj(parameterObject); if (pageParams == null) { //无法获取分页参数，不进行分页。 return invocation.proceed(); } //获取配置中是否启用分页功能. Boolean useFlag = pageParams.getUseFlag() == null? this.defaultUseFlag : pageParams.getUseFlag(); if (!useFlag) { //不使用分页插件. return invocation.proceed(); } //获取相关配置的参数. Integer pageNum = pageParams.getPage() == null? defaultPage : pageParams.getPage(); Integer pageSize = pageParams.getPageSize() == null? defaultPageSize : pageParams.getPageSize(); Boolean checkFlag = pageParams.getCheckFlag() == null? defaultCheckFlag : pageParams.getCheckFlag(); Boolean cleanOrderBy = pageParams.getCleanOrderBy() == null? defaultCleanOrderBy : pageParams.getCleanOrderBy(); //计算总条数 int total = this.getTotal(invocation, metaStatementHandler, boundSql, cleanOrderBy, dbType); //回填总条数到分页参数 pageParams.setTotal(total); //计算总页数. int totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize + 1; //回填总页数到分页参数. pageParams.setTotalPage(totalPage); //检查当前页码的有效性. this.checkPage(checkFlag, pageNum, totalPage); //修改sql return this.preparedSQL(invocation, metaStatementHandler, boundSql, pageNum, pageSize, dbType); } /*** * 分离出分页参数. * @param parameterObject --执行参数 * @return 分页参数 * @throws Exception */ public PageParams getPageParamsForParamObj(Object parameterObject) throws Exception { PageParams pageParams = null; if (parameterObject == null) { return null; } //处理map参数和@Param注解参数，都是MAP if (parameterObject instanceof Map) { @SuppressWarnings("unchecked") Map<String, Object> paramMap = (Map<String, Object>) parameterObject; Set<String> keySet = paramMap.keySet(); Iterator<String> iterator = keySet.iterator(); while(iterator.hasNext()) { String key = iterator.next(); Object value = paramMap.get(key); if (value instanceof PageParams) { return (PageParams)value; } } } else if (parameterObject instanceof PageParams) { //参数POJO继承了PageParams return (PageParams) parameterObject; } else { //从POJO尝试读取分页参数. Field[] fields = parameterObject.getClass().getDeclaredFields(); //尝试从POJO中获得类型为PageParams的属性 for (Field field : fields) { if (field.getType() == PageParams.class) { PropertyDescriptor pd = new PropertyDescriptor (field.getName(), parameterObject.getClass()); Method method = pd.getReadMethod(); return (PageParams) method.invoke(parameterObject); } } } return pageParams; } /** * 判断是否sql语句. * @param sql * @return */ private boolean checkSelect(String sql) { String trimSql = sql.trim(); int idx = trimSql.toLowerCase().indexOf("select"); return idx == 0; } /** * 检查当前页码的有效性. * @param checkFlag * @param pageNum * @param pageTotal * @throws Throwable */ private void checkPage(Boolean checkFlag, Integer pageNum, Integer pageTotal) throws Throwable { if (checkFlag) { //检查页码page是否合法. if (pageNum > pageTotal) { throw new Exception("查询失败，查询页码【" + pageNum + "】大于总页数【" + pageTotal + "】！！"); } } } /** * 预编译改写后的SQL，并设置分页参数 * @param invocation * @param metaStatementHandler * @param boundSql * @param pageNum * @param pageSize * @param dbType * @throws IllegalAccessException * @throws InvocationTargetException */ private Object preparedSQL(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql, int pageNum, int pageSize, String dbType) throws Exception { //获取当前需要执行的SQL String sql = boundSql.getSql(); String newSql = this.getPageDataSQL(sql, dbType); //修改当前需要执行的SQL metaStatementHandler.setValue("delegate.boundSql.sql", newSql); //执行编译，这里相当于StatementHandler执行了prepared()方法，这个时候，就剩下2个分页参数没有设置。 Object statementObj = invocation.proceed(); //设置两个分页参数。 this.preparePageDataParams((PreparedStatement)statementObj, pageNum, pageSize, dbType); return statementObj; } /** * 获取总条数. * * @param ivt Invocation * @param metaStatementHandler statementHandler * @param boundSql sql * @param cleanOrderBy 是否清除order by语句 * * @param dbType * @return sql查询总数. * @throws Throwable 异常. */ private int getTotal(Invocation ivt, MetaObject metaStatementHandler, BoundSql boundSql, Boolean cleanOrderBy, String dbType) throws Throwable { //获取当前的mappedStatement MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); //配置对象 Configuration cfg = mappedStatement.getConfiguration(); //当前需要执行的SQL String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql"); //去掉最后的order by语句 if (cleanOrderBy) { sql = this.cleanOrderByForSql(sql); } String countSql = this.getTotalSQL(sql, dbType); //获取拦截方法参数，根据插件签名，知道是Connection对象. Connection connection = (Connection) ivt.getArgs()[0]; PreparedStatement ps = null; int total = 0; try { //预编译统计总数SQL ps = connection.prepareStatement(countSql); //构建统计总数SQL BoundSql countBoundSql = new BoundSql(cfg, countSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); //构建MyBatis的ParameterHandler用来设置总数Sql的参数。 ParameterHandler handler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), countBoundSql); //设置总数SQL参数 handler.setParameters(ps); //执行查询. ResultSet rs = ps.executeQuery(); while (rs.next()) { total = rs.getInt("total"); } } finally { //这里不能关闭Connection否则后续的SQL就没法继续了。 if (ps != null) { ps.close(); } } return total; } private String cleanOrderByForSql(String sql) { StringBuilder sb = new StringBuilder(sql); String newSql = sql.toLowerCase(); //如果没有order语句,直接返回 if (newSql.indexOf("order") == -1) { return sql; } int idx = newSql.lastIndexOf("order"); return sb.substring(0, idx).toString(); } /** * 从代理对象中分离出真实对象. * * @param ivt --Invocation * @return 非代理StatementHandler对象 */ private Object getUnProxyObject(Object target) { MetaObject metaStatementHandler = SystemMetaObject.forObject(target); // 分离代理对象链(由于目标类可能被多个拦截器拦截，从而形成多次代理，通过循环可以分离出最原始的的目标类) Object object = null; while (metaStatementHandler.hasGetter("h")) { object = metaStatementHandler.getValue("h"); } if (object == null) { return target; } return object; } /** * 生成代理对象 * @param statementHandler 原始对象 * @return 代理对象 */ @Override public Object plugin(Object statementHandler) { return Plugin.wrap(statementHandler, this); } /** * 设置插件配置参数。 * @param props */ @Override public void setProperties(Properties props) { String strDefaultPage = props.getProperty("default.page", "1"); String strDefaultPageSize = props.getProperty("default.pageSize", "50"); String strDefaultUseFlag = props.getProperty("default.useFlag", "false"); String strDefaultCheckFlag = props.getProperty("default.checkFlag", "false"); String StringDefaultCleanOrderBy = props.getProperty("default.cleanOrderBy", "false"); this.defaultPage = Integer.parseInt(strDefaultPage); this.defaultPageSize = Integer.parseInt(strDefaultPageSize); this.defaultUseFlag = Boolean.parseBoolean(strDefaultUseFlag); this.defaultCheckFlag = Boolean.parseBoolean(strDefaultCheckFlag); this.defaultCleanOrderBy = Boolean.parseBoolean(StringDefaultCleanOrderBy); } /** * TODO * 计算总数的SQL, * 这里需要根据数据库的类型改写SQL，目前支持MySQL和Oracle * @param currSql —— 当前执行的SQL * @return 改写后的SQL * @throws NotSupportedException */ private String getTotalSQL(String currSql, String dbType) throws NotSupportedException { if (DB_TYPE_MYSQL.equals(dbType)) { return "select count(*) as total from (" + currSql + ") $_paging"; } else if (DB_TYPE_ORACLE.equals(dbType)) { return "select count(*) as total from (" + currSql +")"; } else { throw new NotSupportedException("当前插件未支持此类型数据库"); } } /** * TODO 需要使用其他数据库需要改写 * 分页获取参数的SQL * 这里需要根据数据库的类型改写SQL，目前支持MySQL和Oracle * @param currSql —— 当前执行的SQL * @return 改写后的SQL * @throws NotSupportedException */ private String getPageDataSQL(String currSql, String dbType) throws NotSupportedException { if (DB_TYPE_MYSQL.equals(dbType)) { return "select * from (" + currSql + ") $_paging_table limit ?, ?"; } else if (DB_TYPE_ORACLE.equals(dbType)) { return " select * from (select cur_sql_result.*, rownum rn from (" + currSql + ") cur_sql_result where rownum <= ?) where rn > ?"; } else { throw new NotSupportedException("当前插件未支持此类型数据库"); } } /** * TODO 需要使用其他数据库需要改写 * 使用PreparedStatement预编译两个分页参数，如果数据库的规则不一样，需要改写设置的参数规则。目前支持MySQL和Oracle * @throws SQLException * @throws NotSupportedException * */ private void preparePageDataParams(PreparedStatement ps, int pageNum, int pageSize, String dbType) throws Exception { //prepared()方法编译SQL，由于MyBatis上下文没有我们分页参数的信息，所以这里需要设置这两个参数. //获取需要设置的参数个数，由于我们的参数是最后的两个，所以很容易得到其位置 int idx = ps.getParameterMetaData().getParameterCount(); if (DB_TYPE_MYSQL.equals(dbType)) { //最后两个是我们的分页参数. ps.setInt(idx -1, (pageNum - 1) * pageSize);//开始行 ps.setInt(idx, pageSize); //限制条数 } else if (DB_TYPE_ORACLE.equals(dbType)) { ps.setInt(idx -1, pageNum * pageSize);//结束行 ps.setInt(idx, (pageNum - 1) * pageSize); //开始行 } else { throw new NotSupportedException("当前插件未支持此类型数据库"); } } /** * * TODO 需要使用其他数据库需要改写 * 目前支持MySQL和Oracle * @param mappedStatement * @return * @throws Exception */ private String getDataSourceType(MappedStatement mappedStatement) throws Exception { Connection conn = null; String dbConnectionStr = null; try { conn = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection(); dbConnectionStr = conn.toString(); } finally { if (conn != null) { conn.close(); } } if (null == dbConnectionStr || dbConnectionStr.trim().equals("")) { throw new NotSupportedException("当前插件未能获得数据库连接信息。"); } dbConnectionStr = dbConnectionStr.toLowerCase(); if (dbConnectionStr.contains(DB_TYPE_MYSQL)) { return DB_TYPE_MYSQL; } else if (dbConnectionStr.contains(DB_TYPE_ORACLE)) { return DB_TYPE_ORACLE; } else { throw new NotSupportedException("当前插件未支持此类型数据库"); } }}如果你是使用非Oracle或者MySQL的，你需要修改注解有TODO的方法，修改是也很简单。
有了这个插件，还要配置一下，MyBatis的配置文件如下：
<configuration>......<plugins><plugin interceptor="com.learn.mybatis.plugin.PagingPlugin"> <!-- 默认页码 --><property name="default.page" value="1" /><!-- 默认每页条数--><property name="default.pageSize" value="20" /><!-- 是否启动分页插件功能 --><property name="default.useFlag" value="true" /><!-- 是否检查页码有效性，如果非有效，则抛出异常. --><property name="default.checkFlag" value="false" /><!-- 针对哪些含有order by的SQL，是否去掉最后一个order by 以后的SQL语句，提高性能 --><property name="default.cleanOrderBy" value="false" /></plugin></plugins>......</configuration>
上面我们可以看到一些默认值，可以根据我自己定义的一个POJO修改上面配置的默认值，这个POJO十分简单，它符合JavaBean的一切规范，如下：（其属性含义见其注解）
package com.learn.mybatis.plugin;import java.util.List;/** * * @author ykzhen2015 */public class PageParams {private Integer page;//第几页private Integer pageSize;//每页限制条数private Boolean useFlag;//是否启动插件，如果不启动，则不作分页private Boolean checkFlag;//是否检测页码的有效性，如果为true，而页码大于最大页数，则抛出异常private Boolean cleanOrderBy;//是否清除最后order by 后面的语句private Integer total; //总条数，插件会回填这个值private Integer totalPage; //总页数，插件会回填这个值.public Integer getPage() {return page;}public void setPage(Integer page) {this.page = page;}public Integer getPageSize() {return pageSize;}public void setPageSize(Integer pageSize) {this.pageSize = pageSize;}public Boolean getUseFlag() {return useFlag;}public void setUseFlag(Boolean useFlag) {this.useFlag = useFlag;}public Boolean getCheckFlag() {return checkFlag;}public void setCheckFlag(Boolean checkFlag) {this.checkFlag = checkFlag;}public Boolean getCleanOrderBy() {return cleanOrderBy;}public void setCleanOrderBy(Boolean cleanOrderBy) {this.cleanOrderBy = cleanOrderBy;}public Integer getTotal() {return total;}public void setTotal(Integer total) {this.total = total;}public Integer getTotalPage() {return totalPage;}public void setTotalPage(Integer totalPage) {this.totalPage = totalPage;}}当以下属性被设置会有这样的效果（如果都不设置则使用xml配置的默认值）：
- page --插件默认设置default.page作废，采取这个值，整数型
- pageSize --插件默认设置default.pageSize作废，采取这个值，整数型
- useFlag --插件默认设置default.useFlag作废，采取这个值，布尔值
- checkFlag --插件默认设置default.checkFlag作废，采取这个值，布尔值
- cleanOrderBy -- 插件默认设置default.cleanOrderBy作废，采用这个值，布尔值
而：
total ——代表总条数，插件回自动帮我们回填，整数型
3、插件的使用：
这个插件使用起来十分简单，只要传递给MyBatis的SqlMap以下的方式都是允许的：
- 继承com.learn.mybatis.plugin.PageParams
- 使用map，但是map中的键值对应，有一个值是com.learn.mybatis.plugin.PageParams类型
- 使用@Param注解，只要里面有一个参数是com.learn.mybatis.plugin.PageParams类型
- 使用POJO，但是这个POJO中有一个属性为com.learn.mybatis.plugin.PageParams类型
以上4种形式都可以使用这个分页插件，是不是很灵活易用？？
让我们试用一下这个插件，看看这四种传递参数的方法。
现在我们定义一个map.xml如下：
<?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.learn.mybatis.chapter1.mapper.RoleMapper"> <select id="getRole" parameterType="long" resultType="com.learn.mybatis.chapter1.po.TRole"> select id, role_name as roleName, note from t_role where id=#{id} </select> <select id="selectAllRole" parameterType="com.learn.mybatis.plugin.TestParams" resultType="com.learn.mybatis.chapter1.po.TRole"> select id, role_name as roleName, note from t_role <where> <if test="roleName != null"> role_name like concat('%', #{roleName}, '%') </if> </where> order by id desc </select> <select id="selectAllRole2" parameterType="com.learn.mybatis.plugin.TestParams2" resultType="com.learn.mybatis.chapter1.po.TRole"> select id, role_name as roleName, note from t_role <where> <if test="roleName != null"> role_name like concat('%', #{roleName}, '%') </if> </where> order by id desc </select> <select id="selectRoleByMap" parameterType="map" resultType="com.learn.mybatis.chapter1.po.TRole"> select id, role_name as roleName, note from t_role <where> <if test="roleName != null"> role_name like concat('%', #{roleName}, '%') </if> </where> order by id desc </select> <select id="selectRoleByMap2" resultType="com.learn.mybatis.chapter1.po.TRole"> select id, role_name as roleName, note from t_role </select> <select id="selectRoleByKey" resultType="com.learn.mybatis.chapter1.po.TRole"> select id, role_name as roleName, note from t_role <where> <if test="roleName != null"> role_name like concat('%', #{roleName}, '%') </if> </where> order by id desc </select> <select id="test$sql" resultType="com.learn.mybatis.chapter1.po.TRole"> select id, role_name as roleName, note from ${tableName} <where> <if test="roleName != null"> role_name like concat('%', #{roleName}, '%') </if> </where> order by role_name desc </select></mapper>定义其接口：
package com.learn.mybatis.chapter1.mapper;import java.util.List;import java.util.Map;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.session.RowBounds;import com.learn.mybatis.chapter1.po.TRole;import com.learn.mybatis.plugin.PageParams;import com.learn.mybatis.plugin.TestParams;import com.learn.mybatis.plugin.TestParams2;public interface RoleMapper {//不含分页参数，就不会启用public TRole getRole(Long id);//继承方式public List<TRole> selectAllRole(TestParams pageParams);//参数为POJO，但是POJO内部包含一个类型为com.learn.mybatis.plugin.PageParams的属性public List<TRole> selectAllRole2(TestParams2 testParams2);//使用map,里面有一个com.learn.mybatis.plugin.PageParams类型的值public List<TRole> selectRoleByMap(Map<String, Object> params);//使用注解的形式传递分页参数。public List<TRole> selectRoleByMap2(@Param("roleName") String roleName, @Param("page221232323") PageParams pageParams);}好，看到注解，里面已经包含了上述4种传参的方法，这个插件都是支持的。先看到selectAllRole()方法
里面的参数TestParams定义（继承PageParams）：
package com.learn.mybatis.plugin;public class TestParams extends PageParams {String roleName;public String getRoleName() {return roleName;}public void setRoleName(String roleName) {this.roleName = roleName;}}再看到selectAllRole2()方法，里面的参数TestParams2定义（其属性有一个PageParams）:package com.learn.mybatis.plugin;public class TestParams2 {private PageParams pageParams = null;private String roleName;public PageParams getPageParams() {return pageParams;}public void setPageParams(PageParams pageParams) {this.pageParams = pageParams;}public String getRoleName() {return roleName;}public void setRoleName(String roleName) {this.roleName = roleName;}}好其他的都很简单了。
我们来测试这个接口，测试代码如下，注意看四种方式传递参数，代码的污染是很小的：
package com.learn.mybatis.chapter1.main;import java.util.HashMap;import java.util.Map;import org.apache.ibatis.session.SqlSession;import org.apache.log4j.Logger;import com.learn.mybatis.chapter1.mapper.RoleMapper;import com.learn.mybatis.chapter1.mb.util.MyBatisUtil;import com.learn.mybatis.plugin.PageParams;import com.learn.mybatis.plugin.TestParams;import com.learn.mybatis.plugin.TestParams2;public class MyBatisExample {private static Logger logger = Logger.getLogger(MyBatisExample.class);public static void main(String[] args) {testGetRole();testInherit();testMap();testAtParam();testPojoProperty();}/** * 继承PageParams的参数传递. */public static void testGetRole() {logger.info("#################测试没有分页参数的查询###########################");SqlSession sqlSession = null;try {sqlSession = MyBatisUtil.getSqlSessionFactroy().openSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);roleMapper.getRole(1L);} finally {sqlSession.close();}}/** * 继承PageParams的参数传递. */public static void testInherit() {logger.info("#################测试分页参数以继承PageParams形式传递###########################");SqlSession sqlSession = null;try {sqlSession = MyBatisUtil.getSqlSessionFactroy().openSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);TestParams params = new TestParams();params.setUseFlag(true);params.setCheckFlag(false);params.setPage(2);params.setPageSize(5);params.setRoleName("test");roleMapper.selectAllRole(params);logger.info("总条数==>" + params.getTotal());logger.info("总页数 ==>" + params.getTotalPage());} finally {sqlSession.close();}}/** * 使用map方式。（map的键随意取） */public static void testMap() {logger.info("#################测试分页参数以Map形式传递###########################");SqlSession sqlSession = null;try {sqlSession = MyBatisUtil.getSqlSessionFactroy().openSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);Map<String, Object> paramMap = new HashMap<String, Object>();PageParams pageParams = new PageParams();pageParams.setUseFlag(true);pageParams.setCheckFlag(false);pageParams.setPage(2);pageParams.setPageSize(5);paramMap.put("roleName", "test");paramMap.put("page_drsdsd2233", pageParams);roleMapper.selectRoleByMap(paramMap);logger.info("总条数==>" + pageParams.getTotal());logger.info("总页数 ==>" + pageParams.getTotalPage());} finally {sqlSession.close();}}/** * 使用MyBatis @Param注解 传递参数. */public static void testAtParam() {logger.info("#################测试分页参数以@Param形式传递###########################");SqlSession sqlSession = null;try {sqlSession = MyBatisUtil.getSqlSessionFactroy().openSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);PageParams pageParams2 = new PageParams();pageParams2.setUseFlag(true);pageParams2.setCheckFlag(true);pageParams2.setPage(2);pageParams2.setPageSize(5);pageParams2.setCleanOrderBy(true);roleMapper.selectRoleByMap2("test", pageParams2);logger.info("总条数==>" + pageParams2.getTotal());logger.info("总页数 ==>" + pageParams2.getTotalPage());} finally {sqlSession.close();}}/** * 使用POJO传递，但是POJO内部有一个PageParams属性，和它的setter方法. */public static void testPojoProperty() {logger.info("#################测试分页参数作为参数属性传递###########################");SqlSession sqlSession = null;try {sqlSession = MyBatisUtil.getSqlSessionFactroy().openSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);PageParams pageParams3 = new PageParams();pageParams3.setUseFlag(true);pageParams3.setCheckFlag(false);pageParams3.setPage(3);pageParams3.setPageSize(5);pageParams3.setCleanOrderBy(true);TestParams2 p2 = new TestParams2();p2.setPageParams(pageParams3);p2.setRoleName("test");roleMapper.selectAllRole2(p2);logger.info("总条数==>" + pageParams3.getTotal());logger.info("总页数 ==>" + pageParams3.getTotalPage());} finally {sqlSession.close();}}}测试的结果如下（我这里使用mysql也可以使用oracle，我也有测试过的）：
INFO 2016-06-23 21:02:37,966 com.learn.mybatis.chapter1.main.MyBatisExample: #################测试没有分页参数的查询###########################DEBUG 2016-06-23 21:02:38,169 org.apache.ibatis.logging.LogFactory: Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.DEBUG 2016-06-23 21:02:38,186 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:02:38,186 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:02:38,186 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:02:38,186 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:02:38,267 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-06-23 21:02:38,470 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 103887628.DEBUG 2016-06-23 21:02:38,470 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,485 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 1752203484.DEBUG 2016-06-23 21:02:38,486 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select id, role_name as roleName, note from t_role where id=? DEBUG 2016-06-23 21:02:38,508 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Long)DEBUG 2016-06-23 21:02:38,532 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 0DEBUG 2016-06-23 21:02:38,533 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,534 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,535 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 103887628 to pool. INFO 2016-06-23 21:02:38,539 com.learn.mybatis.chapter1.main.MyBatisExample: #################测试分页参数以继承PageParams形式传递###########################DEBUG 2016-06-23 21:02:38,565 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-06-23 21:02:38,565 org.apache.ibatis.datasource.pooled.PooledDataSource: Checked out connection 103887628 from pool.DEBUG 2016-06-23 21:02:38,565 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,575 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 1682463303.DEBUG 2016-06-23 21:02:38,576 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select count(*) as total from (select id, role_name as roleName, note from t_role WHERE role_name like concat('%', ?, '%') order by id desc) $_paging DEBUG 2016-06-23 21:02:38,576 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: test(String)DEBUG 2016-06-23 21:02:38,577 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1DEBUG 2016-06-23 21:02:38,577 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from (select id, role_name as roleName, note from t_role WHERE role_name like concat('%', ?, '%') order by id desc) $_paging_table limit ?, ? DEBUG 2016-06-23 21:02:38,579 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 5(Integer), 5(Integer), test(String)DEBUG 2016-06-23 21:02:38,583 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 5 INFO 2016-06-23 21:02:38,584 com.learn.mybatis.chapter1.main.MyBatisExample: 总条数==>10 INFO 2016-06-23 21:02:38,584 com.learn.mybatis.chapter1.main.MyBatisExample: 总页数 ==>2DEBUG 2016-06-23 21:02:38,584 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,585 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,585 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 103887628 to pool. INFO 2016-06-23 21:02:38,585 com.learn.mybatis.chapter1.main.MyBatisExample: #################测试分页参数以Map形式传递###########################DEBUG 2016-06-23 21:02:38,588 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-06-23 21:02:38,588 org.apache.ibatis.datasource.pooled.PooledDataSource: Checked out connection 103887628 from pool.DEBUG 2016-06-23 21:02:38,588 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,601 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 156545103.DEBUG 2016-06-23 21:02:38,601 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select count(*) as total from (select id, role_name as roleName, note from t_role WHERE role_name like concat('%', ?, '%') order by id desc) $_paging DEBUG 2016-06-23 21:02:38,602 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: test(String)DEBUG 2016-06-23 21:02:38,604 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1DEBUG 2016-06-23 21:02:38,604 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from (select id, role_name as roleName, note from t_role WHERE role_name like concat('%', ?, '%') order by id desc) $_paging_table limit ?, ? DEBUG 2016-06-23 21:02:38,605 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 5(Integer), 5(Integer), test(String)DEBUG 2016-06-23 21:02:38,608 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 5 INFO 2016-06-23 21:02:38,609 com.learn.mybatis.chapter1.main.MyBatisExample: 总条数==>10 INFO 2016-06-23 21:02:38,609 com.learn.mybatis.chapter1.main.MyBatisExample: 总页数 ==>2DEBUG 2016-06-23 21:02:38,609 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,610 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,610 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 103887628 to pool. INFO 2016-06-23 21:02:38,610 com.learn.mybatis.chapter1.main.MyBatisExample: #################测试分页参数以@Param形式传递###########################DEBUG 2016-06-23 21:02:38,612 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-06-23 21:02:38,613 org.apache.ibatis.datasource.pooled.PooledDataSource: Checked out connection 103887628 from pool.DEBUG 2016-06-23 21:02:38,613 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,627 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 668210649.DEBUG 2016-06-23 21:02:38,627 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select count(*) as total from (select id, role_name as roleName, note from t_role) $_paging DEBUG 2016-06-23 21:02:38,628 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: DEBUG 2016-06-23 21:02:38,630 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1DEBUG 2016-06-23 21:02:38,630 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from (select id, role_name as roleName, note from t_role) $_paging_table limit ?, ? DEBUG 2016-06-23 21:02:38,631 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 5(Integer), 5(Integer)DEBUG 2016-06-23 21:02:38,633 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 5 INFO 2016-06-23 21:02:38,634 com.learn.mybatis.chapter1.main.MyBatisExample: 总条数==>10 INFO 2016-06-23 21:02:38,634 com.learn.mybatis.chapter1.main.MyBatisExample: 总页数 ==>2DEBUG 2016-06-23 21:02:38,634 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,635 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,635 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 103887628 to pool. INFO 2016-06-23 21:02:38,635 com.learn.mybatis.chapter1.main.MyBatisExample: #################测试分页参数作为参数属性传递###########################DEBUG 2016-06-23 21:02:38,636 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-06-23 21:02:38,636 org.apache.ibatis.datasource.pooled.PooledDataSource: Checked out connection 103887628 from pool.DEBUG 2016-06-23 21:02:38,636 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,643 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 1361289747.DEBUG 2016-06-23 21:02:38,644 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select count(*) as total from (select id, role_name as roleName, note from t_role WHERE role_name like concat('%', ?, '%') ) $_paging DEBUG 2016-06-23 21:02:38,644 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: test(String)DEBUG 2016-06-23 21:02:38,645 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1DEBUG 2016-06-23 21:02:38,645 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from (select id, role_name as roleName, note from t_role WHERE role_name like concat('%', ?, '%') order by id desc) $_paging_table limit ?, ? DEBUG 2016-06-23 21:02:38,646 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 10(Integer), 5(Integer), test(String)DEBUG 2016-06-23 21:02:38,648 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 0 INFO 2016-06-23 21:02:38,648 com.learn.mybatis.chapter1.main.MyBatisExample: 总条数==>10 INFO 2016-06-23 21:02:38,648 com.learn.mybatis.chapter1.main.MyBatisExample: 总页数 ==>2DEBUG 2016-06-23 21:02:38,648 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,649 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]DEBUG 2016-06-23 21:02:38,649 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 103887628 to pool.此时为了测试验证页码的和去掉order by语句的效果，我们再把testPojoProperty()方法修改一下：
/** * 使用POJO传递，但是POJO内部有一个PageParams属性，和它的setter方法. */public static void testPojoProperty() {logger.info("#################测试分页参数作为参数属性传递###########################");SqlSession sqlSession = null;try {sqlSession = MyBatisUtil.getSqlSessionFactroy().openSession();RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);PageParams pageParams3 = new PageParams();pageParams3.setUseFlag(true);pageParams3.setCheckFlag(true);//检验页码的有效性。pageParams3.setPage(6);//超过最大页数pageParams3.setPageSize(5);pageParams3.setCleanOrderBy(false);//不去掉order by 语句TestParams2 p2 = new TestParams2();p2.setPageParams(pageParams3);p2.setRoleName("test");roleMapper.selectAllRole2(p2);logger.info("总条数==>" + pageParams3.getTotal());logger.info("总页数 ==>" + pageParams3.getTotalPage());} finally {sqlSession.close();}}再运行一下，得到日志：
INFO 2016-06-23 21:09:09,643 com.learn.mybatis.chapter1.main.MyBatisExample: #################测试分页参数作为参数属性传递###########################DEBUG 2016-06-23 21:09:09,839 org.apache.ibatis.logging.LogFactory: Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.DEBUG 2016-06-23 21:09:09,856 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:09:09,856 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:09:09,856 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:09:09,857 org.apache.ibatis.datasource.pooled.PooledDataSource: PooledDataSource forcefully closed/removed all connections.DEBUG 2016-06-23 21:09:09,957 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC ConnectionDEBUG 2016-06-23 21:09:10,166 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 142555199.DEBUG 2016-06-23 21:09:10,166 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@87f383f]DEBUG 2016-06-23 21:09:10,181 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 192794887.DEBUG 2016-06-23 21:09:10,182 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select count(*) as total from (select id, role_name as roleName, note from t_role WHERE role_name like concat('%', ?, '%') order by id desc) $_paging DEBUG 2016-06-23 21:09:10,218 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: test(String)DEBUG 2016-06-23 21:09:10,243 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1DEBUG 2016-06-23 21:09:10,245 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@87f383f]DEBUG 2016-06-23 21:09:10,245 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@87f383f]DEBUG 2016-06-23 21:09:10,246 org.apache.ibatis.datasource.pooled.PooledDataSource: Returned connection 142555199 to pool.Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: java.lang.reflect.UndeclaredThrowableException### The error may exist in com\learn\mybatis\chapter1\sqlmap\role.xml### The error may involve com.learn.mybatis.chapter1.mapper.RoleMapper.selectAllRole2-Inline### The error occurred while setting parameters### Cause: java.lang.reflect.UndeclaredThrowableException at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102) at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) at com.sun.proxy.$Proxy4.selectAllRole2(Unknown Source) at com.learn.mybatis.chapter1.main.MyBatisExample.testPojoProperty(MyBatisExample.java:131) at com.learn.mybatis.chapter1.main.MyBatisExample.main(MyBatisExample.java:23)Caused by: java.lang.reflect.UndeclaredThrowableException at com.sun.proxy.$Proxy5.prepare(Unknown Source) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:73) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:59) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108) ... 7 moreCaused by: java.lang.Exception: 查询失败，查询页码【6】大于总总页【2】！！ at com.learn.mybatis.plugin.PagingPlugin.checkPage(PagingPlugin.java:168) at com.learn.mybatis.plugin.PagingPlugin.intercept(PagingPlugin.java:99) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:60) ... 15 more说明页码超过了最大的页数，注意这里的因为有了这句：pageParams3.setCleanOrderBy(false);所以从日志可以看出，和上次不太一样，插件没有帮我们去掉了影响性能的order by语句。这些都是可以通过参数由你来控制的。当然使用默认的参数也是可以的。
3 0
- MyBatis分页插件：使用很灵活，有全部插件源码和配置，同时有测试代码和日志（目前支持Oracle和MySQL）
- MyBatis分页插件--有测试代码哦，可以抄袭哦
- 关于mybatis中对mysql和Oracle数据库分页插件的使用
- 如何在eclipse中使用pydev插件导入和测试已有代码
- spring boot 配置MyBatis，支持多个数据源和分页插件
- sublime text 3 + LiveReload插件实现前端代码实时预览（同时支持Chrome和Firefox）
- mybatis-PageHelper分页插件的原理和使用
- MySQL 高可用性—keepalived+mysql双主（有详细步骤和全部配置项解释）
- mybatis拦截器介绍和分页插件
- spring boot和mybatis集成分页插件
- spring mvc 小记(五)：MyBatis的代码生成器插件和分页插件
- Mybatis插件原理和PageHelper结合实战分页插件
- mybatis 分页插件和插件的安装方法
- oracle的数据文件全部丢失，但有归档和重做日志，如何恢复？
- maven+mybatis+mybatis-generator+sql server 2005自动生成代码，加上自定义分页插件和批量插入更新插件
- Mybatis代码自动生成工具和插件
- AngularJS自定义指令详解（有分页插件代码）
- mybatis中的oracle和mysql分页
- MVC与MVP的区别
- 不可重复读和幻读的区别
- Android design support libarary
- javaScript设计模式之职责链模式
- python科学计算_numpy_常规函数与排序
- MyBatis分页插件：使用很灵活，有全部插件源码和配置，同时有测试代码和日志（目前支持Oracle和MySQL）
- 讯飞语音开发之语音理解
- 一个手机海报合成程序
- fiddler https抓包失败原因 解决
- DAY1 Jun23
- Activity 切换动画
- Qt之QImageWriter
- HDU 5091 扫描线,线段树
- ubuntu启动无桌面图标？
