spring boot 配置多个数据源

2023-09-23 11:59:57

代码实现

1. 修改 `application.yml`配置文件，添加`db_2`数据库连接

注意：配置连接两个数据库，url改为：jdbc-url

server:
  port: 8083
  servlet:
    context-path: /mes
spring:
  db1:
    datasource:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/lmes
      username: root
      password: root
      type: com.alibaba.druid.pool.DruidDataSource
  db2:
    datasource:
      driver-class-name: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@//172.20.10.101:1521/PROD
      username: apps
      password: apps
      type: com.alibaba.druid.pool.DruidDataSource

2. 新建`config`包，添加`db1`和`db2`的配置文件

主数据库与从数据库配置区别：主数据库有 @Primary注解，从数据库都没有

2.1 主数据库db1，项目启动默认连接此数据库：`PrimaryDataSourceConfig`

package com.bbzd.mes.common.datasources;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.Optional;
import java.util.stream.Stream;

@Configuration
@EnableConfigurationProperties(MybatisProperties.class)
public class PrimaryDataSourceConfig {
    @Autowired
    private MybatisProperties properties;

    @Bean(name = "db1DataSource")
    @ConfigurationProperties(prefix = "spring.db1.datasource")
    @Primary
    public DataSource db1DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "sqlSessionFactory")
    @ConfigurationProperties(prefix = "mybatis")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage(properties.getTypeAliasesPackage());
        bean.setMapperLocations(resolveMapperLocations(properties.getMapperLocations()));
        properties.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
        bean.setConfiguration(properties.getConfiguration());
        return bean.getObject();
    }

    private Resource[] resolveMapperLocations(String[] mapperLocations) {
        PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();

        return Stream.of(Optional.ofNullable(mapperLocations).orElse(new String[0]))
                .flatMap(location -> Stream.of(getResources(pathMatchingResourcePatternResolver, location))).toArray(Resource[]::new);
    }

    private Resource[] getResources(PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver, String location) {
        try {
            return pathMatchingResourcePatternResolver.getResources(location);
        } catch (IOException e) {
            return new Resource[0];
        }
    }
    
    @Bean(name = "db1TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


}

2.2 从数据库db2：`DataSource2Config`

package com.bbzd.mes.common.datasources;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.bbzd.mes.oracledao",sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource2Config {

    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.db3.datasource")
    public DataSource db3DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "db2SqlSessionFactory")
    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:oracle_mappers/*.xml"));
        return bean.getObject();
    }

    @Bean(name="db2transactionManager")
    public DataSourceTransactionManager transactionManagerOne(){
        return  new DataSourceTransactionManager(db3DataSource());
    }

    @Bean(name = "db2JdbcTemplate")
    public JdbcTemplate jdbcTemplate(
            @Qualifier("db2DataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

3. dao层

注意：主数据库与从数据库区别：从数据库需要使用@Transactional 注解指向db2数据源

3.1 主数据库db1，dao层写法

public interface UserMapper {
    boolean save(UserVo userVo);
}

3.2 从数据库db2，dao层写法

@Transactional(value = "db2transactionManager")
public interface UserMapper {
    UserDto findById(Inter id);
}

4. resources目录下xml注意事项

db1 和 db2 `xml`文件 `namespace`不同

数据库db1，User.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.bbzd.mes.dao.UserMapper">

</mapper>

数据库db2，User.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.bbzd.mes.oracledao.UserMapper">

</mapper>

开发中遇到的问题

org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'javax.sql.DataSource' available: more than one 'primary' bean found among candidates: [db1DataSource, primaryDataSource]

多数据源报错:No qualifying bean of type 'javax.sql.DataSource' available: more than one 'primary' bean found among candidates: [test2DataSource, test1DataSource]由于之前引入mybatis的时候引入了pom

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-autoconfigure</artifactId>
</dependency>

spring会依赖spring-boot-autoconfigure这个jar包
这个jar包中有个DataSourceAutoConfiguration 会初始化DataSourceInitializer 这个类，这个类有一个init方法会去获取DataSource(数据源)

@Configuration
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
public class DataSourceAutoConfiguration {
 
@Configuration
@ConditionalOnMissingBean(DataSourceInitializer.class)
protected static class DataSourceInitializerConfiguration {
 
@Bean
public DataSourceInitializer dataSourceInitializer() {
return new DataSourceInitializer();
}

初始化方法中会获取数据源需要初始化一些ddl操作也是就runSchemaScripts()方法检查初始化时是否需要执行sql script ,当你有两个数据源的时候，程序不知道取哪一个，所以报错

@PostConstruct
public void init() {
if (!this.properties.isInitialize()) {
logger.debug("Initialization disabled (not running DDL scripts)");
      return;
}
if (this.applicationContext.getBeanNamesForType(DataSource.class, false, false).length > 0) {
this.dataSource = this.applicationContext.getBean(DataSource.class);
}
if (this.dataSource == null) {
logger.debug("No DataSource found so not initializing");
      return;
}
   runSchemaScripts();
}
 
private void runSchemaScripts() {
   List<Resource> scripts = getScripts(this.properties.getSchema(), "schema");
   if (!scripts.isEmpty()) {
      runScripts(scripts);
      try {
this.applicationContext.publishEvent(new DataSourceInitializedEvent(
this.dataSource));
// The listener might not be registered yet, so don't rely on it.
if (!this.initialized) {
            runDataScripts();
            this.initialized = true;
}
      }
catch (IllegalStateException ex) {
logger.warn("Could not send event to complete DataSource initialization ("
+ ex.getMessage() + ")");
}
   }
}

解决办法：

spring boot 启动类加上 exclude = DataSourceAutoConfiguration.class 代表启动项目的时候不加载这个类

 
@ComponentScan(basePackages = "com.pinyu.system")
@MapperScan("com.pinyu.system.mapper")
@EnableTransactionManagement
@SpringBootApplication(exclude={  
        DataSourceAutoConfiguration.class,  
//        HibernateJpaAutoConfiguration.class, //（如果使用Hibernate时，需要加）  
        DataSourceTransactionManagerAutoConfiguration.class,  
        })
public class Application extends SpringBootServletInitializer {
 
}

原文链接：https://blog.csdn.net/github_38336924/article/details/112789455

原文链接：https://blog.csdn.net/xiaoanzi123/article/details/105094059/

码农公寓

代码实现

1. 修改 application.yml配置文件，添加db_2数据库连接

2. 新建config包，添加db1和db2的配置文件

2.1 主数据库db1，项目启动默认连接此数据库：PrimaryDataSourceConfig

2.2 从数据库db2：DataSource2Config