Mybatis-plus找不到数据库分页方言(Dialect)入坑记

 

目录

一 环境

二 错误信息

三 出错原因

1. mybatis-plus源码分析 

2. 分页查询调用mapper.xml中sql时，传入page参数import com.baomidou.mybatisplus.extension.plugins.pagination.Page;造成

3. 测试单表分页使用Wrapper依然报错：

 

四 解决办法

1. 修改mapper.xml 加入statementType="CALLABLE"

2.修改jdbc驱动为postgresql驱动

3.动态设置DbType以及分页方言Dialect

---

一 环境

compile group: 'lib.greenplum', name: 'greenplum', version: '5.1.4'

compile group: 'com.baomidou', name: 'mybatis-plus-boot-starter',

二 错误信息

[ERROR] [2021-01-12 14:17:07] [http-nio-8003-exec-1] com.c3.radoo.odc.common.exception.handler.GlobalExceptionHandler  c.c.r.o.c.e.h.GlobalExceptionHandler - MyBatisSystemException:org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:   

### Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:  not support.  

### The error may exist in file [E:\gitData\ccc\odc\2.0-new\radoo_odc_all\cloud\admin\build\resources\main\mybatis\mapper\OdcSysRoleMapper.xml]  

### The error may involve defaultParameterMap  

### The error occurred while setting parameters  

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:  not support.,请求路径:/sys/role/list  

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:   

### Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:  not support.  

### The error may exist in file [E:\gitData\ccc\odc\2.0-new\radoo_odc_all\cloud\admin\build\resources\main\mybatis\mapper\OdcSysRoleMapper.xml]  

### The error may involve defaultParameterMap  

### The error occurred while setting parameters  

### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:  not support.  

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)  

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)  

at com.sun.proxy.$Proxy154.selectList(Unknown Source)  

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)  

at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177)  

at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)   

at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)  

at com.sun.proxy.$Proxy195.queryPage(Unknown Source)  

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  

at java.lang.reflect.Method.invoke(Method.java:498)  

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)  

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)  

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)  

at com.sun.proxy.$Proxy196.queryPage(Unknown Source)  

at com.c3.radoo.odc.admin.service.impl.OdcSysRoleServiceImpl.queryPage(OdcSysRoleServiceImpl.java:52)  

at com.c3.radoo.odc.admin.service.impl.OdcSysRoleServiceImpl$$FastClassBySpringCGLIB$$2acbb6f4.invoke(<generated>)  

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)  

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685)  

at com.c3.radoo.odc.admin.service.impl.OdcSysRoleServiceImpl$$EnhancerBySpringCGLIB$$b86eeb57.queryPage(<generated>)  

at com.c3.radoo.odc.admin.controller.OdcSysRoleController.queryPage(OdcSysRoleController.java:64)  

at com.c3.radoo.odc.admin.controller.OdcSysRoleController$$FastClassBySpringCGLIB$$e5dc92fa.invoke(<generated>)  

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)  

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)  

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)  

at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)  

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)  

at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)  

at com.c3.radoo.odc.common.aspect.OdcLogAspect.doAround(OdcLogAspect.java:98)  

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  

at java.lang.reflect.Method.invoke(Method.java:498)  

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)  

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)  

at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)  

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)  

at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:55)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)  

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)  

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)  

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)  

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)  

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)  

at com.c3.radoo.odc.admin.controller.OdcSysRoleController$$EnhancerBySpringCGLIB$$5d70271.queryPage(<generated>)  

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  

at java.lang.reflect.Method.invoke(Method.java:498)  

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)  

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)  

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)  

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879)  

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)  

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)  

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)  

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)  

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)  

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)  

at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)  

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)  

at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)  

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)  

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)  

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  

at com.github.xiaoymin.knife4j.spring.filter.SecurityBasicAuthFilter.doFilter(SecurityBasicAuthFilter.java:90)  

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  

at com.github.xiaoymin.knife4j.spring.filter.ProductionSecurityFilter.doFilter(ProductionSecurityFilter.java:53)  

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)  

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)  

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)  

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)  

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)  

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)  

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)  

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)  

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)  

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)  

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)  

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)  

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)  

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)  

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)  

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367)  

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)  

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)  

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639)  

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)  

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)  

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)  

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)  

at java.lang.Thread.run(Thread.java:748)  

三 出错原因

     1. mybatis-plus源码分析 

        DbTpye

        

        

        

2. 分页查询调用mapper.xml中sql时，传入page参数import com.baomidou.mybatisplus.extension.plugins.pagination.Page;造成

原始sql

<!--分页查询角色列表-->  
<select id="queryPage" resultType="com.common.entity.admin.SysRole"  >  
	select  
	 <include refid="Base_Column_List"/>,  
	 p.real_name  
	from sys_role r  
	left join sys_user u on  r.oper_user_id = u.id  
	left join sys_person p on  u.person_id = p.id  
	 <where>  
	     <if test=" keyword != null and keyword != '' ">  
	         AND r.role_name like '%'||#{keyword}||'%'  
	         or r.description like '%'||#{keyword}||'%'  
	     </if>  
	 </where>  
     order by r.role_name asc  
</select>  

3. 测试单表分页使用Wrapper依然报错：

@Override  
public Page<SysRole> queryPage(String keyword, Page<SysRole> page) {  
	   QueryWrapper<SysRole> queryWrapper = new QueryWrapper<>();  
	   queryWrapper.like(StringUtils.isNotBlank(keyword),"role_name",keyword);  
	   Page<SysRole> sysRolePage = baseMapper.selectPage(page, queryWrapper);  
	   return sysRolePage;  
}  

 

四 解决办法

1. 修改mapper.xml 加入statementType="CALLABLE"

<!--分页查询角色列表-->  
<select id="queryPage" resultType="com.common.entity.admin.SysRole" statementType="CALLABLE" >  
	select  
	 <include refid="Base_Column_List"/>,  
	 p.real_name  
	from sys_role r  
	left join sys_user u on  r.oper_user_id = u.id  
	left join sys_person p on  u.person_id = p.id  
	 <where>  
	     <if test=" keyword != null and keyword != '' ">  
	         AND r.role_name like '%'||#{keyword}||'%'  
	         or r.description like '%'||#{keyword}||'%'  
	     </if>  
	 </where>  
     order by r.role_name asc  
</select>  

2.修改jdbc驱动为postgresql驱动

	datasource:  
	    url: jdbc:postgresql://127.0.0.1:5452/test?stringtype=unspecified&useAffectedRows=true  
	    #driver-class-name: com.pivotal.jdbc.GreenplumDriver  
	    #jdbc-url: jdbc:pivotal:greenplum://127.0.0.1:5452;DatabaseName=test  
	    username: xxxxx  
	    password: xxxxx  
	    hikari:  
	      minimum-idle: 1  
	      maximum-pool-size: 5  
	  jpa:  
	    database: postgresql  
	    show-sql: true  

3.动态设置DbType以及分页方言Dialect

@Configuration
@MapperScan("com.**.dao")
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        DynamicEnumUtils.addEnum(DbType.class,"GREENPLUM",new Class<?>[]{String.class,String.class,String.class},new Object[] {
            "greenplum", "greenplum数据库" , "com.baomidou.mybatisplus.extension.plugins.pagination.dialects.PostgreDialect"
        });
        paginationInterceptor.setDialect(new PostgreDialect());
        paginationInterceptor.setDbType(DbType.valueOf("GREENPLUM"));
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
}

 枚举类动态赋值

 

package com.common.utils;

import sun.reflect.ConstructorAccessor;
import sun.reflect.FieldAccessor;
import sun.reflect.ReflectionFactory;

import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class DynamicEnumUtils {

    private static ReflectionFactory reflectionFactory = ReflectionFactory.getReflectionFactory();

    private static void setFailsafeFieldValue(Field field, Object target, Object value) throws NoSuchFieldException,
            IllegalAccessException {

        // let's make the field accessible
        field.setAccessible(true);

        // next we change the modifier in the Field instance to
        // not be final anymore, thus tricking reflection into
        // letting us modify the static final field
        Field modifiersField = Field.class.getDeclaredField("modifiers");
        modifiersField.setAccessible(true);
        int modifiers = modifiersField.getInt(field);

        // blank out the final bit in the modifiers int
        modifiers &= ~Modifier.FINAL;
        modifiersField.setInt(field, modifiers);

        FieldAccessor fa = reflectionFactory.newFieldAccessor(field, false);
        fa.set(target, value);
    }

    private static void blankField(Class<?> enumClass, String fieldName) throws NoSuchFieldException,
            IllegalAccessException {
        for (Field field : Class.class.getDeclaredFields()) {
            if (field.getName().contains(fieldName)) {
                AccessibleObject.setAccessible(new Field[]{field}, true);
                setFailsafeFieldValue(field, enumClass, null);
                break;
            }
        }
    }

    private static void cleanEnumCache(Class<?> enumClass) throws NoSuchFieldException, IllegalAccessException {
        blankField(enumClass, "enumConstantDirectory"); // Sun (Oracle?!?) JDK 1.5/6
        blankField(enumClass, "enumConstants"); // IBM JDK
    }

    private static ConstructorAccessor getConstructorAccessor(Class<?> enumClass, Class<?>[] additionalParameterTypes)
            throws NoSuchMethodException {
        Class<?>[] parameterTypes = new Class[additionalParameterTypes.length + 2];
        parameterTypes[0] = String.class;
        parameterTypes[1] = int.class;
        System.arraycopy(additionalParameterTypes, 0, parameterTypes, 2, additionalParameterTypes.length);
        return reflectionFactory.newConstructorAccessor(enumClass.getDeclaredConstructor(parameterTypes));
    }

    private static Object makeEnum(Class<?> enumClass, String value, int ordinal, Class<?>[] additionalTypes,
                                   Object[] additionalValues) throws Exception {
        Object[] parms = new Object[additionalValues.length + 2];
        parms[0] = value;
        parms[1] = Integer.valueOf(ordinal);
        System.arraycopy(additionalValues, 0, parms, 2, additionalValues.length);
        return enumClass.cast(getConstructorAccessor(enumClass, additionalTypes).newInstance(parms));
    }

    /**
     * Add an enum instance to the enum class given as argument
     *
     * @param <T>      the type of the enum (implicit)
     * @param enumType the class of the enum to be modified
     * @param enumName the name of the new enum instance to be added to the class.
     */
    @SuppressWarnings("unchecked")
    public static <T extends Enum<?>> T addEnum(Class<T> enumType, String enumName, Class<?>[] additionalTypes, Object[] additionalValues) {

        // 0. Sanity checks
        if (!Enum.class.isAssignableFrom(enumType)) {
            throw new RuntimeException("class " + enumType + " is not an instance of Enum");
        }

        // 1. Lookup "$VALUES" holder in enum class and get previous enum instances
        Field valuesField = null;
        Field[] fields = enumType.getDeclaredFields();
        for (Field field : fields) {
            if (field.getName().contains("$VALUES")) {
                valuesField = field;
                break;
            }
        }
        AccessibleObject.setAccessible(new Field[]{valuesField}, true);

        try {

            // 2. Copy it
            if (valuesField == null) {
                throw new RuntimeException("valuesField is null");
            }
            T[] previousValues = (T[]) valuesField.get(enumType);
            List<T> values = new ArrayList<T>(Arrays.asList(previousValues));

            // 3. build new enum
            T newValue = (T) makeEnum(enumType, enumName, values.size(), additionalTypes, additionalValues);

            // 4. add new value
            values.add(newValue);

            // 5. Set new values field
            setFailsafeFieldValue(valuesField, null, values.toArray((T[]) Array.newInstance(enumType, 0)));

            // 6. Clean enum cache
            cleanEnumCache(enumType);

            return newValue;

        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }


    }
}