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

Question

我们对 Hibernate 的 ZonedDateTime 用户类型不满意，因为它们不保留 ZoneId。

因此，我们创建了一个 CompositeUserType，它存储时间戳和字符串，类似于:

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

    [...]

}

用作

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

问题是仅在 Timestamp 列上创建条件查询。

使用 Hibernates criteria API 很容易:

Restrictions.ge("createdAt.zonedDateTime", Date.from(filter.getCreatedAt().toInstant()))

但是到目前为止，我们对 JPA 标准 API 的所有尝试都失败了。我们尝试过什么是:

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

Answer 1

我们发现了一个有点hacky的方法，以防有人想知道:

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

用法:

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