Code
2024-03-09 10:00:00

Spring Boot配置并使用两个数据源

如何配置和使用两个数据源?

例如，这是我的第一个数据源:

application.properties

#first db
spring.datasource.url = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]
spring.datasource.driverClassName = oracle.jdbc.OracleDriver

#second db ...

应用程序类

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

如何修改应用。属性添加另一个数据源?我如何自动装配它以供不同的存储库使用?

当前回答

给你。

添加到您的应用程序中。属性文件:

#first db
spring.datasource.url = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]
spring.datasource.driverClassName = oracle.jdbc.OracleDriver

#second db ...
spring.secondDatasource.url = [url]
spring.secondDatasource.username = [username]
spring.secondDatasource.password = [password]
spring.secondDatasource.driverClassName = oracle.jdbc.OracleDriver

在任何带@Configuration注解的类中添加以下方法:

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

@Bean
@ConfigurationProperties(prefix="spring.secondDatasource")
public DataSource secondaryDataSource() {
    return DataSourceBuilder.create().build();
}
2015-05-20 08:46:06

其他回答

在Spring Boot application.properties中声明一个数据源

spring.datasource.company.url=jdbc:mysql://localhost/company_db?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.company.username=root
spring.datasource.company.password=root
spring.datasource.company.platform=mysql


spring.datasource.employee.url=jdbc:mysql://localhost/employee_db?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.employee.username=root
spring.datasource.employee.password=root
spring.datasource.employee.platform=mysql

使用多个数据源时，我们需要声明多个bean Spring应用程序上下文中的不同映射。 使用配置类

@Configuration
@EnableJpaRepositories(basePackages = "com.example.multiple.datasources.entity.company",
        entityManagerFactoryRef = "companyEntityManagerFactory",
        transactionManagerRef = "companyTransactionManager")
public class CompanyDataSourceConfiguration {

    @Bean
    @ConfigurationProperties("spring.datasource.company")
    public DataSourceProperties companyDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.company.configuration")
    public DataSource companyDataSource() {
        return companyDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean(name = "companyEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean companyEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(companyDataSource()).packages(Company.class).build();
    }

    @Bean
    public PlatformTransactionManager companyTransactionManager(
            final @Qualifier("companyEntityManagerFactory") LocalContainerEntityManagerFactoryBean companyEntityManagerFactory
    ) {
        return new JpaTransactionManager(companyEntityManagerFactory.getObject());

    }


}

我们需要声明其中一个数据源为@Primary。这是因为 EntityManagerFactoryBuilder在jpbasaseconfiguration和中声明 该类需要注入单个数据源。

@Configuration
@EnableJpaRepositories(basePackages = "com.example.multiple.datasources.entity.employee",
        entityManagerFactoryRef = "employeeEntityManagerFactory",
        transactionManagerRef = "employeeTransactionManager")
public class EmployeeDatasourceConfiguration {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.employee")
    public DataSourceProperties employeeDataSourceProperties() {
        return new DataSourceProperties();

    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.employee.configuration")
    public DataSource employeeDataSource() {
        return employeeDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean("employeeEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean employeeEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(employeeDataSource()).packages(Employee.class).build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager employeeTransactionManager(
            final @Qualifier("employeeEntityManagerFactory") LocalContainerEntityManagerFactoryBean employeeEntityManagerFactory
    ) {
        return new JpaTransactionManager(employeeEntityManagerFactory.getObject());

    }

}
2022-05-24 11:31:22

给你。

添加到您的应用程序中。属性文件:

#first db
spring.datasource.url = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]
spring.datasource.driverClassName = oracle.jdbc.OracleDriver

#second db ...
spring.secondDatasource.url = [url]
spring.secondDatasource.username = [username]
spring.secondDatasource.password = [password]
spring.secondDatasource.driverClassName = oracle.jdbc.OracleDriver

在任何带@Configuration注解的类中添加以下方法:

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

@Bean
@ConfigurationProperties(prefix="spring.secondDatasource")
public DataSource secondaryDataSource() {
    return DataSourceBuilder.create().build();
}
2015-05-20 08:46:06

2022-05-29更新Spring Boot 1.5.8。RELEASE应该与Spring Boot 2.x一起工作

大多数答案不提供如何使用它们(作为数据源本身和作为事务)，只提供如何配置它们。

此外，您应该知道如何同时提交/回滚两个数据源的事务。

您可以在https://github.com/surasint/surasint-examples/tree/master/spring-boot-jdbi/10_spring-boot-two-databases中看到可运行的示例和一些解释(看看您可以在README.txt中尝试什么)

我在这里复制了一些代码。

首先你必须设置应用程序。像这样的属性

#Database
database1.datasource.url=jdbc:mysql://localhost/testdb
database1.datasource.username=root
database1.datasource.password=root
database1.datasource.driver-class-name=com.mysql.jdbc.Driver

database2.datasource.url=jdbc:mysql://localhost/testdb2
database2.datasource.username=root
database2.datasource.password=root
database2.datasource.driver-class-name=com.mysql.jdbc.Driver

然后像这样将它们定义为提供者(@Bean):

@Bean(name = "datasource1")
@ConfigurationProperties("database1.datasource")
@Primary
public DataSource dataSource(){
    return DataSourceBuilder.create().build();
}

@Bean(name = "datasource2")
@ConfigurationProperties("database2.datasource")
public DataSource dataSource2(){
    return DataSourceBuilder.create().build();
}

注意，我有@Bean(name="datasource1")和@Bean(name="datasource2")，那么当我们需要数据源时，你可以使用它作为@Qualifier("datasource1")和@Qualifier("datasource2")，例如

@Qualifier("datasource1")
@Autowired
private DataSource dataSource;

如果你真的关心事务，你必须为它们定义DataSourceTransactionManager，就像这样:

@Bean(name="tm1")
@Autowired
@Primary
DataSourceTransactionManager tm1(@Qualifier ("datasource1") DataSource datasource) {
    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
    return txm;
}

@Bean(name="tm2")
@Autowired
DataSourceTransactionManager tm2(@Qualifier ("datasource2") DataSource datasource) {
    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
    return txm;
}

然后你就可以用它

@Transactional //this will use the first datasource because it is @primary

or

@Transactional("tm2")

最重要的部分，你很难在任何地方找到一个例子:如果你想要一个方法来提交/回滚两个数据库的事务，你需要tm1和tm2的ChainedTransactionManager，如下所示:

@Bean(name = "chainedTransactionManager")
public ChainedTransactionManager getChainedTransactionManager(@Qualifier ("tm1") DataSourceTransactionManager tm1, @Qualifier ("tm2") DataSourceTransactionManager tm2){
    return new ChainedTransactionManager(tm1, tm2);
}

要使用它，可以在@Transactional(value="chainedTransactionManager")方法中添加该注释

@Transactional(value="chainedTransactionManager")
public void insertAll() {
    UserBean test = new UserBean();
    test.setUsername("username" + new Date().getTime());
    userDao.insert(test);

    userDao2.insert(test);
}

这应该足够了。参见上面链接中的示例和详细信息。

2018-01-07 19:45:38

我的需求略有不同，但使用了两个数据源。

我为来自同一个包的同一个JPA实体使用了两个数据源。一个用于在服务器启动时执行DDL以创建/更新表，另一个用于运行时执行DML。

DDL连接应该在DDL语句执行之后关闭，以防止在代码的任何地方进一步使用超级用户特权。

属性

spring.datasource.url=jdbc:postgresql://Host:port
ddl.user=ddluser
ddl.password=ddlpassword
dml.user=dmluser
dml.password=dmlpassword
spring.datasource.driver-class-name=org.postgresql.Driver

数据源配置类

// DDL数据源的第一个配置类

  public class DatabaseDDLConfig {
        @Bean
        public LocalContainerEntityManagerFactoryBean ddlEntityManagerFactoryBean() {
            LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
            PersistenceProvider persistenceProvider = new 
            org.hibernate.jpa.HibernatePersistenceProvider();
            entityManagerFactoryBean.setDataSource(ddlDataSource());
            entityManagerFactoryBean.setPackagesToScan(new String[] { 
            "com.test.two.data.sources"});
            HibernateJpaVendorAdapter vendorAdapter = new 
            HibernateJpaVendorAdapter();
            entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
            HashMap<String, Object> properties = new HashMap<>();
            properties.put("hibernate.dialect", 
            "org.hibernate.dialect.PostgreSQLDialect");
            properties.put("hibernate.physical_naming_strategy", 
            "org.springframework.boot.orm.jpa.hibernate.
            SpringPhysicalNamingStrategy");
            properties.put("hibernate.implicit_naming_strategy", 
            "org.springframework.boot.orm.jpa.hibernate.
            SpringImplicitNamingStrategy");
            properties.put("hibernate.hbm2ddl.auto", "update");
            entityManagerFactoryBean.setJpaPropertyMap(properties);
            entityManagerFactoryBean.setPersistenceUnitName("ddl.config");
            entityManagerFactoryBean.setPersistenceProvider(persistenceProvider);
            return entityManagerFactoryBean;
        }


    @Bean
    public DataSource ddlDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.datasource.url"));
        dataSource.setUsername(env.getProperty("ddl.user");
        dataSource.setPassword(env.getProperty("ddl.password"));
        return dataSource;
    }

    @Bean
    public PlatformTransactionManager ddlTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(ddlEntityManagerFactoryBean().getObject());
        return transactionManager;
    }
}

// DML数据源的第二个配置类

public class DatabaseDMLConfig {

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean dmlEntityManagerFactoryBean() {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        PersistenceProvider persistenceProvider = new org.hibernate.jpa.HibernatePersistenceProvider();
        entityManagerFactoryBean.setDataSource(dmlDataSource());
        entityManagerFactoryBean.setPackagesToScan(new String[] { "com.test.two.data.sources" });
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
        entityManagerFactoryBean.setJpaProperties(defineJpaProperties());
        entityManagerFactoryBean.setPersistenceUnitName("dml.config");
        entityManagerFactoryBean.setPersistenceProvider(persistenceProvider);
        return entityManagerFactoryBean;
    }

    @Bean
    @Primary
    public DataSource dmlDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        dataSource.setUrl(envt.getProperty("spring.datasource.url"));
        dataSource.setUsername("dml.user");
        dataSource.setPassword("dml.password");
        return dataSource;
    }

    @Bean
    @Primary
    public PlatformTransactionManager dmlTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(dmlEntityManagerFactoryBean().getObject());
        return transactionManager;
    }


  }

// DDL数据源在代码中的使用。

public class DDLServiceAtStartup {

//Import persistence unit ddl.config for ddl purpose.

@PersistenceUnit(unitName = "ddl.config")
private EntityManagerFactory entityManagerFactory;

public void executeDDLQueries() throws ContentServiceSystemError {
    try {
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        entityManager.getTransaction().begin();
        entityManager.createNativeQuery("query to create/update table").executeUpdate();
        entityManager.flush();
        entityManager.getTransaction().commit();
        entityManager.close();

        //Close the ddl data source to avoid from further use in code.
        entityManagerFactory.close();
    } catch(Exception ex) {}
}

//在代码中使用DML数据源。

public class DDLServiceAtStartup {
  @PersistenceUnit(unitName = "dml.config")
  private EntityManagerFactory entityManagerFactory;

  public void createRecord(User user) {
     userDao.save(user);
  }
}
2018-11-29 20:45:30

我用的是mybatis springboot 2.0技术栈， 解决方案:

//application.properties - start
    sp.ds1.jdbc-url=jdbc:mysql://localhost:3306/mydb?useSSL=false
    sp.ds1.username=user
    sp.ds1.password=pwd
    sp.ds1.testWhileIdle=true
    sp.ds1.validationQuery=SELECT 1
    sp.ds1.driverClassName=com.mysql.jdbc.Driver


    sp.ds2.jdbc-url=jdbc:mysql://localhost:4586/mydb?useSSL=false
    sp.ds2.username=user
    sp.ds2.password=pwd
    sp.ds2.testWhileIdle=true
    sp.ds2.validationQuery=SELECT 1
    sp.ds2.driverClassName=com.mysql.jdbc.Driver

//application.properties - end

//configuration class

    @Configuration
    @ComponentScan(basePackages = "com.mypkg")
    public class MultipleDBConfig {


        public static final String SQL_SESSION_FACTORY_NAME_1 = "sqlSessionFactory1";
        public static final String SQL_SESSION_FACTORY_NAME_2 = "sqlSessionFactory2";

        public static final String MAPPERS_PACKAGE_NAME_1 = "com.mypg.mymapper1";
        public static final String MAPPERS_PACKAGE_NAME_2 = "com.mypg.mymapper2";


        @Bean(name = "mysqlDb1")
        @Primary
        @ConfigurationProperties(prefix = "sp.ds1")
        public DataSource dataSource1() {
            System.out.println("db1 datasource");
            return DataSourceBuilder.create().build();
        }

        @Bean(name = "mysqlDb2")
        @ConfigurationProperties(prefix = "sp.ds2")
        public DataSource dataSource2() {
            System.out.println("db2 datasource");
            return  DataSourceBuilder.create().build();
        }

        @Bean(name = SQL_SESSION_FACTORY_NAME_1)
        @Primary
        public SqlSessionFactory sqlSessionFactory1(@Qualifier("mysqlDb1") DataSource dataSource1) throws Exception {
            System.out.println("sqlSessionFactory1");
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setTypeHandlersPackage(MAPPERS_PACKAGE_NAME_1);
            sqlSessionFactoryBean.setDataSource(dataSource1);
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject();
            sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);
            sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
            return sqlSessionFactory;
        }

        @Bean(name = SQL_SESSION_FACTORY_NAME_2)
        public SqlSessionFactory sqlSessionFactory2(@Qualifier("mysqlDb2") DataSource dataSource2) throws Exception {
            System.out.println("sqlSessionFactory2");
            SqlSessionFactoryBean diSqlSessionFactoryBean = new SqlSessionFactoryBean();
            diSqlSessionFactoryBean.setTypeHandlersPackage(MAPPERS_PACKAGE_NAME_2);
            diSqlSessionFactoryBean.setDataSource(dataSource2);
            SqlSessionFactory sqlSessionFactory = diSqlSessionFactoryBean.getObject();
            sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true);
            sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
            return sqlSessionFactory;
        }

        @Bean
        @Primary
        public MapperScannerConfigurer mapperScannerConfigurer1() {
            System.out.println("mapperScannerConfigurer1");
            MapperScannerConfigurer configurer = new MapperScannerConfigurer();
            configurer.setBasePackage(MAPPERS_PACKAGE_NAME_1);
            configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_1);
            return configurer;
        }

        @Bean
        public MapperScannerConfigurer mapperScannerConfigurer2() {
            System.out.println("mapperScannerConfigurer2");
            MapperScannerConfigurer configurer = new MapperScannerConfigurer();
            configurer.setBasePackage(MAPPERS_PACKAGE_NAME_2);
            configurer.setSqlSessionFactoryBeanName(SQL_SESSION_FACTORY_NAME_2);
            return configurer;
        }



    }

注意: 1)@Primary -> @Primary

2)。属性中的“jdbc-url”-> Spring Boot 2.0迁移后:需要jdbcUrl和driverClassName

2018-08-14 14:11:36

推荐文章

aliyun

最新文章

标签

2025 code 京ICP备15047053号-1