Skip to content
BSWEN
Blog

How to setup datasources dynamically in springboot

Cowrie
Cowrie
Dev @ Bswen

Introduction

This post demonstrates how to dynamically configure multiple data sources in a Spring Boot application. We will explore three different approaches to set up data sources, including using application.properties and DataSourceBuilder.

Environments

  • SpringBoot 1.5.12+
  • Java 1.8

The Pom.xml

Here is the Spring Boot version and dependencies used in this example:

pom.xml
<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>


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

Three Ways to Setup DataSources

There are three primary methods to configure data sources in a Spring application:

  1. The Default Way: Configure via application.properties. Spring Boot automatically sets up the data source based on the provided properties.
  2. Dynamic Way 1: Use DataSourceBuilder along with application.properties in a @Configuration bean.
  3. Dynamic Way 2: Use DataSourceBuilder without any properties file, defining all configurations programmatically.

Here is a visual summary: 20180528_summary

The Database and Table

For demonstration purposes, a local database is set up as follows:

create_database.sql
CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_bin;

A table tbl_student is created in the database:

create_table.sql
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;

A sample record is inserted into the table:

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

The Domain Class Student

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


    // Getters and setters
    ...
}

The Default Way: Setup via application.properties

The application.properties

Configure the database properties in application.properties:

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

The DAO Class

StudentDao.java
@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;
        }
    }
}

The JUnit Test Case

TestDefaultDataSource.java
@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");
    }
}

Running the test case results in a green bar: junit_greenbar

The Dynamic Way 1: Setup via DataSourceBuilder and application.properties

The application.properties

Configure the second data source in application.properties:

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

The Config Bean for the DataSource

DataSourceConfig.java
@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);
    }
}

The DAO Class

StudentDao.java
@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;
        }
    }
}

The JUnit Test Case

TestDefaultDataSource.java
@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");
    }
}

Running the test case results in a green bar: junit_greenbar

The Dynamic Way 2: Setup Only by DataSourceBuilder

The Config Bean for the DataSource

DataSourceConfig.java
@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);
    }
}

The DAO Class

StudentDao.java
@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;
        }
    }
}

The JUnit Test Case

TestDefaultDataSource.java
@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");
    }
}

Running the test case results in a green bar: junit_greenbar

The DataSourceBuilder

The DataSourceBuilder class is a convenient way to create a DataSource with common implementations and properties. It supports HikariCP, Tomcat, and Commons DBCP if they are on the classpath. Here is a list of its methods: 20180528_datasourcebuilder

Summary

In this post, we explored three different ways to configure data sources in a Spring Boot application:

  1. Using application.properties for automatic configuration.
  2. Using DataSourceBuilder with application.properties for dynamic configuration.
  3. Using DataSourceBuilder without any properties file for fully programmatic configuration.

Each method has its use cases, and the choice depends on the specific requirements of your application. The example code is available on GitHub.

Final Words + More Resources

My intention with this article was to help others who might be considering solving such a problem. So I hope that’s been the case here. If you still have any questions, don’t hesitate to ask me by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!

Tags: