尝试将postgres枚举映射到Hibernate / JPA pojo [英] Trying to map postgres enum to Hibernate/JPA pojo

问题描述

我试图将一个名为transmission_result的postgres自定义类型映射到Hibernate / JPA POJO。 postgres自定义类型或多或少是一个枚举类型的字符串值。

我创建了一个名为PGEnumUserType的自定义EnumUserType以及一个代表postgres枚举值的枚举类。当我对真实的数据库运行这个时，我收到以下错误：
'错误：列状态是transmission_result类型的，但表达式的类型是字符变化
提示：您将需要重写或转换表达方式。
位置：135'

看到这个，我想我需要将我的SqlTypes改为Types.OTHER。但是这样做会破坏我的集成测试（在内存数据库中使用HyperSQL），其中包含以下消息：
'由于：java.sql.SQLException：在语句中未找到表[select enrollment0 _。idas id1_47_0_，enrollment0_。 tpa_approval_id作为tpa2_47_0_，登记0 _tpa_status_code作为tpa3_47_0_，登记0 _作为transmit8_47_0_ fromtransmissionsenrollment0_ where enrollment0 _。id=？]'

我不知道为什么在此错误中更改sqlType结果。

JPA / Hibernate实体：

  @实体
 @Access（javax.persistence.AccessType.PROPERTY）
 @Table（name =transmissions）
 public class EnrollmentCycleTransmission {
 
 //枚举元素状态列
 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; 
 
私人长传输ID; 
私人长批准ID; 
私人长发射器ID; 
私有TransmissionStatusType transmissionStatus; 
私人日期transmitDate; 
 private String TPAApprovalID; 
 private String TPAStatusCode; 
私人字符串TPAStatusMessage; 
 
 
 @Column（name =id）
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
 public Long getTransmissionID（）{ 
返回传输ID; 
} 
 
 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; 
 
 $ b @Column（name =tpa_approval_id）
 public String getTPAApprovalID（）{
 return TPAApprovalID; 
} 
 
 public void setTPAApprovalID（String TPAApprovalID）{
 this.TPAApprovalID = TPAApprovalID; 
 
 $ b @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; 
 
 
 $ / code $ / pre 
 $ b $ p自定义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）{
抛出新的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]）; 
返回rs.wasNull（）？ null：Enum.valueOf（enumClass，name）; 
 
 $ b $ 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; //使用File |改变实现方法的主体设置|文件模板。 
 
 
 public可串行化反汇编（Object value）抛出HibernateException {
 return（Enum）value; 
 
 
 public Object assemble（Serializable cached，Object owner）throws HibernateException {
 return cached; 
} 
 
 public Object Object（Object original，Object target，Object owner）throws HibernateException {
 return original; 
} 
 
 public Object fromXMLString（String xmlValue）{
 return Enum.valueOf（enumClass，xmlValue）; 
 
 $ b public String objectToSQLString（Object value）{
 return'\''+（（Enum）value）.name（）+'\''; 
} 
 
 public String toXMLString（Object value）{
 return（（Enum）value）.name（）; 
 
 
 $ / code> $ / pre> 
 
 
枚举类：
  public enum TransmissionStatusType {接受
，拒绝
，
重复，
异常，
重试} 
  
 
 
解决方案
我想通了。我需要在nullSafeSet函数中使用setObject而不是setString，并将Types.OTHER作为java.sql.type传递，以让jdbc知道它是一个postgres类型。
  public void nullSafeSet（PreparedStatement st，Object value，int index）throws HibernateException，SQLException {
 if（value == null）{
 st.setNull（index ，Types.VARCHAR）; 
} 
 else {
 //先前使用的setString，但是这会导致postgresql咆哮不兼容的类型。 
 //现在使用setObject传递给postgres枚举对象的java类型
 // st.setString（index，（（Enum）value）.name（））; 
 st.setObject（index，（（Enum）value），Types.OTHER）; 
} 
} 
  
 
I am trying to map a postgres custom type,named transmission_result, to a Hibernate/JPA POJO. The postgres custom type is more or less an enum type of string values. 

I have created a custom EnumUserType called PGEnumUserType as well as a enum class representing the postgres 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}

解决方案
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);
    }
}


                        
这篇关于尝试将postgres枚举映射到Hibernate / JPA pojo的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！