SpringBoot多种数据源

数据库：Oracle +SqlServer
持久层框架：Mybatis Plus + Jpa
1，pom文件

<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- Spring-Data-Redis-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <!--web模块-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--Spring-Data-JPA-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- MybatisPlus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!--Oracle驱动包-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0</version>
        </dependency>
        <!-- SqlServer驱动包 -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
    </dependencies>

2，application配置文件

spring:
 #JPA
 jpa:
  show-sql: true
  open-in-view: true
  hibernate:
   ddl-auto: none
  #数据源
 datasource:
   #Oracle数据库(主数据库)
   master:
    jdbc-url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
    username: 用户名
    password: 密码
    driver-class-name: oracle.jdbc.OracleDriver
   #从数据库(sql Server数据库)
   slave:
    jdbc-url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=NewBug
    username: 用户名
    password: 密码
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
   hikari:
    #数据库连接超时时间
    connection-timeout: 60000
    #控制池中连接的最长生命周期
    max-lifetime: 60000
    #最大连接数
    maximum-pool-size: 60
    #连接允许在连接池闲置的时间
    idle-timeout: 20000
    #验证与数据库连接的有效时间(每隔这么多时间就要去验证一次与数据库的时间是否有效)
    validation-timeout: 3000
    #心跳检测
    connection-test-query: SELECT 1 

# Mybatis-plus
mybatis-plus:
  # 放在resource目录 classpath:/mapper/*Mapper.xml
  mapper-locations: classpath:mapper/*Mapper.xml
  global-config:
    # 主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
    id-type: 2
    # 字段策略 0:"忽略判断",1:"非 NULL 判断",2:"非空判断"
    field-strategy: 2
    # 驼峰下划线转换
    db-column-underline: false
    # 刷新mapper 调试神器
    refresh-mapper: true
    # SQL 解析缓存，开启后多租户 @SqlParser 注解生效
    sql-parser-cache: true
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false
    # 配置JdbcTypeForNull, oracle数据库必须配置
    jdbc-type-for-null: 'null'
    #用于打印mybatis的sql语句日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl    

3，代码

• Mybatis Plus 多种数据配置

1，MyDataSource注解

/**
 * 数据源注解
 */
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public @interface MyDataSource {
    String value() default "";
}

2，DatasourceConstants类

/**
 * 数据源常量
 */
public class DatasourceConstants {
    public static final String DATASOURCE1 = "master";
    public static final String DATASOURCE2 = "slave";
}

3，DataSourceContextHolder类

/**
 * 数据源容器
 */
public class DataSourceContextHolder {

    public static final ThreadLocal<String> HOLDER = new ThreadLocal<>();

    /**
     * 获取数据源名
     * @return
     */
    public static String getDataSource(){
        return HOLDER.get();
    }

    /**
     * 设置数据源名
     * @param dataSourceName
     */
    public static void setDataSource(String dataSourceName){
        HOLDER.set(dataSourceName);
    }

    /**
     * 移除数据源名
     */
    public  static void removeDataSource(){
        HOLDER.remove();
    }
}

4，DynamicDataSourceAspect类

/**
 * 数据源切面  需要在事务@Transactional之前运行
 */
@Component
@Aspect
@Order(-1)
public class DynamicDataSourceAspect {

    //切点表达式
    @Pointcut("@annotation(com.*.*.common.annotation.MyDataSource)")
    public void  pointcut(){

    }

    @Before("pointcut()")
    public void  beforeMethod(JoinPoint joinPoint) throws NoSuchMethodException {
        //获取class对象
        Class<?> clazz = joinPoint.getTarget().getClass();
        //获取方法名
        String methodName = joinPoint.getSignature().getName();
        //获取方法参数类型
        Class[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getParameterTypes();
        //获取到Method类
        Method method = clazz.getMethod(methodName, parameterTypes);
        //获取到注解
        MyDataSource annotation = method.getAnnotation(MyDataSource.class);
        //获取注解上的值
        String dataSourceName = annotation.value();
        //设置数据源
        DataSourceContextHolder.setDataSource(dataSourceName);
    }

    @After("pointcut()")
    public void afterSwitchDS(JoinPoint joinPoint) {
        DataSourceContextHolder.removeDataSource();
    }
}

5，MasterDataSourcePropertise（主数据源）

/**
 * Oracle数据库配置文件
 */
@ConfigurationProperties(prefix="spring.datasource.master")
@Component
@Data
public class MasterDataSourcePropertise {

    private String jdbcUrl;

    private String username;

    private String password;

    private String driverClassName;
}

6，SlaveDataSourcePropertise（从数据源）

/**
 * sqlServer数据库配置文件
 */

@Component
@Data
@ConfigurationProperties(prefix="spring.datasource.slave")
public class SlaveDataSourcePropertise {

    private String jdbcUrl;

    private String username;

    private String password;

    private String driverClassName;
}

7，DynamicDataSource（动态数据源获取）

/**
 * 获取动态数据源
 */

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}

8，MybatisPlusConfig

/**
 * 定义MybatisPlusConfig配置类,该类是注册PaginationInterceptor
 */
@Configuration
//扫描mapper接口
@MapperScan("com.*.*.modules.dao.mapper")
public class MybatisPlusConfig {

    @Autowired
    private MasterDataSourcePropertise masterDataSourcePropertise;

    @Autowired
    private SlaveDataSourcePropertise slaveDataSourcePropertise;

    /**
     * 分页插件，自动识别数据库类型,注册PaginationInterceptor
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

    @Bean
    public DataSource master() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(masterDataSourcePropertise.getDriverClassName());
        dataSourceBuilder.username(masterDataSourcePropertise.getUsername());
        dataSourceBuilder.password(masterDataSourcePropertise.getPassword());
        dataSourceBuilder.url(masterDataSourcePropertise.getJdbcUrl());
        return dataSourceBuilder.build();
    }

    @Bean
    public DataSource slave() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(slaveDataSourcePropertise.getDriverClassName());
        dataSourceBuilder.username(slaveDataSourcePropertise.getUsername());
        dataSourceBuilder.password(slaveDataSourcePropertise.getPassword());
        dataSourceBuilder.url(slaveDataSourcePropertise.getJdbcUrl());
        return dataSourceBuilder.build();
    }

    /**
     * 动态数据源配置
     * @return
     */
    @Bean
    @Primary
    public DataSource setDynamicDataSource( DataSource master,  DataSource slave) {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        //设置默认的数据源,不设置会报错
        dynamicDataSource.setDefaultTargetDataSource(master);
        //配置多数据源,加入其他数据源到map中value
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DatasourceConstants.DATASOURCE1, master);
        targetDataSources.put(DatasourceConstants.DATASOURCE2, slave);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        return dynamicDataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(setDynamicDataSource(master() , slave()));
        //数据库相关设置
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        //添加分页功能(由于配置了多数据源,默认的分页不起作用,需重新添加),在此可以添加数据权限过滤器
        sqlSessionFactory.setPlugins(new Interceptor[]{paginationInterceptor()});
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        sqlSessionFactory.setConfiguration(configuration);
        return sqlSessionFactory.getObject();
    }
}

至此Mybatis Plus多种数据源配置完成了，下面我们再说一下JPA的多种数据源配置

• JPA多种数据配置

1，DataSourceConfig

/**
 * jpa的数据源
 */
@Configuration
public class DataSourceConfig {

    @Autowired
    private MasterDataSourcePropertise masterDataSourcePropertise;

    @Autowired
    private SlaveDataSourcePropertise slaveDataSourcePropertise;

    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    public DataSource primaryDataSource() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(masterDataSourcePropertise.getDriverClassName());
        dataSourceBuilder.username(masterDataSourcePropertise.getUsername());
        dataSourceBuilder.password(masterDataSourcePropertise.getPassword());
        dataSourceBuilder.url(masterDataSourcePropertise.getJdbcUrl());
        return dataSourceBuilder.build();
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    public DataSource secondaryDataSource() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(slaveDataSourcePropertise.getDriverClassName());
        dataSourceBuilder.username(slaveDataSourcePropertise.getUsername());
        dataSourceBuilder.password(slaveDataSourcePropertise.getPassword());
        dataSourceBuilder.url(slaveDataSourcePropertise.getJdbcUrl());
        return dataSourceBuilder.build();
    }
}

2，PrimaryConfig

/**
 * jpa主数据源的配置
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactoryPrimary",
        transactionManagerRef="transactionManagerPrimary",
        basePackages= { "com.*.*.modules.dao.jpa" })//设置Repository所在位置
public class PrimaryConfig {

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private HibernateProperties hibernateProperties;

    private Map<String, Object> getVendorProperties() {
        Map<String,String> properties = new HashMap<>();
        properties.put("hibernate.dialect","org.hibernate.dialect.Oracle10gDialect");
        jpaProperties.setDatabase(Database.ORACLE);
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);
        jpaVendorAdapter.setShowSql(true);
        LocalContainerEntityManagerFactoryBean factoryBean  = builder
                .dataSource(primaryDataSource)
                .packages("com.*.*.modules.entity.bean") //设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
        factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        return factoryBean;
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

3，SecondaryConfig

/**
 * jpa次数据源的配置
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= {"com.*.*.modules.dao.jpa.slave"}) //设置Repository所在位置
public class SecondaryConfig {

    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private HibernateProperties hibernateProperties;

    private Map<String, Object> getVendorProperties() {
        Map<String,String> properties = new HashMap<>();
        properties.put("hibernate.dialect","org.hibernate.dialect.SQLServer2008Dialect");
        jpaProperties.setDatabase(Database.SQL_SERVER);
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) {
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(false);
        jpaVendorAdapter.setShowSql(true);
        LocalContainerEntityManagerFactoryBean factoryBean  = builder
                .dataSource(secondaryDataSource)
                .packages("com.*.*.modules.entity.pojo") //设置实体类所在位置
                .persistenceUnit("secondaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
        factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        return factoryBean;
    }

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }

}

JPA的配置比Mybatis Plus就少了很多，值得注意的是，如果需要实体类映射自动生成表，将代码中jpaVendorAdapter.setGenerateDdl(true)，如果不想自动生成表，将其设置为false。
至此，SpringBoot多种数据源，并且使用Mybatis Plus和Spring Data JPA的配置全部完成了。