MyBatis 拦截器 （实现分页功能）

时间：2023-03-08 16:47:30

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

好吧 简单来说就是

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

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

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

1.01导入 mybatis和mysql的包

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQgAAABCCAIAAAAkIN8WAAAABmJLR0QA/wD/AP+gvaeTAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAJcklEQVR4nO2df4gbaRnHnzcndysK9ZSWWtreHUwWXReuVqSQrHDsKZhEYb2TRUFusWAGhTNBu4i2+IfsFo71dMajYIJS91oV1mtdKMn0qp67dZPjynn6R2+VZrSg4LWltOmP3c4m2bz+MTOZH3lnMklmk8z2+RCWmffH877v7DzzPjOZ7xtCKQUEQayE+t0BBBlE0DEQhAE6BoIwQMdAEAZ+OYbEE14CAFmMkqgoa1u85JN5BOkt77Ptr/z4UGN77Mhbve0MggwKFsc4+2L4uVdKTrue4FIFmvKlZwjSRwzHWDl+KHHsyMb1jFJWFEVRyuVPfPYLK8cPmUuP/QDnEOShwHCMDajB0AEAGNoNQ3rik898pVFg5cS3WtuTxWh49RjNxLR9iSfxLAAAJPNGKoIMOKZQqgKgXHMp+p8rt9q1no0v5inNgOog/AS6BhIQDMdQAEApN3ZzP/upreiOj+w4+90D6vZzL//di/VkXveE2LQQCS9KmRh6BhIETKHUvY2GY+SyJxPf/iazQrlcLrz62150DUH6h/E9hlLZAFAAlFz2ZCL5VVDKzR/VKzxOFwCQXdS+yJDFqXQxOYHTBRIQ7DNG7lUp8cKXQFGai5ZhqC2vAIAkLBISVzfx3hsJEKTx2rnw/J79T+5KvMA+e8vwoXa9AkGCCzHrMU7yH3Mp+vXMP7e+PwgyEBAUKiFIM/h2LYIwQMdAEAboGAjCAB0DQRigYyAIA7tQqTOWl5edsur1OqV0fHzcl4YQpDf487h2eXl5LBoFAoQQAO1vvV4PhUJvv/3XcrkceiT07Piz3TeEIL3Bn1CKUkqBAgXVzdS/oVAIAKrV6tjYWK1aW1pa8qWtztXkhh69fwxCHxAP+OMYarzU7BsAsLa2dvHiRQCoVqu+tNUrJJ6osHxQFqPEOds9FwkC/txjUEqB0uLlq+8feowAUVMIIRTo0M4nuF0frFQqV0ptysf9wSQpbEOPLovRxQlKM2yFlcRPwTylnGaelyzZrrmoiQ8Ivs4Ym7WDw/s+Obz3aW5P+M7S09yeg8P76rXq7o/u3v/E/nq97ktbPYFLFbSzOTaRhMtXrLFPLFNIcVrBxGTElu2eiwQDPx3jgbJRq9Wq1eqdPwvVOll793ytVnugbLhWlXgSFSUt9uAlI4SJirI9JJd4605TuGKPYSSehNNFyMYJiYqy6d7Ee7AjLWYjkwnOMb+0WhwddsxuyjXfHzE6oWZLvD5+pG9QP8jlcg/W189cWLl79265XL6eO37jxOdv3rx569atMxdWNjdr9fpmLpdjVc0nASAilPRNSOYppbQkRNStfFLLpTSfVJNKQqRRTq2l10lqJS2JEb1oY9OU5ojaCLQoZ+qdt1yjacfetmoT6QXdzhiJ2bOJ2bMn/qY8/xPpl5euK4ry2h/fOv/fx/I7vnbp3X//+vyb99fWVOdzDqUiwnyKAzVqiQjTMQAAbnhUjWBiE8niQk4GAGkxa2gArWpyVSnIpTIpUK/C2sokDnDDo5CNW67JjflHT+RSBUoppROLjhdviSdxyDfiprZyXXprDA3pH906RvGNd8yfSqWyd9fj/3v0qWuVDzz6CCEA6+sPKICrY7gTmxZgISfL4kzWXRsri1FCpmCeUuN672QzQyml8zDV8AQ1hVJqP5NjmbzumRbU895Jleie22ZvkT7QrWPc/tPMF7/xXuPz/b/88O56Zd/OD+/d+fi122tfHv/UZr1OKQUKtVqtsya4xCQszM0tgDaZAABTTV5aLepzj5xbKLpYlEVRnWEKJSFSXG1+WiaLUSPqn9FuMqzL8s6MlCznvddcHe+9RfqBD49rT/3LshrVp/cbu7+7ULhz/z6lFIjxzUbbcKljoyR+WSiZruUMNXlsWpgJh0kaACLJpH4N5hKTkXScZCNCad4wODxnqt18WedS8yNRUwnrLCLNpYsARbUpAIgIpULKY24Ddm+RQaHbV0LePML4nx740R+I+n6IBgAhp06dPnz4cGetSDyZGSk5x+vBwb5SIzKgdDtj3H7v2lMHP25OufrOP9QpglBo+BwBuHfvXodtyOJMNnmMBt8r1KgpMhnudzeQlnQdSpVvX33DHiGrL4WYfIMA0PX19Q7Mq2vfMuOdgKGt4tsUmCEDiT9v1x49etRLsdnZ2e7bQpAegKuEIAgDVPAhCAN0DARhgI6BIAxQ840gDFDzjSAMgqj59o7+6+PbiUCqxh1k+h7H0o8ho+YbATDLvpw0UhLvpupiycYCzbbXfA8aXb8stWWq8Yjg9DaaLEbDaXB+1VF9lY22/9KOx7H0QyiPmm+kJVyqQGlheoSdK4szl7UX6LcRgdR8sxTblljAQ0RqlNfLmi2YzUZFkW8q2ZzItGlLFC0adMdGXZXfbqpxy+GyKkRaBzrFdLiTWEjOLcAkzHk58E3xljEWp0PacsjQ4nB1jC8C2Z5qvhmKbUM0re1oVXSVOKtRqxrbmmLYyyctXTJ1lJHYMGDuva0hc++dGnVVfrurxo1emBTuDHG5I8aAnBu2YDoYzpUdZPqGSeYh9TbkrRHKd+sY8Zkz5s+NGzd+8drr8z9/+VenT+eXL73ym9z871/frFU3N2vnzp1jGTCfT8xt84bpIJqPnW3RAWPXVkWv1bxIgT2F5Ze0JESYPqDbtBIRSu6rITg3ajsFrZ23ZppUsY2Reixmsun4H3HouXNxfc9m31ZX37U4RvNxZrfcNBYva1u0TRA1382K7dZGnCTdzkRG2pVNWP+bHcmqmI06dJ6tGtcWj5BzuhTYoVj7B8SB8Ajrntw/+2Z6KJQPoOa7WbEdm0gW03MmkXYLi5bykijKasqUcTMzlwa3xaRa2QSJ5yVmQ01VOm/UQTUemxbUw6XZ8iQu1/oLABIfb9K4s2jkcolJ0EdhCOQZtP2j730VygdQ881QbMcy+SSJkywAQEQQkrDgbjGWKQnRMCGaiQwAl6F5njSE2h3Iicw2NaOshsDQoBdSXTXqpBrnEpOQTo/mtaennsTlscwErx1TxlNbWYyG0+ppGCeq2CpjzGxcqpBf1UfhMgYffvS9h0L57aj53j4KcRYPoWq8H0NGzXfAeAhV430ZMmq+g8NDqBrv35BR840gDFDzjSAMUMGHIAzQMRCEAToGgjBAx0AQBvbHtUvSS8xyz8S+t/WdQZBBwe4YmzT0mc99x5ZYOv/ikvSSr76xFV9mSjxZnHiovhJGtoz/AyOmhK+t16OIAAAAAElFTkSuQmCC" alt="" />

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

    }

}

最后是下载地址

下载

相关文章