SpringBoot连接多个数据库

idea:IntelliJ IDEA 2022.1.2 (Community Edition)

数据库:MySQL

依赖:mybatis+mysql+druid

连接数据库部分我就不说了,看我这边文章SpringBoot连接数据库

修改application.properties

# 192.168.1.32:3306/test1和192.168.1.32:3306/test2改成自己的数据库,username和password也改成自己的
spring.datasource.db1.url=jdbc:mysql://192.168.1.32:3306/test1?useUnicode=true&characterEncoding=utf-8
spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db1.username=xxxx
spring.datasource.db1.password=xxxx
spring.datasource.db2.url=jdbc:mysql://192.168.1.32:3306/test2?useUnicode=true&characterEncoding=utf-8
spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db2.username=xxxx
spring.datasource.db2.password=xxxx
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

创建包xxx.xxx.xxx.mapper.db1和xxx.xxx.xxx.mapper.db2

在db1和db2内分别创建DB1Mapper.java和DB2Mapper.java,写入查询语句,查询在不同数据库的两个表

@Mapper
public interface DB1Mapper {
    @Select("select v.video_name from video v")
    List<String> getVideoName();
}

@Mapper
public interface DB2Mapper {
    @Select("select u.username from user u")
    List<String> getUserName();
}

再配置druid,创建DB1DataSourceConfig.java和DB2DataSourceConfig.java

DB1DataSourceConfig的basePackages改成DB1Mapper所在的包,prefix改成application.properties内的db1,DB2DataSourceConfig的basePackages改成DB2Mapper所在的包,prefix改成application.properties内的db2

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DB1DataSourceConfig {
    @Bean(name="db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    @Primary
    public DruidDataSource db1DataSource() {
        return new DruidDataSource();
    }
    @Bean(name="db1SqlSessionFactory")
    @Primary
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean=new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }
    @Bean(name="db1TransactionManager")//配置事务
    @Primary
    public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean(name="db1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name="db1DruidServlet")
    @Primary
    public ServletRegistrationBean<Servlet> db1DruidServlet() {
        // 进行 druid 监控的配置处理
        ServletRegistrationBean<Servlet> srb = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/**");
        // 白名单
        srb.addInitParameter("allow", "");
        // 是否可以重置数据源
        srb.addInitParameter("resetEnable", "false");
        return srb;
    }

    @Bean(name="db1FilterRegistrationBean")
    @Primary
    public FilterRegistrationBean<Filter> db1FilterRegistrationBean() {
        FilterRegistrationBean<Filter> frb = new FilterRegistrationBean<>();
        frb.setFilter(new WebStatFilter());
        // 所有请求进行监控处理
        frb.addUrlPatterns("/*");
        // 排除名单
        frb.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");
        return frb;
    }
}

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DB2DataSourceConfig {
    @Bean(name="db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DruidDataSource db2DataSource() {
        return new DruidDataSource();
    }
    @Bean(name="db2SqlSessionFactory")
    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean=new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }
    @Bean(name="db2TransactionManager")//配置事务
    public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
    @Bean(name="db2SqlSessionTemplate")
    public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    @Bean(name="db2DruidServlet")
    public ServletRegistrationBean<Servlet> db2DruidServlet() {
        // 进行 druid 监控的配置处理
        ServletRegistrationBean<Servlet> srb = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/**");
        // 白名单
        srb.addInitParameter("allow", "");
        // 是否可以重置数据源
        srb.addInitParameter("resetEnable", "false");
        return srb;
    }
    @Bean(name="db2FilterRegistrationBean")
    public FilterRegistrationBean<Filter> db2FilterRegistrationBean() {
        FilterRegistrationBean<Filter> frb = new FilterRegistrationBean<>();
        frb.setFilter(new WebStatFilter());
        // 所有请求进行监控处理
        frb.addUrlPatterns("/*");
        // 排除名单
        frb.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");
        return frb;
    }
}

这里还有一个重点,就是DB1DataSourceConfig的函数都要加上@Primary,表示优先运行

写个简单的TestController

@RestController
public class TestController {
    @Autowired
    DB1Mapper db1Mapper;
    @Autowired
    DB2Mapper db2Mapper;

    @GetMapping("testVideo")
    @Transactional(transactionManager = "db1TransactionManager")
    public List<String> test1(){
        return db1Mapper.getVideoName();
    }
    @GetMapping("testUser")
    @Transactional(transactionManager = "db2TransactionManager")
    public List<String> test2(){
        return db2Mapper.getUserName();
    }
}

这里的db1TransactionManager是DB1DataSourceConfig内的db1TransactionManager函数,db2TransactionManager是DB2DataSourceConfig内的db2TransactionManager函数

Run一下,然后在网页上输入地址localhost:8080/test/testVideo和localhost:8080/test/testUser

 两个数据库连接成功