SpringBoot多数据源下Hibernate自动更新Schema失效问题排查

使用SpringBoot搭配Hibernate，配置了两个PostgreSQL数据源：已存在的pims数据库和新建的powwow数据库，同时定义了对应实体类。启动SpringBoot后，Hibernate未自动在powwow库中创建表，插入数据时报错：ERROR: relation "powwowactivity" does not exist。已配置spring.jpa.hibernate.ddl-auto=update，怀疑多数据源导致该功能失效，需排查原因并解决。

相关配置与代码 #

application.properties #

# pims datasource
spring.datasource1.driver-class-name=org.postgresql.Driver
spring.datasource1.jdbc-url=jdbc:postgresql://localhost:5432/pims
spring.datasource1.username=postgres
spring.datasource1.password=postgres
# powwow datasource
spring.datasource2.driver-class-name=org.postgresql.Driver
spring.datasource2.jdbc-url=jdbc:postgresql://localhost:5432/powwow
spring.datasource2.username=postgres
spring.datasource2.password=postgres
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.database-platform=postgres
spring.jpa.hibernate.ddl-auto=update
#spring.jpa.properties.hibernate.hbm2ddl.auto=update
#spring.jpa.generate-ddl=true
#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.datasource2.dbcp2.test-while-idle=true
spring.datasource2.dbcp2.validation-query=select 1
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
# logging
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

实体类 PowWowActivityEntity.java #

@Entity
@Table(name = "powwowactivity")
public class PowWowActivityEntity {
    @Id
    @Column(name = "activity_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long activity_id;

    @Column(name = "status")
    private String status;
}

报错日志片段 #

2022-08-16 09:19:13.281 ERROR 65945 --- [        Async-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "powwowactivity" does not exist

核心原因 #

Spring Boot默认的全局JPA配置仅对主数据源生效，你配置的spring.jpa.hibernate.ddl-auto=update只会作用于pims数据库。powwow作为第二数据源，没有单独配置EntityManagerFactory来绑定对应的实体类和DDL策略，导致Hibernate无法识别该数据源下的实体，自然不会自动创建表。

具体解决步骤 #

1. 拆分数据源配置类 #

分别为两个数据源创建独立的配置类，明确绑定实体包、Repository包及JPA参数：

主数据源（pims）配置类

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.yourpackage.pims.repository", // 替换为你的pims Repository包路径
        entityManagerFactoryRef = "pimsEntityManagerFactory",
        transactionManagerRef = "pimsTransactionManager"
)
public class PimsDataSourceConfig {

    @Primary
    @Bean(name = "pimsDataSource")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSource pimsDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "pimsEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean pimsEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("pimsDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.yourpackage.pims.entity") // 替换为你的pims实体类包路径
                .persistenceUnit("pimsPU")
                .properties(getJpaProperties())
                .build();
    }

    @Primary
    @Bean(name = "pimsTransactionManager")
    public PlatformTransactionManager pimsTransactionManager(
            @Qualifier("pimsEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> getJpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        props.put("hibernate.hbm2ddl.auto", "update");
        props.put("hibernate.naming.implicit-strategy", "org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl");
        props.put("hibernate.naming.physical-strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
        return props;
    }
}

第二数据源（powwow）配置类

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.yourpackage.powwow.repository", // 替换为你的powwow Repository包路径
        entityManagerFactoryRef = "powwowEntityManagerFactory",
        transactionManagerRef = "powwowTransactionManager"
)
public class PowwowDataSourceConfig {

    @Bean(name = "powwowDataSource")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource powwowDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "powwowEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean powwowEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("powwowDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.yourpackage.powwow.entity") // 必须包含PowWowActivityEntity所在的包
                .persistenceUnit("powwowPU")
                .properties(getJpaProperties())
                .build();
    }

    @Bean(name = "powwowTransactionManager")
    public PlatformTransactionManager powwowTransactionManager(
            @Qualifier("powwowEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> getJpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        props.put("hibernate.hbm2ddl.auto", "update"); // 明确指定DDL自动更新策略
        props.put("hibernate.naming.implicit-strategy", "org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl");
        props.put("hibernate.naming.physical-strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
        return props;
    }
}

2. 调整application.properties #

移除全局JPA配置，避免与配置类冲突，保留数据源基础配置即可：

# pims datasource
spring.datasource1.driver-class-name=org.postgresql.Driver
spring.datasource1.jdbc-url=jdbc:postgresql://localhost:5432/pims
spring.datasource1.username=postgres
spring.datasource1.password=postgres

# powwow datasource
spring.datasource2.driver-class-name=org.postgresql.Driver
spring.datasource2.jdbc-url=jdbc:postgresql://localhost:5432/powwow
spring.datasource2.username=postgres
spring.datasource2.password=postgres

spring.datasource2.dbcp2.test-while-idle=true
spring.datasource2.dbcp2.validation-query=select 1

# logging
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

3. 关键验证点 #

• 确认PowWowActivityEntity所在的包被powwowEntityManagerFactory的packages()方法正确指定。
• 对应的Repository接口需放在配置类指定的Repository包下，并添加@Repository注解。
• 启动应用后查看Hibernate日志，确认是否生成了powwow库的表创建SQL。

额外注意事项 #

• 确保powwow数据库已手动创建（Hibernate仅自动创建表，不会创建数据库）。
• 检查数据库用户是否拥有创建表的权限。
• 确认实体类的@Entity注解导入正确（Spring Boot 2.x用javax.persistence.Entity，3.x用jakarta.persistence.Entity）。

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