Spring Boot手动配置多数据库时表名映射异常排查与优化方案

问题背景 #

原Spring Boot 2.5.5项目通过自动配置连接PostgreSQL运行正常，新增第二个数据库后改为手动配置多数据源，执行事务时抛出表不存在异常：实际表名为entity_name，但Hibernate尝试访问entityname（无下划线）。

原单库自动配置 #

配置文件（application.properties）

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 {
}

新多数据源手动配置 #

配置文件（application.properties）

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");
        emf.setPersistenceUnitName("primary");
        emf.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        Map<String, Object> properties = new HashMap<>();

        properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        emf.setJpaPropertyMap(properties);
        return emf;
    }
}

错误日志 #

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

排查原因 #

Spring Boot自动配置JPA时，会默认给PostgreSQL绑定SpringPhysicalNamingStrategy，该策略会将实体类的驼峰命名（如EntityName）自动转换为下划线分隔的数据库表名（如entity_name）。而手动配置EntityManagerFactory时，未指定该命名策略，Hibernate使用默认的命名规则，直接将类名转为小写（无下划线），导致找不到对应表。

最小改动修复方案 #

在手动配置的EntityManagerFactory中添加物理命名策略属性，与自动配置行为保持一致，无需额外添加其他自动配置属性。

修改PrimaryDBConfig中的firstEntityManagerFactory方法，在properties中添加一行：

properties.put("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");

修改后的完整方法：

@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");
    emf.setPersistenceUnitName("primary");
    emf.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    Map<String, Object> properties = new HashMap<>();

    properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
    // 添加Spring Boot默认的物理命名策略，实现驼峰转下划线
    properties.put("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
    emf.setJpaPropertyMap(properties);
    return emf;
}

同时，注意配置文件中spring.auth-db同时配置了jdbcUrl和url，HikariCP优先使用jdbcUrl，可以删除重复的spring.auth-db.url配置以避免混淆。

最后，将相同的修改应用到第二个数据源的配置类中即可。

内容的提问来源于stack exchange，提问作者Krittam Kothari