MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回
为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugin截获MyBatis Executor的statementhandler,重写SQL来执行查询
下面的插件代码只针对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语句实现物理分页。 * 老规矩,签名里要拦截的类型只能是接口。 * */ @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" ); // 分离代理对象链(由于目标类可能被多个拦截器拦截,从而形成多次代理,通过下面的两次循环可以分离出最原始的的目标类) 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); } // property在mybatis settings文件内配置 Configuration configuration = (Configuration) metaStatementHandler.getValue( "delegate.configuration" ); // 设置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" ); // 只重写需要分页的sql语句。通过MappedStatement的ID匹配,默认重写以Page结尾的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); // 采用物理分页后,就不需要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 target) { // 当目标类是StatementHandler类型时,才包装目标类,否者直接返回目标本身,减少目标被代理的次数 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
|
<!-- 测试分页查询 --> < select id = "selectUserByPage" resultMap = "dao.base.userResultMap" > <![CDATA[ SELECT * FROM user ]]> </ select > |
调用示例
1
2
3
4
5
|
@Override public List<User> selectUserByPage( int offset, int limit) { RowBounds rowBounds = new RowBounds(offset, limit); return getSqlSession().selectList( "dao.userdao.selectUserByPage" , new Object(), rowBounds); } |
另外,结合Spring MVC,编写翻页和生成页码代码
页码类
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; // 页码url private int pageSize = 10 ; // 每页显示记录数 private int currentPage = 1 ; // 当前页码 private int maxPage = Integer.MAX_VALUE; // 最大页数 // 获取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; } } |
为了计算最大页码,需要知道数据表的总记录数,查询SQL如下
1
2
3
4
5
6
|
<!-- 记录总数 --> < 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" ; } |
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。
原文链接:http://www.cnblogs.com/zemliu/archive/2013/08/07/3242966.html