I've created a custom type for Hibernate to store an OffsetDateTime's timestamp and offset (because the default JPA 2.2 / Hibernate 5.2 with java 8 support implementation loses the offset information):
public class OffsetDateTimeHibernateType implements CompositeUserType { @Override public Class returnedClass() { return OffsetDateTime.class; } @Override public String[] getPropertyNames() { return new String[] {"dateTime", "zoneOffset"}; } @Override public Type[] getPropertyTypes() { // Not sure if we should use LocalDateTimeType.INSTANCE instead of TIMESTAMP return new Type[]{StandardBasicTypes.TIMESTAMP, StandardBasicTypes.INTEGER}; } @Override public Object getPropertyValue(Object o, int propertyIndex) { if (o == null) { return null; } OffsetDateTime offsetDateTime = (OffsetDateTime) o; switch (propertyIndex) { case 0: return Timestamp.valueOf(offsetDateTime.toLocalDateTime()); case 1: return offsetDateTime.getOffset().getTotalSeconds(); default: throw new IllegalArgumentException("The propertyIndex (" + propertyIndex + ") must be 0 or 1."); } } @Override public OffsetDateTime nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner) throws SQLException { if (resultSet == null) { return null; } Timestamp timestamp = (Timestamp) StandardBasicTypes.TIMESTAMP.nullSafeGet(resultSet, names[0], session, owner); if (timestamp == null) { throw new IllegalStateException("The timestamp (" + timestamp + ") for an " + OffsetDateTime.class.getSimpleName() + "cannot be null."); } LocalDateTime localDateTime = timestamp.toLocalDateTime(); Integer zoneOffsetSeconds = (Integer) StandardBasicTypes.INTEGER.nullSafeGet(resultSet, names[1], session, owner); if (zoneOffsetSeconds == null) { throw new IllegalStateException("The zoneOffsetSeconds (" + zoneOffsetSeconds + ") for an " + OffsetDateTime.class.getSimpleName() + "cannot be null."); } return OffsetDateTime.of(localDateTime, ZoneOffset.ofTotalSeconds(zoneOffsetSeconds)); } @Override public void nullSafeSet(PreparedStatement statement, Object value, int parameterIndex, SessionImplementor session) throws SQLException { if (value == null) { statement.setNull(parameterIndex, StandardBasicTypes.TIMESTAMP.sqlType()); statement.setNull(parameterIndex, StandardBasicTypes.INTEGER.sqlType()); return; } OffsetDateTime offsetDateTime = (OffsetDateTime) value; statement.setTimestamp(parameterIndex, Timestamp.valueOf(offsetDateTime.toLocalDateTime())); statement.setInt(parameterIndex, offsetDateTime.getOffset().getTotalSeconds()); } // ************************************************************************ // Mutable related methods // ************************************************************************ @Override public boolean isMutable() { return false; } @Override public Object deepCopy(Object value) { return value; // OffsetDateTime is immutable } @Override public Object replace(Object original, Object target, SessionImplementor session, Object owner) { return original; // OffsetDateTime is immutable } @Override public void setPropertyValue(Object component, int property, Object value) { throw new UnsupportedOperationException("A OffsetDateTime is immutable."); } // ************************************************************************ // Other methods // ************************************************************************ @Override public boolean equals(Object a, Object b) { if (a == b) { return true; } else if (a == null || b == null) { return false; } return a.equals(b); } @Override public int hashCode(Object o) { if (o == null) { return 0; } return o.hashCode(); } @Override public Serializable disassemble(Object value, SessionImplementor session) { return (Serializable) value; } @Override public Object assemble(Serializable cached, SessionImplementor session, Object owner) { return cached; } } Now, I want to be able to compare it, so this JPA-QL query works:
@NamedQuery(name = "Shift.myQuery", query = "select sa from Shift sa" + " where sa.endDateTime >= :startDateTime" + " and sa.startDateTime < :endDateTime") on this model:
@Entity public class Shift { @Type(type = "...OffsetDateTimeHibernateType") @Columns(columns = {@Column(name = "startDateTime"), @Column(name="startDateTimeOffset")}) private OffsetDateTime startDateTime; @Type(type = "...OffsetDateTimeHibernateType") @Columns(columns = {@Column(name = "endDateTime"), @Column(name="endDateTimeOffset")}) private OffsetDateTime endDateTime; ... }
But that fails with:
HHH000177: Error in named query: Shift.myQuery: org.hibernate.hql.internal.ast.QuerySyntaxException: >= operator not supported on composite types. [select sa from org.optaplanner.openshift.employeerostering.shared.shift.Shift sa where sa.endDateTime >= :startDateTime and sa.startDateTime < :endDateTime] at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79) at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:218) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) at org.hibernate.internal.NamedQueryRepository.checkNamedQueries(NamedQueryRepository.java:155) at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:796) at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:492) at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:422) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:880) How can I make my CustomUserType comparable?
1 Answers
Answers 1
Hibernate has no way of knowing how to compare your custom type with multiple columns. You know how the columns relate to each other, but Hibernate doesn't. Without having tested it (can do if I get time later) I think you can rewrite the query to use the property names of the parts, for example:
select sa from Shift sa where sa.endDateTime.dateTime >= :startDateTimeDateTimePart and sa.startDateTime.dateTime < :endDateTimeDateTimePart To make it work with the offset you would need to normalize the value you compare, i.e. add the number of hours represented by the offset to the date. You can do that with a custom function for your database, see 4.6.17.3 in JPA 2.2 (https://jcp.org/aboutJava/communityprocess/mrel/jsr338/index.html). Of course you could also define a custom compare function in the database that takes both parts as input parameters and call it with the function, but personally I would try to stick to the pre-defined functions as much as possible. Adding hours to a timestamp should be covered no matter what database you are using.
0 comments:
Post a Comment