Spring Boot 配置多数据源demo

SpringBoot 配置多数据源的笔记总结.

首先，通过 spring initializr 新建一个Spring Boot项目. 为了方便，选择H2作为我们的数据库，选择web, h2, lombok等几个依赖，生成我们的Spring Boot项目.

默认情况下，Spring Boot 会帮我们自动装配

1. DataSourceAutoConfiguration.class,
2. JdbcTemplateAutoConfiguration.class,
3. DataSourceTransactionManagerAutoConfiguration.class

这三个类数据源相关的类，为了配置多个数据源，我们需要手动排除掉Spring Boot为我们自动配置的类，同时我们自己手动配置了两个数据源，分别命名为 foo 和 bar, 相关代码如下:

java

@Slf4j
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class,
                                    JdbcTemplateAutoConfiguration.class,
                        DataSourceTransactionManagerAutoConfiguration.class})
// 排除掉Spring boot自动装配的类
public class DatasourceDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DatasourceDemoApplication.class, args);
    }



    @Bean
    @ConfigurationProperties("bar.datasource")
    public DataSourceProperties barDatasourceProp() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("foo.datasource")
    public DataSourceProperties fooDatasourceProp() {
        return new DataSourceProperties();
    }


    @Bean
    public DataSource barDatasource() {
        DataSourceProperties dsProp = barDatasourceProp();
        log.info("bar ds url:{}", dsProp.getUrl());
        return dsProp.initializeDataSourceBuilder().build();
    }

    @Bean
    public DataSource fooDatasource() {
        DataSourceProperties dsProp = fooDatasourceProp();
        log.info("foo ds url:{}", dsProp.getUrl());
        return dsProp.initializeDataSourceBuilder().build();
    }

    @Bean
    public JdbcTemplate fooJdbcTemplate() {
        DataSource foo = fooDatasource();
        return new JdbcTemplate(foo);
    }

    @Bean
    public JdbcTemplate barJdbcTemplate() {
        DataSource bar = barDatasource();
        return new JdbcTemplate(bar);
    }

    @Bean
    public DataSourceTransactionManager barTxManager() {
        return new DataSourceTransactionManager(barDatasource());
    }

    @Bean
    public DataSourceTransactionManager fooTxManager() {
        return new DataSourceTransactionManager(fooDatasource());
    }
}

相应的配置文件application.properties配置项:

properties

foo.datasource.url=jdbc:h2:mem:foo
foo.datasource.username=sa
foo.datasource.password=

bar.datasource.url=jdbc:h2:mem:bar
bar.datasource.username=sa
bar.datasource.password=

之后我们可以写个测试类测试一下，我这里用的是一个Controller类，代码如下:

java

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;

@RestController
@RequestMapping("ds")
@Slf4j
public class DataSourceDemoController {

    @Resource(name="fooDatasource")
    DataSource fooDS;

    @Resource(name="barDatasource")
    DataSource barDS;

    @Resource(name="fooJdbcTemplate")
    JdbcTemplate fooJdbc;

    @Resource(name="barJdbcTemplate")
    JdbcTemplate barJdbc;

    @PostMapping("fooInsert")
    public String insertDataWithFoo() {
        try {
            String sql = "INSERT INTO USER (name) VALUES ('foo')";
            fooJdbc.execute(sql);
        } catch (Exception e) {
            log.error("error:{}", e.getMessage());
        }

        return "success";
    }

    @PostMapping("createTable")
    public String createTable() {
        Connection conn = null;
        try {
            conn = fooDS.getConnection();
            log.info("conn:{}", conn);
            Statement stmt = conn.createStatement();
            stmt.execute("DROP TABLE IF EXISTS `USER`");
            stmt.execute("CREATE TABLE `USER` (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100));");

        } catch (SQLException e) {
            log.error("errors occur when creating table:{} ", e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("exception occurs: ", e);
                }
            }
        }
        return "success";
    }


    @GetMapping("barSelect")
    public String selectDataWithBar() {

        try {
            barJdbc.queryForList("SELECT * FROM `USER`").forEach(row -> System.out.println(row));
        } catch (Exception e) {
            return e.getMessage();
        }
        return "success";
    }

    @GetMapping("fooSelect")
    public String selectDataWithFoo() {
        try {
            fooJdbc.queryForList("SELECT * FROM `USER`").forEach(row -> System.out.println(row));
        } catch (Exception e) {
            return e.getMessage();
        }
        return "success";
    }


}

通过访问 /ds/createTable 先在 foo 数据源新建一个USER 表， 再通过 /ds/fooInsert 插入一条数据。通过访问 /ds/barSelect 会发现返回 “==Table USER NOT EXIST==“ 这样的报错，说明我们刚刚的 USER表确实是来自于 foo 数据源而不是 bar 数据源。 最后通过 /ds/fooSelect 查询一下刚刚插入进去的记录，发现确实是插入成功的。由此证明了我们刚刚确实是配置了两个数据源，而且通过这两个数据源确实也访问到了来源不同的数据。

---

以上