Jpa mybatis mybatis plus 整合 混合使用时，jsqlparser一直报错

背景

公司原项目的持久层使用的是JPA,JPA有他的优势，但劣势也非常明显：
1、如果不明确定义视图类会导致查询时，做很多无意义的关联查询，每行数据都会去查关联的数据，但实际执行关联数据在特定场景下是无用的。
2、无法优化SQL，更别说基于索引进行优化了，除非写原生SQL。既然都写原生SQL了，完全违背了JPA的设计原则，还不如用mybatis+mybatis plus 一把梭.

解决办法

Jpa mybatis mybatis plus 整合,再项目中整合进mybatis.

实施中遇到的问题

net.sf.jsqlparser.statement.insert.Insert cannot be cast to net.sf.jsqlparser.statement.select.Select

这个问题整体来说，是JPA用了jsqlparser,mybatis-plus也用到了jsqlparser,但jpa中jsqlparser的版本和Mybatis-plus中的版本不一直导致的

解决办法，排除mybatis中的JSQLparser

      <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>${mybatis-plus.version}</version>
            <exclusions>
                <exclusion>
                    <artifactId>jsqlparser</artifactId>
                    <groupId>com.github.jsqlparser</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-annotation</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>

这样确实可以引入了mybatis-plus了，也能正常写一些简单的查询，但无法使用mybatis-plus的分页功能，因为：
1、mybatis-plus 的分页功能会用到jsqlparser
2、因为之前排除了mybatis-plus的jsqlparser，所以 用的是项目中的 JPA中的jsqlparser，还是版本不一致的问题，总之各种报错
实在没法办，想到用pageHelper来解决分页问题，但最终还是因为JSQLPARSER的问题，用不起来。

最终的解决办法：

1、依然排除mybatis-plus的jsqlparser
2、自定义mybatis-plus的分页功能

package com.zx.mes.utils;

import cn.hutool.core.collection.ListUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zx.mes.mass.domain.vo.TaskMassVO;
import com.zx.mes.mass.mp.entity.MesTaskMass;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.boot.autoconfigure.data.web.SpringDataWebProperties;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

/**
 * @Description:
 * mybatis mapper 执行方法时的拦截器
 * 兼容Mybatis的Page对象
 * 兼容Springboot PageRequest
 * @Author: Fang.j
 * @Date: 2024/1/3 11:16
 **/
@Component
@Slf4j
@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class PageHelperQueryInterceptor implements Interceptor {


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        log.info("进入拦截器");
        Object[] args = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) args[0];
        //获取参数
        Object param = invocation.getArgs()[1];
        PageRequest pageRequest = this.getPageRequest(param);
        BoundSql boundSql = null;
        Object parameterObject = null;
        /**
         * 判断参数列表是否有PageRequest来判断是否需要进行分页
         */
        if (pageRequest != null) {
            Object whereParam = getWhereParameter(param);
            boundSql = mappedStatement.getBoundSql(whereParam);

            PageRequest pageVo = pageRequest;
            String sql = boundSql.getSql();
            //获取相关配置
            Configuration config = mappedStatement.getConfiguration();
            Connection connection = config.getEnvironment().getDataSource().getConnection();
            //拼接查询当前条件的sql的总条数  这个效率有点低
            //  String countSql = "select count(*) from (" + sql + ") a";
            //sql全部转小写
            sql = sql.toLowerCase();
            String countSql = "select count(1) " + sql.substring(sql.indexOf("from"));

            log.debug("count sql:" + countSql);

            PreparedStatement preparedStatement = connection.prepareStatement(countSql);
            BoundSql countBoundSql = new BoundSql(config, countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
            ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBoundSql);
            parameterHandler.setParameters(preparedStatement);
            //执行获得总条数
            ResultSet rs = preparedStatement.executeQuery();
            Integer count = 0;
            if (rs.next()) {
                count = rs.getInt(1);
            }
            //拼接分页sql
            String pageSql = sql + " limit " + pageVo.getOffset() + " , " + pageVo.getPageSize();
            //重新执行新的sql
            doNewSql(invocation, pageSql);
            Object result = invocation.proceed();
            connection.close();

            //Spring boot的实现了Page接口的PageImpl
//            PageImpl<?> page = new PageImpl((List) result, pageVo, count);
//            List<PageImpl> returnResultList = new ArrayList<>();
//            returnResultList.add(page);
//            return returnResultList;

            return useMybatisPage(result,pageVo,count==null?0:count);
        }
        return invocation.proceed();
    }

    private Object useMybatisPage(Object result,PageRequest pageRequest,int count){
        Page page = new Page();
        page.setCurrent( pageRequest.getPageNumber() );
        page.setSize( pageRequest.getPageSize() );
        page.setTotal( count );
        page.setRecords( (List) result );
        //只能返回List 所以外部list.get(0);
        List<Page> returnResultList = new ArrayList<>();
        returnResultList.add(page);
        return returnResultList;
    }

    private Object useSpringBootPage(Object result,PageRequest pageRequest,int count){
        //Spring boot的实现了Page接口的PageImpl
        PageImpl<?> page = new PageImpl((List) result, pageRequest, count);
        List<PageImpl> returnResultList = new ArrayList<>();
        returnResultList.add(page);
        return returnResultList;
    }


    private void doNewSql(Invocation invocation, String sql) {
        final Object[] args = invocation.getArgs();
        MappedStatement statement = (MappedStatement) args[0];
        Object parameterObject = getWhereParameter(args[1]);
        BoundSql boundSql = statement.getBoundSql(parameterObject);
        MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
        MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
        msObject.setValue("sqlSource.boundSql.sql", sql);
        args[0] = newStatement;
    }

    /**
     * 获取新的MappedStatement
     *
     * @param ms
     * @param newSqlSource
     * @return
     */
    private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder =
                new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
            StringBuilder keyProperties = new StringBuilder();
            for (String keyProperty : ms.getKeyProperties()) {
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
            builder.keyProperty(keyProperties.toString());
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());
        return builder.build();
    }

    @Override
    public Object plugin(Object o) {
        Object wrap = Plugin.wrap(o, this);
        return wrap;
    }

    @Override
    public void setProperties(Properties properties) {
    }

    /**
     * 新的SqlSource需要实现
     */
    class BoundSqlSqlSource implements SqlSource {
        private BoundSql boundSql;

        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }

    /**
     * 从参数列表返回PageRequest
     */
    public PageRequest getPageRequest(Object paramMap) {
        if (paramMap == null) {
            return null;
        } else if (PageRequest.class.isAssignableFrom(paramMap.getClass())) {
            return (PageRequest) paramMap;
        } else if (Page.class.isAssignableFrom(paramMap.getClass())) {
            //兼容处理 如果传入的是mybatis的Page分页对象
            Page mybatisPage = (Page)paramMap;
            return  PageRequest.of(Integer.valueOf(String.valueOf(mybatisPage.getCurrent())) , Integer.valueOf(String.valueOf(mybatisPage.getSize())) );
        }else {
            if (paramMap instanceof MapperMethod.ParamMap) {
                MapperMethod.ParamMap map = (MapperMethod.ParamMap) paramMap;
                Iterator iterator = map.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry entry = (Map.Entry) iterator.next();
                    Object obj = entry.getValue();
                    if (obj != null && PageRequest.class.isAssignableFrom(obj.getClass())) {
                        return (PageRequest) obj;
                    }
                    if (obj != null && Page.class.isAssignableFrom(obj.getClass())) {
                        //兼容处理 如果传入的是mybatis的Page分页对象
                        Page mybatisPage = (Page) obj;
                        return PageRequest.of(Integer.valueOf(String.valueOf(mybatisPage.getCurrent())), Integer.valueOf(String.valueOf(mybatisPage.getSize())));
                    }

                }
            }
            return null;
        }
    }

    private Object getWhereParameter(Object obj) {
        if (obj instanceof MapperMethod.ParamMap) {
            MapperMethod.ParamMap paramMap = (MapperMethod.ParamMap) obj;
            if (paramMap.size() == 4) {
                Iterator iterator = paramMap.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry var4 = (Map.Entry) iterator.next();
                    Object var5 = var4.getValue();
                    if (SpringDataWebProperties.Sort.class.isAssignableFrom(var5.getClass()) || PageRequest.class.isAssignableFrom(var5.getClass())) {
                        return paramMap.get("param1");
                    }
                }
            }
        }
        return obj;
    }
}






import cn.hutool.core.lang.Assert;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zx.common.base.domain.ResponsePageVO;
import com.zx.mes.mass.mp.entity.MesTaskMass;
import org.springframework.data.domain.Pageable;

public class PageBaby {

    /**
     * @description:
     * 转成mybatis IPage的实现类
     * @author: Fang.j
     * @date: 2024/1/3 16:28
     * @param  * @param null
     * @return
    */
    public static Page toIPage(Pageable pageable){
        Page page = new Page<>();
        page.setSize( pageable.getPageSize() );
        page.setCurrent( pageable.getPageNumber() );
        return page;
    }
    /**
     * @description:
     * mybatis的IPage转成Vo 在很多年以后，这个方法会被淘汰
     * @author: Fang.j
     * @date: 2024/1/3 16:18
     * @param  * @param null
     * @return
    */
    public static <T> ResponsePageVO<T> tradIPageToPageVo(IPage<T> page) {
        Page data = (Page) page.getRecords().get(0);
        if( data==null )  return new ResponsePageVO(null, 0, 0);
        return new ResponsePageVO(data.getRecords(), Integer.valueOf( String.valueOf(   data.getPages())) , data.getTotal());
    }


}

代码交代

ResponsePageVO 项目返回的封装对象可以无视
PageBaby.toIPage 由于整个项目分页参数使用的是Pageable 所以传给mybatis-plus时需要转成mybatis-plus的Page对象

    public  ResponsePageVO<TaskMassVO> qualityDone(Pageable page, TaskMassQueryCriteria taskMassQueryCriteria) {
        IPage<TaskMassVO> data = mesTaskMassMapper.qualityDone(PageBaby.toIPage(page),taskMassQueryCriteria);
        return PageBaby.tradIPageToPageVo(data);
    }

所有依赖版本

 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <spring-boot.version>2.7.3</spring-boot.version>
        <spring-cloud.version>2021.0.4</spring-cloud.version>
        <spring-cloud-alibaba.version>2021.0.4.0</spring-cloud-alibaba.version>
        <spring-boot-admin.version>2.7.5</spring-boot-admin.version>
        <spring-boot.mybatis>2.2.2</spring-boot.mybatis>
        <swagger.fox.version>3.0.0</swagger.fox.version>
        <swagger.core.version>1.6.2</swagger.core.version>
        <tobato.version>1.27.2</tobato.version>
        <swagger.core.io.version>2.2.0</swagger.core.io.version>
        <kaptcha.version>2.3.2</kaptcha.version>
        <pagehelper.boot.version>1.4.5</pagehelper.boot.version>
        <druid.version>1.2.12</druid.version>
        <dynamic-ds.version>3.5.2</dynamic-ds.version>
        <commons.io.version>2.11.0</commons.io.version>
        <commons.fileupload.version>1.4</commons.fileupload.version>
        <velocity.version>2.3</velocity.version>
        <fastjson.version>2.0.16</fastjson.version>
        <jjwt.version>0.9.1</jjwt.version>
        <minio.version>8.2.2</minio.version>
        <mybatis-plus.version>3.5.2</mybatis-plus.version>
        <poi.version>3.17</poi.version>
        <commons-collections.version>3.2.2</commons-collections.version>
        <transmittable-thread-local.version>2.13.2</transmittable-thread-local.version>
        <hutool.version>5.3.4</hutool.version>
        <mapstruct.version>1.3.1.Final</mapstruct.version>
        <jjwt.version>0.11.1</jjwt.version>
        <lombok.version>1.18.14</lombok.version>