Spring Boot - DataSource

Posted on In

Configuring DataSource in a Spring Boot Project.

Config a DataSource

Add Maven dependencies for MySQL

1
2
3
4
5
6
7
8
9
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

configuration for MySQL datasource in application.properties file

1
2
3
4
spring.datasource.url=jdbc:mysql://localhost:3306/orderDB
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

That is all you need for datasource configuration. You can now autowire DataSource or JdbcTemplate in your Spring Boot app.

To test the datasource, you can list the tables using DataSource.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class MyCommandLineRunner implements CommandLineRunner {
    protected final Logger LOG = LoggerFactory.getLogger(getClass());

    @Autowired
    private DataSource ds;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
        // list tables in the databse
        DatabaseMetaData md = ds.getConnection().getMetaData();
        String[] types = { "TABLE" };
        ResultSet rs = md.getTables(null, null, "%", types);
        while (rs.next()) {
            LOG.info("Table in Database: {}", rs.getString(3));
        }
        
        LOG.info("datasource: {}", ds.toString());
        LOG.info("jdbcTemplate: {}", jdbcTemplate.toString());
    }
}

H2 Datasource

When developing database application, H2 database can be used to quickly test ideas. It has a lot of benifits:

  • Very fast, open source, JDBC API
  • Embedded and server modes; in-memory databases
  • Browser based Console application
  • Small footprint: around 2 MB jar file size

You can download H2 Database from https://www.h2database.com/html/main.html page.
And then run java -jar h2-*.jar to spin up the local server at http://127.0.1.1:8082.

To use H2 database, include the following dependencies

1
2
3
4
5
6
7
8
9
<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
</dependency>

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

In reality, H2 dependency’s scope to be test because it is only used in unit test unless you want to use h2 in memory database for prototyping.

By default, Spring Boot configures the application to connect to an in-memory H2 store with the username sa and an empty password. However, we can change those parameters by adding the following properties to the application.properties file

application.properties file for in memory h2 datasource. You can skip the configuration if h2 maven dependency is detected.

1
2
3
4
5
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb
# spring.datasource.username=sa
# spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

For H2 database, the default username is “sa” and no password is required. If you are using in memory database, you actually don’t need to specify username and password at all.

application.properties file for external h2 service as datasource

1
2
3
4
5
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:tcp://localhost/~/test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

You need to configure connection URl for the embedded database to ensure automatic shutdown is disabled. So use DB_CLOSE_ON_EXIT=FALSE

H2 Console is a very good tool to exammine the database. it can be enabled by adding the following line to the application.properties file.

1
spring.h2.console.enabled=true

You can access H2 console using url http://localhost:8080/h2-console. The url and credentials are set in applicaiton.properties file.

DataSourceBuilder

When there are multiple DataSources, you will probably need to have more than one datasource set up in application.properties file. In this case, we will need to use DataSourceBuilder to manually create individual DataSource with different bean name.

application.properties file with a custom datasource

1
2
3
4
spring.datasource.customsource.driver-class-name=org.h2.Driver
spring.datasource.customsource.jdbc-url=jdbc:h2:tcp://localhost/~/test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.customsource.username=sa
spring.datasource.customsource.password=

Configure a Datasource using DataSourceBuilder

1
2
3
4
5
6
7
8
9
10
11
12
13
@Configuration
public class CustomDataSourceConfig {

    @Bean(name="customDataSource")
    @Qualifier("customDataSource")
    public DataSource CustomDataSource() {
        return DataSourceBuilder.create()
            .driverClassName(env.getProperty("spring.datasource.customsource.driver-class-name"))
            .url(env.getProperty("spring.datasource.customsource.jdbc-url"))
            .username(env.getProperty("spring.datasource.customsource.username"))
            .password(env.getProperty("spring.datasource.customsource.password")).build();
    }
}

Configure a Datasource using DataSourceBuilder with the help of @ConfigurationProperties annotation

1
2
3
4
5
6
7
8
9
10
11
@Configuration
public class CustomDataSourceConfig {

    @Primary
    @Bean(name="customDataSource")
    @Qualifier("customDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.customsource")
    public DataSource CustomDataSource() {
        return DataSourceBuilder.create().build();
    }
}

data.sql and schema.sql File

You can load initial data in data.sql file. When this file is presented in the classpath, spring boot will pick it up and populate the tables in the database.

If you want to use a customized schema, you can provide a customized schema.sql file under src/main/resources folder. If you do that, you need to disable JPA DDL generation.

1
spring.jpa.hibernate.ddl-auto=none

DDL Generation

JPA has features for DDL(Data Definition Language) generation, and these can be set up to run on startup against the database. This is controlled through two external properties:

There are two ways to set this configuration

  • spring.jpa.generate-ddl - (boolean) switches the feature on and off and is vendor independent.
  • spring.jpa.hibernate.ddl-auto (enum) is a Hibernate feature that controls the behavior in a more fine-grained way. See below for more detail.

ddl-auto property can have one of the values: create, create-drop, validate, update and none.

  • create – Hibernate first drops existing tables, then creates new tables
  • update – Hibernate attempt to update schema
  • create-drop – similar to create, with the addition that Hibernate will drop the database after all operations are completed. Typically used for unit testing
  • validate – Hibernate only validates whether the tables and columns exist, otherwise it throws an exception
  • none – turns off the DDL generation.

In production environment, you usually use none so that schema is not accidentially dropped.

generate-ddl will be ignored if ddl-auto is set because ddl-auto is more fine-grained.

Log SQL Statement

Add the following to application.properties file to log sql statement. format_sql will format the sql.

Only add logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE when you need to log bind values too.

1
2
3
4
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

This will log the formatted sql statement with the bind values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2020-05-03 15:39:00.749  INFO 17557 --- [  XNIO-1 task-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 15 ms
Hibernate: 
    /* select
        generatedAlias0 
    from
        User as generatedAlias0 
    where
        generatedAlias0.username=:param0 */ select
            user0_.id as id1_2_,
            user0_.enabled as enabled2_2_,
            user0_.password as password3_2_,
            user0_.username as username4_2_ 
        from
            user user0_ 
        where
            user0_.username=?
2020-05-03 15:39:06.081 TRACE 17557 --- [  XNIO-1 task-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [user]

Reference

Source code: SpringBootExamples - datasourcebuilder