Spring JPA + Oracle 12 C Schema Validation Issue

Question

Facing schema validation issue in "Spring Boot + JPA + Oracle 12C". This work as expected with Postgres database.

After debugging further I found that the below code snippet from class InformationExtractorJdbcDatabaseMetaDataImpl returns an empty table list due to which hibernate tries to create tables instead of update existing tables.

ResultSet resultSet = 
     extractionContext.getJdbcDatabaseMetaData()
                      .getTables(catalogFilter,
                                 schemaFilter,
                                 "%",
                                 tableTypes);   

Datasource/TransactionManager etc. Configuration

@Primary
@Bean(name = "first-db")
public DataSource firstDataSource() {
    HikariDataSource hikariDS = new HikariDataSource();
    hikariDS.setJdbcUrl(env.getProperty("primary.datasource.jdbc-url"));
    hikariDS.setUsername(env.getProperty("primary.datasource.username"));
    hikariDS.setPassword(env.getProperty("primary.datasource.password"));
    hikariDS.setDriverClassName(env.getProperty("primary.datasource.driver-class-name"));      
    
    hikariDS.setConnectionTimeout(env.getProperty("primary.datasource.hikari.connection-timeout", Integer.class));
    hikariDS.setIdleTimeout(env.getProperty("primary.datasource.hikari.idle-timeout", Integer.class));
    hikariDS.setMaxLifetime(env.getProperty("primary.datasource.hikari.max-lifetime", Integer.class));
    hikariDS.setMinimumIdle(env.getProperty("primary.datasource.hikari.minimum-idle", Integer.class));      
    hikariDS.setMaximumPoolSize(env.getProperty("primary.datasource.hikari.maximum-pool-size", Integer.class));     
    hikariDS.setPoolName(env.getProperty("primary.datasource.hikari.pool-name", String.class));
    
    return hikariDS;
}


@Primary
@Bean(name = "firstEntityManagerFactory")
@Qualifier("primary")
public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory() {

    LocalContainerEntityManagerFactoryBean entityManagerFactory =  new LocalContainerEntityManagerFactoryBean();

    entityManagerFactory.setDataSource(firstDataSource());

    // Classpath scanning of @Component, @Service, etc annotated class
    entityManagerFactory.setPackagesToScan(
            env.getProperty("first.entitymanager.packagesToScan"));

    // Vendor adapter
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setShowSql(Boolean.getBoolean(env.getProperty("first.hibernate.show_sql")));
    vendorAdapter.setDatabasePlatform(env.getProperty("first.hibernate.dialect"));
    vendorAdapter.setDatabase(Database.ORACLE); //Database.POSTGRESQL

    // Hibernate properties
    Properties jpaProperties = new Properties();
    jpaProperties.setProperty("hibernate.dialect", env.getProperty("first.hibernate.dialect"));
    jpaProperties.setProperty("hibernate.show_sql", env.getProperty("first.hibernate.show_sql"));
    jpaProperties.setProperty("hibernate.hbm2ddl.auto", env.getProperty("first.hibernate.hbm2ddl.auto"));
    jpaProperties.setProperty("database-platform", env.getProperty("first.hibernate.dialect"));
    jpaProperties.setProperty("hibernate.temp.use_jdbc_metadata_defaults", "false");

    jpaProperties.setProperty("hibernate.globally_quoted_identifiers", "true");
    jpaProperties.setProperty("spring.jpa.hibernate.ddl-auto", "update");
    jpaProperties.setProperty("hibernate.ddl-auto", "update");

    entityManagerFactory.setJpaVendorAdapter(vendorAdapter);
    entityManagerFactory.setJpaProperties(jpaProperties);
    entityManagerFactory.setPersistenceUnitName("EWS-Primary-DB");

    return entityManagerFactory;
}

@Primary
@Bean(name = "firstTransactionManager")
public PlatformTransactionManager firstTransactionManager() {       
    JpaTransactionManager tm = new JpaTransactionManager();
    tm.setEntityManagerFactory(firstEntityManagerFactory().getObject());

    return tm;
}

Framework Code (GroupSchemaMigratorImpl.java)

final NameSpaceTablesInformation tables = existingDatabase.getTablesInformation( namespace );
for ( Table table : namespace.getTables() ) {
    if ( schemaFilter.includeTable( table ) && table.isPhysicalTable() ) {
        checkExportIdentifier( table, exportIdentifiers );
        final TableInformation tableInformation = tables.getTableInformation( table );
        if ( tableInformation == null ) {
            createTable( table, dialect, metadata, formatter, options, targets );
        }
        else if ( tableInformation != null && tableInformation.isPhysicalTable() ) {
            tablesInformation.addTableInformation( tableInformation );
            migrateTable( table, tableInformation, dialect, metadata, formatter, options, targets );
        }
    }
}

Answer 1

hibernate.hbm2ddl.auto Automatically validates or exports schema DDL to the database when the SessionFactory is created with:

update : update the schema and create a new one if not exists

create : creates the schema, destroying previous data

create-drop : drops the schema when the application is stopped or SessionFactory is closed explicitly.

validate: validate the schema, makes no changes to the database, and this is the recommended one for a production environment.

I didn't notice the value of hibernate.hbm2ddl.auto since first.hibernate.hbm2ddl.auto is not defined in your question, anyway in your case you have to change it to validate or update.

just change the value of hibernate.hbm2ddl.auto to validate

Answer 2

The property "hibernate.temp.use_jdbc_metadata_defaults" was set to false in my code, this was causing entire problem. Issue has been resolved with below change hibernate.temp.use_jdbc_metadata_defaults=true