Configure OracleDataSource programmatically in Spring Boot with a default schema

Question

How to configure Oracle DataSource programmatically in Spring Boot with a default schema?

@Bean
public DataSource getDataSource() throws SQLException {
    OracleDataSource d = new OracleDataSource();
    d.setURL(Secrets.get("DB_URL"));
    d.setUser(Secrets.get("DB_USER"));
    d.setPassword(Secrets.get("DB_PASS"));
    // d.setSchema(System.getenv("DB_SCHEMA")); ???
    return d;
}

is it possible to use application.properties file for configuration or need manual datasource creation? — Maxim Kasyanov
– Maxim Kasyanov, Commented Jul 12, 2018 at 16:38
I see good property in spring configuration file reference spring.datasource.schema but, unfortunatelly, can't figure out how to apply this programmatically. (manual datasource creation needed) — Daryl
– Daryl, Commented Jul 12, 2018 at 16:43

Karol Dowbecki · Accepted Answer · 2018-07-12 16:36:21Z

3

You can't change the schema in the OracleDataSource or using connection URL, you need to execute

ALTER SESSION SET CURRENT_SCHEMA=targetschema;

statement as explained in this answer. According to Connection Properties Recognized by Oracle JDBC Drivers there is no driver property for initial schema.

answered Jul 12, 2018 at 16:36

Karol Dowbecki

45.3k9 gold badges83 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daryl Over a year ago

Thanks Karol for answer. I know about this alter session trick but I need to find a good place to put this sql when connection initializing. Can you suggest this ?

Karol Dowbecki Over a year ago

@Daryl is it mandatory to use the OracleDataSource? Other pooling libraries have a property for this, e.g. HiakriCP offers connectionInitSql config attribute

Daryl Over a year ago

Thank you for advice Karol. Looks like HikariDataSource does the job what I need. It has datasource wrapper method and connectionInitSql.

HikariDataSource hikariDs = new HikariDataSource(); hikariDs.setDataSource(oracleDs); hikariDs.setConnectionInitSql("ALTER SESSION SET CURRENT_SCHEMA = SOME_SCHEMA");

Accepting your answer. Please update accordingly.

Daryl · Accepted Answer · 2018-07-13 09:45:11Z

2

Full example:

@Bean
public DataSource getDataSource() throws SQLException {
    OracleDataSource oracleDs = new OracleDataSource();
    oracleDs.setURL(Secrets.get("DB_URL"));
    oracleDs.setUser(Secrets.get("DB_USER"));
    oracleDs.setPassword(Secrets.get("DB_PASS"));
    // other Oracle related settings...

    HikariDataSource hikariDs = new HikariDataSource();
    hikariDs.setDataSource(oracleDs);
    hikariDs.setConnectionInitSql("ALTER SESSION SET CURRENT_SCHEMA = MY_SCHEMA");

    return hikariDs;
}

answered Jul 13, 2018 at 9:45

Daryl

6841 gold badge9 silver badges21 bronze badges

1 Comment

jumping_monkey Over a year ago

You do not need to instantiate OracleDataSource, just HikariDataSource would do, with the appropriate settings.

Maxim Kasyanov · Accepted Answer · 2018-07-12 16:48:03Z

1

Try to add sql execution into datasources creation method

@Bean
public DataSource getDataSource() throws SQLException {
    OracleDataSource d = new OracleDataSource();
    d.setURL(Secrets.get("DB_URL"));
    d.setUser(Secrets.get("DB_USER"));
    d.setPassword(Secrets.get("DB_PASS"));

    Resource initSchema = new ClassPathResource("scripts/schema-alter.sql");
    DatabasePopulator databasePopulator = new ResourceDatabasePopulator(initSchema);
    DatabasePopulatorUtils.execute(databasePopulator, dataSource);

    return d;
}

In scripts/schema-alter.sql will be this code

ALTER SESSION SET CURRENT_SCHEMA=targetschema;

answered Jul 12, 2018 at 16:48

Maxim Kasyanov

1,0687 silver badges14 bronze badges

3 Comments

Daryl Over a year ago

Thanks Maxim for answer. HikariDataSource is more suitable for our purposes. Voting up your answer.

Maxim Kasyanov Over a year ago

@Daryl oooh, i didn't know that we have ability to change dataSources implementation. I glad that answer helped to you)

MK Yung Over a year ago

Will the schema-alter.sql being run for every new connection coming from this datasource?

skataja · Accepted Answer · 2020-04-23 05:53:53Z

1

In Spring Boot 2 the wanted schema can be set in application.properties file with the following property:

spring.datasource.hikari.connection-init-sql=ALTER SESSION SET CURRENT_SCHEMA = MY_SCHEMA

HikariCP is the default connection pool in Spring Boot 2. To see all HikariCP settings (including "connectionInitSql") in you log file add also the following in application.properties:

logging.level.com.zaxxer.hikari=DEBUG

answered Apr 23, 2020 at 5:53

skataja

912 silver badges4 bronze badges

Collectives™ on Stack Overflow

Configure OracleDataSource programmatically in Spring Boot with a default schema

4 Answers 4

3 Comments

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related