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

Answer 3

If you are using hikari, use spring.datasource.hikari.schema=YOUR_SCHEMA. Works for me with SpringBoot + tomcat using Oracle.

Answer 4

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

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

Answer 5

I was having issues with the currently accepted answer; specifically, the schema would only be changed from the initial connection. If your app uses a connection pool, you need to configure the pool to apply SQL for each connection.

For instance, using the default jdbc pool in Spring Boot 1.5.x (Tomcat):

spring.datasource.tomcat.init-s-q-l = ALTER SESSION SET CURRENT_SCHEMA=mySchema

Answer 6

Connecting to the database as your user, you can create a trigger that will change the schema each time you login:

CREATE OR REPLACE TRIGGER LOGON_TRG 
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foo';
EXCEPTION 
  when others 
    then null;
END;
/  

Answer 7

Another option is to create a datasource wrapper. Create the datasource as normal and then create the wrapper that forwards all methods except for the getConnection methods. For those I just added SQL to set the schema. We have multiple datasources and this allowed us to specify a different schema for each datasource. If anyone knows if there's an issue with this I'd love comments. Or if there's an alternative that uses the properties.

Answer 8

@Entity
@Table(schema = "Schema_name",  name = "table_name")