由于業務關系 巴拉巴拉巴拉
好吧 簡單來說就是
原來的業務是 需要再實現類里寫 selectCount 和selectPage兩個方法才能實現分頁功能
現在想要達到效果是 只通過一個方法就可以實現 也就是功能合并 所以就有了下面的實踐
既然是基于MyBatis 所以就先搭建一個Mybatis的小項目
1.01導入 mybatis和mysql的包
1.02.配置文件 Configuration.xml 中添加
|
1
2
3
4
5
6
7
8
9
10
11
|
<environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/test" /> <property name="username" value="root"/> <property name="password" value=""/> </dataSource> </environment></environments> |
2.01.然后創建一個模塊user 創建user表
|
1
2
3
4
5
6
7
8
9
|
DROP TABLE IF EXISTS `user`;CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(32) NOT NULL, `t1` char(32) DEFAULT NULL, `t2` char(32) DEFAULT NULL, `t3` char(32) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; |
3.01.寫對應bean：User.java
|
1
2
3
4
5
6
7
8
9
10
11
12
|
package lqb.bean;public class User extends Common{ private String id; private String name; private String t1; private String t2; private String t3; //省略get set } |
3.02.對應的mapper： UserMapper.java和UserMapper.xml
簡單實現下CRUD
|
1
2
3
4
5
6
7
8
|
public interface UserMapper { public User selectByID(int id); public List<User> select(); public int insert(User u); public int update(User u); public int delete(User u); } |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<mapper namespace="lqb.mapper.UserMapper"> <select id="selectByID" parameterType="int" resultType="lqb.bean.User"> select * from `user` where id = #{id} </select> <select id="select" resultType="lqb.bean.User" parameterType="lqb.bean.User"> select * from `user` </select> <insert id="insert" parameterType="lqb.bean.User"> insert into user (id,name,t1,t2,t3) values (#{id},#{name},#{t1},#{t2},#{t3}) </insert> <update id="update" parameterType="lqb.bean.User"> update user set name=#{name},t1=#{t1},t2=#{t2},t3=#{t3} where id=#{id} </update> <delete id="delete" parameterType="lqb.bean.User"> delete from user where id=#{id} </delete></mapper> |
3.03.然后 在配置文件Configuration.xml中添加user的配置
|
1
2
3
|
<mappers> <mapper resource="lqb/mapper/UserMapper.xml"/></mappers> |
3.04.然后是實現：UserService.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
public class UserService { private static SqlSessionFactory sqlSessionFactory; private static Reader reader; static{ try{ reader = Resources.getResourceAsReader("Configuration.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); }catch(Exception e){ e.printStackTrace(); } } public static SqlSessionFactory getSession(){ return sqlSessionFactory; }} |
4.01 好 然后是重點了
思路： 截獲查詢的sql 然后拼成 sqlPage和sqlCount 再進行查找取值 然后賦傳入對象
所以我們就需要創建一個基礎類來讓user.java來繼承
|
1
2
3
4
5
6
7
|
public class Common { private int pagesize; private int pageid; private int pagebegin; private int count; //省略 get set } |
4.02 然后 讓User繼承Common
|
1
|
public class User extends Common{ |
4.03 那怎么截獲sql呢 我們就要寫一個mybatis的攔截器 用來攔截sql請求 PageInterceptor
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}), @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})}) public class PageInterceptor implements Interceptor { //插件運行的代碼，它將代替原有的方法 @Override public Object intercept(Invocation invocation) throws Throwable { } // 攔截類型StatementHandler @Override public Object plugin(Object target) { } @Override public void setProperties(Properties properties) { } |
4.04 首先 設置攔截類型 重寫plugin方法
|
1
2
3
4
5
6
7
8
|
@Override public Object plugin(Object target) { if (target instanceof StatementHandler) { return Plugin.wrap(target, this); } else { return target; } } |
4.05 然后 就要重寫最重要的intercept了
這里我們有一個設定 如果查詢方法含有searchpage 就進行分頁 其他方法無視
所以就要獲取方法名
|
1
2
3
4
|
StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler); MappedStatement mappedStatement=(MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");String selectId=mappedStatement.getId(); |
4.06 然后判斷下 如果含有searchpage 就獲取sql
|
1
2
3
4
|
BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); // 分頁參數作為參數對象parameterObject的一個屬性 String sql = boundSql.getSql();Common co=(Common)(boundSql.getParameterObject()); |
4.07 然后 根據這個sql 重新拼寫countsql和pagesql
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
String countSql=concatCountSql(sql);String pageSql=concatPageSql(sql,co);...public String concatCountSql(String sql){ StringBuffer sb=new StringBuffer("select count(*) from "); sql=sql.toLowerCase(); if(sql.lastIndexOf("order")>sql.lastIndexOf(")")){ sb.append(sql.substring(sql.indexOf("from")+4, sql.lastIndexOf("order"))); }else{ sb.append(sql.substring(sql.indexOf("from")+4)); } return sb.toString(); } public String concatPageSql(String sql,Common co){ StringBuffer sb=new StringBuffer(); sb.append(sql); sb.append(" limit ").append(co.getPagebegin()).append(" , ").append(co.getPagesize()); return sb.toString(); } |
4.08 然后 通過jdbc查詢count 然后把值綁定給common
|
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
|
Connection connection = (Connection) invocation.getArgs()[0]; PreparedStatement countStmt = null; ResultSet rs = null; int totalCount = 0; try { countStmt = connection.prepareStatement(countSql); rs = countStmt.executeQuery(); if (rs.next()) { totalCount = rs.getInt(1); } } catch (SQLException e) { System.out.println("Ignore this exception"+e); } finally { try { rs.close(); countStmt.close(); } catch (SQLException e) { System.out.println("Ignore this exception"+ e); } } //綁定count co.setCount(totalCount); |
4.09 再把pagesql賦給元BoundSql
|
1
|
metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); |
4.10 最后在配置文件中添加攔截器配置
|
1
2
3
|
<plugins> <plugin interceptor="lqb.interceptor.PageInterceptor"/></plugins> |
4.11 好然后 在UserMapper.java和UserMapper.xml中添加分頁代碼
|
1
2
3
|
<select id="selectPage" parameterType="lqb.bean.User" resultType="lqb.bean.User"> select * from `user` where id in(3,4,6,8) order by id</select> |
|
1
|
public List<User> selectPage(User u); |
5.01 最后是測試了
main...請允許本人的懶 就姑且在main方法測下吧
|
1
2
3
4
5
6
7
|
User u=new User();u.setPagebegin(2);u.setPagesize(3);System.out.println("-u.getCount()------"+u.getCount());List<User> l=userService.selectPage(u);System.out.println(l.size());System.out.println("-u.getCount()------"+u.getCount()); |
5.02 結果 略 然后就成功了
下面附上攔截器的代碼
|
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
|
package lqb.interceptor;import java.util.Properties;import org.apache.ibatis.executor.resultset.ResultSetHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.plugin.*; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import java.sql.*; import lqb.bean.Common;@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}), @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})}) public class PageInterceptor implements Interceptor { private static final String SELECT_ID="selectpage"; //插件運行的代碼，它將代替原有的方法 @Override public Object intercept(Invocation invocation) throws Throwable { System.out.println("PageInterceptor -- intercept"); if (invocation.getTarget() instanceof StatementHandler) { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler); MappedStatement mappedStatement=(MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); String selectId=mappedStatement.getId(); if(SELECT_ID.equals(selectId.substring(selectId.lastIndexOf(".")+1).toLowerCase())){ BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); // 分頁參數作為參數對象parameterObject的一個屬性 String sql = boundSql.getSql(); Common co=(Common)(boundSql.getParameterObject()); // 重寫sql String countSql=concatCountSql(sql); String pageSql=concatPageSql(sql,co); System.out.println("重寫的 count sql :"+countSql); System.out.println("重寫的 select sql :"+pageSql); Connection connection = (Connection) invocation.getArgs()[0]; PreparedStatement countStmt = null; ResultSet rs = null; int totalCount = 0; try { countStmt = connection.prepareStatement(countSql); rs = countStmt.executeQuery(); if (rs.next()) { totalCount = rs.getInt(1); } } catch (SQLException e) { System.out.println("Ignore this exception"+e); } finally { try { rs.close(); countStmt.close(); } catch (SQLException e) { System.out.println("Ignore this exception"+ e); } } metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); //綁定count co.setCount(totalCount); } } return invocation.proceed(); } /** * 攔截類型StatementHandler */ @Override public Object plugin(Object target) { if (target instanceof StatementHandler) { return Plugin.wrap(target, this); } else { return target; } } @Override public void setProperties(Properties properties) { } public String concatCountSql(String sql){ StringBuffer sb=new StringBuffer("select count(*) from "); sql=sql.toLowerCase(); if(sql.lastIndexOf("order")>sql.lastIndexOf(")")){ sb.append(sql.substring(sql.indexOf("from")+4, sql.lastIndexOf("order"))); }else{ sb.append(sql.substring(sql.indexOf("from")+4)); } return sb.toString(); } public String concatPageSql(String sql,Common co){ StringBuffer sb=new StringBuffer(); sb.append(sql); sb.append(" limit ").append(co.getPagebegin()).append(" , ").append(co.getPagesize()); return sb.toString(); } public void setPageCount(){ } } |
最后是下載地址：mybatisResolve.rar
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://www.cnblogs.com/jethypc/p/5149183.html
