Map MS SQL Server DateTimeOffset to Java 8 OffsetDateTime?

Hibernate ORM
7

Today I managed to make it works without marking anything by @Type(type = XLocalDateTime) & @TypeDef.

My solution is inspired by:

I define usual Hibernate 5 boilerplate:

public class OffsetDateTimeJavaTypeDescriptor extends AbstractTypeDescriptor<OffsetDateTime> {
    public static final OffsetDateTimeJavaTypeDescriptor INSTANCE = new OffsetDateTimeJavaTypeDescriptor();

    public OffsetDateTimeJavaTypeDescriptor() {
        super(OffsetDateTime.class);
    }

    @Override
    public OffsetDateTime fromString(String str) {
        if (str == null) { return null; }
        return OffsetDateTime.parse(str);
    }

    @SuppressWarnings({"unchecked"})
    @Override
    public <X> X unwrap(OffsetDateTime value, Class<X> type, WrapperOptions options) {
        if (value == null) {
            return null;
        } else if (OffsetDateTime.class.isAssignableFrom(type)) {
            return (X) value;
        } else if (ZonedDateTime.class.isAssignableFrom(type)) {
            return (X) value.atZoneSameInstant(ZoneOffset.systemDefault());
        } else if (String.class.isAssignableFrom(type)) {
            return (X) value.toString();
        }
        throw unknownUnwrap(type);
    }

    @Override
    public <X> OffsetDateTime wrap(X value, WrapperOptions options) {
        if (value == null) {
            return null;
        } else if (value instanceof OffsetDateTime) {
            return (OffsetDateTime) value;
        } else if (value instanceof ZonedDateTime) {
            return ((ZonedDateTime) value).toOffsetDateTime();
        } else if (value instanceof String) {
            return fromString((String) value);
        }
        throw unknownWrap(value.getClass());
    }
}

public class OffsetDateTimeMsSqlTypeDescriptor implements SqlTypeDescriptor {
    public static final OffsetDateTimeMsSqlTypeDescriptor INSTANCE = new OffsetDateTimeMsSqlTypeDescriptor();

    public OffsetDateTimeMsSqlTypeDescriptor() {}

    @Override
    public int getSqlType() {
        return Types.TIMESTAMP_WITH_TIMEZONE;
    }

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

    @Override
    public <X> ValueBinder<X> getBinder(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>(javaTypeDescriptor, this) {
            @Override
            protected void doBind(PreparedStatement st, X value, int index, WrapperOptions options) throws SQLException {
                if (value instanceof OffsetDateTime) {
                    st.setObject(index, value, JDBCType.TIMESTAMP_WITH_TIMEZONE);
                }
//                else if (value instanceof Calendar) {
//                    st.setTimestamp(index, timestamp, (Calendar) value);
//                } else if (options.getJdbcTimeZone() != null) {
//                    st.setTimestamp(index, timestamp, Calendar.getInstance( options.getJdbcTimeZone() ) );
//                } else {
//                    st.setTimestamp(index, timestamp);
//                }
            }

            @Override
            protected void doBind(CallableStatement st, X value, String name, WrapperOptions options)
                    throws SQLException {
//                final Timestamp timestamp = javaTypeDescriptor.unwrap(value, Timestamp.class, options);
                if (value instanceof OffsetDateTime) {
                    st.setObject(name, value, JDBCType.TIMESTAMP_WITH_TIMEZONE);
                }
//                else if (value instanceof Calendar) {
//                    st.setTimestamp(name, timestamp, (Calendar) value);
//                } else if (options.getJdbcTimeZone() != null) {
//                    st.setTimestamp( name, timestamp, Calendar.getInstance( options.getJdbcTimeZone() ) );
//                } else {
//                    st.setTimestamp( name, timestamp );
//                }
            }
        };
    }

    @Override
    public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicExtractor<X>(javaTypeDescriptor, this) {
            @Override
            protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
                final DateTimeOffset msTs = rs.getObject(name, DateTimeOffset.class);
                return javaTypeDescriptor.wrap(msTs.getOffsetDateTime(), options);
//                return options.getJdbcTimeZone() != null ?
//                        javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
//                        javaTypeDescriptor.wrap( rs.getTimestamp( name ), options );
            }

            @Override
            protected X doExtract(CallableStatement st, int index, WrapperOptions options) throws SQLException {
                final DateTimeOffset msTs = st.getObject(index, DateTimeOffset.class);
                return javaTypeDescriptor.wrap(msTs.getOffsetDateTime(), options);
//                return options.getJdbcTimeZone() != null ?
//                        javaTypeDescriptor.wrap( statement.getTimestamp( index, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
//                        javaTypeDescriptor.wrap( statement.getTimestamp( index ), options );
            }

            @Override
            protected X doExtract(CallableStatement st, String name, WrapperOptions options) throws SQLException {
                final DateTimeOffset msTs = st.getObject(name, DateTimeOffset.class);
                return javaTypeDescriptor.wrap(msTs.getOffsetDateTime(), options);
//                return options.getJdbcTimeZone() != null ?
//                        javaTypeDescriptor.wrap( statement.getTimestamp( name, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
//                        javaTypeDescriptor.wrap( statement.getTimestamp( name ), options );
            }
        };
    }
}

/**
 * Replacement for {@link org.hibernate.type.OffsetDateTimeType}
 * to save OffsetDateTime as MS SQL server type "datetimeoffset".
 */
public class OffsetDateTimeSingleColumnType extends AbstractSingleColumnStandardBasicType<OffsetDateTime> {
    public static final OffsetDateTimeSingleColumnType INSTANCE = new OffsetDateTimeSingleColumnType();

    public OffsetDateTimeSingleColumnType() {
        super(OffsetDateTimeMsSqlTypeDescriptor.INSTANCE, OffsetDateTimeJavaTypeDescriptor.INSTANCE);
    }

    @Override
    public String getName() {
        return OffsetDateTime.class.getCanonicalName();
    }
}

And some Spring Boot magic to register above types in the Hibernate (the key is EntityManagerFactoryBuilderImpl.TYPE_CONTRIBUTORS here):

@Bean
public HibernatePropertiesCustomizer customHibernateTypeRegistrar() {
    return (Map<String, Object> props) -> {
        props.put(
                EntityManagerFactoryBuilderImpl.TYPE_CONTRIBUTORS,
                (TypeContributorList) () -> Arrays.asList((TypeContributor) (typeContributions, serviceRegistry) -> {
                    // Deregister built-in org.hibernate.type.OffsetDateTimeSingleColumnType as it hides our mapping.
                    final BasicTypeRegistry basicTypeRegistry = typeContributions.getTypeConfiguration().getBasicTypeRegistry();
                    Class<OffsetDateTime> clazz = OffsetDateTime.class;
                    basicTypeRegistry.unregister(clazz.getName());
                    basicTypeRegistry.unregister(clazz.getSimpleName());

                    typeContributions.contributeSqlTypeDescriptor(OffsetDateTimeMsSqlTypeDescriptor.INSTANCE);
                    typeContributions.contributeJavaTypeDescriptor(OffsetDateTimeJavaTypeDescriptor.INSTANCE);
                    typeContributions.contributeType(OffsetDateTimeSingleColumnType.INSTANCE);
                }));
    };
}

Default definitions shadow my customization. BasicTypeRegistry.unregister() does the job )) I get an idea from TRACE logs:

01:43:38.851 DEBUG [    Test worker] org.hibernate.type.BasicTypeRegistry 
Adding type registration OffsetDateTime -> org.hibernate.type.OffsetDateTimeType@450352d9
01:43:38.851 DEBUG [    Test worker] org.hibernate.type.BasicTypeRegistry 
Adding type registration java.time.OffsetDateTime -> org.hibernate.type.OffsetDateTimeType@450352d9

The Spring configuration should be adapted to work with H2, otherwise this solution looks solid till Hibernate 6 arrives.

1 Like