logo标签列表

如何在Spring中使用2个或多个数据库？

springspring-bootmultiple-databases
69

我有一个运行Spring MVC的应用程序。

我需要让它在我的应用程序中访问 2 个不同的数据库（一个是PostgreSQL，另一个是MySQL数据库）。

我该如何仅使用注释或application.properties文件来配置此操作？

谢谢。

1
@duffymo：首先，感谢您抽出时间来回答。我相信这很容易，但我找不到一个可以做到这一点的文档/示例。您能分享一些文档吗？谢谢。 - Plicatibu
@MarcioAndreyOliveira，请查看这里或者这个coderanch主题。我认为这可能会对你有所帮助。 - Diogo Calazans
1
你提到了application.properties，让我怀疑你在询问Spring Boot相关的问题 - 如果是这种情况，请更新标签，因为它会对问题产生很大影响。 - kryger
@MarcioAndreyOliveira 如果我的回答正确，请点击我回答左侧的灰色勾号，这样我就可以得到积分，谢谢。 - jimagic
1
@calazans，感谢您提供的链接。但正如我在问题中所写的那样，我不想使用XML文件。敬礼。 - Plicatibu
4个回答
80

以下是在 Spring-Boot 中使用 多个数据库/数据源 的示例代码，希望对您有所帮助！

application.properties

spring.ds_items.driverClassName=org.postgresql.Driver 
spring.ds_items.url=jdbc:postgresql://srv0/test 
spring.ds_items.username=test0 
spring.ds_items.password=test0 


spring.ds_users.driverClassName=org.postgresql.Driver 
spring.ds_users.url=jdbc:postgresql://srv1/test 
spring.ds_users.username=test1 
spring.ds_users.password=test1

DatabaseItemsConfig.java

package sb; 

import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration; 
import org.springframework.boot.context.properties.ConfigurationProperties; 
import org.springframework.context.annotation.Bean; 
import org.springframework.context.annotation.Configuration; 
import org.springframework.jdbc.core.JdbcTemplate; 

import javax.sql.DataSource; 

@Configuration 
@ConfigurationProperties(name = "spring.ds_items") 
public class DatabaseItemsConfig extends TomcatDataSourceConfiguration { 

    @Bean(name = "dsItems") 
    public DataSource dataSource() { 
        return super.dataSource(); 
    } 

    @Bean(name = "jdbcItems") 
    public JdbcTemplate jdbcTemplate(DataSource dsItems) { 
        return new JdbcTemplate(dsItems); 
    } 
}

DatabaseUsersConfig.java

package sb; 

import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration; 
import org.springframework.boot.context.properties.ConfigurationProperties; 
import org.springframework.context.annotation.Bean; 
import org.springframework.context.annotation.Configuration; 
import org.springframework.jdbc.core.JdbcTemplate; 

import javax.sql.DataSource; 

@Configuration 
@ConfigurationProperties(name = "spring.ds_users") 
public class DatabaseUsersConfig extends TomcatDataSourceConfiguration { 

    @Bean(name = "dsUsers") 
    public DataSource dataSource() { 
        return super.dataSource(); 
    } 

    @Bean(name = "jdbcUsers") 
    public JdbcTemplate jdbcTemplate(DataSource dsUsers) { 
        return new JdbcTemplate(dsUsers); 
    } 

}

ItemRepository.java

package sb; 

import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.beans.factory.annotation.Qualifier; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.RowMapper; 
import org.springframework.stereotype.Repository; 

import java.sql.ResultSet; 
import java.sql.SQLException; 

@Repository 
public class ItemRepository { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 

    @Autowired 
    @Qualifier("jdbcItems") 
    protected JdbcTemplate jdbc; 

    public Item getItem(long id) { 
        return jdbc.queryForObject("SELECT * FROM sb_item WHERE id=?", itemMapper, id); 
    } 

    private static final RowMapper<Item> itemMapper = new RowMapper<Item>() {
        public Item mapRow(ResultSet rs, int rowNum) throws SQLException { 
            Item item = new Item(rs.getLong("id"), rs.getString("title")); 
            item.price = rs.getDouble("id"); 
            return item; 
        } 
    }; 
}

UserRepository.java

package sb; 

import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.beans.factory.annotation.Qualifier; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.RowMapper; 
import org.springframework.stereotype.Repository; 

import java.sql.ResultSet; 
import java.sql.SQLException; 

@Repository 
public class UserRepository { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 

    @Autowired 
    @Qualifier("jdbcUsers") 
    protected JdbcTemplate jdbc; 

    public User getUser(long id) { 
        return jdbc.queryForObject("SELECT * FROM sb_user WHERE id=?", userMapper, id); 
    } 

    private static final RowMapper<User> userMapper = new RowMapper<User>() {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException { 
            User user = new User(rs.getLong("id"), rs.getString("name")); 
            user.alias = rs.getString("alias"); 
            return user; 
        } 
    }; 
}

Controller.java

package sb; 

import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.web.bind.annotation.RequestMapping; 
import org.springframework.web.bind.annotation.RequestParam; 
import org.springframework.web.bind.annotation.RestController; 

@RestController 
public class Controller { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 

    @Autowired 
    private UserRepository users; 

    @Autowired 
    private ItemRepository items; 

    @RequestMapping("test") 
    public String test() { 
        log.info("Test"); 
        return "OK"; 
    } 

    @RequestMapping("user") 
    public User getUser(@RequestParam("id") long id) { 
        log.info("Get user"); 
        return users.getUser(id); 
    } 

    @RequestMapping("item") 
    public Item getItem(@RequestParam("id") long id) { 
        log.info("Get item"); 
        return items.getItem(id); 
    } 

}

Application.java

package sb; 

import org.springframework.boot.SpringApplication; 
import org.springframework.boot.autoconfigure.EnableAutoConfiguration; 
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; 
import org.springframework.context.annotation.ComponentScan; 
import org.springframework.context.annotation.Configuration; 

@EnableAutoConfiguration(exclude = DataSourceAutoConfiguration.class) 
@Configuration 
@ComponentScan(basePackages = "sb") 
public class Application { 

    public static void main(String[] args) throws Throwable { 
        SpringApplication app = new SpringApplication(Application.class); 
        app.run(); 
    } 
}
有没有可能使用约定配置来代替@Qualifier注释？我的意思是，使用相同的名称配置方法和注入属性？我尝试过了，但它没有起作用。 - Raffael Bechara Rameh
这些数据源是否也可以通过JMX进行监控？ - sme
根据getUser的参数值，是否有可能在UserRepository内更改限定符？ - jesicadev18
请注意，以上配置仅适用于MySQL而不是mongoDB。 - piepi
1
TomcatDataSourceConfiguration 的正确 Maven 依赖是什么？ - Jay
我们可以在一个Spring应用程序中使用两个不同的数据库，例如MySQL和Postgres吗？ - Rishi Agrawal
26

这是如何在Spring XML文件中设置多个数据源的方法，以下是我的示例，希望能对您有所帮助。

<bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url"
              value="jdbc:mysql://localhost:3306/gl?characterEncoding=UTF-8" />
    <property name="username" value="root" />
    <property name="password" value="2238295" />
</bean>



<bean id="mainDataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url"
              value="jdbc:mysql://localhost:3306/gl_main?characterEncoding=UTF-8" />
    <property name="username" value="root" />
    <property name="password" value="2238295" />
</bean>

<!-- Hibernate 4 SessionFactory Bean definition -->
<bean id="sfAccounting"
      class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="com.gl.domain.accounting" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.connection.useUnicode">true</prop>
            <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
            <prop key="hibernate.connection.charSet">UTF-8</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect
            </prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.show_sql">false</prop>
        </props>
    </property>
</bean>



<!-- Hibernate 4 SessionFactory Bean definition -->
<bean id="sfCommon"
      class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="mainDataSource" />
    <property name="packagesToScan" value="com.gl.domain.common" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.connection.useUnicode">true</prop>
            <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
            <prop key="hibernate.connection.charSet">UTF-8</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect
            </prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.show_sql">false</prop>
        </props>
    </property>
</bean>



<tx:annotation-driven transaction-manager="txnManagerAccounting"/>
<tx:annotation-driven transaction-manager="txnManagerCommon"/>

<bean id="txnManagerAccounting"
      class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sfAccounting" />

</bean>


<bean id="txnManagerCommon"
      class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sfCommon" />

</bean>

<bean id="persistenceExceptionTranslationPostProcessor"
      class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
Jiten，感谢您抽出时间来帮助我。我不想使用XML文件，只想在application.properties文件中使用注释或属性。谢谢。 - Plicatibu
我使用了你的答案，但是出现了javax.persistence.TransactionRequiredException: no transaction is in progress错误，请查看https://stackoverflow.com/questions/47430321/inject-2-data-sources-into-springhibernate-application-always-throw-no-transact获取更多详细信息。 - Melad Basilius
嗨，jimagic。你的回答对我非常有帮助。这些数据源是如何选择的？ - SHIVA
1
你也可以尝试定义多个数据源并将其中一个指定为主要数据源。
这是演示代码。
主要数据源：
@MapperScan(basePackages = "com.demo.mysqldao",
sqlSessionFactoryRef = "mysqlSqlSessionFactory")
@Configuration
public class MysqlDatabaseConfig {

  @Value("${mysql.datasource.url}")
  String jdbcUrl;

  @Value("${mysql.datasource.username}")
  String jdbcUser;

  @Value("${mysql.datasource.password}")
  String jdbcPass;

  @Value("${mysql.datasource.driverClassName}")
  String jdbcProvider;

  BasicDataSource src = null;

  Logger log = LoggerFactory.getLogger(MysqlDatabaseConfig.class);

  @Bean(name = "mysqlDataSource")
  @Primary
  @PostConstruct
  public DataSource mysqlDataSource() {
    if (jdbcUrl == null) {
      throw new RuntimeException("initialization datasource error with null jdbcUrl");
    }
    log.info("Using JDBC ------------> " + jdbcUrl);
    if (src == null) {
      BasicDataSource dataSource = new BasicDataSource();
      dataSource.setDriverClassName(jdbcProvider);
      dataSource.setUrl(jdbcUrl);
      dataSource.setUsername(jdbcUser);
      dataSource.setPassword(jdbcPass);
      dataSource.setMaxActive(100);
      dataSource.setMinIdle(3);
      dataSource.setMaxIdle(10);
      dataSource.setMinEvictableIdleTimeMillis(60 * 1000);
      dataSource.setNumTestsPerEvictionRun(100);
      dataSource.setRemoveAbandoned(true);
      dataSource.setRemoveAbandonedTimeout(60 * 1000);
      dataSource.setTestOnBorrow(true);
      dataSource.setTestOnReturn(true);
      dataSource.setTestWhileIdle(true);
      dataSource.setTimeBetweenEvictionRunsMillis(30 * 60 * 1000);
      src = dataSource;
    }
    return src;
  }

  @Autowired
  @Qualifier(value = "mysqlDataSource")
  DataSource mysqlDataSource;

  @Bean("mysqlTransactionManager")
  @Primary
  public DataSourceTransactionManager mysqlTransactionManager() {
    return new DataSourceTransactionManager(mysqlDataSource);
  }

  @Bean("mysqlSqlSessionFactory")
  @Primary
  public SqlSessionFactory mysqlSqlSessionFactory() throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(mysqlDataSource);
    return sessionFactory.getObject();
  }

另一个数据源（在我的演示中，它是SQL Server）
@MapperScan(basePackages = "com.demo.sqlserverdao",
sqlSessionFactoryRef = "sqlserverSqlSessionFactory")
@Configuration
public class SQLServerDatabaseConfig {

  @Value("${sqlserver.datasource.url}")
  String jdbcUrl;

  @Value("${sqlserver.datasource.username}")
  String jdbcUser;

  @Value("${sqlserver.datasource.password}")
  String jdbcPass;

  @Value("${sqlserver.datasource.driverClassName}")
  String jdbcProvider;


  BasicDataSource src = null;

  @Bean(name = "sqlServerDataSource")
  @PostConstruct
  public DataSource sqlServerDataSource() {

    if (jdbcUrl == null) {
      throw new RuntimeException("initialization sqlserver datasource error with null jdbcUrl");
    }
    if (src == null) {
      BasicDataSource dataSource = new BasicDataSource();
      dataSource.setDriverClassName(jdbcProvider);
      dataSource.setUrl(jdbcUrl);
      dataSource.setUsername(jdbcUser);
      dataSource.setPassword(jdbcPass);
      dataSource.setMaxActive(100);
      dataSource.setMinIdle(3);
      dataSource.setMaxIdle(10);
      dataSource.setMinEvictableIdleTimeMillis(60 * 1000);
      dataSource.setNumTestsPerEvictionRun(100);
      dataSource.setRemoveAbandoned(true);
      dataSource.setRemoveAbandonedTimeout(60 * 1000);
      dataSource.setTestOnBorrow(true);
      dataSource.setTestOnReturn(true);
      dataSource.setTestWhileIdle(true);
      dataSource.setTimeBetweenEvictionRunsMillis(30 * 60 * 1000);
      src = dataSource;
    }
    return src;
  }

  @Autowired
  @Qualifier(value = "sqlServerDataSource")
  DataSource sqlServerDataSource;

  @Bean("sqlserverTransactionManager")
  public DataSourceTransactionManager sqlserverTransactionManager() {
    return new DataSourceTransactionManager(sqlServerDataSource);
  }

  @Bean("sqlserverSqlSessionFactory")
  public SqlSessionFactory sqlserverSqlSessionFactory() throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(sqlServerDataSource);
    return sessionFactory.getObject();
  }

Ref:https://dev59.com/W14c5IYBdhLWcg3wuMN7#27679997

1
我提供另一种适用于Java类和注释的版本。我必须连接到postgresql数据库和mysql数据库。
这是我的RootConfiguration类，我在其中定义了我的数据源和SessionFactories。
@Configuration
@ComponentScan(basePackages="com.netzwerge.nzadmin")
@EnableTransactionManagement
public class RootConfiguration {

  @Autowired
  ServletContext context;

  @Bean
  @Qualifier(value="postgre")
  public HibernateTransactionManager txManagerPostgre() {
      HibernateTransactionManager txManager = new HibernateTransactionManager();
      txManager.setSessionFactory(sessionFactoryPostgre().getObject());

      return txManager;
  }

  @Bean
  @Qualifier(value="postgre")
  public LocalSessionFactoryBean sessionFactoryProvab() {
      LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();

      Properties hibernateProperties = new Properties();
      hibernateProperties.setProperty("hibernate.show_sql", "false");
      hibernateProperties.setProperty("hibernate.format_sql", "true");
      hibernateProperties.setProperty("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
      hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQL95Dialect");
      hibernateProperties.setProperty("hibernate.connection.datasource", "java:comp/env/jdbc/postgres");
      sessionFactory.setHibernateProperties(hibernateProperties);

      sessionFactory.setPackagesToScan("com.programname.model");
      sessionFactory.setDataSource(dataSourcePostgre());

      return sessionFactory;
  }

  @Bean
  public DataSource dataSourcePostgre() {
      JndiObjectFactoryBean dataSource = new JndiObjectFactoryBean();
      dataSource.setJndiName("java:comp/env/jdbc/postgres");
      dataSource.setResourceRef(true);

      return (DataSource) dataSource.getObject();
  }



  @Bean
  @Qualifier(value="mysql")
  public HibernateTransactionManager txManagerMySql() {
      HibernateTransactionManager txManager = new HibernateTransactionManager();
      txManager.setSessionFactory(sessionFactoryMysql().getObject());

      return txManager;
  }

  @Bean
  @Qualifier(value="mysql")
  public LocalSessionFactoryBean sessionFactorySystemdaten() {
      LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();

      Properties hibernateProperties = new Properties();
      hibernateProperties.setProperty("hibernate.show_sql", "false");
      hibernateProperties.setProperty("hibernate.format_sql", "true");
      hibernateProperties.setProperty("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
      hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
      hibernateProperties.setProperty("hibernate.connection.datasource", "java:comp/env/jdbc/mysql");
      sessionFactory.setHibernateProperties(hibernateProperties);

      sessionFactory.setPackagesToScan("com.programname.model");
      sessionFactory.setDataSource(dataSourceSystemdaten());

      return sessionFactory;
  }

  @Bean
  public DataSource dataSourceMysql() {
      JndiObjectFactoryBean dataSource = new JndiObjectFactoryBean();
      dataSource.setJndiName("java:comp/env/jdbc/mysql");
      dataSource.setResourceRef(true);

      return (DataSource) dataSource.getObject();
  }

  // Other definitions etc.
}

以下是如何在DAO中访问不同的数据库。选择你需要访问的限定符以访问正确的数据库，同时选择正确的事务管理器。在我的情况下，我在这两种情况下都使用Hibernate，但你可以自由选择适合你的任何工具。

@Repository
@Transactional("mysql")
public class CustomerDaoImplMySql implements CustomerDao {


  @Autowired
  @Qualifier("mysql")
  private SessionFactory sessionFactory;

  // The Routines to access the data like CRUD go here

}
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接