MyBatis使用RowBounds實(shí)現(xiàn)的分頁(yè)是邏輯分頁(yè),也就是先把數(shù)據(jù)記錄全部查詢出來(lái),然在再根據(jù)offset和limit截?cái)嘤涗浄祷?/p>
為了在數(shù)據(jù)庫(kù)層面上實(shí)現(xiàn)物理分頁(yè),又不改變?cè)瓉?lái)MyBatis的函數(shù)邏輯,可以編寫plugin截獲MyBatis Executor的statementhandler,重寫SQL來(lái)執(zhí)行查詢
下面的插件代碼只針對(duì)MySQL
plugin代碼
|
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
|
package plugin;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import org.apache.ibatis.executor.parameter.ParameterHandler;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.logging.Log;import org.apache.ibatis.logging.LogFactory;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.scripting.defaults.DefaultParameterHandler;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.session.RowBounds;/** * 通過攔截<code>StatementHandler</code>的<code>prepare</code>方法，重寫sql語(yǔ)句實(shí)現(xiàn)物理分頁(yè)。 * 老規(guī)矩，簽名里要攔截的類型只能是接口。 * */@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})public class PaginationInterceptor implements Interceptor { private static final Log logger = LogFactory.getLog(PaginationInterceptor.class); private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory(); private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory(); private static String DEFAULT_PAGE_SQL_ID = ".*Page$"; // 需要攔截的ID(正則匹配) @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue("delegate.rowBounds"); // 分離代理對(duì)象鏈(由于目標(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è)代理對(duì)象的目標(biāo)類 while (metaStatementHandler.hasGetter("target")) { Object object = metaStatementHandler.getValue("target"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY); } // property在mybatis settings文件內(nèi)配置 Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration"); // 設(shè)置pageSqlId String pageSqlId = configuration.getVariables().getProperty("pageSqlId"); if (null == pageSqlId || "".equals(pageSqlId)) { logger.warn("Property pageSqlId is not setted,use default '.*Page$' "); pageSqlId = DEFAULT_PAGE_SQL_ID; } MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); // 只重寫需要分頁(yè)的sql語(yǔ)句。通過MappedStatement的ID匹配，默認(rèn)重寫以Page結(jié)尾的MappedStatement的sql if (mappedStatement.getId().matches(pageSqlId)) { BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); Object parameterObject = boundSql.getParameterObject(); if (parameterObject == null) { throw new NullPointerException("parameterObject is null!"); } else { String sql = boundSql.getSql(); // 重寫sql String pageSql = sql + " LIMIT " + rowBounds.getOffset() + "," + rowBounds.getLimit(); metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); // 采用物理分頁(yè)后，就不需要mybatis的內(nèi)存分頁(yè)了，所以重置下面的兩個(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 target) { // 當(dāng)目標(biāo)類是StatementHandler類型時(shí)，才包裝目標(biāo)類，否者直接返回目標(biāo)本身,減少目標(biāo)被代理的次數(shù) if (target instanceof StatementHandler) { return Plugin.wrap(target, this); } else { return target; } } @Override public void setProperties(Properties properties) { //To change body of implemented methods use File | Settings | File Templates. }} |
配置plugin
|
1
2
3
|
<plugins> <plugin interceptor="plugin.PaginationInterceptor" /></plugins> |
查詢SQL
|
1
2
3
4
5
6
|
<!-- 測(cè)試分頁(yè)查詢 --><select id="selectUserByPage" resultMap="dao.base.userResultMap"> <![CDATA[ SELECT * FROM user ]]></select> |
調(diào)用示例
|
1
2
3
4
5
|
@Overridepublic List<User> selectUserByPage(int offset, int limit) { RowBounds rowBounds = new RowBounds(offset, limit); return getSqlSession().selectList("dao.userdao.selectUserByPage", new Object(), rowBounds);} |
另外,結(jié)合Spring MVC,編寫翻頁(yè)和生成頁(yè)碼代碼
頁(yè)碼類
|
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
|
package util;/** * Created with IntelliJ IDEA. * User: zhenwei.liu * Date: 13-8-7 * Time: 上午10:29 * To change this template use File | Settings | File Templates. */public class Pagination { private String url; // 頁(yè)碼url private int pageSize = 10; // 每頁(yè)顯示記錄數(shù) private int currentPage = 1; // 當(dāng)前頁(yè)碼 private int maxPage = Integer.MAX_VALUE; // 最大頁(yè)數(shù) // 獲取offset public int getOffset() { return (currentPage - 1) * pageSize; } // 獲取limit public int getLimit() { return getPageSize(); } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { if (currentPage < 1) currentPage = 1; if (currentPage > maxPage) currentPage = maxPage; this.currentPage = currentPage; } public int getMaxPage() { return maxPage; } public void setMaxPage(int maxPage) { this.maxPage = maxPage; }} |
為了計(jì)算最大頁(yè)碼,需要知道數(shù)據(jù)表的總記錄數(shù),查詢SQL如下
|
1
2
3
4
5
6
|
<!-- 記錄總數(shù) --><select id="countUser" resultType="Integer"> <![CDATA[ SELECT COUNT(*) FROM user ]]></select> |
|
1
2
3
4
|
@Override public Integer countTable() { return getSqlSession().selectOne("dao.userdao.countUser"); } |
Controller中的使用
|
1
2
3
4
5
6
7
8
9
10
11
12
|
@RequestMapping("/getUserByPage")public String getUserByPage(@RequestParam int page, Model model) { pagination.setCurrentPage(page); pagination.setUrl(getCurrentUrl()); pagination.setMaxPage(userDao.countTable() / pagination.getPageSize() + 1); List<User> userList = userDao.selectUserByPage( pagination.getOffset(), pagination.getLimit()); model.addAttribute(pagination); model.addAttribute(userList); return "index";} |
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持服務(wù)器之家。
原文鏈接：http://www.cnblogs.com/zemliu/archive/2013/08/07/3242966.html
