How to configure multiple datasources when using SpringBoot
When use springboot, we often configure one datasource ,eg, mysql or oracle, but,If we want to use multiple datasources in a springboot project, how to code ? The answer is as follows.
1. setup mulitiple databases for test
Here, we assume you want to use two different databases of mysql in one project, we setup two databases in mysql like this:
- database:test contains one table named TBL_USERS,which stores all users. TBL_USERS sql DDL is:
CREATE TABLE `TBL_USERS` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`pub_date` datetime DEFAULT CURRENT_TIMESTAMP,
`depart_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8- database:test2 contains one table named TBL_DEPARTS,which stores all departments. TBL_DEPARTS sql DDL is:
CREATE TABLE `TBL_DEPARTS` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8You should insert some data in the tables for test.
2. Setup springboot project for demo
pom.xml
<dependencyManagement>
<dependencies>
<dependency>
<!-- Import dependency management from Spring Boot -->
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>1.4.3.RELEASE</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>Here we use springboot 1.4.3, the latest release. Then we define the dependency ,includes mysql,spring-boot-starter-jdbc and sprint-boot-starter-test. These are the minimal dependencies.
configure the springboot application.properties
spring.datasource.url=jdbc:mysql://10.21.16.1:3306/test
spring.datasource.username=testuser
spring.datasource.password=testuser
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource2.url=jdbc:mysql://10.21.16.1:3306/test2
spring.datasource2.username=testuser
spring.datasource2.password=testuser
spring.datasource2.driverClassName=com.mysql.jdbc.Driver
define domain objects
User.java
contains following properties
private int id;
private String name;
private Date pubDate;
private int departId;Department.java
contains following properties
private int id;
private String name;Define the DataSource beans and JdbcTemplate Beans
This is the core part. You must define some spring-beans to use multiple datasources.
MultiDatasourceConfig.java
@Configuration
public class MultiDatasourceConfig {
/**
* The default datasource.
*/
@Bean(name = "dsMaster")
@Primary
@ConfigurationProperties(prefix="spring.datasource")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
/**
* The second datasource.
*/
@Bean(name = "dsSecondary")
@ConfigurationProperties(prefix="spring.datasource2")
public DataSource secondDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "jdbcMaster")
@Autowired
public JdbcTemplate masterJdbcTemplate(@Qualifier("dsMaster") DataSource dsMaster) {
return new JdbcTemplate(dsMaster);
}
@Bean(name = "jdbcSecondary")
@Autowired
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("dsSecondary") DataSource dsMaster) {
return new JdbcTemplate(dsMaster);
}
}Here we define two datasources named dsMaster and dsSecondary, then we defined two jdbcTemplate object using the two datasources. Be careful the datasource prefix must match the application.properites names.
Use the datasources to query from different databases
Firstly ,we autowire two jdbcTemplate objects
@Autowired
@Qualifier("jdbcMaster")
private JdbcTemplate jdbcTemplate;
@Autowired
@Qualifier("jdbcSecondary")
private JdbcTemplate jdbcTemplate2;Then we use the objects to do the query:
Query from the datasource1: dsMaster
public List<User> queryUsers() {
String sql = "select * from TBL_USERS";
return this.jdbcTemplate.query(
sql, new Object[]{},
new RowMapper<User>() {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPubDate(rs.getDate("pub_date"));
user.setDepartId(rs.getInt("depart_id"));
return user;
}
});
}Query from the datasource2: dsSecondary
public Department queryDepartment(int departId) {
String sql = "select * from TBL_DEPARTS where id=?";
return jdbcTemplate2.queryForObject(
sql, new RowMapper<Department>() {
@Override
public Department mapRow(ResultSet rs, int i) throws SQLException {
Department department = new Department();
department.setId(rs.getInt("id"));
department.setName(rs.getString("name"));
return department;
}
}, departId);
}
3. Test
Now we use the springboot unittest framework to test the upper settings and codes.
TestUserDao.java
We define a TestUserDao.java in the test source folder as follows.
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestUserDao {
@Autowired
private UserDao userDao;
@Test
public void testQueryUsers() {
List<User> users = userDao.queryUsers();
assertNotNull(users);
assertTrue(users.size()>0);
}
@Test
public void testQueryDeparts() {
List<User> users = userDao.queryUsers();
for(User user:users) {
Department department = userDao.queryDepartment(user.getDepartId());
assertNotNull(department);
}
}
}You must use the @RunWith(SpringRunner.class) and @SpringBootTest to let the unittest run with springboot. Here we define two testcases, one testcase for one datasource. Here is the result:
All 2 tests passed.All the above complete project codes is uploaded to github ,check it by goto example sourcecodes.
You can find detail documents about the springboot using multiple datasources here: