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.

score 88 · Accepted Answer · edited Nov 09 '15 at 09:45

88

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();
}

edited Nov 09 '15 at 09:45

yglodt

11,334
13
74
114

answered Mar 03 '15 at 00:47

Eddú Meléndez

5,098
1
23
27

7

@Primary is not necessary, since the AutoConfiguration only creates a DataSource bean, when no other bean is defined. – dunni Mar 03 '15 at 10:21
create() should probably be first – Marsellus Wallace Mar 14 '15 at 02:21
Primary not required. @Bean enables Spring boot to register configuration. driver class name, url, user, pwd required – Kishore Guruswamy Jun 16 '16 at 18:49
As per the JavaDoc @Primary "Indicates that a bean should be given preference when multiple candidates are qualified to autowire a single-valued dependency." https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/context/annotation/Primary.html – Andres Felipe May 30 '17 at 15:47
6

But how about set `spring.datasource.validation-query` and `spring.datasource.test-while-idle` and `spring.datasource.time-between-eviction-runs-millis` – zhuguowei Jun 27 '17 at 00:40
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(). – Vijay Shegokar Aug 08 '17 at 06:35
how to do this same thing with different datasources for junit/local server/dev server – Kalpesh Soni Mar 28 '18 at 20:46
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. – dantebarba Sep 24 '18 at 16:12
This does not seem to work anymore, you should update your answer – Madeo Mar 11 '21 at 08:54

score 26 · Answer 2 · edited Jul 27 '18 at 20:42

26

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();
    }
}

edited Jul 27 '18 at 20:42

Community

1
1

answered Jul 15 '16 at 06:05

Alfer Wei

271
3
2

3

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. – Sep 10 '17 at 02:44
@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. – Fadhlie Ikram May 17 '18 at 10:48
1

Yeah, I've used this approach to externalize passwords into Vault. – May 18 '18 at 07:24
1

@WillieWheeler You are right. I just tried and it works. – Fadhlie Ikram May 18 '18 at 07:55
Good to hear @FadhlieIkram. Thanks for checking. – May 21 '18 at 00:45

score 15 · Answer 3 · answered Oct 08 '15 at 19:24

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();   
}

score 11 · Answer 4 · edited Feb 28 '19 at 17:05

11

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:

In your application.properties:

datasource.oracle.url=youroracleurl

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();
}

edited Feb 28 '19 at 17:05

Andronicus

23,098
14
38
73

answered Sep 18 '18 at 15:34

ACV

8,090
4
56
72

I also had to declare the DataSource bean as @Primary. – Nis Feb 28 '19 at 16:40
3

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) – davide79 Aug 19 '20 at 18:57

score 5 · Answer 5 · edited May 23 '17 at 12:02

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

score 2 · Answer 6 · answered Apr 20 '18 at 13:53

As an alternative way you can use DriverManagerDataSource such as:

public DataSource getDataSource(DBInfo db) {

    DriverManagerDataSource dataSource = new DriverManagerDataSource();

    dataSource.setUsername(db.getUsername());
    dataSource.setPassword(db.getPassword());
    dataSource.setUrl(db.getUrl());
    dataSource.setDriverClassName(db.getDriverClassName());

    return dataSource;
}

However be careful about using it, because:

NOTE: This class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call. reference

`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).` — specializt, Oct 18 '18 at 15:12

score 1 · Answer 7 · answered Sep 12 '19 at 23:05

for springboot 2.1.7 working with url seems not to work. change with jdbcUrl instead.

In properties:

security:
      datasource:
        jdbcUrl: jdbc:mysql://ip:3306/security
        username: user
        password: pass

In java:

@ConfigurationProperties(prefix = "security.datasource")
@Bean("dataSource")
@Primary
public DataSource dataSource(){

    return DataSourceBuilder
            .create()
            .build();
}

score 0 · Answer 8 · answered Nov 22 '19 at 11:33

I customized Tomcat DataSource in Spring-Boot 2.

Dependency versions:

spring-boot: 2.1.9.RELEASE
tomcat-jdbc: 9.0.20

May be it will be useful for somebody.

application.yml

spring:
    datasource:
        driver-class-name: org.postgresql.Driver
        type: org.apache.tomcat.jdbc.pool.DataSource
        url: jdbc:postgresql://${spring.datasource.database.host}:${spring.datasource.database.port}/${spring.datasource.database.name}
        database:
            host: localhost
            port: 5432
            name: rostelecom
        username: postgres
        password: postgres
        tomcat:
            validation-query: SELECT 1
            validation-interval: 30000           
            test-on-borrow: true
            remove-abandoned: true
            remove-abandoned-timeout: 480
            test-while-idle: true
            time-between-eviction-runs-millis: 60000
            log-validation-errors: true
            log-abandoned: true

Java

@Bean
@Primary
@ConfigurationProperties("spring.datasource.tomcat")
public PoolConfiguration postgresDataSourceProperties() {
    return new PoolProperties();
}

@Bean(name = "primaryDataSource")
@Primary
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource primaryDataSource() {
    PoolConfiguration properties = postgresDataSourceProperties();
    return new DataSource(properties);
}

The main reason why it had been done is several DataSources in application and one of them it is necessary to mark as a @Primary.

Configure DataSource programmatically in Spring Boot

8 Answers8

Linked