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, Jul 12 '18 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, Jul 12 '18 at 16:43

score 3 · Answer 1 · answered Jul 12 '18 at 16:36

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 '18 at 16:36

Karol Dowbecki

43,645
9
78
111

1

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 ? – Daryl Jul 12 '18 at 16:40
@Daryl is it mandatory to use the `OracleDataSource`? Other pooling libraries have a property for this, e.g. HiakriCP offers `connectionInitSql` config attribute – Karol Dowbecki Jul 12 '18 at 16:44
1

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 Jul 12 '18 at 17:00

score 2 · Accepted Answer · answered Jul 13 '18 at 09:45

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

You do not need to instantiate OracleDataSource, just HikariDataSource would do, with the appropriate settings. — jumping_monkey, Dec 16 '19 at 09:33

score 1 · Answer 3 · answered Jul 12 '18 at 16:48

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 '18 at 16:48

Maxim Kasyanov

938
5
14

Thanks Maxim for answer. HikariDataSource is more suitable for our purposes. Voting up your answer. – Daryl Jul 12 '18 at 17:03
@Daryl oooh, i didn't know that we have ability to change dataSources implementation. I glad that answer helped to you) – Maxim Kasyanov Jul 12 '18 at 17:04
Will the `schema-alter.sql` being run for every new connection coming from this datasource? – MK Yung Nov 19 '19 at 09:35

score 1 · Answer 4 · answered Apr 23 '20 at 05:53

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

Configure OracleDataSource programmatically in Spring Boot with a default schema

4 Answers4

Linked