JZTXT

基于mybatis的Interceptor接口去实现的分页

发布时间 2023-06-14 16:05:04作者: 小福gui

因为mybatis在执行的时候会去初始化我们的拦截器和执行我们的拦截器。有的时候就想是否没必要去书写我们的一个分页可以基于mybatis去进行一个实现只要传递对应的参数就行。在基于当前连接的驱动去判断是mysql还是oracle去进行一个分页。实现的代码如下：

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


    @Autowired
    HttpServletRequest request;

    private final Logger LOGGER = LoggerFactory.getLogger(MyPageInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //获取执行器对象
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //获取执行的sql
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
        //执行的sql
        String operrationSql = metaObject.getValue("delegate.boundSql.sql").toString();

        LOGGER.info("当前正在执行的SQL---"+operrationSql);
        String page = request.getParameter("page");
        String pagesize = request.getParameter("pagesize");
        if (StringUtils.isEmpty(page) || StringUtils.isEmpty(pagesize)){
            return invocation.proceed();
        }
        operrationSql = getPageSql(getConnectionName(invocation),operrationSql,page,pagesize);

        BoundSql boundSql = statementHandler.getBoundSql();
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, operrationSql);
        LOGGER.info("当前分页后的SQL---"+operrationSql);
        Object proceed = invocation.proceed();
        return proceed;
    }


    private static String mysqlPage(String operrationSql,long page,long pageSize ){
        if (!StringUtils.isEmpty(operrationSql)){
            operrationSql = String.format(operrationSql +" limit %s,%s", page, pageSize);
        }
        return operrationSql;
    }

    private static String oraclePage(String operrationSql,long page,long pageSize){

        if (!StringUtils.isEmpty(operrationSql)){
            operrationSql = String.format("SELECT * FROM(SELECT t1.*,ROWNUM RN FROM ( %s )t1) WHERE RN >= %s AND RN <= %s ",operrationSql, page, pageSize);
        }
        return operrationSql;
    }

    private static String getPageSql(String connectionName,String operrationSql,String page,String pageSize){
        if (connectionName.contains("mysql")){
            operrationSql = mysqlPage(operrationSql,Integer.valueOf(page),Integer.valueOf(pageSize));
        }else if (connectionName.contains("oracle")) {
            operrationSql = oraclePage(operrationSql,Integer.valueOf(page),Integer.valueOf(pageSize));
        }
        return operrationSql;
    }

    private static String getConnectionName(Invocation invocation) throws SQLException {
        Connection connection = (Connection) invocation.getArgs()[0];
        DatabaseMetaData metaData = connection.getMetaData();
        return  metaData.getDatabaseProductName().toLowerCase();
    }



}