Spring boot with manual database configuration picking up incorrect table names

Question

I've a spring boot setup (2.5.5) using a postgresql db using autoconfiguration which is working fine below is the current configuration

spring.datasource.url=${DATASOURCE_URL}
spring.datasource.username=${DATASOURCE_USERNAME}
spring.datasource.password=${DATASOURCE_PASSWORD}
spring.datasource.hikari.maximum-pool-size=${DB_POOL_MAX_SIZE:2}
spring.datasource.hikari.minimum-idle=${DB_POOL_MIN_IDLE:1}




@Configuration
@ComponentScan(basePackages = {"com.xyz.querty"})
@EntityScan(basePackages = "com.xyz.querty")
@EnableJpaRepositories(basePackages = "com.xyz.querty")
public class AppConfig {
}

Now i have to connect to another database also hence i cant do autoconfiguration anymore.

So i've to create manual configuration for jdbc, jpa, hibernate, hikari etc

Changed configuration :

spring.auth-db.jdbcUrl=${DATASOURCE_URL}
spring.auth-db.url=${DATASOURCE_URL}
spring.auth-db.username=${DATASOURCE_USERNAME}
spring.auth-db.password=${DATASOURCE_PASSWORD}
spring.auth-db.driver-class-name=org.postgresql.Driver
spring.auth-db.hikari.maximum-pool-size=${DB_POOL_MAX_SIZE:2}
spring.auth-db.hikari.minimum-idle=${DB_POOL_MIN_IDLE:1}


spring.mas-db.url=${MAS_DATASOURCE_URL}
spring.mas-db.username=${MAS_DATASOURCE_USERNAME}
spring.mas-db.password=${MAS_DATASOURCE_PASSWORD}
spring.mas-db.driver-class-name=org.postgresql.Driver
spring.mas-db.hikari.maximum-pool-size=${MAS_DB_POOL_MAX_SIZE:2}
spring.mas-db.hikari.minimum-idle=${MAS_DB_POOL_MIN_IDLE:1}



@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    entityManagerFactoryRef = "primaryEntityManagerFactory",
    transactionManagerRef = "primaryTransactionManager",
    basePackages = { com.xyz.querty.auth.domain.repository" }
)
public class PrimaryDBConfig {

    @Bean(name="primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.auth-db")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }




    @Bean(name = "primaryTransactionManager")
    @Primary
    public PlatformTransactionManager primaryTransactionManager(
        @Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {
        return new JpaTransactionManager(primaryEntityManagerFactory);
    }

    @Bean(name = "primaryEntityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory(
        @Qualifier("primaryDataSource") DataSource dataSource) {

        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource);
        emf.setPackagesToScan("com.xyz.querty.auth.domain.model"); // Package containing your JPA entities
        emf.setPersistenceUnitName("primary");
        emf.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        Map<String, Object> properties = new HashMap<>();

        properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect"); // Change to your database dialect
        emf.setJpaPropertyMap(properties);
        return emf;
    }



}

and similar config for secondary DB also

However this is not working when i try to perform some db transaction, The error message that i get is:

    could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement","error.stack_trace":"org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

org.postgresql.util.PSQLException: ERROR: relation \"entityname\" does not exist\n  Position: 13

However the correct table name should have been entity_name. There is something wrong with the configuration. Can you help me figure it out. Also what is the best way to achieve this with minimum changes? I dont want to end up in a scenario where i've to manually keep on adding more and more settings which were a part of spring autoconfiguration originally

Answer 1

You need to explicit define the Naming Strategy at LocalContainerEntityManagerFactoryBean, here you can create an HashMap with these properties and set in EntityBean using setJpaPropertyMap()

 private Map<String, Object> primaryJpaProperties() {
    Map<String, Object> props = new HashMap<>();
    props.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
    props.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
    props.put("hibernate.dialect","org.hibernate.dialect.PostgreSQLDialect");
    return props;
}

  @Bean(name = "primaryTransactionManager")
  @Primary
public PlatformTransactionManager primaryTransactionManager(
    @Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {
    return new JpaTransactionManager(primaryEntityManagerFactory);
}

@Bean(name = "primaryEntityManagerFactory")
@Primary
public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory(
    @Qualifier("primaryDataSource") DataSource dataSource) {

    LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
    emf.setDataSource(dataSource);
    emf.setPackagesToScan("com.xyz.querty.auth.domain.model"); // Package containing your JPA entities
    emf.setPersistenceUnitName("primary");
    emf.setJpaPropertyMap(jpaProperties());
    emf.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    return emf;
}