Spring Boot Database - Running Application with different DB User / Flyway

Question

i have currently an issue with my application. It is a Spring Boot Application where i am creating Database Tables via Flyway. For Flyway i created an DB User which has the sufficent rights to create DB Tables.

For the Application i would like to use a different DB User (APP_DB_USER) which should be used to insert the Business Data.

So far the Flyway Script is running perfectly and Tables are getting created I am also granting rights to the APP_DB_USER by using following command. (Schema name and User name are the same in my example)

GRANT ALL ON DB_USER.TABLE1 TO APP_DB_USER;

In SQL Developer i can run SELECT / INSERT Statements with the APP_DB_USER on the Tables of DB_USER (e.g DB_USER.TABLE1)

My issue is now in the spring boot application. I have set:

spring:
  datasource:
    url: DB_URL
    username: APP_DB_USER 
    password: password
    driver-class-name: oracle.jdbc.OracleDriver
    initialization-mode: EMBEDDED
    hikari:
      connection-timeout: 20000
      minimum-idle : 1
      maximum-pool-size : 2
      idle-timeout: 10000
      max-lifetime: 1000
      auto-commit: true
      schema: DB_USER

Application is starting correctly, but as soon as i am trying to save an Entity it is giving me the error message

java.sql.SQLSyntaxErrorException: ORA-00942: Table or View not found

Additionally i tried following property

jpa:
    show-sql: true
    properties:
      hibernate:
        default_schema: DB_USER
        dialect: org.hibernate.dialect.Oracle12cDialect

Does anybody has an idea what i am doing wrong. How can i created via Flyway with an different DB_User Tables which can be then used by the Application via a different APP_DB_USER

Are you sure that the app and flyway migration use the same schemas? — Maxim Popov, Apr 03 '20 at 16:43

score 0 · Answer 1 · answered Jul 09 '20 at 13:14

Probably Hikari cannot access the schema parameter because it is in a nested structure. This may occur if you have multiple data sources. The following solution worked for me:

In application.yml

spring:
  datasource:
    url: DB_URL
    username: APP_DB_USER 
    password: password
    driver-class-name: oracle.jdbc.OracleDriver
    initialization-mode: EMBEDDED
    connection-timeout: 20000
    minimum-idle : 1
    maximum-pool-size : 2
    idle-timeout: 10000
    max-lifetime: 1000
    auto-commit: true
    schema: DB_USER

In Application.class

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public HikariConfig firstHikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource firstDataSource() {
        return new HikariDataSource(firstHikariConfig());
    }

    @Bean
    public JdbcTemplate firstJdbcTemplate() {
        return new JdbcTemplate(firstDataSource());
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.second")
    public HikariConfig secondHikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource secondDataSource() {
        return new HikariDataSource(secondHikariConfig());
    }

    @Bean
    public JdbcTemplate secondJdbcTemplate() {
        return new JdbcTemplate(secondDataSource());
    }
}

Spring Boot Database - Running Application with different DB User / Flyway

1 Answers1