MyBatis拦截器实现分页功能实例

时间：2022-06-10 05:13:00

由于业务关系 巴拉巴拉巴拉

好吧 简单来说就是

原来的业务是 需要再实现类里写 selectCount 和selectPage两个方法才能实现分页功能

现在想要达到效果是 只通过一个方法就可以实现 也就是功能合并 所以就有了下面的实践

既然是基于MyBatis 所以就先搭建一个Mybatis的小项目

1.01导入 mybatis和mysql的包

MyBatis拦截器实现分页功能实例

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

标签：

相关文章