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

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

好吧 简单来说就是

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

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

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

1.01导入 mybatis和mysql的包

1.02.配置文件 configuration.xml 中添加

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

简单实现下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.xml中添加user的配置

<mappers>
  <mapper resource="lqb/mapper/usermapper.xml"/>
</mappers>

3.04.然后是实现：userservice.java

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来继承

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.xml中添加分页代码

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

最后是下载地址：mybatisresolve_jb51.rar

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。