Hibernate Validator检测不到users_roles表问题求助

启用hibernate.ddl-auto: validate配置后，JPA EntityManagerFactory初始化失败，错误提示Schema-validation: missing table [users_roles]。该表已通过Flyway迁移脚本在zhem模式下创建，尝试过单向、双向多对多等多种实体关联方式，问题仍未解决。

Flyway迁移脚本 #

create schema if not exists zhem;

create table if not exists zhem.users
(
    user_id    bigserial primary key,
    phone      varchar(11)  not null check ( phone similar to '79[0-9]{9}' ) unique,
    password   varchar(256) not null,
    email      varchar(256) check ( email similar to '([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9_-]+)' ) unique,
    first_name varchar(32)  not null check ( length(trim(first_name)) >= 2 ),
    last_name  varchar(32) check ( length(trim(last_name)) >= 2 ),
    created_at timestamp default now(),
    updated_at timestamp default now()
);

create table if not exists zhem.roles
(
    role_id serial primary key,
    title   varchar(32) not null check ( length(trim(title)) >= 2 ) unique
);

create table if not exists zhem.users_roles
(
    user_role_id bigserial primary key,
    user_id      bigint not null references zhem.users (user_id),
    role_id      int    not null references zhem.roles (role_id),
    constraint unique_user_id_role_id unique (user_id, role_id)
);

基础实体类 #

@Data
@MappedSuperclass
public class BaseEntity {

    @CreationTimestamp
    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @UpdateTimestamp
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

}

User实体类 #

@Data
@EqualsAndHashCode(callSuper = true)
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(schema = "zhem", name = "users")
public class User extends BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private Long id;

    @CheckPhoneNumber
    @Column(name = "phone", unique = true)
    private String phone;

    @NotBlank
    @Size(max = 256)
    @Column(name = "password")
    private String password;

    @Email
    @Size(max = 256)
    @Column(name = "email", unique = true)
    private String email;

    @NotBlank
    @Size(min = 2, max = 32)
    @Column(name = "first_name")
    private String firstName;

    @Size(min = 2, max = 32)
    @NullOrNotBlank
    @Column(name = "last_name")
    private String lastName;

    @ManyToMany(fetch = FetchType.EAGER)
    private Set<Role> roles;

}

Role实体类 #

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(schema = "zhem", name = "roles")
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "role_id")
    private Integer id;

    @NotBlank
    @Size(min = 2, max = 32)
    @Column(name = "title", unique = true)
    private String title;

}

application.yaml配置 #

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/zhem-db
    username: postgres
    password: root
  flyway:
    schemas:
      - public
      - zhem
  jpa:
    hibernate:
      ddl-auto: validate

排查与解决步骤 #

1. 显式指定中间表的schema和名称 #

Hibernate默认会根据实体名生成中间表名，且不会自动关联到zhem schema。需在@ManyToMany注解中通过@JoinTable明确指定：

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(
    schema = "zhem",
    name = "users_roles",
    joinColumns = @JoinColumn(name = "user_id"),
    inverseJoinColumns = @JoinColumn(name = "role_id")
)
private Set<Role> roles;

2. 配置JPA默认schema #

Hibernate可能默认使用public schema进行校验，在application.yaml中添加默认schema配置：

spring:
  jpa:
    properties:
      hibernate.default_schema: zhem

3. 确认Flyway脚本执行顺序 #

确保创建users_roles表的迁移脚本是最早执行的一批，Flyway脚本需遵循V<版本号>__<描述>.sql命名格式（如V1__init_schema.sql），避免Hibernate校验时表还未创建。

4. 验证数据库表存在性 #

直接连接数据库，执行\dt zhem.*（PostgreSQL命令）检查zhem schema下是否确实存在users_roles表，排除脚本执行失败导致表未创建的情况。

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