如何在 spring 中使用 2 个或更多数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30362546/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-08 00:35:43 来源:igfitidea点击:
How to use 2 or more databases with spring?
提问by Plicatibu
I have an application that runs Spring MVC.
我有一个运行 Spring MVC 的应用程序。
I need it to access 2 different databases in my app (one is a PostgreSQL and the other one is a MySQL database).
我需要它来访问我的应用程序中的 2 个不同的数据库（一个是 PostgreSQL，另一个是 MySQL 数据库）。
How do I configure this using just annotations or application.properties file?
如何仅使用注释或 application.properties 文件来配置它？
Regards.
问候。
回答by jimagic
Here is the example code for having multiple Database/datasourceon Spring-BootI hope it helps!
这是使用的示例代码，希望multiple Database/datasource对您有所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
数据库项配置.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
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
用户存储库.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
控制器.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
应用程序.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();
}
}
回答by jimagic
this is how you set up multiple data sources on spring xml file, here is mine for example, hope it helps
这是你如何在 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" />
回答by Dai Kaixian
You can also try to define multiple datasources & assign one of them as primary.
您还可以尝试定义多个数据源并将其中一个指定为主数据源。
Here is the demo code.
这是演示代码。
The Primary Datasource:
主要数据源：
@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();
}
Another Datasource (In my demo, it's sqlserver)
另一个数据源（在我的演示中，它是 sqlserver）
@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();
}
回答by M46
I offer another version which works only with Java classes and annotations. I have to connect to a postgresql-database and a mysql-database.
我提供了另一个仅适用于 Java 类和注释的版本。我必须连接到 postgresql 数据库和 mysql 数据库。
Here is my RootConfiguration class where I've defined my datasources and my SessionFactories
这是我的 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.
}
And here is how you access the different databases in your DAOs. Choose the qualifier you need to access the correct database and also choose the correct transaction manager. In my case I use hibernate in both cases but you are free to use whatever seems appropriate to you.
以下是您访问 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
}
