我正在尝试将名为transmission_result的postgres自定义类型映射到Hibernate/JPA POJO. postgres自定义类型或多或少是字符串值的枚举类型.
我创建了一个名为PGEnumUserType的自定义EnumUserType以及一个表示postgres枚举值的枚举类.当我对一个真实的数据库运行它时,我收到以下错误: '错误:状态"列的类型为transmission_result,但表达式的类型为字符变化 提示:您将需要重写或强制转换表达式. 位置:135'
看到这一点,我想我需要将SqlTypes更改为Types.OTHER.但是这样做会破坏我的集成测试(在内存数据库中使用HyperSQL),并显示以下消息: '原因:java.sql.SQLException:在语句[select enrollment0 _."id"作为id1_47_0_,enrollment0 _."tpa_approval_id"作为tpa2_47_0_,enrollment0 _."tpa_status_code"作为tpa3_47_0_,enrollment0_age,作为状态_4, ."approval_id"作为批准5_47_0_,注册0 _."transmission_date"作为transmis6_47_0_,注册0 _."status"作为status7_47_0_,enrollment0_.传送器"作为transmission8_47_0_,而"transmissions"注册0_,其中enrollment0_.
我不确定为什么更改sqlType会导致此错误.感谢您的帮助.
JPA/休眠实体:
@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; } }自定义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(); } }枚举类:
public enum TransmissionStatusType { accepted, rejected, duplicate, exception, retry}解决方案
我知道了.我需要在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 { // 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); } }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); } }
更多推荐
如何使用JPA和Hibernate映射PostgreSQL枚举
发布评论