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>
Rust编程语言群 1036955113
java新手自学群 626070845
java/springboot/hadoop/JVM 群 4915800
Hadoop/mongodb(搭建/开发/运维)Q群481975850
GOLang Q1群:6848027
GOLang Q2群:450509103
GOLang Q3群:436173132
GOLang Q4群:141984758
GOLang Q5群:215535604
C/C++/QT群 1414577
单片机嵌入式/电子电路入门群群 306312845
MUD/LIB/交流群 391486684
Electron/koa/Nodejs/express 214737701
大前端群vue/js/ts 165150391
操作系统研发群:15375777
汇编/辅助/破解新手群:755783453
大数据 elasticsearch 群 481975850
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
java新手自学群 626070845
java/springboot/hadoop/JVM 群 4915800
Hadoop/mongodb(搭建/开发/运维)Q群481975850
GOLang Q1群:6848027
GOLang Q2群:450509103
GOLang Q3群:436173132
GOLang Q4群:141984758
GOLang Q5群:215535604
C/C++/QT群 1414577
单片机嵌入式/电子电路入门群群 306312845
MUD/LIB/交流群 391486684
Electron/koa/Nodejs/express 214737701
大前端群vue/js/ts 165150391
操作系统研发群:15375777
汇编/辅助/破解新手群:755783453
大数据 elasticsearch 群 481975850
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。