Here is my setup: spring boot 3 + hibernate 6 + postgres 15
I have an entity with field that is enum:
@Slf4j
@Getter
@Setter
@Entity
@Table(name = "foo")
public class FooEntity {
@Column(name = "favorite_fruit_type", nullable = true, columnDefinition = "some_enum_type_name")
@Enumerated(EnumType.STRING)
@JdbcTypeCode(SqlTypes.NAMED_ENUM)
@JdbcType(PostgreSQLEnumJdbcType.class)
private FruitType fruitType;
}
//and enum
public enum FruitType {
APPLE, ORANGE, GRAPES
}
and I use dedicated enum type in postgres
create type some_enum_type_name as enum ('APPLE', 'ORANGE', 'GRAPES');
create table foo
(
favorite_fruit_type some_enum_type_name null,
...
)
All works fine when I save/update/read records with such field but when I try to make a query in repository:
@Query("""
select ...
where
fruitType in (
some.package.enums.FruitType.APPLE,
some.package.enums.FruitType.ORANGE
)
""")
List<FooEntity> findSomething();
I have sql exception as hibernate generated query uses wrong enum name in case operator.
Ignoring the name that I specified with
@Column(name = "favorite_fruit_type", nullable = true, columnDefinition = "some_enum_type_name")" )
and wrong generated sql is:
select .... where in ('APPLE'::FruitType,'ORANGE'::FruitType)
as you can see sql query uses cast to type equal to java enum class name and not name that I explicitly specified in column definition.
Any ideas ?
well,
I had my share of debugging hibernate code to find code that is responsible for generating sql and now can say for sure that absolutely ignores your column definition and supposes that enum name must match java class name:
org.hibernate.dialect.PostgreSQLEnumJdbcType
@Override
public <T> JdbcLiteralFormatter<T> getJdbcLiteralFormatter(JavaType<T> javaType) {
return (appender, value, dialect, wrapperOptions) -> appender.appendSql( "'" + ((Enum<?>) value).name() + "'::"
+ dialect.getEnumTypeDeclaration( (Class<? extends Enum<?>>) javaType.getJavaType() ) );
}
and method “dialect.getEnumTypeDeclaration” has no access to column definition and always returns java class name
why it was implemented so poorly I dunno: this is clear no-go for inherited db and vary bad design practice when names in java and postgres must match