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

I found another way to get around this by updating entity class with

@Table(schema = "SCHEMA_NAME" ,name = "TABLE_NAME")

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

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