你的位置:首页 > Java教程

[Java教程]MyBatis 拦截器 (实现分页功能)


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

好吧 简单来说就是

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

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

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

1.01导入 mybatis和mysql的包

1.02.配置文件 Configuration.

 <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表

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

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.

简单实现下CRUD

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);  }

<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.

<mappers>    <mapper resource="lqb/mapper/UserMapper./></mappers>

 

3.04.然后是实现:UserService.java

public class UserService {  private static SqlSessionFactory sqlSessionFactory;  private static Reader reader;  static{    try{      reader  = Resources.getResourceAsReader("Configuration.);      sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);    }catch(Exception e){      e.printStackTrace();    }  }  public static SqlSessionFactory getSession(){    return sqlSessionFactory;  }}

 

4.01 好 然后是重点了

思路: 截获查询的sql 然后拼成 sqlPage和sqlCount 再进行查找取值 然后赋传入对象

所以我们就需要创建一个基础类来让user.java来继承

public class Common {  private int pagesize;  private int pageid;  private int pagebegin;  private int count;  //省略 get set }

4.02 然后 让User继承Common

public class User extends Common{

 

4.03 那怎么截获sql呢 我们就要写一个mybatis的拦截器 用来拦截sql请求 PageInterceptor

 

@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方法

@Override  public Object plugin(Object target) {    if (target instanceof StatementHandler) {       return Plugin.wrap(target, this);     } else {       return target;     }   }

4/05 然后 就要重写最重要的intercept了

这里我们有一个设定  如果查询方法含有searchpage 就进行分页 其他方法无视

所以就要获取方法名

 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

BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); // 分页参数作为参数对象parameterObject的一个属性 String sql = boundSql.getSql();Common co=(Common)(boundSql.getParameterObject());

4.07 然后 根据这个sql 重新拼写countsql和pagesql 

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

 

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

  metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); 

 

4.10 最后在配置文件中添加拦截器配置

 <plugins>   <plugin interceptor="lqb.interceptor.PageInterceptor"/></plugins> 

 

4.11 好然后 在UserMapper.java和UserMapper.

 <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>

 public List<User> selectPage(User u);

5.01 最后是测试了

main...请允许本人的懒 就姑且在main方法测下吧

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 结果 略   然后就成功了  

 

下面附上拦截器的代码

 

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(){      }  } 

View Code

 

最后是下载地址

 下载