最終dao層結(jié)果：
|
1
2
3
|
public interface ModelMapper { Page<Model> pageByConditions(RowBounds rowBounds, Model record); } |
接下來一步一步來實(shí)現(xiàn)分頁。
一.創(chuàng)建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;// 頁碼，默認(rèn)是第一頁 private int pageSize = 15;// 每頁顯示的記錄數(shù)，默認(rèn)是15 private int totalRecord;// 總記錄數(shù) private int totalPage;// 總頁數(shù) 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; // 在設(shè)置總頁數(shù)的時(shí)候計(jì)算出對應(yīng)的總頁數(shù)，在下面的三目運(yùn)算中加法擁有更高的優(yōu)先級，所以最后可以不加括號。 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(); }} |
可以發(fā)現(xiàn)，這里繼承了一個(gè)PageList類；這個(gè)類也是自己創(chuàng)建的一個(gè)類，實(shí)現(xiàn)List接口。為什么要PageList這個(gè)類，是因?yàn)镻age需要實(shí)現(xiàn)List接口，而接口中的抽象方法，需要逐一實(shí)現(xiàn)，所以提供PageList在統(tǒng)一的地方寫實(shí)現(xiàn)List接口的方法。
為什么Page需要實(shí)現(xiàn)List接口，這個(gè)會在稍后的代碼中做解釋。
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> <!-- 數(shù)據(jù)表所有列名 --> <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的基本操作，就不做多余解釋。
三.創(chuàng)建攔截器：
我們需要做的是創(chuàng)建一個(gè)攔截器（PageInterceptor）、一個(gè)執(zhí)行者(PageExecutor)。
1.PageInteceptor:實(shí)現(xiàn)Inteceptor接口，將PageExecutor進(jìn)行執(zhí)行，攔截sql添加分頁sql（limit xx,xx）
2.PageExecutor:實(shí)現(xiàn)Executor接口，在查詢時(shí)，添加查詢總數(shù)并修改返回值類型。因?yàn)橐龅氖欠猪摚遣樵儾僮鳎岳镞叺姆遣樵兎椒ǘ际褂没镜膶?shí)現(xiàn)，只修改兩個(gè)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.*$"; // 需要進(jìn)行分頁操作的字符串正則表達(dá)式 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(); } // 分離代理對象鏈(由于目標(biāo)類可能被多個(gè)攔截器攔截，從而形成多次代理，通過下面的兩次循環(huán)可以分離出最原始的的目標(biāo)類) while (metaStatementHandler.hasGetter("h")) { Object object = metaStatementHandler.getValue("h"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); } // 分離最后一個(gè)代理對象的目標(biāo)類 while (metaStatementHandler.hasGetter("target")) { Object object = metaStatementHandler.getValue("target"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); } // 將mybatis的內(nèi)存分頁，調(diào)整為物理分頁 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的內(nèi)存分頁了，所以重置下面的兩個(gè)參數(shù) metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET); metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT); // 將執(zhí)行權(quán)交給下一個(gè)攔截器 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; } /** * 獲取總數(shù) * @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(); }} |
關(guān)于Page需要實(shí)現(xiàn)List接口的原因：可以看到，query方法返回值是List<E>,而我們現(xiàn)在要在dao中使用Page<E>對象來接收mybatis返回的結(jié)果，所以需要讓Page實(shí)現(xiàn)List接口。
分頁查詢執(zhí)行順序：進(jìn)入PageInterceptor的plugin方法，攔截到執(zhí)行者，進(jìn)入PageExecutor的query方法，執(zhí)行executor.query()時(shí)，又再次回到PageInterceptor的plugin方法，這次會執(zhí)行
進(jìn)入intercept方法，將執(zhí)行的sql拼接上分頁限制語句，然后查詢出數(shù)據(jù)結(jié)果集合。executor.query()執(zhí)行完成后，繼續(xù)執(zhí)行pageResolver，如果方法名稱和配置的需要執(zhí)行分頁操作的字符串匹配時(shí)，查詢數(shù)據(jù)總量，并返回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攔截器實(shí)現(xiàn)分頁的步驟與方法。具有很好的參考價(jià)值，下面跟著小編一起來看下吧
原文鏈接：http://www.cnblogs.com/taocong/p/6346663.html
