Marco Schulte Marco Schulte - 1 year ago 114
Java Question

JPA 2.1/Hibernate 5: Search on inner type of Composite User Type with criteria API

We weren't pleased with Hibernate's usertypes for ZonedDateTime, as they don't persist the ZoneId.

Therefore we created a CompositeUserType which stores both as Timestamp and String, something along the lines of:

public class ZonedDateTimeUserType implements CompositeUserType {

public static final String PROPERTY_NAME_TIMESTAMP = "zonedDateTime";
public static final String PROPERTY_NAME_ZONEID = "zoneId";

private static final Type[] SQL_TYPES = {TimestampType.INSTANCE, StringType.INSTANCE};

public Object nullSafeGet(final ResultSet resultSet, final String[] names, final SessionImplementor sessImpl, final Object owner)
throws SQLException {
assert names.length == 2;
Timestamp date = resultSet.getTimestamp(names[0]);
if (date == null) {
return null;
ZoneId zoneId = ZoneId.of(resultSet.getString(names[1]));
return getZonedDateTime4Timestamp(date, zoneId);

public void nullSafeSet(final PreparedStatement preparedStatement, final Object value, final int index, final SessionImplementor sessImpl)
throws SQLException {
if (null == value) {
TimestampType.INSTANCE.set(preparedStatement, null, index, sessImpl);
StringType.INSTANCE.set(preparedStatement, null, index + 1, sessImpl);
} else {
ZonedDateTime zonedDateTime = (ZonedDateTime) value;
TimestampType.INSTANCE.set(preparedStatement, getTimestamp4ZonedDateTime(zonedDateTime), index, sessImpl);
StringType.INSTANCE.set(preparedStatement, zonedDateTime.getZone().getId(), index + 1, sessImpl);

private ZonedDateTime getZonedDateTime4Timestamp(final Timestamp ts, final ZoneId zoneId) {
Instant instant = Instant.ofEpochMilli(ts.getTime());
return ZonedDateTime.ofInstant(instant, zoneId);

private Timestamp getTimestamp4ZonedDateTime(final ZonedDateTime zonedDateTime) {
return Timestamp.from(zonedDateTime.toInstant());



Used as

@Columns(columns = {@Column(name = "createdAt"), @Column(name = "createdAtZone")})
private ZonedDateTime createdAt;

The problem is to create a criteria query just on the Timestamp column.

It was easy with Hibernates criteria API:"createdAt.zonedDateTime", Date.from(filter.getCreatedAt().toInstant()))

But all our attempts with JPA's criteria API failed so far. What we tried e.g. is:

cb.greaterThanOrEqualTo(root.get(Entity_.createdAt).get("zonedDateTime"), filter.getCreatedAt())
cb.greaterThanOrEqualTo(root.get("createdAt.zonedDateTime"), filter.getCreatedAt())

Answer Source

We found a bit hacky way, in case anyone is wondering:

public class LiteralExpression<T> extends ExpressionImpl<T> {

    private final String literal;

    private LiteralExpression(String literal, CriteriaBuilderImpl cb, Class<T> clazz) {
        super(cb, clazz);
        this.literal = literal;

    public void registerParameters(ParameterRegistry registry) {

    public String render(RenderingContext renderingContext) {
        return literal;

    public String renderProjection(RenderingContext renderingContext) {
        return null;

    public static <T> LiteralExpression<T> of(String literal, CriteriaBuilder cb, Class<T> clazz) {
        return new LiteralExpression<>(literal, (CriteriaBuilderImpl) cb, clazz);


LiteralExpression<Timestamp> createdAt = LiteralExpression.of(Entity_.createdAt.getName() + "." + ZonedDateTimeUserType.PROPERTY_NAME_TIMESTAMP, cb, Timestamp.class);
cb.greaterThanOrEqualTo(createdAt, filter.getCreatedAt()))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download