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

Jose Diaz Diaz picture Jose Diaz Diaz · Jun 22, 2016 · Viewed 39.2k times · Source

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

Luiggi Mendoza picture Luiggi Mendoza · Jun 22, 2016

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