MyBatis3.2 使用Interceptor进行分页

苏氏IT馆 2016-04-17

展开全文

本文主要介绍如何使用Mybaits的拦截器对Sql进行改造，以便支持分页。

关于mybatis的拦截器使用以及原理可以参考我的另外一篇文章（http://www.cnblogs.com/daxin/p/3544188.html）。

首先说一下实现原理，就是当Mybatis查询数据的时候，利用Mybatis提供的Interceptor对查询方法进行拦截，动态修改SQL，把不带分页的SQL改造成带分页的SQL

比如在MySql数据库下面正常查询的SQL 为 selelct id, name from user where name = ? 那么改造后的SQL 为 selelct id, name from user where name = ? limit 5 , 10

这样就支持分页了。

1、首先扩展mybatis提供的RowBounds

什么都没变，就是加了一个属性total 用来存放查询到的总记录数。另外加了setMeToDefault这个方法，目的是将这个类设置为初始状态，这样Mybatis底层就不会在去分页了。

 1 package com.framework.core.mybatis;
 2 
 3 import org.apache.ibatis.session.RowBounds;
 4 
 5 public class PagingBounds extends RowBounds {
 6     
 7     //总记录数
 8     private int total;
 9     //查询的起始位置
10     private int offset;
11     //查询多少行记录
12     private int limit;
13 
14     public PagingBounds() {
15         this.offset = NO_ROW_OFFSET;
16         this.limit = NO_ROW_LIMIT;
17     }
18 
19     public PagingBounds(int offset, int limit) {
20         this.offset = offset;
21         this.limit = limit;
22     }
23 
24     public int getTotal() {
25         return total;
26     }
27 
28     public void setTotal(int total) {
29         this.total = total;
30     }
31 
32     public int getOffset() {
33         return offset;
34     }
35 
36     public int getLimit() {
37         return limit;
38     }
39 
40     public void setMeToDefault() {
41         this.offset = NO_ROW_OFFSET;
42         this.limit = NO_ROW_LIMIT;
43     }
44     
45     public int getSelectCount() {
46         return limit + offset;
47     }
48 }

2、定义一个抽象类实现Mybatis的拦截器接口，将分页的核心逻辑抽离出来，这样可以针对不同的数据库进行扩展，这个类也是分页逻辑的核心，细节相对复杂。

可以看到有俩个抽象方法需要子类扩展

1：protected abstract String getSelectTotalSql(String targetSql);

这个方法是把目标SQL（这里的SQL就是你XML当中定义的，比如 select id, name from user where name = ?）传递给子类，子类可以针对不同数据库进行包装，并返回查询总数的SQL。

2：protected abstract String getSelectPagingSql(String targetSql, PagingBounds pagingBounds);

这个方法是把目标SQL改造成带分页的SQL，PagingBounds这个参数有分页的信息。

  1 package com.framework.core.mybatis;
  2 
  3 import java.lang.reflect.Field;
  4 import java.lang.reflect.Modifier;
  5 import java.sql.Connection;
  6 import java.sql.PreparedStatement;
  7 import java.sql.ResultSet;
  8 import java.sql.SQLException;
  9 import java.util.List;
 10 import java.util.Properties;
 11 import java.util.regex.Matcher;
 12 import java.util.regex.Pattern;
 13 
 14 import org.apache.ibatis.executor.parameter.ParameterHandler;
 15 import org.apache.ibatis.executor.statement.RoutingStatementHandler;
 16 import org.apache.ibatis.executor.statement.StatementHandler;
 17 import org.apache.ibatis.mapping.BoundSql;
 18 import org.apache.ibatis.mapping.MappedStatement;
 19 import org.apache.ibatis.mapping.ParameterMapping;
 20 import org.apache.ibatis.plugin.Interceptor;
 21 import org.apache.ibatis.plugin.Invocation;
 22 import org.apache.ibatis.plugin.Plugin;
 23 import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
 24 import org.apache.ibatis.session.RowBounds;
 25 
 26 public abstract class AbstractPagingInterceptor implements Interceptor {
 27 
 28     private static final Pattern PATTERN_SQL_BLANK = Pattern.compile("\\s+");
 29 
 30     private static final String FIELD_DELEGATE = "delegate";
 31     
 32     private static final String FIELD_ROWBOUNDS = "rowBounds";
 33     
 34     private static final String FIELD_MAPPEDSTATEMENT = "mappedStatement";
 35     
 36     private static final String FIELD_SQL = "sql";
 37     
 38     private static final String BLANK = " ";
 39 
 40     public static final String SELECT = "select";
 41     
 42     public static final String FROM = "from";
 43     
 44     public static final String ORDER_BY = "order by";
 45     
 46     public static final String UNION = "union";
 47     
 48     @Override
 49     public Object intercept(Invocation invocation) throws Throwable {
 50         Connection connection = (Connection) invocation.getArgs()[0];
 51         RoutingStatementHandler statementHandler = (RoutingStatementHandler) invocation.getTarget();
 52 
 53         StatementHandler handler = (StatementHandler) readField(statementHandler, FIELD_DELEGATE);
 54         PagingBounds pagingBounds = (PagingBounds) readField(handler, FIELD_ROWBOUNDS);
 55         MappedStatement mappedStatement = (MappedStatement) readField(handler, FIELD_MAPPEDSTATEMENT);
 56         BoundSql boundSql = handler.getBoundSql();
 57 
 58         //replace all blank
 59         String targetSql = replaceSqlBlank(boundSql.getSql());
 60         
 61         //paging
 62         getTotalAndSetInPagingBounds(targetSql, boundSql, pagingBounds, mappedStatement, connection);
 63         String pagingSql = getSelectPagingSql(targetSql, pagingBounds);
 64         writeDeclaredField(boundSql, FIELD_SQL, pagingSql);
 65         
 66         //ensure set to default
 67         pagingBounds.setMeToDefault(); 
 68         return invocation.proceed();
 69     }
 70 
 71     private void getTotalAndSetInPagingBounds(String targetSql, BoundSql boundSql, PagingBounds bounds, 
 72                             MappedStatement mappedStatement, Connection connection) throws SQLException {
 73         String totalSql = getSelectTotalSql(targetSql);
 74         List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
 75         Object parameterObject = boundSql.getParameterObject();
 76         BoundSql totalBoundSql = new BoundSql(mappedStatement.getConfiguration(), totalSql, parameterMappings, parameterObject);
 77         ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, totalBoundSql);
 78         
 79         PreparedStatement pstmt = null;
 80         ResultSet rs = null;
 81         try {
 82             pstmt = connection.prepareStatement(totalSql);
 83             parameterHandler.setParameters(pstmt);
 84             rs = pstmt.executeQuery();
 85             if(rs.next()) {
 86                 int totalRecord = rs.getInt(1);
 87                 bounds.setTotal(totalRecord);
 88             }
 89         } finally {
 90             if(rs != null) {
 91                 rs.close();
 92             }
 93             if(pstmt != null) {
 94                 pstmt.close();
 95             }
 96         }
 97     }
 98 
 99     protected abstract String getSelectTotalSql(String targetSql);
100     
101     protected abstract String getSelectPagingSql(String targetSql, PagingBounds pagingBounds);
102 
103     private String replaceSqlBlank(String originalSql) {
104         Matcher matcher = PATTERN_SQL_BLANK.matcher(originalSql);
105         return matcher.replaceAll(BLANK);
106     }
107 
108     @Override
109     public Object plugin(Object target) {
110         if (target instanceof RoutingStatementHandler) {
111             try {
112                 Field delegate = getField(RoutingStatementHandler.class, FIELD_DELEGATE);
113                 StatementHandler handler = (StatementHandler) delegate.get(target);
114                 RowBounds rowBounds = (RowBounds) readField(handler, FIELD_ROWBOUNDS);
115                 if (rowBounds != RowBounds.DEFAULT && rowBounds instanceof PagingBounds) {
116                     return Plugin.wrap(target, this);
117                 }
118             } catch (IllegalAccessException e) {
119                 // ignore
120             }
121         }
122         return target;
123     }
124 
125     private void writeDeclaredField(Object target, String fieldName, Object value) 
126             throws IllegalAccessException {
127         if (target == null) {
128             throw new IllegalArgumentException("target object must not be null");
129         }
130         Class<?> cls = target.getClass();
131         Field field = getField(cls, fieldName);
132         if (field == null) {
133             throw new IllegalArgumentException("Cannot locate declared field " + cls.getName() + "." + fieldName);
134         }
135         field.set(target, value);
136     }
137 
138     private Object readField(Object target, String fieldName)
139             throws IllegalAccessException {
140         if (target == null) {
141             throw new IllegalArgumentException("target object must not be null");
142         }
143         Class<?> cls = target.getClass();
144         Field field = getField(cls, fieldName);
145         if (field == null) {
146             throw new IllegalArgumentException("Cannot locate field " + fieldName + " on " + cls);
147         }
148         if (!field.isAccessible()) {
149             field.setAccessible(true);
150         }
151         return field.get(target);
152     }
153 
154     private static Field getField(final Class<?> cls, String fieldName) {
155         for (Class<?> acls = cls; acls != null; acls = acls.getSuperclass()) {
156             try {
157                 Field field = acls.getDeclaredField(fieldName);
158                 if (!Modifier.isPublic(field.getModifiers())) {
159                     field.setAccessible(true);
160                     return field;
161                 }
162             } catch (NoSuchFieldException ex) {
163                 // ignore
164             }
165         }
166         return null;
167     }
168     
169     @Override
170     public void setProperties(Properties properties) {
171 
172     }
173 }

3、针对不同数据库进行扩展，这里给出俩个例子。

MSSQL2008

package com.framework.core.mybatis;

import java.sql.Connection;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Signature;

@Intercepts(@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})) 
public class SqlServerPagingInterceptor extends AbstractPagingInterceptor {

    /**
     * 改造sql变成查询总数的sql
     * @param targetSql 正常查询数据的sql: select id, name from user where name = ?
     * @return select count(1) from user where name = ?
     */
    @Override
    protected String getSelectTotalSql(String targetSql) {
        String sql = targetSql.toLowerCase();
        StringBuilder sqlBuilder = new StringBuilder(sql);
        
        int orderByPos = 0;
        if((orderByPos = sqlBuilder.lastIndexOf(ORDER_BY)) != -1) {
            sqlBuilder.delete(orderByPos, sqlBuilder.length());
        }
        
        if(sqlBuilder.indexOf(UNION) != -1) {
            sqlBuilder.insert(0, "select count(1) as _count from ( ").append(" ) as _alias");
            return sqlBuilder.toString();
        }
        
        int fromPos = sqlBuilder.indexOf(FROM);
        if(fromPos != -1) {
            sqlBuilder.delete(0, fromPos);
            sqlBuilder.insert(0, "select count(1) as _count ");
        }
        
        return sqlBuilder.toString();
    }

    /**
     * 改造sql变成支持分页的sql
     * @param targetSql 正常查询数据的sql: select id, name from user where name = ?
     * @return WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) 
     * as __mybatis_row_nr__ FROM ( select id, name from user where name = ? ) inner_query ) 
     * SELECT * FROM query WHERE __mybatis_row_nr__ >= 3 AND __mybatis_row_nr__ <= 6 
     */
    @Override
    protected String getSelectPagingSql(String targetSql, PagingBounds bounds) {
        String sql = targetSql.toLowerCase();
        StringBuilder sqlBuilder = new StringBuilder(sql);
        
        if(sqlBuilder.indexOf(ORDER_BY) != -1) {
            int selectPos = sqlBuilder.indexOf(SELECT);
            sqlBuilder.insert(selectPos + SELECT.length(), " TOP(" + bounds.getSelectCount() + ")");
        }
        
        sqlBuilder.insert(0, "SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __mybatis_row_nr__ FROM ( ");
        sqlBuilder.append(" ) inner_query ");
        
        sqlBuilder.insert(0, "WITH query AS (").append(") SELECT ").append("*").append(" FROM query ");
        sqlBuilder.append("WHERE __mybatis_row_nr__ >= " + (bounds.getOffset() + 1) + " AND __mybatis_row_nr__ <= " + bounds.getSelectCount());
        
        return sqlBuilder.toString();
    }

}

Oracle10G的

package com.framework.core.mybatis;

import java.sql.Connection;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Signature;

@Intercepts(@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})) 
public class OraclePagingInterceptor extends AbstractPagingInterceptor {

    @Override
    protected String getSelectTotalSql(String targetSql) {
        String sql = targetSql.toLowerCase();
        StringBuilder sqlBuilder = new StringBuilder(sql);
        
        int orderByPos = 0;
        if((orderByPos = sqlBuilder.lastIndexOf(ORDER_BY)) != -1) {
            sqlBuilder.delete(orderByPos, sqlBuilder.length());
        }
        sqlBuilder.insert(0, "select count(1) as _count from ( ").append(" )");
        
        return sqlBuilder.toString();
    }

    @Override
    protected String getSelectPagingSql(String targetSql, PagingBounds bounds) {
        String sql = targetSql.toLowerCase();
        StringBuilder sqlBuilder = new StringBuilder(sql);
        
        sqlBuilder.insert(0, "select * from ( select table_alias.*, rownum mybatis_rowNo from (");
        sqlBuilder.append(") ");
        sqlBuilder.append("table_alias where rownum <= " + bounds.getSelectCount()).append(")");
        sqlBuilder.append("where mybatis_rowNo >= " + (bounds.getOffset() + 1));
        
        return sqlBuilder.toString();
    }

}

4、就是将拦截器注册到mybatis的配置文件当中去。你使用什么数据库就注册那个类就行了。

<configuration>
    <settings>
        <setting name="logImpl" value="LOG4J" />
        <!-- <setting name="cacheEnabled" value="false"/> -->
    </settings>

    <plugins>
        <plugin interceptor="com.framework.core.mybatis.SqlServerPagingInterceptor" />
    </plugins>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="net.sourceforge.jtds.jdbc.Driver" />
                <property name="url"
                    value="jdbc:jtds:sqlserver://127.0.0.1/FDK2" />
                <property name="username" value="sa" />
                <property name="password" value="sql2008" />
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/framework/code/mapper/ConnectMapper.xml" />
    </mappers>
</configuration>

5、接下来写个DEMO测试一下吧。

View Code

 1 public class Demo {
 2 
 3     
 4     public static void main(String[] args) throws NoSuchMethodException, SecurityException, IOException {
 5         String resource = "mybatis-config.xml";
 6         InputStream inputStream = Resources.getResourceAsStream(resource);
 7         DefaultSqlSessionFactory sqlSessionFactory = (DefaultSqlSessionFactory) new SqlSessionFactoryBuilder().build(inputStream);
 8         DefaultSqlSession session = (DefaultSqlSession) sqlSessionFactory.openSession();
 9         ConnectMapper mapper = (ConnectMapper)session.getMapper(ConnectMapper.class);
10         
11         //创建一个PagingBounds 设置起始位置2 查询4调记录
12         PagingBounds bounds = new PagingBounds(2, 4);
13         HashMap<String, Object> params = new HashMap<String, Object>();
14         
15         //返回结果
16         List<Connect> list = mapper.selectPaging(bounds, params);
17         System.out.println(list);
18         //总记录数
19         System.out.println(bounds.getTotal());
20         
21         session.commit();
22         session.close();
23     }
24     
25 }

看一下日志输出已经分页了。

DEBUG 2014-07-23 11:25:47,216 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select count(1) as _count from connect 
DEBUG 2014-07-23 11:25:47,259 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 
DEBUG 2014-07-23 11:25:47,290 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __mybatis_row_nr__ FROM ( select id, pid, number, numbertype, numbertypename, previousreceiveuser, previousreceiveusername, receiveuser, receiveusername, happentime, whethersuccess, successtime, whetherhhold from connect ) inner_query ) SELECT * FROM query WHERE __mybatis_row_nr__ >= 3 AND __mybatis_row_nr__ <= 6 
DEBUG 2014-07-23 11:25:47,290 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 
[com.framework.code.domain.Connect@c26dd5, com.framework.code.domain.Connect@b8cab9]
4