Configure DataSource programmatically in Spring Boot

Question

With Spring Boot I can instantiate a JdbcTemplate with the following:

Code:

@Autowired
private JdbcTemplate jdbcTemplate;

Properties:

spring.datasource.url=jdbc:postgresql://my_url:my_port/my_other_stuff
spring.datasource.username=my_user_name
spring.datasource.password=my_password
spring.datasource.driver-class-name=org.postgresql.Driver

This create a DataSource of class: org.apache.tomcat.jdbc.pool.DataSource

How do I set the DataSource username/password programmatically?

We have a policy not to store credentials in plain text and I have to use a specific credential provider where I work.

Solution 1

You can use DataSourceBuilder if you are using jdbc starter. Also, in order to override the default autoconfiguration bean you need to mark your bean as a @Primary

In my case I have properties starting with datasource.postgres prefix.

E.g

@ConfigurationProperties(prefix = "datasource.postgres")
@Bean
@Primary
public DataSource dataSource() {
    return DataSourceBuilder
        .create()
        .build();
}

If it is not feasible for you, then you can use

@Bean
@Primary
public DataSource dataSource() {
    return DataSourceBuilder
        .create()
        .username("")
        .password("")
        .url("")
        .driverClassName("")
        .build();
}

Solution 2

My project of spring-boot has run normally according to your assistance. The yaml datasource configuration is:

spring:
  # (DataSourceAutoConfiguration & DataSourceProperties)
  datasource:
    name: ds-h2
    url: jdbc:h2:D:/work/workspace/fdata;DATABASE_TO_UPPER=false
    username: h2
    password: h2
    driver-class: org.h2.Driver

Custom DataSource

@Configuration
@Component
public class DataSourceBean {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    @Primary
    public DataSource getDataSource() {
        return DataSourceBuilder
                .create()
//                .url("jdbc:h2:D:/work/workspace/fork/gs-serving-web-content/initial/data/fdata;DATABASE_TO_UPPER=false")
//                .username("h2")
//                .password("h2")
//                .driverClassName("org.h2.Driver")
                .build();
    }
}

Solution 3

All you need to do is annotate a method that returns a DataSource with @Bean. A complete working example follows.

@Bean
public DataSource dataSource() {
    DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.url(dbUrl);
        dataSourceBuilder.username(username);
        dataSourceBuilder.password(password);
        return dataSourceBuilder.build();   
}

Solution 4

If you're using latest spring boot (with jdbc starter and Hikari) you'll run into: java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName. To solve this:

1. In your application.properties:

datasource.oracle.url=youroracleurl

2. In your application define as bean (@Primary is mandatory!):

@Bean
@Primary
@ConfigurationProperties("datasource.oracle")
public DataSourceProperties getDatasourceProperties() {
    return new DataSourceProperties();
}

@Bean
@ConfigurationProperties("datasource.oracle")
public DataSource getDatasource() {
    return getDatasourceProperties().initializeDataSourceBuilder()
           .username("username")
           .password("password")
           .build();
}

Solution 5

If you want more datesource configs e.g.

spring.datasource.test-while-idle=true 
spring.datasource.time-between-eviction-runs-millis=30000
spring.datasource.validation-query=select 1

you could use below code

@Bean
public DataSource dataSource() {
    DataSource dataSource = new DataSource(); // org.apache.tomcat.jdbc.pool.DataSource;
    dataSource.setDriverClassName(driverClassName);
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    dataSource.setTestWhileIdle(testWhileIdle);     
    dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMills);
    dataSource.setValidationQuery(validationQuery);
    return dataSource;
}

refer: Spring boot jdbc Connection

View more solutions

Comments

[dunni]

@Primary is not necessary, since the AutoConfiguration only creates a DataSource bean, when no other bean is defined.

[Marsellus Wallace]

create() should probably be first

[Kishore Guruswamy]

Primary not required. @Bean enables Spring boot to register configuration. driver class name, url, user, pwd required

[Andres Felipe]

As per the JavaDoc @Primary "Indicates that a bean should be given preference when multiple candidates are qualified to autowire a single-valued dependency." docs.spring.io/spring/docs/current/javadoc-api/org/…

[zhuguowei]

But how about set spring.datasource.validation-query and spring.datasource.test-while-idle and spring.datasource.time-between-eviction-runs-millis

[Vijay Shegokar]

I also have the same question - How to set "spring.datasource.validation-query" in multi tenancy project where we are creating DataSource at runtime using DataSourceBuilder.create().

[Admin]

This answer helps because it shows how to use the default spring.datasource properties to define the DataSource. Note that if you want to override only the password, say, then you would need to remove the password definition from application.properties (or application.yml) and set that one property in the code.

[Kalpesh Soni]

how to do this same thing with different datasources for junit/local server/dev server

[Fadhlie Ikram]

@Willie Wheeler Are you sure that would work? Because what I understand the above code will return an entirely new datasource bean. Meaning the spring.datasource properties wont take effect, and will be replaced by the new bean.

[Admin]

Yeah, I've used this approach to externalize passwords into Vault.

[Fadhlie Ikram]

@WillieWheeler You are right. I just tried and it works.

[Admin]

Good to hear @FadhlieIkram. Thanks for checking.

[dantebarba]

Can I combine @ConfigurationProperties annotation with programmatic settings on the DataSourceBuilder?. I want to configure test-on-borrow and eviction runs, but there is no option in the builder to set those values.

[specializt]

NOTE: Within special class loading environments such as OSGi, this class is effectively superseded by SimpleDriverDataSource due to general class loading issues with the JDBC DriverManager that be resolved through direct Driver usage (which is exactly what SimpleDriverDataSource does).

[Nis]

I also had to declare the DataSource bean as @Primary.

[Davide Martorana]

There is a simpler way: in the application properties (i.e. applicaiotion.yml) rename: spring.datasource.url to spring.datasource.jdbc-url. This should do the trick!!! (It did in my case - SpringBoot v2.3.2)