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=utf8

You 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: