最終dao層結果：
|
1
2
3
|
public interface ModelMapper { Page<Model> pageByConditions(RowBounds rowBounds, Model record); } |
接下來一步一步來實現分頁。
一.創建Page對象：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
public class Page<T> extends PageList<T> { private int pageNo = 1;// 頁碼，默認是第一頁 private int pageSize = 15;// 每頁顯示的記錄數，默認是15 private int totalRecord;// 總記錄數 private int totalPage;// 總頁數 public Page() { } public Page(int pageNo, int pageSize, int totalRecord, List<T> results) { this.pageNo = pageNo; this.pageSize = pageSize; this.totalRecord = totalRecord; this.setResult(results); int totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize : totalRecord / pageSize + 1; this.setTotalPage(totalPage); } 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; } public int getTotalRecord() { return totalRecord; } public void setTotalRecord(int totalRecord) { this.totalRecord = totalRecord; // 在設置總頁數的時候計算出對應的總頁數，在下面的三目運算中加法擁有更高的優先級，所以最后可以不加括號。 int totalPage = totalRecord % pageSize == 0 ? totalRecord / pageSize : totalRecord / pageSize + 1; this.setTotalPage(totalPage); } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } @Override public String toString() { StringBuilder builder = new StringBuilder(); builder.append("Page [pageNo=").append(pageNo).append(", pageSize=").append(pageSize).append(", results=") .append(getResult()).append(", totalPage=").append(totalPage).append(", totalRecord=").append(totalRecord) .append("]"); return builder.toString(); }} |
可以發現，這里繼承了一個PageList類；這個類也是自己創建的一個類，實現List接口。為什么要PageList這個類，是因為Page需要實現List接口，而接口中的抽象方法，需要逐一實現，所以提供PageList在統一的地方寫實現List接口的方法。
為什么Page需要實現List接口，這個會在稍后的代碼中做解釋。
PageList類：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
public class PageList<T> implements List<T> { private List<T> result; public List<T> getResult() { return result; } public void setResult(List<T> result) { this.result = result; } @Override public int size() { return result.size(); } @Override public boolean isEmpty() { return result.isEmpty(); } @Override public boolean contains(Object o) { return result.contains(o); } @Override public Iterator<T> iterator() { return result.iterator(); } @Override public Object[] toArray() { return result.toArray(); } @Override public <E> E[] toArray(E[] a) { return result.toArray(a); } @Override public boolean add(T e) { return result.add(e); } @Override public boolean remove(Object o) { return result.remove(o); } @Override public boolean containsAll(Collection<?> c) { return result.containsAll(c); } @Override public boolean addAll(Collection<? extends T> c) { return result.addAll(c); } @Override public boolean addAll(int index, Collection<? extends T> c) { return result.addAll(index, c); } @Override public boolean removeAll(Collection<?> c) { return result.removeAll(c); } @Override public boolean retainAll(Collection<?> c) { return result.retainAll(c); } @Override public void clear() { result.clear(); } @Override public T get(int index) { return result.get(index); } @Override public T set(int index, T element) { return result.set(index, element); } @Override public void add(int index, T element) { result.add(index, element); } @Override public T remove(int index) { return result.remove(index); } @Override public int indexOf(Object o) { return result.indexOf(o); } @Override public int lastIndexOf(Object o) { return result.lastIndexOf(o); } @Override public ListIterator<T> listIterator() { return result.listIterator(); } @Override public ListIterator<T> listIterator(int index) { return result.listIterator(index); } @Override public List<T> subList(int fromIndex, int toIndex) { return result.subList(fromIndex, toIndex); }} |
二.提供Dao以及mapper.xml
dao的寫法：
Page<Model> pageByConditions(RowBounds rowBounds, Model record);
mapper.xml:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
<!-- 表名 --> <sql id="tableName" > model </sql> <!-- 數據表所有列名 --> <sql id="Base_Column_List" > id, name </sql> <!-- 查詢字段 --> <sql id="Base_Search_Param" > <if test="id != null" > and id = #{id,jdbcType=INTEGER} </if> <if test="name != null" > and name = #{name,jdbcType=VARCHAR} </if> </sql> <!-- 分頁查詢語句 --> <select id="pageByConditions" resultMap="BaseResultMap"> SELECT <include refid="Base_Column_List" /> FROM <include refid="tableName" /> WHERE 1=1 <include refid="Base_Search_Param" /> </select> |
ok,以上都是mybatis的基本操作，就不做多余解釋。
三.創建攔截器：
我們需要做的是創建一個攔截器（PageInterceptor）、一個執行者(PageExecutor)。
1.PageInteceptor:實現Inteceptor接口，將PageExecutor進行執行，攔截sql添加分頁sql（limit xx,xx）
2.PageExecutor:實現Executor接口，在查詢時，添加查詢總數并修改返回值類型。因為要做的是分頁，是查詢操作，所以里邊的非查詢方法都使用基本的實現，只修改兩個query方法。
PageInteceptor完整代碼：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
import java.lang.reflect.InvocationTargetException;import java.sql.Connection;import java.util.Properties;import org.apache.ibatis.executor.Executor;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.factory.DefaultObjectFactory;import org.apache.ibatis.reflection.factory.ObjectFactory;import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.RowBounds;@Intercepts({ @Signature(method = "query", type = Executor.class, args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }), @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class }) })public class PageInterceptor implements Interceptor { private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory(); private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory(); private String pattern = "^.*page.*$"; // 需要進行分頁操作的字符串正則表達式 public String getPattern() { return pattern; } public void setPattern(String pattern) { this.pattern = pattern; } @Override public Object intercept(Invocation invocation) throws Throwable { if (invocation.getTarget() instanceof StatementHandler) { return handleStatementHandler(invocation); } return invocation.proceed(); } /** * @param invocation * @return * @throws IllegalAccessException * @throws InvocationTargetException */ private Object handleStatementHandler(Invocation invocation) throws InvocationTargetException, IllegalAccessException { StatementHandler statementHandler = (StatementHandler) invocation .getTarget(); MetaObject metaStatementHandler = MetaObject.forObject( statementHandler, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); RowBounds rowBounds = (RowBounds) metaStatementHandler .getValue("delegate.rowBounds"); if (rowBounds == null || (rowBounds.getOffset() == RowBounds.NO_ROW_OFFSET && rowBounds .getLimit() == RowBounds.NO_ROW_LIMIT)) { return invocation.proceed(); } // 分離代理對象鏈(由于目標類可能被多個攔截器攔截，從而形成多次代理，通過下面的兩次循環可以分離出最原始的的目標類) while (metaStatementHandler.hasGetter("h")) { Object object = metaStatementHandler.getValue("h"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); } // 分離最后一個代理對象的目標類 while (metaStatementHandler.hasGetter("target")) { Object object = metaStatementHandler.getValue("target"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); } // 將mybatis的內存分頁，調整為物理分頁 BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); String sql = boundSql.getSql(); // 重寫sql String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit(); metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); // 采用物理分頁后，就不需要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 o) { if (Executor.class.isAssignableFrom(o.getClass())) { PageExecutor executor = new PageExecutor((Executor)o, pattern); return Plugin.wrap(executor, this); } else if (o instanceof StatementHandler) { return Plugin.wrap(o, this); } return o; } @Override public void setProperties(Properties properties) { }} |
PageExecutor完整代碼：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
|
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.ibatis.cache.CacheKey;import org.apache.ibatis.executor.BatchResult;import org.apache.ibatis.executor.Executor;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.RowBounds;import org.apache.ibatis.transaction.Transaction;public class PageExecutor implements Executor { private Executor executor; private String pattern; public PageExecutor(Executor executor, String pattern) { this.executor = executor; this.pattern = pattern; } @Override public int update(MappedStatement ms, Object parameter) throws SQLException { return executor.update(ms, parameter); } @Override public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey cacheKey, BoundSql boundSql) throws SQLException { RowBounds rb = new RowBounds(rowBounds.getOffset(), rowBounds.getLimit()); List<E> rows = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql); return pageResolver(rows, ms, parameter, rb); } /** * 修改返回值類型 * @param rows * @param ms * @param parameter * @param rowBounds * @return */ private <E> List<E> pageResolver(List<E> rows, MappedStatement ms, Object parameter, RowBounds rowBounds) { String msid = ms.getId(); // 如果需要分頁查詢，修改返回類型為Page對象 if (msid.matches(pattern)) { int count = getCount(ms, parameter); int offset = rowBounds.getOffset(); int pagesize = rowBounds.getLimit(); return new Page<E>(offset/pagesize + 1, pagesize, count, rows); } return rows; } /** * 獲取總數 * @param ms * @param parameter * @return */ private int getCount(MappedStatement ms, Object parameter) { BoundSql bsql = ms.getBoundSql(parameter); String sql = bsql.getSql(); String countSql = getCountSql(sql); Connection connection = null; PreparedStatement stmt = null; ResultSet rs = null; try { connection = ms.getConfiguration().getEnvironment().getDataSource() .getConnection(); stmt = connection.prepareStatement(countSql); rs = stmt.executeQuery(); if (rs.next()) return rs.getInt(1); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (connection != null && !connection.isClosed()) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return 0; } private String getCountSql(String sql) { String countHql = " SELECT count(*) " + removeSelect(removeOrders(sql)); return countHql; } protected String removeOrders(String sql) { Pattern p = Pattern.compile("ORDER\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE); Matcher m = p.matcher(sql); StringBuffer sb = new StringBuffer(); while (m.find()) { m.appendReplacement(sb, ""); } m.appendTail(sb); return sb.toString(); } // 去除sql語句中select子句 private static String removeSelect(String hql) { int beginPos = hql.toLowerCase().indexOf("from"); if (beginPos < 0) { throw new IllegalArgumentException(" hql : " + hql + " must has a keyword 'from'"); } return hql.substring(beginPos); } @Override public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException { BoundSql boundSql = ms.getBoundSql(parameter); return query(ms, parameter, rowBounds, resultHandler, executor.createCacheKey(ms, parameter, rowBounds, boundSql), boundSql); } @Override public List<BatchResult> flushStatements() throws SQLException { return executor.flushStatements(); } @Override public void commit(boolean required) throws SQLException { executor.commit(required); } @Override public void rollback(boolean required) throws SQLException { executor.rollback(required); } @Override public CacheKey createCacheKey(MappedStatement ms, Object parameterObject, RowBounds rowBounds, BoundSql boundSql) { return executor .createCacheKey(ms, parameterObject, rowBounds, boundSql); } @Override public boolean isCached(MappedStatement ms, CacheKey key) { return executor.isCached(ms, key); } @Override public void clearLocalCache() { executor.clearLocalCache(); } @Override public void deferLoad(MappedStatement ms, MetaObject resultObject, String property, CacheKey key, Class<?> targetType) { executor.deferLoad(ms, resultObject, property, key, targetType); } @Override public Transaction getTransaction() { return executor.getTransaction(); } @Override public void close(boolean forceRollback) { executor.close(forceRollback); } @Override public boolean isClosed() { return executor.isClosed(); }} |
關于Page需要實現List接口的原因：可以看到，query方法返回值是List<E>,而我們現在要在dao中使用Page<E>對象來接收mybatis返回的結果，所以需要讓Page實現List接口。
分頁查詢執行順序：進入PageInterceptor的plugin方法，攔截到執行者，進入PageExecutor的query方法，執行executor.query()時，又再次回到PageInterceptor的plugin方法，這次會執行
進入intercept方法，將執行的sql拼接上分頁限制語句，然后查詢出數據結果集合。executor.query()執行完成后，繼續執行pageResolver，如果方法名稱和配置的需要執行分頁操作的字符串匹配時，查詢數據總量，并返回Page對象；如果不匹配，直接返回List對象。
四.xml配置：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:/conf/mybatis/mybaties-config.xml"></property> <property name="mapperLocations"> <list> <value>classpath:/conf/mybatis/**/*-mapper.xml</value> </list> </property> <property name="plugins"> <list> <ref bean="pageInterceptor"/> </list> </property> </bean> <bean id="pageInterceptor" class="cn.com.common.PageInterceptor"> <property name="pattern" value="^.*page.*$"></property> </bean> |
五.測試代碼：
|
1
2
3
4
5
6
7
8
|
@Test public void testPage() { int pageNo = 1; int pageSize = 10; RowBounds bounds = new RowBounds((pageNo - 1) * pageSize, pageSize); Model record = new Model(); Page<Model> list = modelMapper.pageByConditions(bounds, record); } |
本文主要介紹了Mybatis攔截器實現分頁的步驟與方法。具有很好的參考價值，下面跟著小編一起來看下吧
原文鏈接：http://www.cnblogs.com/taocong/p/6346663.html
