Why doesn't JPA Query cast enum automatically?

Question

This is my entity (mapping to Postgres)

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

This is the enum ProjectTypes

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

And this is the CREATE statement for the table price_project with the enum project_type.

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',
);

Now, I am trying to create a query with the enum ProjectTypes

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

But this gives me

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]

Why doesn't JPA not type cast automatically? I am trying with a native query

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

But this gives me

[ERROR: cannot cast type smallint to project_type]

Answer 1

The solution to this is

@JdbcType(PostgreSQLEnumJdbcType::class) // org-hibernate-orm:hibernate-core
@Enumerated
@Column(columnDefinition = "project_types") // make sure this is the exact name of the type in postgres
val projectType: ProjectTypes

which makes this one

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

work.

If you have an array of enum types in Postgres, you need to do the following

@Type(
    value = EnumArrayType::class,
    parameters = [Parameter(name = AbstractArrayType.SQL_ARRAY_TYPE, value = "project_types")]
) // this tells hibernate what type the elements have in the array, thus project_types without []!
@Column(columnDefinition = "project_types[]") val projectTypes: Array<ProjectTypes>  // note the [] here. Also, you have to use an array. A list won't work!

You also need an additional dependency

implementation("io.hypersistence:hypersistence-utils-hibernate-63:3.7.0")

The most recent version of Spring Boot won't work with it. There is a bug. Downgrade to org.springframework.boot version "3.2.0".