JPA查询无法自动转换PostgreSQL枚举类型的原因及问题

问题背景 #

映射至PostgreSQL的实体类：

@Entity
@Table(name = "price_project")
class PriceProject(
    @Column val priceEur: Double,
    @Column(name = "valid_from", columnDefinition = "TIMESTAMP WITH TIME ZONE")
    val validFrom: ZonedDateTime?,
    @Enumerated(EnumType.STRING)
    @Column val projectType: ProjectTypes
) {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Long = 0
}

对应的枚举类ProjectTypes：

@EnumNaming(EnumNamingStrategies.CamelCaseStrategy::class)
enum class ProjectTypes {
    national,
    international,
    trans_national,
    global,
}

price_project表及project_types枚举的创建语句：

CREATE TABLE public.price_project (
    id bigint NOT NULL,
    price double precision,
    project_type public.project_types NOT NULL,
    valid_from timestamp with time zone NOT NULL
);

CREATE TYPE public.project_types AS ENUM (
    'national',
    'international',
    'trans_national',
    'global'
);

查询尝试与错误 #

尝试使用ProjectTypes枚举创建JPA查询：

interface ProjectPriceRepository : JpaRepository<ProjectPrice, Long> {
    @Query("""
        SELECT p FROM PriceProject p
        WHERE p.validFrom <= :currentDate AND
        p.activityType = :projectType 
        """)
    fun findProject(
        @Param("projectType") projectType: ProjectTypes,
        @Param("validFrom") currentDate: ZonedDateTime,
    ): PriceProject?
}

触发错误：

JDBC exception executing SQL [select ... from price_project where ... project_type=? [ERROR: operator does not exist: project_type = character varying
Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 386] [n/a]; SQL [n/a]

改用原生查询：

@Query("""
    SELECT * FROM project_price p
    valid_from <= :currentDate AND
    activity_type = CAST(:projectType AS project_type)
    """,
    nativeQuery = true)

又出现错误：

ERROR: cannot cast type smallint to project_type

问题原因 #

1. JPA查询报错根源：
   使用@Enumerated(EnumType.STRING)时，JPA会将枚举值转为字符串传递给PostgreSQL，但PostgreSQL的自定义枚举类型project_types与字符串类型之间无默认相等运算符，导致数据库无法匹配操作符报错。

2. 原生查询报错根源：
   原生查询中JPA默认将枚举按序号（smallint类型）传递，而非字符串，此时用CAST(:projectType AS project_type)是尝试将smallint转为枚举类型，自然失败。

解决方法 #

方法1：修复JPA映射与查询 #

在实体类的枚举字段上明确指定数据库枚举类型，同时修正查询中的字段名与参数名错误：

@Entity
@Table(name = "price_project")
class PriceProject(
    @Column val priceEur: Double,
    @Column(name = "valid_from", columnDefinition = "TIMESTAMP WITH TIME ZONE")
    val validFrom: ZonedDateTime?,
    @Enumerated(EnumType.STRING)
    // 明确指定数据库枚举类型
    @Column(name = "project_type", columnDefinition = "public.project_types")
    val projectType: ProjectTypes
) {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Long = 0
}

修正后的JPA查询：

interface ProjectPriceRepository : JpaRepository<PriceProject, Long> {
    @Query("""
        SELECT p FROM PriceProject p
        WHERE p.validFrom <= :currentDate AND
        p.projectType = :projectType 
        """)
    fun findProject(
        @Param("projectType") projectType: ProjectTypes,
        // 参数名与查询中的占位符保持一致
        @Param("currentDate") currentDate: ZonedDateTime,
    ): PriceProject?
}

此方式下JPA会正确识别枚举与PostgreSQL自定义类型的映射，自动完成类型转换。

方法2：修复原生查询 #

强制JPA传递枚举的字符串值，再转换为数据库枚举类型，同时修正表名与字段名错误：

interface ProjectPriceRepository : JpaRepository<PriceProject, Long> {
    @Query("""
        SELECT * FROM price_project p
        WHERE p.valid_from <= :currentDate AND
        p.project_type = CAST(:projectType AS public.project_types)
        """,
        nativeQuery = true)
    fun findProject(
        // 接收字符串类型的枚举名称
        @Param("projectType") projectType: String,
        @Param("currentDate") currentDate: ZonedDateTime,
    ): PriceProject?
}

调用时传入枚举的名称：findProject(ProjectTypes.national.name, currentDate)。

内容的提问来源于stack exchange，提问作者four-eyes