springMVC,myBatis的物理分页和高级查询
springMVC、myBatis的物理分页和高级查询
分类：JavaEE 2013-06-09 13:44 797人阅读评论(4) 收藏举报springMVCmybatis高级查询分页ajax
最新项目用到springMVC和mybatis，分页其实用一个RowBounds可以实现，但是高级查询不好封装，经过反复测试，总算搞出来了，感觉封装的不是很好，有待优化和提高！
原理：利用mybatis自定义插件功能，自定义一个拦截器，拦截需要分页的sql，并想办法通过BoundSql对象进行处理，大致分8步：
1、获得BoundSql对象
2、获取原始的写在配置文件中的SQL
3、拦截到mapper中定义的执行查询方法中的参数
4、解析参数，获取高级查询参数信息
5、解析参数，获取查询限制条件
6、根据4、5中的参数拼装并重新生成SQL语句
7、将SQL设置回BoundSql对象中
8、完成。
拦截器：
[java]view plaincopyprint?
1.package com.wtas.page.interceptor;
2.
3.import java.sql.Connection;
4.import java.sql.PreparedStatement;
5.import java.sql.ResultSet;
6.import java.sql.SQLException;
7.import java.util.List;
8.import java.util.Map;
9.import java.util.Properties;
10.import java.util.Set;
11.
12.import javax.xml.bind.PropertyException;
13.
14.import org.apache.ibatis.executor.ErrorContext;
15.import org.apache.ibatis.executor.ExecutorException;
16.import org.apache.ibatis.executor.statement.BaseStatementHandler;
17.import org.apache.ibatis.executor.statement.RoutingStatementHandler;
18.import org.apache.ibatis.executor.statement.StatementHandler;
19.import org.apache.ibatis.mapping.BoundSql;
20.import org.apache.ibatis.mapping.MappedStatement;
21.import org.apache.ibatis.mapping.ParameterMapping;
22.import org.apache.ibatis.mapping.ParameterMode;
23.import org.apache.ibatis.plugin.Interceptor;
24.import org.apache.ibatis.plugin.Intercepts;
25.import org.apache.ibatis.plugin.Invocation;
26.import org.apache.ibatis.plugin.Plugin;
27.import org.apache.ibatis.plugin.Signature;
28.import org.apache.ibatis.reflection.MetaObject;
29.import org.apache.ibatis.reflection.property.PropertyTokenizer;
30.import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
31.import org.apache.ibatis.session.Configuration;
32.import org.apache.ibatis.type.TypeHandler;
33.import org.apache.ibatis.type.TypeHandlerRegistry;
34.import org.slf4j.Logger;
35.import org.slf4j.LoggerFactory;
36.
37.import com.wtas.page.PageContext;
38.import com.wtas.page.Pager;
39.import com.wtas.page.Query;
40.import com.wtas.utils.SystemUtil;
41.
42./**
43. * 查询分页拦截器，用户拦截SQL，并加上分页的参数和高级查询条件
44. *
45. * @author dendy
46. *
47. */
48.@Intercepts({ @Signature(type = StatementHandler.class, method = "prep
are", args = { Connection.class }) })
49.public class PaginationInterceptor implements Interceptor {
50.
51. private final Logger logger = LoggerFactory
52. .getLogger(PaginationInterceptor.class);
53.
54. private String dialect = "";
55.
56. // 暂时不需要这个参数，现在根据参数类型来判断是否是分页sql
57. // private String pageMethodPattern = "";
58.
59. public Object intercept(Invocation ivk) throws Throwable {
60. if (!(ivk.getTarget() instanceof RoutingStatementHandler)) {
61. return ivk.proceed();
62. }
63. RoutingStatementHandler statementHandler = (RoutingStatementHa
ndler) ivk
64. .getTarget();
65. BaseStatementHandler delegate = (BaseStatementHandler) SystemU
til
66. .getValueByFieldName(statementHandler, "delegate");
67. MappedStatement mappedStatement = (MappedStatement) SystemUtil
68. .getValueByFieldName(delegate, "mappedStatement");
69.
70. // BoundSql封装了sql语句
71. BoundSql boundSql = delegate.getBoundSql();
72. // 获得查询对象
73. Object parameterObject = boundSql.getParameterObject();
74. // 根据参数类型判断是否是分页方法
75. if (!(parameterObject instanceof Query)) {
76. return ivk.proceed();
77. }
78. logger.debug(" beginning to intercept page SQL...");
79. Connection connection = (Connection) ivk.getArgs()[0];
80. String sql = boundSql.getSql();
81. Query query = (Query) parameterObject;
82. // 查询参数对象
83. Pager pager = null;
84. // 查询条件Map
85. Map
86. pager = query.getPager();
87. // 拼装查询条件
88. if (conditions != null) {
89. Set
90. Object value = null;
91. StringBuffer sb = new StringBuffer();
92. boolean first = true;
93. for (String key : keys) {
94. value = conditions.get(key);
95. if (first) {
96. sb.append(" where ").append(key).append(value);
97. first = !first;
98. } else {
99. sb.append(" and ").append(key).append(value); 100. }
101. }
102. sql += sb.toString();
103. }
104.
105. // 获取查询数来的总数目
106. String countSql = "SELECT COUNT(0) FROM (" + sql + ") AS t mp ";
107. PreparedStatement countStmt = connection.prepareStatement( countSql);
108. BoundSql countBS = new BoundSql(mappedStatement.getConfigu ration(),
109. countSql, boundSql.getParameterMappings(), paramet erObject);
110. setParameters(countStmt, mappedStatement, countBS, paramet erObject);
111. ResultSet rs = countStmt.executeQuery();
112. int count = 0;
113. if (rs.next()) {
114. count = rs.getInt(1);
115. }
116. rs.close();
117. countStmt.close();
118.
119. // 设置总记录数
120. pager.setTotalResult(count);
121. // 设置总页数
122. pager.setTotalPage((count + pager.getShowCount() - 1) 123. / pager.getShowCount());
124. // 放到作用于
125. PageContext.getInstance().set(pager);
126.
127. // 拼装查询参数
128. String pageSql = generatePageSql(sql, pager);
129. SystemUtil.setValueByFieldName(boundSql, "sql", pageSql);
130. logger.debug("generated pageSql is : " + pageSql); 131.
132. return ivk.proceed();
133. }
134.
135. /**
136. * setting parameters
137. *
138. * @param ps
139. * @param mappedStatement
140. * @param boundSql
141. * @param parameterObject
142. * @throws SQLException
143. */
144. private void setParameters(PreparedStatement ps,
145. MappedStatement mappedStatement, BoundSql boundSql, 146. Object parameterObject) throws SQLException {
147. ErrorContext.instance().activity("setting parameters") 148. .object(mappedStatement.getParameterMap().getId()) ;
149. List
151. if (parameterMappings != null) {
152. Configuration configuration = mappedStatement.getConfi guration();
153. TypeHandlerRegistry typeHandlerRegistry = configuratio n
154. .getTypeHandlerRegistry();
155. MetaObject metaObject = parameterObject == null ? null
156. : configuration.newMetaObject(parameterObject) ;
157. for (int i = 0; i < parameterMappings.size(); i++) { 158. ParameterMapping parameterMapping = parameterMappi ngs.get(i);
159. if (parameterMapping.getMode() != ParameterMode.OU T) {
160. Object value;
161. String propertyName = parameterMapping.getProp erty();
162. PropertyTokenizer prop = new PropertyTokenizer (propertyName);
163. if (parameterObject == null) {
164. value = null;
165. } else if (typeHandlerRegistry
166. .hasTypeHandler(parameterObject.getCla ss())) {
167. value = parameterObject;
168. } else if (boundSql.hasAdditionalParameter(pro pertyName)) {
169. value = boundSql.getAdditionalParameter(pr opertyName);
170. } else if (propertyName
171. .startsWith(ForEachSqlNode.ITEM_PREFIX )
172. && boundSql.hasAdditionalParameter(pro p.getName())) {
173. value = boundSql.getAdditionalParameter(pr op.getName());
174. if (value != null) {
175. value = configuration.newMetaObject(va lue)
176. .getValue(
177. propertyName.substring (prop
178. .getName().len gth()));
179. }
180. } else {
181. value = metaObject == null ? null : metaOb ject
182. .getValue(propertyName);
183. }
184.@SuppressWarnings("unchecked")
185. TypeHandler
186. .getTypeHandler();
187. if (typeHandler == null) {
188. throw new ExecutorException(
189. "There was no TypeHandler found fo r parameter "
190. + propertyName + " of stat ement "
191. + mappedStatement.getId()) ;
192. }
193. typeHandler.setParameter(ps, i + 1, value, 194. parameterMapping.getJdbcType()); 195. }
196. }
197. }
198. }
199.
200. /**
201. * 生成Sql语句
202. *
203. * @param sql
204. * @param page
205. * @return
206. */
207. private String generatePageSql(String sql, Pager page) { 208. if (page != null && (dialect != null || !dialect.equals(""
))) {
209. StringBuffer pageSql = new StringBuffer();
210. if ("mysql".equals(dialect)) {
211. pageSql.append(sql);
212. pageSql.append(" LIMIT " + page.getCurrentResult() + ","
213. + page.getShowCount());
214. } else if ("oracle".equals(dialect)) {
215. pageSql.append("SELECT * FROM (SELECT t.*,ROWNUM r FROM (");
216. pageSql.append(sql);
217. pageSql.append(") t WHERE r <= ");
218. pageSql.append(page.getCurrentResult() + page.getS howCount());
219. pageSql.append(") WHERE r >");
220. pageSql.append(page.getCurrentResult());
221. }
222. return pageSql.toString();
223. } else {
224. return sql;
225. }
226. }
227.
228. public Object plugin(Object arg0) {
229. return Plugin.wrap(arg0, this);
230. }
231.
232. public void setProperties(Properties p) {
233. dialect = p.getProperty("dialect");
234. if (dialect == null || dialect.equals("")) {
235. try {
236. throw new PropertyException("dialect property is n ot found!");
237. } catch (PropertyException e) {
238. e.printStackTrace();
239. }
240. }
241. // pageMethodPattern = p.getProperty("pageMethodPattern");
242. if (dialect == null || dialect.equals("")) {
243. try {
244. throw new PropertyException(
245. "pageMethodPattern property is not found!"
);
246. } catch (PropertyException e) {
247. e.printStackTrace();
248. }
249. }
250. }
251.
252.}
查询对象的封装：
1、map封装查询条件
2、pager对象封装查询限制条件，就是MySql中limit后的参数等附加信息[java]view plaincopyprint?
1.package com.wtas.page;
2.
3./**
4. * 分页描述信息
5. *
6. * @author dendy
7. *
8. */
9.public class Pager {
10. // 每一页的显示条数
11. private int showCount;
12. // 总的页数
13. private int totalPage;
14. // 查询的数据总条数
15. private int totalResult;
16. // 当前页
17. private int currentPage;
18. // 从第几条开始获取数据
19.@SuppressWarnings("unused")
20. private int currentResult;
21.
22. public Pager() {
23. this(1);
24. }
25.
26. public Pager(int currentPage) {
27. // 默认每页显示10条记录
28. this(currentPage, 10);
29. }
30.
31. public Pager(int currentPage, int showCount) {
32. this.currentPage = currentPage;
33. if (showCount > 0) {
34. this.showCount = showCount;
35. }
36. // 错误处理
37. if (this.currentPage < 1) {
38. this.currentPage = 1;
39. }
40. }
41.
42. //只列出关键的getter和setter……
43.
44. public int getTotalPage() {
45. // 分页算法，计算总页数
46. return this.totalPage;
47. }
48.
49. public int getCurrentResult() {
50. // 计算从第几条获取数据
51. return (currentPage - 1) * showCount;
52. }
53.
54.}
[java]view plaincopyprint?
1.package com.wtas.page;
2.
3.import java.util.Map;
4.
5./**
6. * 封装查询蚕食和查询条件
7. *
8. * @author dendy
9. *
10. */
11.public class Query {
12. private Map
13. private Pager pager;
14.
15. public Map
16. return queryParams;
17. }
18.
19. public void setQueryParams(Map
20. this.queryParams = queryParams;
21. }
22.
23. //省略getter和setter
24.}
控制层关键代码：
[java]view plaincopyprint?
1. /**
2. * 分页时获取所有的学生
3. *
4. * @return
5. */
6.@RequestMapping("pageStus")
7.@ResponseBody
8.public List
9. try {
10. Query query = new Query();
11. Pager pager = new Pager();
12. Map
);
13.
14. // 获取分页参数
15. String showCount = req.getParameter("showCount");
16. String currentPage = req.getParameter("currentPage");
18. if (StringUtils.hasLength(showCount)) {
19. pager.setShowCount(Integer.parseInt(showCount));
20. }
21. if (StringUtils.hasLength(currentPage)) {
22. pager.setCurrentPage(Integer.parseInt(currentPage));
23. }
24.
25. // 高级查询条件：学生真实姓名
26. String trueNameForQuery = req.getParameter("trueNameForQuery")
;
27. if (StringUtils.hasLength(trueNameForQuery)) {
28. queryParams.put(" u.REAL_NAME like ", "'%" + trueNameForQu
ery
29. + "%'");
30. }
31.
32. query.setPager(pager);
33. query.setQueryParams(queryParams);
34. List
35.
36. // req.setAttribute("pager", PageContext.getInstance().get());
37.
38. return users;
39. } catch (Exception e) {
40. LOG.error("getAllStudents error : " + e.getMessage());
41. }
42. return null;
43.}
44.
45.@RequestMapping("getPager")
46.@ResponseBody
47.public Pager getPager() {
48. return PageContext.getInstance().get();
dao中的方法：
[java]view plaincopyprint?
1. /**
2.* 级联查询所有某一角色的用户信息，带分页
3.*
4.* @param roleValue
5.* @param page
6.* @return
7.*/
8.ist
dao的Mappder.xml定义：
[html]view plaincopyprint?
1.
页面通过javascript来异常发送请求获取数据，关键代码：
[javascript]view plaincopyprint?
1./**
2. * 处理分页
3. *
4. * @param curPage
5. * @param id
6. */
7.function page(curPage, id) {
8. if(curPage <= 0){
9. curPage = 1;
10. }
11.
12. var trueNameForQuery = $("#findByTrueNameInput").val().trim();
13.
14. var url = path + "/studygroup/pageStus.do";
15. var thCss = "class='s-th-class'";
16. var tdCss = "class='s-td-class'";
17. $.ajax({
18. type : "POST",
19. url : url,
20. dataType : "json",
21. data : {
22. "id" : id,
23. "currentPage" : curPage,
24. "trueNameForQuery" : trueNameForQuery
25. },
26. success : function(data) {
27. var json = eval(data);
28. var res = "
29. + "
30. + "
31. + "
32. + "
33. + "
34. + "
35. for ( var i = 0; i < json.length; i++) {
36. var userId = json[i].id;
37. var name = json[i].name;
38. var trueName = json[i].trueName;
39. var sex = json[i].sex;
40. var school = "";
41. if (json[i].school) {
42. school = json[i]https://www.sodocs.net/doc/9816227240.html,;
43. }
44. var grade = "";
45. if (json[i].grade) {
46. grade = json[i]https://www.sodocs.net/doc/9816227240.html,;
47. }
48. var clazz = "";
49. if (json[i].clazz) {
50. clazz = json[i]https://www.sodocs.net/doc/9816227240.html,;
51. }
52. res += "
pe='checkbox' value='" + userId + "' />
53. + "
|| "") + "
54. + "
Name || "") + "
55. + "
== 1 ? '女' : '男' || "") + "
56. + "
l + "
57. + "
+ "
58. + "
+ "
59. + "
60. }
61. $("#inviteStudentsTbl").html(res);
62. // 每次加载完成都要刷新分页栏数据
63. freshPager(id);
64. }
65. });
66.}
67.
68./**
69. * 重新获取分页对象，刷新分页工具栏
70. */
71.function freshPager(id){
72. var url = path + "/studygroup/getPager.do";
73. var studyGroupId = id;
74. $.ajax({
75. type : "POST",
76. url : url,
77. dataType : "json",
78. success : function (data) {
79. var pager = eval(data);
80. var currentPage = pager.currentPage;
81.// var currentResult = pager.currentResult;
82.// var showCount = pager.showCount;
83. var totalPage = pager.totalPage;
84.// var totalResult = pager.totalResult;
85.
86. var prePage = currentPage - 1;
87. var nextPage = currentPage + 1;
88. if (prePage <= 0) {
89. prePage = 1;
90. }
91. if (nextPage > totalPage) {
92. nextPage = totalPage;
93. }
94.
95. $("#topPageId").attr("href", "javascript:page(1, " + study
GroupId + ");");
96. $("#prefixPageId").attr("href", "javascript:page(" + prePa
ge + ", " + studyGroupId + ");");
97. $("#nextPageId").attr("href", "javascript:page(" + nextPag
e + ", " + studyGroupId + ");");
98. $("#endPageId").attr("href", "javascript:page(" + totalPag
e + ", " + studyGroupId + ");");
99. $("#curPageId").html(currentPage);
100. $("#totalPageId").html(totalPage);
101. }
102. });
103.}
104.
105./**
106. * 按真实姓名搜索
107. */
108.function findByTrueName() {
109. page(1, studyGroupId);
110.}
end. ————————————————————————————————————————————————
应网友需要，贴上SystemUtil的代码：
[java]view plaincopyprint?
1.package https://www.sodocs.net/doc/9816227240.html,mon.utils;
2.
3.import https://www.sodocs.net/doc/9816227240.html,ng.reflect.Field;
4.
5.import javax.servlet.http.HttpSession;
6.
7.import https://www.sodocs.net/doc/9816227240.html,mon.consts.SystemConst;
8.import https://www.sodocs.net/doc/9816227240.html,er;
9.
10./**
11. * 系统工具类，定义系统常用的工具方法
12. *
13. * @author dendy
14. *
15. */
16.public class SystemUtil {
17. private SystemUtil() {
18.
19. }
20.
21. /**
22. * 获取系统访问的相对路径，如:/WTAS
23. *
24. * @return
25. */
26. public static String getContextPath() {
27. return System.getProperty(SystemConst.SYSTEM_CONTEXT_PATH_KEY)
;
28. }
29.
30. /**
31. * 修改一个bean(源)中的属性值，该属性值从目标bean获取
32. *
33. * @param dest
34. * 目标bean，其属性将被复制到源bean中
35. * @param src
36. * 需要被修改属性的源bean
37. * @param filtNullProps
38. * 源bean的null属性是否覆盖目标的属性
只有为null的属性才会被覆盖
39. * : 不管源bean的属性是否为null，均覆盖
40. * @throws IllegalArgumentException
41. * @throws IllegalAccessException
42. */
43. public static void copyBean(Object dest, Object src, boolean filtN
ullProps)
44. throws IllegalArgumentException, IllegalAccessException {
45. if (dest.getClass() == src.getClass()) {
46. // 目标bean的所有字段
47. Field[] destField = dest.getClass().getDeclaredFields();
48. // 源bean的所有字段
49. Field[] srcField = src.getClass().getDeclaredFields();
50. for (int i = 0; i < destField.length; i++) {
51. String destFieldName = destField[i].getName();
52. String destFieldType = destField[i].getGenericType().t
oString();
53. for (int n = 0; n < srcField.length; n++) {
54. String srcFieldName = srcField[n].getName();
55. String srcFieldType = srcField[n].getGenericType()
56. .toString();
57. // String srcTypeName =
58. // srcField[n].getType().getSimpleName();
59. if (destFieldName.equals(srcFieldName)
60. && destFieldType.equals(srcFieldType)) {
61. destField[i].setAccessible(true);
62. srcField[n].setAccessible(true);
63. Object srcValue = srcField[n].get(src);
64. Object destValue = destField[i].get(dest);
65. if (filtNullProps) {
66. // 源bean中的属性已经非空，则不覆盖
67. if (srcValue == null) {
68. srcField[n].set(src, destValue);
69. }
70. } else {
71. srcField[n].set(dest, srcValue);
72. }
73. }
74. }
75. }
76. }
77. }
78.
79. /**
80. * 根据字段的值获取该字段
81. *
82. * @param obj
83. * @param fieldName