Set default schema = SOMETHING in oracle using Spring Boot and Spring JDBC

Question

I am working now with oracle and spring jdbc but I don't want to use the schema in my sql statements:

Example: Select * from SCHEMA.table

Is there any way to set default schema in application.properties or application.yml?

Answer 1

Assuming you define your database connections using spring datasources, you can set the default schema when defining the datasource configuration:

spring.datasource.schema = #value for your default schema to use in database

You can find more info here: Spring Boot Reference Guide. Appendix A. Common application properties

---

After doing some research, looks like Oracle driver doesn't let you set a default schema to work with, as noted here:

Default Schema in Oracle Connection URL

From that post, you have two options:

1. Execute this statement before executing your statements:

   ALTER SESSION SET CURRENT_SCHEMA=yourSchema
   

2. Create synonyms for your tables/views/etc (which I find really cumbersome if we're talking about lots of elements in your database).

I would advice using the first option. From what I see, Spring boot doesn't offer a simple way to execute a statement when retrieving the connection, so the best bet will be to use an aspect around the getConnection method (or the method that retrieves the connection from the data source) and execute the statement there.

---

From your comment, an easier way to solve it is by using a script in spring.datasource.schema:

spring.datasource.schema = schema.sql

And then a file squema.sql with the following:

ALTER SESSION SET CURRENT_SCHEMA=mySchema

Answer 2

In spring boot, I've found another way of doing it,

@Bean
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource(@Value("${spring.datasource.schema}") String schema) {
    DataSource datasource = DataSourceBuilder.create().build();
    if(!schema.isEmpty() && datasource instanceof org.apache.tomcat.jdbc.pool.DataSource){
            ((org.apache.tomcat.jdbc.pool.DataSource) datasource).setInitSQL("ALTER SESSION SET CURRENT_SCHEMA=" + schema);
    }
    return datasource;
}