Spring Boot DataSource Configuration

Learn what is a datasource and how to create and customize DataSource bean in Spring boot applications.

1. What is DataSource

A DataSource is a factory for connections to the physical databases. It is an alternative to the DriverManager facility. A datasource uses a URL along with username/password credentials to establish the database connection.

In Java, a datasource implements the javax.sql.DataSource interface. This datasource will typically be registered with the JNDI service and can be discovered using its JNDI name.

We may use a datasource to obtain :

  • standard Connection object
  • connection which can be used in connection pooling
  • connection which can be used in distributed transactions and connection pooling

2. Configuring a DataSource

Spring boot allows defining datasource configuration in two ways:

  1. Java configuration
  2. Properties configuration

During application startup, the DataSourceAutoConfiguration checks for DataSource.class (or EmbeddedDatabaseType.class) on the classpath and a few other things before configuring a DataSource bean for us.

2.1. Maven Dependency

If not already defined, include spring-boot-starter-data-jpa to project. This dependency brings all necessary dependencies including JDBC drivers for various databases e.g. mysql-connector-java for connecting to MySQL database.

If we plan to use an embedded database at some step (e.g., testing), we can import H2 DB separately.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
 
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.4.1</version> 
    <scope>runtime</scope> 
</dependency>

2.2. Properties Configuration

DataSource configuration is provided by configuration properties entries ( spring.datasource.* ) in application.properties file.

The properties configuration decouples the configuration from the application code. This way, we can import the datasource configurations from even configuration provider systems.

Below given configuration shows sample properties for H2, MySQL, Oracle and SQL Server databases.

We often do not need to specify the driver-class-name, since Spring Boot can deduce it for the most databases from the connection url.

# H2 DB
spring.datasource.url=jdbc:h2:file:C:/temp/test
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

# MySQL
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

# Oracle
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

# SQL Server
spring.datasource.url=jdbc:sqlserver://localhost;databaseName=springbootdb
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

2.3. Java Configuration

The recommended way to create a DataSource bean is using DataSourceBuilder class within a class annotated with the @Configuration annotation.

Given is an example bean for H2 DB. Please configure other beans as necessary.

@Configuration
public class JpaConfig {

    @Bean
    public DataSource getDataSource()
    {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("org.h2.Driver");
        dataSourceBuilder.url("jdbc:h2:file:C:/temp/test");
        dataSourceBuilder.username("sa");
        dataSourceBuilder.password("");
        return dataSourceBuilder.build();
    }
}

2.4. JNDI DataSource

Suppose we deploy our Spring Boot application to an application server. In that case, we might want to configure and manage the DataSource by using the Application Server’s built-in features and access it by using JNDI.

We can do this using the spring.datasource.jndi-name property.

#JBoss defined datasource using JNDI

spring.datasource.jndi-name = java:jboss/datasources/testDB

3. Connection Pooling

3.1. HikariCP, Tomcat Pooling and Commons DBCP2

For a pooling datasource to be created, Spring boot verifies that a valid Driver class is available. If we set spring.datasource.driver-class-name property then the mentioned driver class must be found and loaded.

  • The auto-configuration first tries to find and configure HikariCP. If HikariCP is available, it always chooses it.
  • Otherwise, if the Tomcat Pooling is found, it is configured.
  • If neither HikariCP nor the Tomcat Pooling datasource are available and if Commons DBCP2 is available, it is used.

spring-boot-starter-data-jpa starter automatically get a dependency to HikariCP.

3.2. Customize Connection Pool Defaults

It is also possible to fine-tune implementation-specific settings by using their respective prefix (spring.datasource.hikari.*, spring.datasource.tomcat.*, and spring.datasource.dbcp2.*).

For example, we can use the below properties to customize a DBCP2 connection pool.

spring.datasource.dbcp2.initial-size = 50
spring.datasource.dbcp2.max-idle = 50
spring.datasource.dbcp2.default-query-timeout = 10000
spring.datasource.dbcp2.default-auto-commit = true

4. Multiple DataSources with Spring boot

To configure multiple data sources, create as many bean definitions as you want but mark one of the DataSource instances as @Primary.

Remember that if we create our own DataSource, the auto-configuration backs off. In this case, we are responsible for providing configurations for all datasource beans.

@Configuration
public class JpaConfig {

    @Bean(name = "h2DataSource")
    public DataSource h2DataSource()
    {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("org.h2.Driver");
        dataSourceBuilder.url("jdbc:h2:file:C:/temp/test");
        dataSourceBuilder.username("sa");
        dataSourceBuilder.password("");
        return dataSourceBuilder.build();
    }

    @Bean(name = "mySqlDataSource")
    @Primary
    public DataSource mySqlDataSource()
    {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.url("jdbc:mysql://localhost/testdb");
        dataSourceBuilder.username("dbuser");
        dataSourceBuilder.password("dbpass");
        return dataSourceBuilder.build();
    }
}

While autowiring the datasource, spring boot will prefer the primary datasource i.e., “mySqlDataSource”. To autowire another non-primary datasource, use @Qualifier annotation.

@Autowired
DataSource dataSource;
@Autowired
@Qualifier("h2DataSource")
DataSource dataSource;

5. Conclusion

Spring boot provides straightforward ways to create datasource beans – either using properties configuration or using java configuration. Spring boot offers ready-made auto configuration to use which can be further customized with advanced options in application.properties file.

Spring boot tries to find and configure connection pooling, first HikariCP, second Tomcat pooling, and finally Commons DBCP2. HikariCP comes inbuilt with spring-boot-starter-jdbc or spring-boot-starter-data-jpa starters.

We can configure multiple datasources, and we must mark as one of them @Primary. The primary datasource is autowired by default, and other datasources need to be autowired along with @Qualifier annotation.

Happy Learning !!

Leave a Reply

4 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

About Us

HowToDoInJava provides tutorials and how-to guides on Java and related technologies.

It also shares the best practices, algorithms & solutions, and frequently asked interview questions.

Our Blogs

REST API Tutorial