自定义mybatis分页插件(mysql数据库)

最新推荐文章于 2024-04-19 15:49:21 发布

_翚_

最新推荐文章于 2024-04-19 15:49:21 发布

阅读量446

点赞数

分类专栏： orm 文章标签： mybatis plugin

本文链接：https://blog.csdn.net/juzhenxing/article/details/107452154

版权

orm 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

代码实现

分页插件实现

// 定义分页请求参数对象
package com.example.plugin.page.request;

import lombok.Data;

@Data
public class PageParams {

    /**
     * 当前页码
     */
    private Integer page;

    /**
     * 每页限制条数
     */
    private Integer pageSize;

    /**
     * 是否启动分页插件, 如果不启动, 则不作分页
     */
    private Boolean useFlag;

    /**
     * 是否检测页码的有效性, 如果为true, 当页码大于最大页数时, 则抛出异常
     */
    private Boolean checkFlag;

    /**
     * 是否清除order by后面的语句
     */
    private Boolean cleanOrderBy;

    /**
     * 总条数, 插件回填
     */
    private Integer total;

    /**
     * 总页数, 插件回填
     */
    private Integer totalPage;
}

// 通过代理StatementHandler的prepare方法实现插件
package com.example.plugin.page;

import com.example.plugin.page.request.PageParams;
import org.apache.ibatis.executor.parameter.ParameterHandler;
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 org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.Properties;

@Intercepts({
        @Signature(type = StatementHandler.class,
                method = "prepare",
                args = {Connection.class, Integer.class})
})
public class PagePlugin implements Interceptor {

    private Integer defaultPage;

    private Integer defaultPageSize;

    private Boolean defaultUseFlag;

    private Boolean defaultCheckFlag;

    private Boolean defaultCleanOrderBy;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) getUnProxyObject(invocation.getTarget());
        // 返回StatementHandler对象描述
        MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
        String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
        if (!checkSelect(sql)) {
            return invocation.proceed();
        }

        BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
        Object parameterObject = boundSql.getParameterObject();
        PageParams pageParams = getPageParams(parameterObject);
        if (pageParams == null) {
            return invocation.proceed();
        }

        Boolean useFlag = pageParams.getUseFlag() == null ? this.defaultUseFlag : pageParams.getUseFlag();
        if (!useFlag) {
            return invocation.proceed();
        }

        Integer page = pageParams.getPage() == null ? this.defaultPage : pageParams.getPage();
        Integer pageSize = pageParams.getPageSize() == null ? this.defaultPageSize : pageParams.getPageSize();
        Boolean checkFlag = pageParams.getCheckFlag() == null ? this.defaultCheckFlag : pageParams.getCheckFlag();
        Boolean cleanOrderBy = pageParams.getCleanOrderBy() == null ? this.defaultCleanOrderBy : pageParams.getCleanOrderBy();
        int total = getTotal(invocation, metaStatementHandler, boundSql, cleanOrderBy);
        pageParams.setTotal(total);

        int totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
        pageParams.setTotalPage(totalPage);

        checkPage(checkFlag, page, pageSize);

        return preparedSQL(invocation, metaStatementHandler, boundSql, page, pageSize);
    }

    /**
     * 获取非代理的StatementHandler对象
     *
     * @param target
     * @return
     */
    private Object getUnProxyObject(Object target) {
        MetaObject metaStatementHandler = SystemMetaObject.forObject(target);

        // 分离代理对象链, 由于目标类可能被多个拦截器拦截, 从而形成多次代理, 通过循环可以分离出最原始的目标类
        Object object = null;
        while (metaStatementHandler.hasGetter("h")) {
            object = metaStatementHandler.getValue("h");
            metaStatementHandler = SystemMetaObject.forObject(object);
        }
        if (object == null) {
            return target;
        }
        return object;
    }

    /**
     * 判断是否是查询语句
     *
     * @param sql
     * @return
     */
    private boolean checkSelect(String sql) {
        String trimSql = sql.trim();
        int index = trimSql.toLowerCase().indexOf("select");
        return index == 0;
    }

    private PageParams getPageParams(Object parameterObject) throws IntrospectionException, InvocationTargetException, IllegalAccessException {
        PageParams pageParams = null;
        if (parameterObject == null) {
            return pageParams;
        }
        // 处理map参数, 多个匿名参数和@param注解参数, 都是map
        if (parameterObject instanceof Map) {
            Map<String, Object> paramMap = (Map<String, Object>) parameterObject;
            for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
                if (entry.getValue() instanceof PageParams) {
                    return (PageParams) entry.getValue();
                }
            }
        } else if (parameterObject instanceof PageParams) {
            // 参数是或者继承PageParams
            return (PageParams) parameterObject;
        } else {
            // 从pojo属性尝试读取分页参数
            Field[] fields = parameterObject.getClass().getDeclaredFields();
            for (Field field : fields) {
                if (field.getType() == PageParams.class) {
                    PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), field.getClass());
                    Method readMethod = propertyDescriptor.getReadMethod();
                    return (PageParams) readMethod.invoke(parameterObject);
                }
            }
        }
        return pageParams;
    }

    /**
     * 获取总条数
     *
     * @param invocation
     * @param metaStatementHandler
     * @param oldBoundSql
     * @param cleanOrderBy
     * @return
     * @throws SQLException
     */
    private int getTotal(Invocation invocation, MetaObject metaStatementHandler, BoundSql oldBoundSql, Boolean cleanOrderBy) throws SQLException {
        MappedStatement oldMappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
        Configuration configuration = oldMappedStatement.getConfiguration();
        String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
        if (cleanOrderBy) {
            sql = this.cleanOrderByForsql(sql);
        }
        String countSql = String.format("select count(*) as total from (%s) $_paging", sql);
        Connection connection = (Connection) invocation.getArgs()[0];
        PreparedStatement countPreparedStatement = null;
        int total = 0;
        try {
            countPreparedStatement = connection.prepareStatement(countSql);

            BoundSql countBoundSql = new BoundSql(configuration, countSql, oldBoundSql.getParameterMappings(), oldBoundSql.getParameterObject());
            ParameterHandler parameterHandler = new DefaultParameterHandler(oldMappedStatement, oldBoundSql.getParameterObject(), countBoundSql);
            // 设置总数sql参数
            parameterHandler.setParameters(countPreparedStatement);
            ResultSet resultSet = countPreparedStatement.executeQuery();
            while (resultSet.next()) {
                total = resultSet.getInt("total");
            }
        } finally {
            if (countPreparedStatement != null) {
                countPreparedStatement.close();
            }
        }
        return total;
    }

    private void checkPage(Boolean checkFlag, Integer pageNum, Integer pageTotal) {
        if (checkFlag) {
            if (pageNum > pageTotal) {
                throw new RuntimeException(String.format("查询失败, 查询页码[%d]大于总页数[%d]", pageNum, pageTotal));
            }
        }
    }

    /**
     * 预编译改写后的sql, 并设置分页参数
     *
     * @param invocation
     * @param metaStatementHandler
     * @param boundSql
     * @param pageNum
     * @param pageSize
     * @return
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws SQLException
     */
    private Object preparedSQL(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql, int pageNum, int pageSize) throws InvocationTargetException, IllegalAccessException, SQLException {
        String sql = boundSql.getSql();
        String newSql = String.format("select * from (%s) $_paging_table limit ?, ?", sql);
        metaStatementHandler.setValue("delegate.boundSql.sql", newSql);
        // 执行编译, 相当于statementHandler执行了prepare()方法
        Object statementObj = invocation.proceed();
        this.preparePageDataParams((PreparedStatement) statementObj, pageNum, pageSize);
        return statementObj;
    }

    private void preparePageDataParams(PreparedStatement ps, int pageNum, int pageSize) throws SQLException {
        int parameterCount = ps.getParameterMetaData().getParameterCount();
        ps.setInt(parameterCount - 1, (pageNum - 1));
        ps.setInt(parameterCount, pageSize);
    }


    private String cleanOrderByForsql(String sql) {
        String newSql = sql.toLowerCase();
        int index = newSql.lastIndexOf("order");
        if (index == -1) {
            return newSql;
        }
        return newSql.substring(0, index);
    }

    @Override
    public Object plugin(Object target) {
        // 生成代理对象
        return Plugin.wrap(target, this);
    }

    /**
     * 设置插件配置参数
     *
     * @param properties
     */
    @Override
    public void setProperties(Properties properties) {
        // 从配置中获取参数
        String strDefaultPage = properties.getProperty("default.page", "1");
        String strDefaultPageSize = properties.getProperty("default.pageSize", "20");
        String strDefaultUseFlag = properties.getProperty("default.useFlag", "true");
        String strDefaultCheckFlag = properties.getProperty("default.checkFlag", "false");
        String strDefaultCleanOrderBy = properties.getProperty("default.cleanOrderBy", "true");

        // 设置默认参数
        defaultPage = Integer.parseInt(strDefaultPage);
        defaultPageSize = Integer.parseInt(strDefaultPageSize);
        defaultUseFlag = Boolean.parseBoolean(strDefaultUseFlag);
        defaultCheckFlag = Boolean.parseBoolean(strDefaultCheckFlag);
        defaultCleanOrderBy = Boolean.parseBoolean(strDefaultCleanOrderBy);
    }
}

注册分页插件

package com.example.plugin.page.config;

import com.example.plugin.page.PagePlugin;
import org.apache.ibatis.session.Configuration;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;

import java.util.Properties;

@org.springframework.context.annotation.Configuration
@MapperScan({"com.example.plugin.page.mapper"})
public class MapperConfig {

    //将插件加入到mybatis插件拦截链中
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                //插件拦截链采用了责任链模式，执行顺序和加入连接链的顺序有关
                PagePlugin pagePlugin = new PagePlugin();
                //设置参数，比如阈值等，可以在配置文件中配置，这里直接写死便于测试
                Properties properties = new Properties();
                properties.setProperty("default.page", "1");
                properties.setProperty("default.pageSize", "20");
                properties.setProperty("default.useFlag", "true");
                properties.setProperty("default.checkFlag", "false");
                properties.setProperty("default.cleanOrderBy", "true");
                pagePlugin.setProperties(properties);
                configuration.addInterceptor(pagePlugin);
            }
        };
    }
}

实现业务代码

// mapper接口
public interface Table1Mapper {

    List<Table1> getList(GetListRequest request);
}

// mapper xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.plugin.page.mapper.Table1Mapper">

    <select id="getList" resultType="com.example.plugin.page.entity.Table1">
        select *
        from table1
        where
        id >= #{minId}
        order by id
    </select>
</mapper>

配置

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456

mybatis.type-aliases-package=com.example.plugin.page.entity
mybatis.mapperLocations=classpath:mappers/*.xml
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

测试

测试代码

package com.example.plugin.page;

import com.alibaba.fastjson.JSON;
import com.example.plugin.page.entity.Table1;
import com.example.plugin.page.mapper.Table1Mapper;
import com.example.plugin.page.request.GetListRequest;
import com.example.plugin.page.response.GetListResponse;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class PagePluginTest {

    @Autowired
    private Table1Mapper table1Mapper;

    @Test
    public void test1() {
        GetListRequest request = new GetListRequest();
        request.setPage(1);
        request.setPageSize(2);
        request.setMinId(2);
        request.setCleanOrderBy(true);
        List<Table1> list = table1Mapper.getList(request);

        GetListResponse<Table1> getListResponse = new GetListResponse();
        getListResponse.setList(list);
        BeanUtils.copyProperties(request, getListResponse);
        System.out.println(JSON.toJSONString(getListResponse));
    }
    
}

效果

2020-07-19 21:30:09.313  WARN 11932 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Thread starvation or clock leap detected (housekeeper delta=1m18s456ms611µs800ns).
==>  Preparing: select count(*) as total from (select * from table1 where id >= ? ) $_paging 
2020-07-19 21:39:34.632  WARN 11932 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Thread starvation or clock leap detected (housekeeper delta=9m28s34ms475µs600ns).
==> Parameters: 2(Integer)
<==    Columns: total
<==        Row: 5
<==      Total: 1
2020-07-19 21:40:30.668  WARN 11932 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Thread starvation or clock leap detected (housekeeper delta=56s35ms593µs700ns).
==>  Preparing: select * from (select * from table1 where id >= ? order by id) $_paging_table limit ?, ? 
==> Parameters: 0(Integer), 2(Integer), 2(Integer)
<==    Columns: id, name
<==        Row: 2, name2
<==        Row: 3, name3
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@191a0351]
{"list":[{"id":2,"name":"name2"},{"id":3,"name":"name3"}],"page":1,"pageSize":2,"total":5,"totalPage":3}