Spring Boot 整合多数据源

运行环境

• Spring Boot 2.4.0
• MySQL
• Oracle

配置文件

注意：必须使用jdbc-url，否则会报jdbcUrl is required with driverClassName。

spring:
  mysql:
    datasource:
      jdbc-url: jdbc:mysql://127.0.0.1:3306/test
      username: testuser
      password: testpwd
      driver-class-name: com.mysql.cj.jdbc.Driver
      hikari:
        maximum-pool-size: 2
  oracle:
    datasource1:
      jdbc-url: jdbc:oracle:thin:@//127.0.0.1:1521/orcl
      username: testuser1
      password: testpwd1
      driver-class-name: oracle.jdbc.driver.OracleDriver
      hikari:
        maximum-pool-size: 2
    datasource2:
      jdbc-url: jdbc:oracle:thin:@//127.0.0.1:1521/orcl
      username: testuser2
      password: testpwd2
      driver-class-name: oracle.jdbc.driver.OracleDriver
      hikari:
        maximum-pool-size: 2

配置类

须使用@Primary标注主要数据源。

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean(name = "mysqlDatasource")
    @ConfigurationProperties(prefix = "spring.mysql.datasource")
    public DataSource mysqlDatasource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "oracleDatasource1")
    @ConfigurationProperties(prefix = "spring.oracle.datasource1")
    public DataSource oracleDatasource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "oracleDatasource2")
    @ConfigurationProperties(prefix = "spring.oracle.datasource2")
    public DataSource oracleDatasource2() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlJdbcTemplate")
    public NamedParameterJdbcTemplate mysqlJdbcTemplate(@Qualifier("mysqlDatasource") DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    @Bean(name = "oracle1JdbcTemplate")
    public NamedParameterJdbcTemplate oracle1JdbcTemplate(@Qualifier("oracleDatasource1") DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    @Bean(name = "oracle2JdbcTemplate")
    public NamedParameterJdbcTemplate oracle2JdbcTemplate(@Qualifier("oracleDatasource2") DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

}

使用MySQL

@Service
public class MySQLService {

    @Resource
    @Qualifier("mysqlJdbcTemplate")
    NamedParameterJdbcTemplate mysqlJdbcTemplate;

    public List<MySQLInfo> query(Set<String> ids) {
        Map<String, Set<String>> map = new HashMap<>();
        map.put("ids", ids);
        return mysqlJdbcTemplate.query("select id, name from info where id in (:ids)", map, BeanPropertyRowMapper.newInstance(MySQLInfo.class));
    }

}

使用Oracle

Oracle第一个数据库

@Service
public class Oracle1Service {

    @Resource
    @Qualifier("oracle1JdbcTemplate")
    NamedParameterJdbcTemplate oracle1JdbcTemplate;

    public List<Oracle1Info> query(Set<String> ids) {
        Map<String, Set<String>> map = new HashMap<>();
        map.put("ids", ids);
        return oracle1JdbcTemplate.query("select user_name \"userName\", exter_info \"exterInfo\" from info where id in (:ids)", map, BeanPropertyRowMapper.newInstance(Oracle1Info.class));
    }

}

Oracle第二个数据库

@Service
public class Oracle2Service {

    @Resource
    @Qualifier("oracle2JdbcTemplate")
    NamedParameterJdbcTemplate oracle2JdbcTemplate;

    public List<Oracle2Info> queryUpstreamId(Set<Long> ids) {
        Map<String, Set<Long>> map = new HashMap<>();
        map.put("ids", ids);
        return oracle2JdbcTemplate.query("select id, user_id \"userId\" from info2 where id in (ids)", map, BeanPropertyRowMapper.newInstance(Oracle2Info.class));
    }

}