Hibernate not creating PostgreSQL schema object using JPA

Question

I have the following code:

@Configuration
public class HibernateConfig {

    @Autowired
    private DataSourceProperties dataSourceProperties;

    @Autowired
    private JpaProperties jpaProperties;

    @Bean
    JpaVendorAdapter jpaVendorAdapter() {
        return new HibernateJpaVendorAdapter();
    }

    @Bean
    @Scope(
        value = ConfigurableBeanFactory.SCOPE_PROTOTYPE,
        proxyMode = ScopedProxyMode.TARGET_CLASS)
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(customDataSource(dataSourceProperties));
    }

    @Bean
    public DataSource customDataSource(DataSourceProperties properties) {

        final CustomHikariDataSource dataSource = properties.initializeDataSourceBuilder().type(CustomHikariDataSource.class).build();
        if (properties.getName() != null) {
            dataSource.setPoolName(properties.getName());
        }
        
        return dataSource;
    }

//    @Bean
//    LocalContainerEntityManagerFactoryBean entityManagerFactory(
////        DataSource dataSource,
//        MultiTenantConnectionProvider multiTenantConnectionProviderImpl,
//        CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl
//    ) {
//
//        Map<String, Object> jpaPropertiesMap = createJpaPropertiesMap(multiTenantConnectionProviderImpl, currentTenantIdentifierResolverImpl);
//
//        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
//
//        factoryBean.setDataSource(customDataSource(dataSourceProperties));
//        factoryBean.setPackagesToScan("com.sample.billing*");
//        factoryBean.setJpaVendorAdapter(this.jpaVendorAdapter());
//        factoryBean.setJpaPropertyMap(jpaPropertiesMap);
//
//        return factoryBean;
//    }


    private Map<String, Object> createJpaPropertiesMap(MultiTenantConnectionProvider multiTenantConnectionProviderImpl,
                                                       CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl) {
        Map<String, Object> jpaPropertiesMap = new HashMap<>(jpaProperties.getProperties());

        jpaPropertiesMap.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
        jpaPropertiesMap.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, multiTenantConnectionProviderImpl);
        jpaPropertiesMap.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, currentTenantIdentifierResolverImpl);

        jpaPropertiesMap.put(Environment.FORMAT_SQL, true);
        jpaPropertiesMap.put(Environment.SHOW_SQL, true);

        return jpaPropertiesMap;
    }
}


public class CustomHikariDataSource extends HikariDataSource {

    @Override
    public Connection getConnection() throws SQLException {

        Connection connection = super.getConnection();
        connection.setSchema("public");
        return connection;
    }
}

The project runs fine, but I would like to select the schema based on the client. Then, I uncomment the bean in the first class which was provided earlier:

@Bean
    LocalContainerEntityManagerFactoryBean entityManagerFactory(
//        DataSource dataSource,
        MultiTenantConnectionProvider multiTenantConnectionProviderImpl,
        CurrentTenantIdentifierResolver currentTenantIdentifierResolverImpl
    ) {

        Map<String, Object> jpaPropertiesMap = createJpaPropertiesMap(multiTenantConnectionProviderImpl, currentTenantIdentifierResolverImpl);

        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        factoryBean.setDataSource(customDataSource(dataSourceProperties));
        factoryBean.setPackagesToScan("com.sample.billing*");
        factoryBean.setJpaVendorAdapter(this.jpaVendorAdapter());
        factoryBean.setJpaPropertyMap(jpaPropertiesMap);

        return factoryBean;
    }

With the bean uncommented, I get the error provided:

2020-11-24 18:03:24.602 ERROR 31729 --- [   scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "account" does not exist
  Position: 515
2020-11-24 18:03:24.603  INFO 31729 --- [   scheduling-1] c.m.b.m.c.d.TenantConnectionProvider     : Release connection for tenant public
2020-11-24 18:03:24.608 ERROR 31729 --- [   scheduling-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) ~[spring-orm-5.3.1.jar:5.3.1]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) ~[spring-orm-5.3.1.jar:5.3.1]

The SQLGrammarException error comes because tables in the schema are not created but tables do create without this bean perfectly.

What's the issue here and how do I solve it?

2020-11-24 18:03:24.602 ERROR 31729 --- [ scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "account" does not exist , your table account doesnt exist. I dont know how the tables get created in your logic, but usually when I work with Hibernate, I use hibernate.hbm2ddl.auto Possible options: validate | update | create | create-drop Maybe try creating it for this datasource will resolve this? docs.spring.io/spring-boot/docs/1.1.0.M1/reference/html/… — jcompetence
– jcompetence, Commented Nov 24, 2020 at 12:40
@SusanMustafa Interestingly, this is working after I put this in the properties file. Thank you. — Arefe
– Arefe, Commented Nov 24, 2020 at 13:48
@SusanMustafa Please, write it as an answer and will accept it. — Arefe
– Arefe, Commented Nov 24, 2020 at 13:56

jcompetence · Accepted Answer · 2020-11-24 14:10:06Z

1

o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: relation "account" does not exist , your table account doesnt exist.

I dont know how the tables get created in your logic, but usually when I work with Hibernate, I use hibernate.hbm2ddl.auto Possible options: validate | update | create | create-drop

Maybe try creating it for this datasource will resolve this?

https://docs.spring.io/spring-boot/docs/1.1.0.M1/reference/html/howto-database-initialization.html

answered Nov 24, 2020 at 14:10

jcompetence

8,6233 gold badges28 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate not creating PostgreSQL schema object using JPA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related