Issue with H2 DATEDIFF function using Criteria API

Question

There is a simple entity Item with two date type properties:

@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

I want to use the propritary function of H2 database "DATEDIFF" using Criteria API. So I have below snippet code to do this:

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

When I try to execute query I get this error:

WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 90008, SQLState: 90008 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Invalid value "1" for parameter "parameterIndex" [90008-200] at org.h2.message.DbException.getJdbcSQLException(DbException.java:590) at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) at org.h2.message.DbException.get(DbException.java:205) at org.h2.message.DbException.getInvalidValueException(DbException.java:280) at org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:1503) at org.h2.jdbc.JdbcPreparedStatement.setString(JdbcPreparedStatement.java:413) at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$1.doBind(VarcharTypeDescriptor.java:46) at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271) at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:53) at org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:648) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2120) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2034) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012) at org.hibernate.loader.Loader.doQuery(Loader.java:953) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2815) at org.hibernate.loader.Loader.doList(Loader.java:2797) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2629) at org.hibernate.loader.Loader.list(Loader.java:2624) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1396) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1558) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1526) at org.hibernate.query.Query.getResultList(Query.java:165) at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:76) at com.ico.ltd.querying.domain.Restriction.executeQueries(Restriction.java:329)

But if I use JPQL:

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

It completes without any issue. Moreover if I switch version of H2 to 1.3.171, Criteria API works fine. I use the following versions of H2 and Hibernate 1.4.200, 5.4.8.Final accordingly. Could somebody help with this?

Have you found any solution? If yes could you please post it? — bazsoja

Ahmed Ahmed · Accepted Answer · 2020-10-21T13:28:53

I think you should use a literal for your integer value 1 that you compare to:

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

Issue with H2 DATEDIFF function using Criteria API

1 Answers