springboot连接多个数据库

大家好，又见面了，我是你们的朋友全栈君。

今天借到一个新的需求，需要把自己数据库某个表的数据迁移到别的数据库中，于是百度，中间出现了一些细节的问题，解决花了点时间，在此记录一下，下次避免出现过的错误

这里把连接一个数据库的情况也记录一下，好做对比

一、连接一个数据库

1.启动类

@SpringBootApplication
//扫描mapper映射类所在路径
@MapperScan(basePackages = "com.xh.iot.repositories.mapper")
public class EmDataProcApplication {

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

2.配置文件application.properties

# MySql Database
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/iothub_base?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=Xtjc0551Mysql!


# MyBaits，xml映射文件所在目录，resources下
mybatis.mapper-locations=classpath:mapping/*.xml

3.添加mapper对应的service和serviceImpl，最后在controller中添加service实例操作数据库

二、连接多个数据库

1.启动类

//EnableAutoConfiguration注解，关闭springBoot关于mybatis的一些自动注入
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class,DataSourceTransactionManagerAutoConfiguration.class, MybatisAutoConfiguration.class})
//加入定时器
@EnableScheduling
@SpringBootApplication
public class DataTransGsiotApplication {

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

2.配置文件application.properties，配置了2个库的连接

#  MySql Database
#org_base库
spring.datasource.org.jdbcUrl=jdbc:mysql://127.0.0.1:3306/org_base?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
spring.datasource.org.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.org.username=root
spring.datasource.org.password=Xtjc0551Mysql!
#gs_iot_v1库
spring.datasource.gsiot.jdbcUrl=jdbc:mysql://127.0.0.1:3306/gs_iot_v1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
spring.datasource.gsiot.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.gsiot.username=root
spring.datasource.gsiot.password=root

3.创建2个数据库连接的配置类

GsIotDataSourceConfig类

package com.xh.iot.repositories.datasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.xh.iot.repositories.mapper.gsiot",sqlSessionFactoryRef = "gsiotSqlSessionFactory")
public class GsIotDataSourceConfig {
    @Primary
    @Bean(name = "gsiotDataSource")
    @ConfigurationProperties("spring.datasource.gsiot")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "gsiotSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("gsiotDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapping/gsiot/*.xml"));
        return sessionFactoryBean.getObject();
    }
}

 OrgDataSourceConfig类

package com.xh.iot.repositories.datasource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.xh.iot.repositories.mapper.org",sqlSessionFactoryRef = "orgSqlSessionFactory")
public class OrgDataSourceConfig {
    @Primary
    @Bean(name = "orgDataSource")
    @ConfigurationProperties("spring.datasource.org")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "orgSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("orgDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapping/org/*.xml"));
        return sessionFactoryBean.getObject();
    }
}

注意：

1、注意多个数据库的application.properties文件，数据库连接用jdbcUrl或者jdbc-url

2、如果有更多的数据库连接，可以按照这种方式添加；不同的数据库，需要不同的配置类，可以把这些配置类放在同一个目录中
3、 每个配资类中，需要指明当前数据库表的xml文件和mapper映射文件所在的包名，并且把xml和mapper放置到对应的目录中
4、其他的比如：xml里面sql语句的写法和正常一个连接是一模一样，添加mapper对应的service和serviceImpl，最后在controller中添加不同的service实例就可以操作对应数据库了