【springboot整合多数据源】

配置多数据

application.yaml

spring:
  application:
    name: erp9-syn
  aop:
    proxy-target-class: true
    auto: true
  main:
    allow-bean-definition-overriding: true

  datasource:
    erp9:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.80.211:3306/1231?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8&allowPublicKeyRetrieval=true
        username: b1b_goods
        password: asdad
        initialSize: 10
        minIdle: 10
        maxActive: 80
        type: com.alibaba.druid.pool.DruidDataSource

    rate:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://101.200.55.149:6522;Databasename=sacsac
      username: ascac
      password: 121sads
      initialSize: 5
      minIdle: 5
      maxActive: 40
      type: com.alibaba.druid.pool.DruidDataSource
# 日志配置
mybatis-plus:
  mapper-locations: classpath:xml/**/*Mapper.xml
  typeAliasesPackage: com.yuanda.erp9.syn.entity
  configuration:
    map-underscore-to-camel-case: true
    # console输出日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

动态路由数据源

package com.yuanda.erp9.syn.config;

import lombok.extern.slf4j.Slf4j;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * 动态路由数据源
 */
@Order(1)
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        String datasource = DynamicDataSourceContextHolder.getDataSourceKey();
        super.setDefaultTargetDataSource(datasource);
        log.debug("使用数据源 {}", datasource);
        return datasource;
    }
}

数据源上下文管理

package com.yuanda.erp9.syn.config;

import com.yuanda.erp9.syn.contant.DatabaseGlobal;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @ClassName DynamicDataSourceContextHolder
 * @Description  数据上下文管理
 * @Date 2022/12/15
 * @Author myq
 */
public class DynamicDataSourceContextHolder {
    /**
     * 实际上就是开启多个线程，每个线程进行初始化一个数据源
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<String>() {

        /**
         * 将 master 数据源的 key作为默认数据源的 key
         */
        @Override
        protected String initialValue() {
            return DatabaseGlobal.erp9;
        }
    };


    /**
     * 数据源的 key集合，用于切换时判断数据源是否存在
     */
    public static List<Object> dataSourceKeys = new ArrayList<Object>();

    /**
     * 切换数据源
     *
     * @param key
     */
    public static void setDataSourceKey(String key) {
        CONTEXT_HOLDER.set(key);
    }

    /**
     * 获取数据源
     *
     * @return
     */
    public static String getDataSourceKey() {
        return CONTEXT_HOLDER.get();
    }

    /**
     * 重置数据源
     */
    public static void clearDataSourceKey() {
        CONTEXT_HOLDER.remove();
    }

    /**
     * 判断是否包含数据源
     *
     * @param key 数据源key
     * @return
     */
    public static boolean containDataSourceKey(String key) {
        return dataSourceKeys.contains(key);
    }

    /**
     * 添加数据源keys
     *
     * @param keys
     * @return
     */
    public static boolean addDataSourceKeys(Collection<? extends Object> keys) {
        return dataSourceKeys.addAll(keys);
    }
}

数据源切面

package com.yuanda.erp9.syn.config;

import com.yuanda.erp9.syn.contant.DatabaseGlobal;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Component
@Order(value = -100)
@Aspect
@Slf4j
public class DataSourceAspect {
    @Pointcut("execution( * com.yuanda.erp9.syn.service.erp9.impl..*.*(..))")
    private void erp9Aspect() {
    }

    @Pointcut("execution(* com.yuanda.erp9.syn.service.rate.impl..*.*(..))")
    private void rateAspect() {
    }

    @Before("erp9Aspect()")
    public void cyz() {
        log.debug("切换到{} 数据源...", DatabaseGlobal.erp9);
        DynamicDataSourceContextHolder.setDataSourceKey(DatabaseGlobal.erp9);
    }

    @Before("rateAspect()")
    public void pveStandard() {
        log.debug("切换到{} 数据源...", DatabaseGlobal.rate);
        DynamicDataSourceContextHolder.setDataSourceKey(DatabaseGlobal.rate);
    }
}

MybatisPlusConfig配置

package com.yuanda.erp9.syn.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.wall.WallConfig;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.yuanda.erp9.syn.contant.DatabaseGlobal;
import com.yuanda.erp9.syn.core.CustomizedSqlInjector;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @ClassName MybatisPlusConfig
 * @Description 配置文件-自定义sql注入器
 * @Date 2022/11/16
 * @Author myq
 */
@MapperScan({"com.yuanda.erp9.syn.mapper.erp9", "com.yuanda.erp9.syn.mapper.rate"})
@Configuration
@EnableTransactionManagement
@ConfigurationProperties(prefix = "mybatis-plus")
public class MybatisPlusConfig {
	
	/**
	* 可以写死 可以冲配置文件中读取
	*/
    private Class logImpl;

    static {
        System.setProperty("druid.mysql.usePingMethod","false");
    }


    @Bean
    public CustomizedSqlInjector customizedSqlInjector() {
        return new CustomizedSqlInjector();
    }

	/**
	*  这个bean可以帮助我们一次执行多条dml语句，没有哦这个配置会出现执行多次sql会出现报错。
	*/
    @Bean
    public WallConfig wallConfig() {
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);//允许一次执行多条语句
        config.setNoneBaseStatementAllow(true);//允许非基本语句的其他语句
        return config;
    }

    @Bean(name = DatabaseGlobal.erp9)
    @ConfigurationProperties(prefix = "spring.datasource.erp9")
    public DataSource erp9() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = DatabaseGlobal.rate)
    @ConfigurationProperties(prefix = "spring.datasource.rate")
    public DataSource rate() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 动态数据源配置
     *
     * @return
     */
    @Bean
    @Primary
    public DataSource multipleDataSource(
            @Qualifier(DatabaseGlobal.erp9) DataSource erp9DataSource,
            @Qualifier(DatabaseGlobal.rate) DataSource rateDataSource) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        targetDataSources.put(DatabaseGlobal.erp9, erp9DataSource);
        targetDataSources.put(DatabaseGlobal.rate, rateDataSource);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        // 程序默认数据源，这个要根据程序调用数据源频次，经常把常调用的数据源作为默认
        dynamicDataSource.setDefaultTargetDataSource(erp9DataSource);
        return dynamicDataSource;
    }


    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier(DatabaseGlobal.erp9) DataSource erp9DataSource,
                                               @Qualifier(DatabaseGlobal.rate) DataSource rateDataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        // 多数据源必须设置
        sqlSessionFactory.setMapperLocations(new						PathMatchingResourcePatternResolver().getResources("classpath:xml/**/*Mapper.xml"));
        sqlSessionFactory.setDataSource(multipleDataSource(erp9DataSource, rateDataSource));
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        configuration.setLogImpl(logImpl);
        sqlSessionFactory.setGlobalConfig(globalConfiguration());
        sqlSessionFactory.setConfiguration(configuration);
        return sqlSessionFactory.getObject();
    }

    @Bean
    public GlobalConfig globalConfiguration() {
        GlobalConfig conf = new GlobalConfig();
        // 自定义的注入需要在这里进行配置
        conf.setSqlInjector(customizedSqlInjector());
        return conf;
    }


}

自定义方法注入器

package com.yuanda.erp9.syn.core;

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * 自定义方法SQL注入器
 */
@Component
public class CustomizedSqlInjector extends DefaultSqlInjector {
    /**
     * 如果只需增加方法，保留mybatis plus自带方法，
     * 可以先获取super.getMethodList()，再添加add
     */
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        ## 这俩是mybatisPlus实现的批量更新操作 也可以注释掉
        methodList.add(new InsertBatchMethod());
        methodList.add(new UpdateBatchMethod());
        return methodList;
    }
}

扩展配置，帮助我们批量更新、新增等操作

RootMapper

package com.yuanda.erp9.syn.core;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;
 
import java.util.List;
 
/**
 * 根Mapper，给表Mapper继承用的，可以自定义通用方法
 * {@link com.baomidou.mybatisplus.core.mapper.BaseMapper}
 * {@link com.baomidou.mybatisplus.extension.service.IService}
 * {@link com.baomidou.mybatisplus.extension.service.impl.ServiceImpl}
 */
public interface RootMapper<T> extends BaseMapper<T> {
 
    /**
     * 自定义批量插入
     * 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一
     */
    int insertBatch(@Param("list") List<T> list);
 
    /**
     * 自定义批量更新，条件为主键
     * 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一
     */
    int updateBatch(@Param("list") List<T> list);
 
}

InsertBatchMethod

package com.yuanda.erp9.syn.core;

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
 
/**
 * 批量插入方法实现
 */
@Slf4j
public class InsertBatchMethod extends AbstractMethod {
    /**
     * insertBatch into user(id, name, age) values (1, "a", 17), (2, "b", 18);
     <script>
     insertBatch into user(id, name, age) values
     <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
     #{item.id}, #{item.name}, #{item.age}
     </foreach>
     </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldSql = prepareFieldSql(tableInfo);
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
        log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        // 第三个参数必须和RootMapper的自定义方法名一致
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);
    }
 
    private String prepareFieldSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        fieldSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> {
            fieldSql.append(x.getColumn()).append(",");
        });
        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
        fieldSql.insert(0, "(");
        fieldSql.append(")");
        return fieldSql.toString();
    }
 
    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
        valueSql.delete(valueSql.length() - 1, valueSql.length());
        valueSql.append("</foreach>");
        return valueSql.toString();
    }
}

UpdateBatchMethod

package com.yuanda.erp9.syn.core;

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
 
/**
 * 批量更新方法实现，条件为主键，选择性更新
 */
@Slf4j
public class UpdateBatchMethod extends AbstractMethod {
    /**
     * update user set name = "a", age = 17 where id = 1;
     * update user set name = "b", age = 18 where id = 2;
     <script>
     <foreach collection="list" item="item" separator=";">
     update user
     <set>
     <if test="item.name != null and item.name != ''">
     name = #{item.name,jdbcType=VARCHAR},
     </if>
     <if test="item.age != null">
     age = #{item.age,jdbcType=INTEGER},
     </if>
     </set>
     where id = #{item.id,jdbcType=INTEGER}
     </foreach>
     </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sql = "<script>\n<foreach collection=\"list\" item=\"item\" separator=\";\">\nupdate %s %s where %s=#{%s} %s\n</foreach>\n</script>";
        String additional = tableInfo.isWithVersion() ? tableInfo.getVersionFieldInfo().getVersionOli("item", "item.") : "" + tableInfo.getLogicDeleteSql(true, true);
        String setSql = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "item", "item.");
        String sqlResult = String.format(sql, tableInfo.getTableName(), setSql, tableInfo.getKeyColumn(), "item." + tableInfo.getKeyProperty(), additional);
        log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        // 第三个参数必须和RootMapper的自定义方法名一致
        return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);
    }
 
}