How to use `PostgreSQLEnumType` with Hibernate 6?

[dmitry-weirdo]

In Hibernate 6, org.hibernate.annotations.TypeDef has been removed, and org.hibernate.annotations.Type has been changed.

Therefore, this tutorial Is unfortunately not relevant anymore.

How to set the enum type with Hibernate 6 and PostgreSQLEnumType?

We can set the class in the @Type annotation on an enum field, like this

    @Type(value = PostgreSQLEnumType.class)

, but how do we specify the name of the enum type in the PostgreSQL Database? Earlier it was in @Type(type) and @TypeDef(name) annotation attributes.

[vladmihalcea]

The hibernate-types-60 provides plenty of integration tests that not only will tell you how to use these mappings, but you can run them as well and use a reference.

Here's the Hibernate 6 PostgreSQLEnumTest:

@Enumerated(EnumType.STRING)
@Column(columnDefinition = "post_status_info")
@Type(PostgreSQLEnumType.class)
private PostStatus status;

You don't really have to do anything special. It basically works as before, even if the Hibernate @Type annotation has changed.

but how do we specify the name of the enum type in the PostgreSQL Database?

That was never required by Hibernate Types. The PostgreSQL enum type is the responsibility of the DB schema tool, like Flyway. Hibernate Types doesn't need it.

[BParnikel]

Hi @vladmihalcea, thank you for this example provided and all your effort. I have a question, do I get it right that PostgreSQLEnumType doesn't work with ddl-auto=create in tests anymore? I believe in Hibernate 5 VARCHAR type was used in this case, but in Hibernate 6 it throws No type mapping for org.hibernate.type.SqlTypes code: 1111 (OTHER). I understand it and see Flyway for tests as a workaround. But it requires a lot of changes for us, so I'm just wondering if it is possible to make it work with ddl-auto=create? Thank you!

[vladmihalcea]

@BParnikel The ddl-auto=create uses the JPA mappings, so as long as you can use columnDefinition, you shouldn't have any issues. For custom types like PostgreSQL enums, the hbm2ddl tool cannot generate the custom types for you, no matter the version of Hibernate.

As for No type mapping for org.hibernate.type.SqlTypes code: 1111 (OTHER), that's not from hbm2dll. That issue is thrown after Hibernate bootstraps, so it's unrelated to hbm2dll.

[BParnikel]

@vladmihalcea thank you for quick response. I agree this behaviour makes sense and with Flyway we'd just use columnDefinition='my_enum_type'. The thing is that it worked in Hibernate 5 (probably not intentionally) as ddl was using VARCHAR in this case. So I'd prefer to migrate it to Spring 3/Hibernate 6 first and make it work and then make a switch from ddl-auto to Flyway... So there is no way to give a jdbc type hint for ddl for tests purposes only?

Re exception, I think it is related to ddl as wrapping exception is Error creating SQL create commands for table : my_table.

[vladmihalcea]

If you are using a VARCHAR column to store the Enum, then just use the default Hibernate type. No need to use the PostgreSQLEnumType.

[BParnikel]

@vladmihalcea I have Postgres Enum column in prod database, so PostgreSQLEnumType makes sense. For tests though it was VARCHAR created by ddl. Now it doesn't work because it cannot get descriptor for OTHER (1111) type code in org.hibernate.type.descriptor.sql.spi.DdlTypeRegistry#getTypeName. Which is interesting taken into account jdbcType for EnumType is VarcharJdbcType in case @Enumerated(EnumType.STRING) is specified... But not sure if ddl has something to do with custom types

Upd: looks like columnDefinition = "varchar" does the trick. Let me check if it will work against real db

[vladmihalcea]

Not using the same DB for testing is very bad. Thanks to Testcontainers, it's very easy to use the same DB engine for tests.

[avinashjeevanandham]

PostgreSQLEnumType is broken with spring boot 3.1.1

[vladmihalcea]

@avinashjeevanandham No, it's not broken at all. Here's the proof that it works just fine with Hibernate 6.2.

[avinashjeevanandham]

Sorry, i was not aware of this new module. I was using com.vladmihalcea:hibernate-types-60 Thank you

[rs10615]

@vladmihalcea I am running into an issue after migration of existing project to Spring Boot 3 and Java 17, I am using the following dependency . I would appreciate your help

implementation (group: 'com.vladmihalcea', name: 'hibernate-types-60', version: '2.20.0')

    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "import_status")
    @Type(PostgreSQLEnumType.class)
    private RMAFileStatusEnum importStatus;

The error Message I am getting on running the application is as -

---

APPLICATION FAILED TO START

---

Description:

An attempt was made to call a method that does not exist. The attempt was made from the following location:

com.vladmihalcea.hibernate.type.basic.PostgreSQLEnumType.setParameterValues(PostgreSQLEnumType.java:54)

The following method did not exist:

 'org.hibernate.metamodel.model.convert.spi.EnumValueConverter org.hibernate.type.EnumType.getEnumValueConverter()'

The calling method's class, com.vladmihalcea.hibernate.type.basic.PostgreSQLEnumType, was loaded from the following location:**

    jar:file:/Users/global/.gradle/caches/modules-2/files-2.1/com.vladmihalcea/hibernate-types-60/2.20.0/b56eced44eb94e20cc71e5a0367885df9c099cae/hibernate-types-60-2.20.0.jar!/com/vladmihalcea/hibernate/type/basic/PostgreSQLEnumType.class

[vladmihalcea]

@rs10615 This test case shows you that the PostgreSQLEnumType works just fine with Hibernate 6.2.

[brandonfl]

Hello,

I am getting the same issue :

com.vladmihalcea.hibernate.type.basic.PostgreSQLEnumType.setParameterValues(PostgreSQLEnumType.java:54)

I am using Spring boot v3.2.0 that use hibernate v6.3.1

@rs10615 Did you managed to fix this ? @vladmihalcea Your test case is for hibernate 6.2 but did it work for hibernate 6.3 ?

Thanks in advance for the help :smile:

[tillkuhn]

@brandonfl I was stuck with a similar issue before I realized that PostgreSQLEnumType has been deprecated in hypersistence-utils-hibernate-62 and eventually removed in hypersistence-utils-hibernate-63 in favor of Hibernate's new PostgreSQLEnumJdbcType

So this should work (kotlin code):

@Enumerated(EnumType.STRING)
@Column(columnDefinition = "level")
@JdbcType(PostgreSQLEnumJdbcType::class) // from package org.hibernate.dialect
var level: Area_Level = Area_Level.COUNTRY,

As opposed to former PostgreSQLEnumType, the postgres type name was not translated from CamelCase to under_score case automatically, so I had to rename the enum class, but there's hopefully a better way to control this.

Related issue: Add a hypersistence-utils-hibernate-63 module for Hibernate 6.3 #657

[brandonfl]

@brandonfl I was stuck with a similar issue before I realized that PostgreSQLEnumType has been

deprecated in hypersistence-utils-hibernate-62 and eventually removed in hypersistence-utils-hibernate-63 in favor of Hibernate's new PostgreSQLEnumJdbcType

So this should work (kotlin code):

@Enumerated(EnumType.STRING)

@Column(columnDefinition = "level")

@JdbcType(PostgreSQLEnumJdbcType::class) // from package org.hibernate.dialect

var level: Area_Level = Area_Level.COUNTRY,

As opposed to former PostgreSQLEnumType, the postgres type name was not translated from CamelCase to under_score case automatically, so I had to rename the enum class, but there's hopefully a better way to control this.

Related issue: Add a hypersistence-utils-hibernate-63 module for Hibernate 6.3 #657

Thanks for the help ☺️

[kaperusov]

Hi there,

I am using spring boot v3.2.0 and hibernate v6.3.1

And tring to use JdbcType annotation for my enum field:

enum STATUS {
    DRAFT, NEW, IN_PROGRESS, COMPLETED, REJECTED;
}

@Enumerated(EnumType.STRING)
@Column(name = "status")
@JdbcType(PostgreSQLEnumJdbcType.class)
private STATUS status;

Next, I wrote a method in my Repository interface with native query as follows:

@Query(nativeQuery = true, value = "SELECT * FROM requests r WHERE r.status IN (?1)")
List<Request> getRequestsByStatus(List<Request.STATUS> statuses);

When I call the getRequestsByStatus method, I get an error:

Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [SELECT * FROM requests r WHERE r.status IN (?,?,?,?,?)] [ERROR: operator does not exist: request_statuses = smallint

Oh, yeah. In the database, the status column also has the ENUM type (request_statuses). But it doesn't matter here, as I think.

Digging a bit into the Hibernate code, I found the following:

link to source L149

    private static <E extends Enum<E>> BasicTypeImpl<E> createEnumType(ExecutionContext executionContext, Class<E> enumClass) {
        final EnumJavaType<E> enumJavaType = new EnumJavaType<>( enumClass );
        final JdbcTypeIndicators indicators =
                executionContext.getSession().getTypeConfiguration().getCurrentBaseSqlTypeIndicators();
        final JdbcType jdbcType =
                // we don't know whether to map the enum as ORDINAL or STRING,
                // so just accept the default from the TypeConfiguration, which
                // is usually ORDINAL (the default according to JPA)
                enumJavaType.getRecommendedJdbcType(indicators);
        return new BasicTypeImpl<>( enumJavaType, jdbcType );
    }

As I understand it, the problem is to specify JdbcType as STRING instead of ORDINAL, but I don't know how to do that. Can anyone tell me how to do it, and is it possible in version 6.3.1?

[AndrewLazarus]

@kaperusov Do you need a cast in the Native Query? (cast as pg_enum_whatever_name)?

[PierrickPuimeanChieze]

@kaperusov It seems that you need to use the annotation @JdbcTypeCode(SqlTypes. NAMED_ENUM) Extract from PostgreSQLEnumJdbcType javadoc :

Hibernate does not automatically use this for enums mapped as EnumType. STRING, and instead this type must be explicitly requested using: @JdbcTypeCode(SqlTypes. NAMED_ENUM)