MySQL8导致SQL查询需要区分大小写处理方案

SpringBoot + Mybatis + activiti6 + MySQL8

在linux环境中，MySQL8版本一旦创建成功是不能调整配置my.ini(大小写不敏感)

lower_case_table_names = 1

重启MySQL就会报错

然activiti默认查询是大写表名, 数据库导入是小写表名, 导致SpringBoot包启动报错：表不存在

处理方案如下：

参考:

https://blog.csdn.net/image_2012/article/details/108387022

1. 利用拦截器

package com.luan.iterceptor;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.Properties;

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

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = statementHandler.getBoundSql();
        //获取到原始sql语句
        String sql = boundSql.getSql();
        sql = convertTableNameToLower(sql);
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, sql);
        return invocation.proceed();
    }

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


    private String convertTableNameToLower(String sql) {
        //TODO 对sql进行处理
        return sql.toLowerCase();
    }


    @Override
    public void setProperties(Properties properties) {

    }
}

　　2.创建在自定义处理中上下文添加拦截器

package com.luan.activiti.config;

import com.luan.iterceptor.MySqlInterceptor;
import org.activiti.engine.cfg.AbstractProcessEngineConfigurator;
import org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl;
import org.apache.ibatis.session.SqlSessionFactory;

public class MyProcessEngineConfigurator extends AbstractProcessEngineConfigurator {

    @Override
    public void configure(ProcessEngineConfigurationImpl processEngineConfiguration) {

        SqlSessionFactory sqlSessionFactory = processEngineConfiguration.getSqlSessionFactory();
        //这里是自定义的MySqlInterceptor，跟上文一样
        sqlSessionFactory.getConfiguration().addInterceptor(new MySqlInterceptor());

    }

}

　　3.在流程引擎配置中加入自定义处理配置（MyProcessEngineConfigurator）

package com.luan.activiti.config;

import org.activiti.engine.cfg.ProcessEngineConfigurator;
import org.activiti.spring.SpringProcessEngineConfiguration;
import org.activiti.spring.boot.ProcessEngineConfigurationConfigurer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

@Configuration
public class ActivitiConfig implements ProcessEngineConfigurationConfigurer {

    @Autowired
    private ICustomProcessDiagramGenerator customProcessDiagramGenerator;

    /**
     * 解決工作流生成图片乱码问题
     *
     * @param processEngineConfiguration
     */
    @Override
    public void configure(SpringProcessEngineConfiguration processEngineConfiguration) {
        processEngineConfiguration.setActivityFontName("宋体");
        processEngineConfiguration.setAnnotationFontName("宋体");
        processEngineConfiguration.setLabelFontName("宋体");
        processEngineConfiguration.setProcessDiagramGenerator(customProcessDiagramGenerator);

        List<ProcessEngineConfigurator> list = new ArrayList<>();
        list.add(new MyProcessEngineConfigurator());
        processEngineConfiguration.setConfigurators(list);
    }
}