¿Cómo usar 2 o más bases de datos con la spring?

Tengo una aplicación que ejecuta Spring MVC.

Necesito acceder a 2 bases de datos diferentes en mi aplicación (una es una PostgreSQL y la otra es una base de datos MySQL).

¿Cómo configuro esto usando solo las anotaciones o el archivo application.properties?

Saludos.

Aquí está el código de ejemplo para tener multiple Database/datasource de multiple Database/datasource en Spring-Boot ¡Espero que ayude!

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 itemMapper = new RowMapper() { 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 userMapper = new RowMapper() { 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(); } } 

esta es la forma de configurar múltiples fonts de datos en el archivo xml de spring, aquí está el mío, por ejemplo, espero que ayude

                   true UTF-8 UTF-8 org.hibernate.dialect.MySQLDialect  update false          true UTF-8 UTF-8 org.hibernate.dialect.MySQLDialect  update false             

También puede tratar de definir múltiples fonts de datos y asignar uno de ellos como primario.

Aquí está el código de demostración.

El origen de datos primario:

 @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(); } 

Otro Datasource (en mi demo, es 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(); } 

Ref: https://stackoverflow.com/a/27679997/6037575