How to map PostgreSQL enum with JPA and Hibernate

I am trying to map a PostgreSQL custom type,named transmission_result, to a Hibernate/JPA POJO. The PostgreSQL custom type is more or less an enum type of string values.

I have created a custom EnumUserType called PGEnumUserType as well as an enum class representing the PostgreSQL enumerated values. When I run this against a real database, I receive the following error:

'ERROR: column "status" is of type transmission_result but expression is of type
character varying 
  Hint: You will need to rewrite or cast the expression.
  Position: 135 '

Upon seeing this, I figured I needed to change my SqlTypes to Types.OTHER. But doing so breaks my integration tests (using HyperSQL in memory DB) with the message:

'Caused by: java.sql.SQLException: Table not found in statement
[select enrollment0_."id" as id1_47_0_,
 enrollment0_."tpa_approval_id" as tpa2_47_0_,
 enrollment0_."tpa_status_code" as tpa3_47_0_,
 enrollment0_."status_message" as status4_47_0_,
 enrollment0_."approval_id" as approval5_47_0_,
 enrollment0_."transmission_date" as transmis6_47_0_,
 enrollment0_."status" as status7_47_0_,
 enrollment0_."transmitter" as transmit8_47_0_
 from "transmissions" enrollment0_ where enrollment0_."id"=?]'

I'm not sure why changing the sqlType results in this error. Any help is appreciated.

JPA/Hibernate Entity:

@Entity
@Access(javax.persistence.AccessType.PROPERTY)
@Table(name="transmissions")
public class EnrollmentCycleTransmission {

// elements of enum status column
private static final String ACCEPTED_TRANSMISSION = "accepted";
private static final String REJECTED_TRANSMISSION = "rejected";
private static final String DUPLICATE_TRANSMISSION = "duplicate";
private static final String EXCEPTION_TRANSMISSION = "exception";
private static final String RETRY_TRANSMISSION = "retry";

private Long transmissionID;
private Long approvalID;
private Long transmitterID;
private TransmissionStatusType transmissionStatus;
private Date transmissionDate;
private String TPAApprovalID;
private String TPAStatusCode;
private String TPAStatusMessage;


@Column(name = "id")
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
public Long getTransmissionID() {
    return transmissionID;
}

public void setTransmissionID(Long transmissionID) {
    this.transmissionID = transmissionID;
}

@Column(name = "approval_id")
public Long getApprovalID() {
    return approvalID;
}

public void setApprovalID(Long approvalID) {
    this.approvalID = approvalID;
}

@Column(name = "transmitter")
public Long getTransmitterID() {
    return transmitterID;
}

public void setTransmitterID(Long transmitterID) {
    this.transmitterID = transmitterID;
}

@Column(name = "status")
@Type(type = "org.fuwt.model.PGEnumUserType" , parameters ={@org.hibernate.annotations.Parameter(name = "enumClassName",value = "org.fuwt.model.enrollment.TransmissionStatusType")} )
public TransmissionStatusType getTransmissionStatus() {
    return this.开发者_运维问答transmissionStatus ;
}

public void setTransmissionStatus(TransmissionStatusType transmissionStatus) {
    this.transmissionStatus = transmissionStatus;
}

@Column(name = "transmission_date")
public Date getTransmissionDate() {
    return transmissionDate;
}

public void setTransmissionDate(Date transmissionDate) {
    this.transmissionDate = transmissionDate;
}

@Column(name = "tpa_approval_id")
public String getTPAApprovalID() {
    return TPAApprovalID;
}

public void setTPAApprovalID(String TPAApprovalID) {
    this.TPAApprovalID = TPAApprovalID;
}

@Column(name = "tpa_status_code")
public String getTPAStatusCode() {
    return TPAStatusCode;
}

public void setTPAStatusCode(String TPAStatusCode) {
    this.TPAStatusCode = TPAStatusCode;
}

@Column(name = "status_message")
public String getTPAStatusMessage() {
    return TPAStatusMessage;
}

public void setTPAStatusMessage(String TPAStatusMessage) {
    this.TPAStatusMessage = TPAStatusMessage;
}
}

Custom EnumUserType:

public class PGEnumUserType implements UserType, ParameterizedType {

private Class<Enum> enumClass;

public PGEnumUserType(){
    super();
}

public void setParameterValues(Properties parameters) {
    String enumClassName = parameters.getProperty("enumClassName");
    try {
        enumClass = (Class<Enum>) Class.forName(enumClassName);
    } catch (ClassNotFoundException e) {
        throw new HibernateException("Enum class not found ", e);
    }

}

public int[] sqlTypes() {
    return new int[] {Types.VARCHAR};
}

public Class returnedClass() {
    return enumClass;
}

public boolean equals(Object x, Object y) throws HibernateException {
    return x==y;
}

public int hashCode(Object x) throws HibernateException {
    return x.hashCode();
}

public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
    String name = rs.getString(names[0]);
    return rs.wasNull() ? null: Enum.valueOf(enumClass,name);
}

public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
    if (value == null) {
        st.setNull(index, Types.VARCHAR);
    }
    else {
        st.setString(index,((Enum) value).name());
    }
}

public Object deepCopy(Object value) throws HibernateException {
    return value;
}

public boolean isMutable() {
    return false;  //To change body of implemented methods use File | Settings | File Templates.
}

public Serializable disassemble(Object value) throws HibernateException {
    return (Enum) value;
}

public Object assemble(Serializable cached, Object owner) throws HibernateException {
    return cached;
}

public Object replace(Object original, Object target, Object owner) throws HibernateException {
    return original;
}

public Object fromXMLString(String xmlValue) {
    return Enum.valueOf(enumClass, xmlValue);
}

public String objectToSQLString(Object value) {
    return '\'' + ( (Enum) value ).name() + '\'';
}

public String toXMLString(Object value) {
    return ( (Enum) value ).name();
}
}

Enum class:

public enum TransmissionStatusType {
accepted,
rejected,
duplicate,
exception,
retry}

If you have following post_status_info enum type in PostgreSQL:

CREATE TYPE post_status_info AS ENUM (
    'PENDING', 
    'APPROVED', 
    'SPAM'
)

You can easily map Java Enum to a PostgreSQL Enum column type using the following custom Hibernate Type:

public class PostgreSQLEnumType extends org.hibernate.type.EnumType {
     
    public void nullSafeSet(
            PreparedStatement st, 
            Object value, 
            int index, 
            SharedSessionContractImplementor session) 
        throws HibernateException, SQLException {
        if(value == null) {
            st.setNull( index, Types.OTHER );
        }
        else {
            st.setObject( 
                index, 
                value.toString(), 
                Types.OTHER 
            );
        }
    }
}

To use it, you need to annotate the field with the Hibernate @Type annotation as illustrated in the following example:

@Entity(name = "Post")
@Table(name = "post")
@TypeDef(
    name = "pgsql_enum",
    typeClass = PostgreSQLEnumType.class
)
public static class Post {
 
    @Id
    private Long id;
 
    private String title;
 
    @Enumerated(EnumType.STRING)
    @Column(columnDefinition = "post_status_info")
    @Type( type = "pgsql_enum" )
    private PostStatus status;
 
    //Getters and setters omitted for brevity
}

That's it, it works like a charm. Here's a test on GitHub that proves it.

I figured it out. I needed to use setObject instead of setString in the nullSafeSet function and pass in the Types.OTHER as the java.sql.type to let jdbc know that it was a postgres type.

public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
    if (value == null) {
        st.setNull(index, Types.VARCHAR);
    }
    else {
//            previously used setString, but this causes postgresql to bark about incompatible types.
//           now using setObject passing in the java type for the postgres enum object
//            st.setString(index,((Enum) value).name());
        st.setObject(index,((Enum) value), Types.OTHER);
    }
}

The following might also help to have Postgres convert strings silently to your SQL enum type so you can use @Enumerated(STRING) and don't need @Type.

CREATE CAST (character varying as post_status_type) WITH INOUT AS IMPLICIT;

A quick solution will be

jdbc:postgresql://localhost:5432/postgres?stringtype=unspecified

?stringtype=unspecified is the answer

build.gradle.kts

dependencies {
    api("javax.persistence", "javax.persistence-api", "2.2")
    api("org.hibernate",  "hibernate-core",  "5.4.21.Final")
}

In Kotlin it is important to make a generic extension with EnumType<Enum<*>>()

PostgreSQLEnumType.kt

import org.hibernate.type.EnumType
import java.sql.Types

class PostgreSQLEnumType : EnumType<Enum<*>>() {

    @Throws(HibernateException::class, SQLException::class)
    override fun nullSafeSet(
            st: PreparedStatement,
            value: Any,
            index: Int,
            session: SharedSessionContractImplementor) {
        st.setObject(
                index,
                value.toString(),
                Types.OTHER
        )
    }
}

Custom.kt

import org.hibernate.annotations.Type
import org.hibernate.annotations.TypeDef
import javax.persistence.*

@Entity
@Table(name = "custom")
@TypeDef(name = "pgsql_enum", typeClass = PostgreSQLEnumType::class)
data class Custom(
        @Id @GeneratedValue @Column(name = "id")
        val id: Int,
    
        @Enumerated(EnumType.STRING) @Column(name = "status_custom") @Type(type = "pgsql_enum")
        val statusCustom: StatusCustom
)

enum class StatusCustom {
    FIRST, SECOND
}

A simpler option that I don't recommend is the first option in Arthur's answer which adds a parameter in the connection URL to the db so that the enum data type is not lost. I believe that the responsibility of mapping the data type between the backend server and the database is precisely the backend.

<property name="connection.url">jdbc:postgresql://localhost:5432/yourdatabase?stringtype=unspecified</property>

Source

As TypeDef has disappeared in Hibernate 6, and we thus need to annotate each affected property anyway, I've found that using

@ColumnTransformer(write="?::transmission_result ")

to force a type cast works, without any Hibernate usertype classes needed.

I solved this problem by creating a custom Hibernate Basic Type

reference Hibernate doc

Versions

1. Hibernate: 6.1.7.Final
2. SpringBoot: 3.0.3
3. PostgreSql: 15

LOG

If you want custom classes to log, write these in the application.properties file

logging.level.**.**.**.GenderType=trace

StudentEntity public class Student extends BaseEntity {

@Size(max = 30)
@NotNull
@Column(name = "student_id", nullable = false, length = 30)
private String studentId;

@Size(max = 40)
@NotNull
@Column(name = "name", nullable = false, length = 40)
private String name;

@NotNull
@Column(name = "major_id", nullable = false)
@ToString.Exclude
private Long majorId;

@Column(name = "gender", columnDefinition = "gender")
@Type(GenderType.class)
@Enumerated(EnumType.STRING)
private Gender gender;

@NotNull
@Column(name = "grade", nullable = false)
private Integer grade;

@Column(name = "user_id", nullable = false)
private Long userId;

@Column(name = "activity_id")
private Long activityId;}

GenderType

public class GenderType implements UserType<Gender> {
public static final GenderType INSTANCE = new GenderType();

private static final Logger log = Logger.getLogger(GenderType.class);
@Override
public int getSqlType() {
    return Types.OTHER;
}

@Override
public Class<Gender> returnedClass() {
    return Gender.class;
}

@Override
public boolean equals(Gender x, Gender y) {
    return x.getCode()
            .equals(y.getCode());
}

@Override
public int hashCode(Gender x) {
    return Objects.hashCode(x);
}

@Override
public Gender nullSafeGet(ResultSet rs, int position, SharedSessionContractImplementor session,
                          Object owner) throws SQLException {
    String columnValue = (String) rs.getObject(position);
    if (rs.wasNull()) {
        columnValue = null;
    }
    log.debugv("Result set column {0} value is {1}", position, columnValue);
    return Gender.fromCode(columnValue);
}

@Override
public void nullSafeSet(PreparedStatement st, Gender value, int index,
                        SharedSessionContractImplementor session) throws SQLException {
    if (value == null) {
        log.debugv("Binding null to parameter {0} ",index);
        st.setNull(index, Types.OTHER);
    }
    else {
        log.debugv("binding parameter [{1}] as [gender] - [{0}] ", value.getCode(), index);
        st.setObject(index, value, Types.OTHER);
    }
}

@Override
public Gender deepCopy(Gender value) {
    return value == null ? null :
           Gender.fromCode(value.getCode());
}

@Override
public boolean isMutable() {
    return true;
}

@Override
public Serializable disassemble(Gender value) {
    return deepCopy(value);
}

@Override
public Gender assemble(Serializable cached, Object owner) {
    return deepCopy((Gender) cached);
}

@Override
public Gender replace(Gender detached, Gender managed, Object owner) {
    return deepCopy(detached);
}}

Gender

public enum Gender {

MALE("MALE"),

FEMALE("FEMALE"),

UNKNOWN("UNKNOWN");

private final String code;

Gender(String gender) {
    code = gender;
}


public String getCode() {
    return code;
}

public static Gender fromCode(String gender) {
    if (gender == null) {
        return null;
    }
    return switch (gender) {
        case "MALE", "男" -> Gender.MALE;
        case "FEMALE", "女" -> Gender.FEMALE;
        default -> throw new IllegalArgumentException("Invaild input value");
    };
}}

StudentServie

    public Stream<Student> findStudent(@Nullable Integer grade, @Nullable String gender,
                                   @Nullable Long majorId, @Nullable Long activityId) {

    return studentRepo.findAll((Specification<Student>) (root, query, criteriaBuilder) -> {
        List<Predicate> predicates = new ArrayList<>();
        if (grade != null) {
            predicates.add(criteriaBuilder.equal(root.get("grade"), grade));
        }

        if (gender != null) {
            predicates.add(criteriaBuilder.equal(root.get("gender"), Gender.fromCode(gender)));
        }

        return query.where(predicates.toArray(new Predicate[predicates.size()]))
                    .getRestriction();
    }).stream();

}