Marco Schulte Marco Schulte - 29 days ago 13
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};
private static final String[] PROPERTY_NAMES = {PROPERTY_NAME_TIMESTAMP, PROPERTY_NAME_ZONEID};

@Override
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);
}

@Override
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")})
@Type(type = ZONED_DATE_TIME_USER_TYPE)
private ZonedDateTime createdAt;


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

It was easy with Hibernates criteria API:

Restrictions.ge("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

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;
    }

    @Override
    public void registerParameters(ParameterRegistry registry) {
    }

    @Override
    public String render(RenderingContext renderingContext) {
        return literal;
    }

    @Override
    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);
    }
}

Usage:

LiteralExpression<Timestamp> createdAt = LiteralExpression.of(Entity_.createdAt.getName() + "." + ZonedDateTimeUserType.PROPERTY_NAME_TIMESTAMP, cb, Timestamp.class);
cb.greaterThanOrEqualTo(createdAt, filter.getCreatedAt()))
Comments