1.封裝分頁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
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
|
package com.framework.common.page.impl;import java.io.Serializable;import com.framework.common.page.IPage;/** * * * */public abstract class BasePage implements IPage, Serializable { /** * */ private static final long serialVersionUID = -3623448612757790359L; public static int DEFAULT_PAGE_SIZE = 20; private int pageSize = DEFAULT_PAGE_SIZE; private int currentResult; private int totalPage; private int currentPage = 1; private int totalCount = -1; public BasePage(int currentPage, int pageSize, int totalCount) { this.currentPage = currentPage; this.pageSize = pageSize; this.totalCount = totalCount; } public int getTotalCount() { return this.totalCount; } public void setTotalCount(int totalCount) { if (totalCount < 0) { this.totalCount = 0; return; } this.totalCount = totalCount; } public BasePage() { } public int getFirstResult() { return (this.currentPage - 1) * this.pageSize; } public void setPageSize(int pageSize) { if (pageSize < 0) { this.pageSize = DEFAULT_PAGE_SIZE; return; } this.pageSize = pageSize; } public int getTotalPage() { if (this.totalPage <= 0) { this.totalPage = (this.totalCount / this.pageSize); if ((this.totalPage == 0) || (this.totalCount % this.pageSize != 0)) { this.totalPage += 1; } } return this.totalPage; } public int getPageSize() { return this.pageSize; } public void setPageNo(int currentPage) { this.currentPage = currentPage; } public int getPageNo() { return this.currentPage; } public boolean isFirstPage() { return this.currentPage <= 1; } public boolean isLastPage() { return this.currentPage >= getTotalPage(); } public int getNextPage() { if (isLastPage()) { return this.currentPage; } return this.currentPage + 1; } public int getCurrentResult() { this.currentResult = ((getPageNo() - 1) * getPageSize()); if (this.currentResult < 0) { this.currentResult = 0; } return this.currentResult; } public int getPrePage() { if (isFirstPage()) { return this.currentPage; } return this.currentPage - 1; }} |
|
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
|
package com.framework.common.page.impl;import java.util.List;/** * * * */public class Page extends BasePage { /** * */ private static final long serialVersionUID = -970177928709377315L; public static ThreadLocal<Page> threadLocal = new ThreadLocal<Page>(); private List<?> data; public Page() { } public Page(int currentPage, int pageSize, int totalCount) { super(currentPage, pageSize, totalCount); } public Page(int currentPage, int pageSize, int totalCount, List<?> data) { super(currentPage, pageSize, totalCount); this.data = data; } public List<?> getData() { return data; } public void setData(List<?> data) { this.data = data; } } |
2.封裝分頁插件
|
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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
package com.framework.common.page.plugin;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Properties;import javax.xml.bind.PropertyException;import org.apache.commons.lang3.StringUtils;import org.apache.ibatis.executor.ErrorContext;import org.apache.ibatis.executor.ExecutorException;import org.apache.ibatis.executor.statement.BaseStatementHandler;import org.apache.ibatis.executor.statement.RoutingStatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.mapping.ParameterMode;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.reflection.MetaObject;import org.apache.ibatis.reflection.property.PropertyTokenizer;import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.type.TypeHandler;import org.apache.ibatis.type.TypeHandlerRegistry;import com.framework.common.page.impl.Page;import com.framework.common.utils.ReflectUtil;/** * * * */@Intercepts({ @org.apache.ibatis.plugin.Signature(type = org.apache.ibatis.executor.statement.StatementHandler.class, method = "prepare", args = { Connection.class }) })public class PagePlugin implements Interceptor { private String dialect = ""; private String pageSqlId = ""; @Override public Object intercept(Invocation invocation) throws Throwable { if (invocation.getTarget() instanceof RoutingStatementHandler) { BaseStatementHandler delegate = (BaseStatementHandler) ReflectUtil .getValueByFieldName( (RoutingStatementHandler) invocation.getTarget(), "delegate"); MappedStatement mappedStatement = (MappedStatement) ReflectUtil .getValueByFieldName(delegate, "mappedStatement"); Page page = Page.threadLocal.get(); if (page == null) { page = new Page(); Page.threadLocal.set(page); } if (mappedStatement.getId().matches(".*(" + this.pageSqlId + ")$") && page.getPageSize() > 0) { BoundSql boundSql = delegate.getBoundSql(); Object parameterObject = boundSql.getParameterObject(); String sql = boundSql.getSql(); String countSqlId = mappedStatement.getId().replaceAll(pageSqlId, "Count"); MappedStatement countMappedStatement = null; if (mappedStatement.getConfiguration().hasStatement(countSqlId)) { countMappedStatement = mappedStatement.getConfiguration().getMappedStatement(countSqlId); } String countSql = null; if (countMappedStatement != null) { countSql = countMappedStatement.getBoundSql(parameterObject).getSql(); } else { countSql = "SELECT COUNT(1) FROM (" + sql + ") T_COUNT"; } int totalCount = 0; PreparedStatement countStmt = null; ResultSet resultSet = null; try { Connection connection = (Connection) invocation.getArgs()[0]; countStmt = connection.prepareStatement(countSql); BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject); setParameters(countStmt, mappedStatement, countBoundSql, parameterObject); resultSet = countStmt.executeQuery(); if(resultSet.next()) { totalCount = resultSet.getInt(1); } } catch (Exception e) { throw e; } finally { try { if (resultSet != null) { resultSet.close(); } } finally { if (countStmt != null) { countStmt.close(); } } } page.setTotalCount(totalCount); ReflectUtil.setValueByFieldName(boundSql, "sql", generatePageSql(sql,page)); } } return invocation.proceed(); } /** * 對SQL參數(?)設值,參考org.apache.ibatis.executor.parameter.DefaultParameterHandler * @param ps * @param mappedStatement * @param boundSql * @param parameterObject * @throws SQLException */ private void setParameters(PreparedStatement ps,MappedStatement mappedStatement,BoundSql boundSql,Object parameterObject) throws SQLException { ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId()); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); if (parameterMappings != null) { Configuration configuration = mappedStatement.getConfiguration(); TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); MetaObject metaObject = parameterObject == null ? null: configuration.newMetaObject(parameterObject); for (int i = 0; i < parameterMappings.size(); i++) { ParameterMapping parameterMapping = parameterMappings.get(i); if (parameterMapping.getMode() != ParameterMode.OUT) { Object value; String propertyName = parameterMapping.getProperty(); PropertyTokenizer prop = new PropertyTokenizer(propertyName); if (parameterObject == null) { value = null; } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { value = parameterObject; } else if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) { value = boundSql.getAdditionalParameter(prop.getName()); if (value != null) { value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length())); } } else { value = metaObject == null ? null : metaObject.getValue(propertyName); } TypeHandler typeHandler = parameterMapping.getTypeHandler(); if (typeHandler == null) { throw new ExecutorException("There was no TypeHandler found for parameter "+ propertyName + " of statement "+ mappedStatement.getId()); } typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType()); } } } } /** * 根據數據庫方言，生成特定的分頁sql * @param sql * @param page * @return */ private String generatePageSql(String sql,Page page){ if(page!=null && StringUtils.isNotBlank(dialect)){ StringBuffer pageSql = new StringBuffer(); if("mysql".equals(dialect)){ pageSql.append(sql); pageSql.append(" LIMIT "+page.getCurrentResult()+","+page.getPageSize()); }else if("oracle".equals(dialect)){ pageSql.append("SELECT * FROM (SELECT TMP_TB.*,ROWNUM ROW_ID FROM ("); pageSql.append(sql); pageSql.append(") AS TMP_TB WHERE ROWNUM <= "); pageSql.append(page.getCurrentResult()+page.getPageSize()); pageSql.append(") WHERE ROW_ID > "); pageSql.append(page.getCurrentResult()); } return pageSql.toString(); }else{ return sql; } } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { try { if (StringUtils.isEmpty(this.dialect = properties .getProperty("dialect"))) { throw new PropertyException("dialect property is not found!"); } if (StringUtils.isEmpty(this.pageSqlId = properties .getProperty("pageSqlId"))) { throw new PropertyException("pageSqlId property is not found!"); } } catch (PropertyException e) { e.printStackTrace(); } }} |
3.MyBatis配置文件:mybatis-config.xml
|
1
2
3
4
5
6
7
8
9
10
|
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL Map Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <plugins> <plugin interceptor="com.framework.common.page.plugin.PagePlugin"> <property name="dialect" value="mysql" /> <property name="pageSqlId" value="ByPage" /> </plugin> </plugins></configuration> |
4.分頁攔截器
|
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
|
package com.framework.common.page.interceptor;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang3.math.NumberUtils;import org.springframework.web.servlet.ModelAndView;import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;import com.framework.common.page.impl.Page;/*** * 14 **/public class PageInterceptor extends HandlerInterceptorAdapter { @Override public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView) throws Exception { super.postHandle(request, response, handler, modelAndView); Page page = Page.threadLocal.get(); if (page != null) { request.setAttribute("page", page); } Page.threadLocal.remove(); } @Override public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception { String pageSize = request.getParameter("pageSize"); String pageNo = request.getParameter("pageNo"); Page page = new Page(); if (NumberUtils.isNumber(pageSize)) { page.setPageSize(NumberUtils.toInt(pageSize)); } if (NumberUtils.isNumber(pageNo)) { page.setPageNo(NumberUtils.toInt(pageNo)); } Page.threadLocal.set(page); return true; }} |
5.Spring配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
<!-- =================================================================== - Load property file - =================================================================== --><context:property-placeholder location="classpath:application.properties" /><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:mybatis-config.xml" /> <property name="mapperLocations"> <list> <value>classpath:/com/framework/mapper/**/*Mapper.xml</value> </list> </property></bean><!-- =================================================================== - 通過掃描的模式，掃描目錄下所有的dao， 根據對應的mapper.xml為其生成代理類 - =================================================================== --><bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.framework.dao" /> <property name="processPropertyPlaceHolders" value="true" /> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /></bean> |
6.SpringMVC配置攔截器
|
1
2
3
4
5
6
7
8
9
10
11
|
<!-- 分頁攔截器 --> <bean id="pageInterceptor" class="com.framework.common.page.interceptor.PageInterceptor"></bean> <!-- 配置攔截器 --> <bean class="org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping"> <property name="interceptors"> <list> <ref bean="pageInterceptor" /> </list> </property> </bean> |
