使用Criteria API发布H2 DATEDIFF函数

Question

有一个简单的实体Item，具有两个日期类型属性：

@Entity
public class Item {
@Id
@GeneratedValue(generator = Constants.ID_GENERATOR)
protected Long id;
@NotNull
protected String name;
@NotNull
protected Date from;
@NotNull
protected Date to

我想使用Criteria API使用H2数据库的固有函数"DATEDIFF">。所以我有下面的代码片段来做这件事：

CriteriaQuery<Item> criteria = cb.createQuery(Item.class);
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
cb.gt(
cb.function(
"DATEDIFF",
Integer.class,
cb.literal("DAY"),
i.get("from"),
i.get("to")
),
1
)
);
... // other code to handle result 

当我试图执行查询时，我得到了这个错误：

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper-SQL错误：90008，SQLState:90008错误org.hubinate.engine.jdbc.spi.SqlExceptionHelper-无效值"1"对于位于的参数"parameterIndex"[90008-200]org.h2.message.DbException.getJdbcSQLException(DbException.java:590(在org.h2.message.DbException.getJdbcSQLException(DbException.java:429(在org.h2.message.DbException.get(DbException.java:205(org.h2.message.DbException.getInvalidValueException(DbException.java:280(在org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcpreparedStatements.java:1503(在org.h2.jdbc.JdbcPreparedStatement.setString(JdbcpreparedStatements.java:413(org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$1.doBind(VarcharTypeDescriptor.java:46(在org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73(在org.hibernate.type.AbstractStandardBasicType.nullSafeSet(抽象标准基本类型.java:276(在org.hibernate.type.AbstractStandardBasicType.nullSafeSet(抽象标准基本类型.java:271(在org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:53(在org.hibernate loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:648(在org.hibernate loader.loader.prepareQueryStatement(loader.java:2120(在org.hibernate loader.loader.executeQueryStatement(loader.java:2034(在org.hibernate loader.loader.executeQueryStatement(loader.java:2012(在org.hibernate.loader.loader.doQuery(loader.java:953(org.hibernate loader.loader.doQueryAndInitializeNonLazyCollection(loader.java:354(在org.hibernate.loader.loader.doList(loader.java:2815(org.hibernate.loader.loader.doList(loader.java:2797(org.hibernate.loader.loader.listIgnoreQueryCache(loader.java:2629(org.hibernate。loader.loader.list(loader.java:2624(org.hibernate loader.hql.QueryLoader.list(QueryLoader.java:506(org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslator Impl.java:396(在org.hibernate。engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219(位于org.hibernate.internal.SessionImpl.list(SessionImpl.java:1396(org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProduced query.java：1558(在org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProduced query.java：1526(在org.hibernate.query.query.getResultList(query.java:165(org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryType QueryAdapter.java:76(在com.ico.ltd.querying.domain.Restriction.executeQueries(Restriction.java:329(

但是如果我使用JPQL:

select i from Item i where function('DATEDIFF', 'DAY', i.from, i.to) > 1

它完成时没有任何问题。此外，如果我将H2版本切换到1.3.171，则标准API可以正常工作。我使用以下版本的H2和Hibernate1.4.200，5.4.8。相应地，最终版本。有人能帮忙吗？

Answer 1

我认为您应该为您的整数值1使用一个文字，将其与进行比较

CriteriaQuery<Item> criteria = cb.createQuery(Item.class);
Root<Item> i = criteria.from(Item.class);
criteria.select(i).where(
cb.gt(
cb.function(
"DATEDIFF",
Integer.class,
cb.literal("DAY"),
i.get("from"),
i.get("to")
),
cb.literal(1)
)
);