How to setup datasources dynamically in springboot

1. Introduction

This post would demo how to setup the datasources dynamically in the spring or springboot application.

2. Environments

• SpringBoot 1.5.12+
• Java 1.8

3. The Pom.xml

spring boot version:

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-dependencies</artifactId>
            <version>1.5.12.RELEASE</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

all dependencies:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</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>

4. Three ways to setup datasources

To summarize, there are three ways to setup the datasource in spring application:

• 1. The default way
     • setup via the application.properties, in springboot app, the process is automatically, you just configure the database properties and then you can use the JdbcTemplate object
• 1. The dynamic ways:
     • 2.1 Setup via DataSourceBuilder and application.properties in a @Configuration Bean
     • 2.2 Setup only by DataSourceBuilder, there is no properties file needed

Just as the picture shows:

5. The database and table

For demo purpose, I setup one database in localhost as follows:

CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_bin;

There is a table ‘tbl_student’ in both of the databases:

CREATE TABLE `tbl_student` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) NOT NULL,
  `BRANCH` varchar(255) NOT NULL,
  `PERCENTAGE` int(3) NOT NULL,
  `PHONE` int(10) NOT NULL,
  `EMAIL` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

And insert one record into that table:

INSERT INTO `tbl_student` (`ID`, `NAME`, `BRANCH`, `PERCENTAGE`, `PHONE`, `EMAIL`)
VALUES
    (1, 'jack', 'it', 20, 1211232, '[email protected]');

6. The domain class Student

public class Student {
    private int id;
    private String name;
    private String branch;
    private int percentage;
    private int phone;
    private String email;

    //getters and setters
    ...
}

7. The default way: setup via the application.properties

7.1 the application.properties

Supply these properties in your application.properties:

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.datasource.driver=com.mysql.jdbc.Driver

7.2 the DAO class

The DAO class:

@Component
public class StudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplateObject;

    public Student getStudent(Integer id) {
        String SQL = "select * from tbl_student where id = ?";
        Student student = jdbcTemplateObject.queryForObject(SQL,
                new Object[]{id}, new StudentMapper());
    
        return student;
    }
    
    class StudentMapper implements RowMapper<Student> {
        public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
    
            return student;
        }
    }
}

7.3 The JUnit testcase

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestDefaultDataSource {
    @Autowired
    private StudentDao studentDao;
    @Test
    public void testSimpleSelect() {
        Student student = studentDao.getStudent(1);
        assertNotNull(student);
        assertEquals(student.getName(),"jack");
    }
}

Run the testcase, we got a green bar.

8. The dynamic way 1:Setup via DataSourceBuilder and application.properties

8.1 the application.properties

Supply these properties in your application.properties:

spring.datasource2.username=root
spring.datasource2.password=123456
spring.datasource2.url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.datasource2.driver=com.mysql.jdbc.Driver

8.2 The config bean for the datasource

@Configuration
public class DataSourceConfig {
    @Bean(name = "dsMaster")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource2")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "jdbcMaster")
    @Autowired
    public JdbcTemplate masterJdbcTemplate(@Qualifier("dsMaster") DataSource dsMaster) {
        return new JdbcTemplate(dsMaster);
    }

Here we did these:

• define a dynamic datasource named ‘dsMaster’ and use the DatasourceBuilder to create it
• define a jdbcTemplate named ‘jdbcMaster’ to point to the datasource ‘dsMaster’

8.3 the DAO class

The DAO class:

@Component
public class StudentDao {
    @Autowired
    @Qualifier("jdbcMaster")
    private JdbcTemplate jdbcTemplateObject;

    public Student getStudent(Integer id) {
        String SQL = "select * from tbl_student where id = ?";
        Student student = jdbcTemplateObject.queryForObject(SQL,
                new Object[]{id}, new StudentMapper());
    
        return student;
    }
    
    class StudentMapper implements RowMapper<Student> {
        public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
    
            return student;
        }
    }
}

We only add one line: @Qualifier(“jdbcMaster”) to the jdbcTemplateObject.

8.4 The JUnit testcase

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestDefaultDataSource {
    @Autowired
    private StudentDao studentDao;
    @Test
    public void testSimpleSelect() {
        Student student = studentDao.getStudent(1);
        assertNotNull(student);
        assertEquals(student.getName(),"jack");
    }
}

Run the testcase, we got a green bar.

9. The dynamic way 2:Setup only by DataSourceBuilder

There is no need for application.properties, we would define the database total dynamically.

9.1 The config bean for the datasource

@Configuration
public class DataSourceConfig {
    @Bean(name="dsCustom")
    public DataSource dataSource() {
        return DataSourceBuilder
                .create()
                .username("root")
                .password("123456")
                .url("jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8")
                .driverClassName("com.mysql.jdbc.Driver")
                .build();
    }

    @Bean(name = "jdbcCustom")
    @Autowired
    public JdbcTemplate jdbcTemplate(@Qualifier("dsCustom") DataSource dsCustom) {
        return new JdbcTemplate(dsCustom);
    }

Here we did these:

• define a dynamic datasource named ‘dsCustom’ and use the DatasourceBuilder to create it, the datasource properties is supplied dynamically in java code
• define a jdbcTemplate named ‘jdbcCustom’ to point to the datasource ‘dsCustom’

9.2 the DAO class

The DAO class:

@Component
public class StudentDao {
    @Autowired
    @Qualifier("jdbcCustom")
    private JdbcTemplate jdbcTemplateObject;

    public Student getStudent(Integer id) {
        String SQL = "select * from tbl_student where id = ?";
        Student student = jdbcTemplateObject.queryForObject(SQL,
                new Object[]{id}, new StudentMapper());
    
        return student;
    }
    
    class StudentMapper implements RowMapper<Student> {
        public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
            Student student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
    
            return student;
        }
    }
}

We only change one line: @Qualifier(“jdbcCustom”) Then everything is done.

9.3 The JUnit testcase

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestDefaultDataSource {
    @Autowired
    private StudentDao studentDao;
    @Test
    public void testSimpleSelect() {
        Student student = studentDao.getStudent(1);
        assertNotNull(student);
        assertEquals(student.getName(),"jack");
    }
}

Run the testcase, we got a green bar.

10 The DataSourceBuilder

This post used the DataSourceBuilder to create the datasource, The class description is as follows:

Convenience class for building a DataSource with common implementations and properties. If HikariCP, Tomcat or Commons DBCP are on the classpath one of them will be selected (in that order with Hikari first). In the interest of a uniform interface, and so that there can be a fallback to an embedded database if one can be detected on the classpath, only a small set of common configuration properties are supported. To inject additional properties into the result you can downcast it, or use @ConfigurationProperties.

here is a list of the methods of it:

It’s so easy, do you think so?

The example code has been uploaded to github, you can visit here to view the example codes.

You can find detail documents about the springboot and dynamic datasource here:

• springboot dynamic datasource routing