Hibernate CompositeUserType可以在JPA-QL（或HQL）查询中进行比较 [英] Hibernate CompositeUserType that is comparible in JPA-QL (or HQL) query
本文介绍了Hibernate CompositeUserType可以在JPA-QL（或HQL）查询中进行比较的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我为Hibernate创建了一个自定义类型来存储 OffsetDateTime 的时间戳和偏移量（因为默认的JPA 2.2 / Hibernate 5.2用java 8支持实现
public class OffsetDateTimeHibernateType implements CompositeUserType {
@Override
public Class returnedClass（）{
return OffsetDateTime。类;
$ b @Override
public String [] getPropertyNames（）{
return new String [] {dateTime，zoneOffset};
}
@Override
public Type [] getPropertyTypes（）{
//不知道是否应该使用LocalDateTimeType.INSTANCE而不是TIMESTAMP
return新类型[] {StandardBasicTypes.TIMESTAMP，StandardBasicTypes.INTEGER};
$ b @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（））;
情况1：
返回offsetDateTime.getOffset（）。getTotalSeconds（）;
default：
throw new IllegalArgumentException（propertyIndex（+ propertyIndex
+）必须是0或1。
$ b @Override
public OffsetDateTime nullSafeGet（ResultSet resultSet，String [] names，SessionImplementor session，Object owner）
throws SQLException {
if（resultSet == null）{
return null;
}
时间戳timestamp =（时间戳）StandardBasicTypes.TIMESTAMP.nullSafeGet（resultSet，names [0]，session，owner）;
+ OffsetDateTime.class.getSimpleName（）+的
if（timestamp == null）{
throw new IllegalStateException（timestamp（+ timestamp +））不能为空。）;
}
LocalDateTime localDateTime = timestamp.toLocalDateTime（）;
Integer zoneOffsetSeconds =（Integer）StandardBasicTypes.INTEGER.nullSafeGet（resultSet，names [1]，session，owner）;
如果（zoneOffsetSeconds == NULL）{
抛出新IllegalStateException异常（以下简称 zoneOffsetSeconds（ + zoneOffsetSeconds + ）为
+ OffsetDateTime.class.getSimpleName（）+不能为空。）;
}
返回OffsetDateTime.of（localDateTime，ZoneOffset.ofTotalSeconds（zoneOffsetSeconds））;
$ b @Override
public void nullSafeSet（PreparedStatement语句，Object值，int parameterIndex，SessionImplementor会话）
抛出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（））;
}
// *********************************** *************************************
//可变相关方法
// ************************************************ ************************
@Override
public boolean isMutable（）{
return false ;
}
@Override
public Object deepCopy（Object value）{
返回值; // OffsetDateTime是不可变的
$ b @Override
public Object replace（Object original，Object target，SessionImplementor session，Object owner）{
return original; // OffsetDateTime是不可变的
$ b $覆盖$ b $ public void setPropertyValue（Object component，int property，Object value）{
throw new UnsupportedOperationException（A OffsetDateTime is不可改变）;
}
// *********************************** *************************************
//其他方法
/ / ************************************************* ***********************
@Override
public boolean equals（Object a，Object b）{
if（a == b）{
return true;
} else if（a == null || b == null）{
return false;
}
返回a.equals（b）;
}
@Override
public int hashCode（Object o）{
if（o == null）{
return 0;
}
return o.hashCode（）;
$ b @Override
public可串行化反汇编（Object value，SessionImplementor session）{
return（Serializable）value;
}
@Override
public Object assemble（Serializable cached，SessionImplementor session，Object owner）{
return cached;
}
}
现在，我希望能够来比较它，所以这个JPA-QL查询工作：
$ $ p $ $ $ $ c $ @NamedQuery（name =Shift.myQuery，
query =select sa from Shift sa+
where sa.endDateTime> =：startDateTime+
and sa.startDateTime<：endDateTime）
$ p
$ b $ p
$ b $ p
$ b $实体
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）}）
专用OffsetDateTime endDateTime;
...
}
但是失败：
HHH000177：命名查询错误：Shift.myQuery：org.hibernate。 hql.internal.ast.QuerySyntaxException：> =运算符在复合类型上不受支持。 [从org.optaplanner.openshift.employeerostering.shared.shift.Shift sa选择sa，其中sa.endDateTime> =：startDateTime和sa.startDateTime< ：endDateTime]在org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException（QuerySyntaxException.java:79）
。在org.hibernate.QueryException.wrapWithQueryString（QueryException.java:103）
。在org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile（QueryTranslatorImpl.java:218）
在org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile（QueryTranslatorImpl.java:142）
。在org.hibernate.engine.query.spi.HQLQueryPlan<初始化>（HQLQueryPlan.java:115）
。在org.hibernate.engine.query.spi.HQLQueryPlan<初始化>（HQLQueryPlan.java ：在org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan（QueryPlanCache.java:150 76）
）
。在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）
在org.hibernate.boot.internal.SessionFactoryBuilderImpl.build（SessionFactoryBuilderImpl.java:422）
在org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build（EntityManagerFactoryBuilderImpl.java:880）
如何让我的CustomUserType具有可比性？
从Shift sa中选择sa sa
其中sa.endDateTime.dateTime> =：startDateTimeDateTimePart
和sa.startDateTime.dateTime< ：endDateTimeDateTimePart
为了使它与偏移量一起工作，您需要将您比较的值标准化，由抵消日期代表的小时数。您可以使用数据库的自定义函数来实现该功能，请参见JPA 2.2中的4.6.17.3（ https://jcp.org/aboutJava/communityprocess/mrel/jsr338/index.html ）。当然，你也可以在数据库中定义一个自定义的比较函数，它将两个部分作为输入参数，并用函数调用它，但是我个人会尽量使用预定义的函数。无论您使用何种数据库，都应该为时间戳添加小时。
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?
解决方案
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.
这篇关于Hibernate CompositeUserType可以在JPA-QL（或HQL）查询中进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
