api/src/main/java/com/xjrsoft/config/MybatisPlusConfig.java

package com.xjrsoft.config;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.GlobalDbConfig;
import com.baomidou.dynamic.datasource.provider.AbstractJdbcDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.druid.DruidConfig;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.BlockAttackInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.xjrsoft.common.constant.GlobalConstant;
import com.xjrsoft.common.handler.MyLocalDateTimeTypeHandler;
import com.xjrsoft.common.handler.XjrLocalTimeTypeHandler;
import com.xjrsoft.common.interceptor.DataScopeInnerInterceptor;
import com.xjrsoft.common.utils.DatasourceUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BooleanTypeHandler;
import org.apache.ibatis.type.EnumTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.ssssssss.magicapi.datasource.model.MagicDynamicDataSource;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


/**
 * mybatis-plus配置
 *
 * @author tzx
 */
@Slf4j
@Configuration
public class MybatisPlusConfig {

    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;

    private final Map<String, String> datasourceMap = new HashMap<>();


    /**
     * 分页插件
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }

    /**
     * 数据权限插件
     */
    @Bean
    public MybatisPlusInterceptor dataScopeInnerInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new DataScopeInnerInterceptor());
        return interceptor;
    }

    /**
     * 防止全表更新与删除
     */
    @Bean
    public MybatisPlusInterceptor blockAttackInnerInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());
        return interceptor;
    }

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        GlobalDbConfig.setCaseInsensitive(false);    // 自定义表单hutool包配置，查询表单发布的菜单数据，设置返回的字段名大小写敏感
        DataSourceProperty datasource = dynamicDataSourceProperties.getDatasource().get(GlobalConstant.DEFAULT_DATASOURCE_KEY);
        return new AbstractJdbcDataSourceProvider(datasource.getDriverClassName(), datasource.getUrl(), datasource.getUsername(), datasource.getPassword()) {

            @Override
            protected Map<String, DataSourceProperty> executeStmt(Statement statement) throws SQLException {
                Map<String, DataSourceProperty> map = new HashMap<>(16);

                // 数据库里的所有库
                ResultSet rs = statement.executeQuery("SELECT * FROM xjr_databaselink WHERE delete_mark = 0 AND enabled_mark = 1");
                while (rs.next()) {
                    long id = rs.getLong("id");
                    String host = rs.getString("host");
                    String username = rs.getString("username");
                    String password = rs.getString("password");
                    String driver = rs.getString("driver");
                    String dbType = rs.getString("db_type");

                    //缓存起来
                    datasourceMap.put(Convert.toStr(id), rs.getString("db_name"));

                    DataSourceProperty property = new DataSourceProperty();
                    property.setUsername(username);
                    property.setPassword(password);
                    property.setUrl(host);
                    property.setDriverClassName(driver);
                    // 测试连接，如果连接不上则跳过，避免启动失败
                    if (!DatasourceUtil.testConnection(host, username, password)) {
                        continue;
                    }

                    DruidConfig druidConfig = property.getDruid();
                    druidConfig.setInitialSize(10); // 初始化大小
                    druidConfig.setMaxActive(100);// 最大连接池
                    druidConfig.setMinIdle(10);// 最小连接池
                    druidConfig.setMaxWait(60000); //最大等待超时时间
                    druidConfig.setPoolPreparedStatements(false); // 是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
                    druidConfig.setMaxPoolPreparedStatementPerConnectionSize(20);//是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
                    druidConfig.setTimeBetweenEvictionRunsMillis(60000L);// 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
                    druidConfig.setMinEvictableIdleTimeMillis(300000L); //  配置一个连接在池中最小生存的时间，单位是毫秒
                    if (StrUtil.equals(dbType, "oracle")) {
                        druidConfig.setValidationQuery("SELECT 1 FROM DUAL");
                    } else {
                        druidConfig.setValidationQuery("SELECT 1 "); //测试链接 语句
                    }
                    druidConfig.setTestWhileIdle(true);
                    druidConfig.setTestOnReturn(false);
                    druidConfig.setTestOnBorrow(true);
                    druidConfig.setFilters("stat,slf4j"); // #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
                    druidConfig.setUseGlobalDataSourceStat(true);
                    Properties properties = new Properties();
                    properties.put("druid.stat.mergeSql", true); //打开mergeSql功能
                    properties.put("druid.stat.slowSqlMillis", true); // 打开慢sql 记录功能
                    druidConfig.setConnectionProperties(properties);

                    map.put(Long.toString(id), property);

                }
                // yml配置的数据源
                Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
                for (DataSourceProperty dataSourceProperty : datasourceMap.values()) {
                    // 测试连接，如果连接不上则跳过，避免启动失败
                    if (!DatasourceUtil.testConnection(dataSourceProperty.getUrl(), dataSourceProperty.getUsername(), dataSourceProperty.getPassword())) {
                        continue;
                    }

                    DruidConfig druidConfig = dataSourceProperty.getDruid();
                    druidConfig.setInitialSize(10); // 初始化大小
                    druidConfig.setMaxActive(100);// 最大连接池
                    druidConfig.setMinIdle(10);// 最小连接池
                    druidConfig.setMaxWait(60000); //最大等待超时时间
                    druidConfig.setValidationQueryTimeout(10);//是检测连接是否有效的超时时间，单位：秒
                    druidConfig.setPoolPreparedStatements(false); // 是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
                    druidConfig.setMaxPoolPreparedStatementPerConnectionSize(20);//是否缓存preparedStatement，也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
                    druidConfig.setTimeBetweenEvictionRunsMillis(2000L);// 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
                    druidConfig.setMinEvictableIdleTimeMillis(300000L); //  配置一个连接在池中最小生存的时间，单位是毫秒
                    if (dataSourceProperty.getUrl().contains("oracle")) {
                        druidConfig.setValidationQuery("SELECT 1 FROM DUAL"); //测试链接 如果是oracle 语句不一样
                    } else {
                        druidConfig.setValidationQuery("SELECT 1 "); //测试链接 语句
                    }
                    druidConfig.setTestWhileIdle(true);
                    druidConfig.setTestOnReturn(false);
                    druidConfig.setTestOnBorrow(false);
                    druidConfig.setFilters("stat,slf4j"); // #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
                    druidConfig.setUseGlobalDataSourceStat(true);
                    Properties properties = new Properties();
                    properties.put("druid.stat.mergeSql", true); //打开mergeSql功能
                    properties.put("druid.stat.slowSqlMillis", true); // 打开慢sql 记录功能
                    druidConfig.setConnectionProperties(properties);
                }
                map.putAll(datasourceMap);

                rs.close();
                return map;
            }
        };
    }

    /**
     * 配置MagicApi多数据源
     *
     * @return
     */
    @Bean
    public MagicDynamicDataSource magicDynamicDataSource() {

        MagicDynamicDataSource dynamicDataSource = new MagicDynamicDataSource();

        Map<String, DataSource> dataSources = dynamicDataSourceProvider().loadDataSources();
        for (String ds : dataSources.keySet()) {
            if (StrUtil.equals(GlobalConstant.DEFAULT_DATASOURCE_KEY, ds)) {
                dynamicDataSource.setDefault(dataSources.get(ds));
            } else {

                //缓存起来的name
                if (datasourceMap.containsKey(ds)) {
                    String name = MapUtil.getStr(datasourceMap, ds);
                    dynamicDataSource.add(name, dataSources.get(ds));
                }
                //配置在yml上的  可能获取不到name
                else {
                    dynamicDataSource.add("datasource_" + ds, dataSources.get(ds));
                }

            }
        }

        return dynamicDataSource;
    }

    /**
     * MybatisPlus数据库类型处理
     * @return
     */
    @Bean
    public ConfigurationCustomizer getConfig(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(MybatisConfiguration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
                configuration.setJdbcTypeForNull(JdbcType.NULL);
                configuration.getTypeAliasRegistry().registerAlias("EnumTypeHandler", EnumTypeHandler.class);
                configuration.getTypeAliasRegistry().registerAlias("BooleanTypeHandler", BooleanTypeHandler.class);
                configuration.getTypeAliasRegistry().registerAlias("AbstractJsonTypeHandler", AbstractJsonTypeHandler.class);
                configuration.getTypeHandlerRegistry().register(LocalDateTime.class, MyLocalDateTimeTypeHandler.class);
                configuration.getTypeHandlerRegistry().register(LocalTime.class, XjrLocalTimeTypeHandler.class);
            }
        };
    }
}