[hibernate-dev] How to deal with a constant in CriteriaQuery

[Steve Ebersole]

When you say "standardize the definition of functions", are you speaking
about the which functions are defined as "built-in functions" or a
mechanism in JPA to define `SQLFunction` overrides/extras?

JPA already has quite a number of built-in functions defined: MIN, TRIM,
LOWER, etc.  I think we'd have a decent chance to get specific additions to
the spec - especially EXTRACT.  DATEDIFF would probably be a harder sell
since it is not an ANSI SQL defined function - in ANSI SQL you'd accomplish
this using date arithmetic and EXTRACT, something like

extract( DAY, enddate - startdate )

Technically TIMESTAMP minus TIMESTAMP is supposed to return an INTERVAL (as
opposed to an INTEGER) which itself can be an argument to EXTRACT.

While DATEDIFF is not standardized in ANSI SQL, it is widely implemented
across different databases.  So honestly not sure how well various
databases support DATEDIFF versus EXTRACT-from-INTERVAL


We also try to standardize support for additional functions beyond JPA in
Hibernate - so we have most Dialects implement YEAR, MONTH, DAY, HOUR,
MINUTE, SECOND functions, e.g.:

select year(appt.startTime) from Appointment appt





On Tue, Apr 24, 2018 at 8:42 AM Christian Beikov <christian.beikov at gmail.com>
wrote:

> The JPA spec actually says the function invocation syntax can be used
> for database functions in 4.6.17.3.
>
> By "supported" you mean something like a note that says JPA providers
> may allow the invocation of JPA provider specific functions via that
> syntax? I'd love to go even further and standardize the definition of
> functions, but I guess that's not going to happen, so this seems to be a
> good compromise/first step.
>
>
> Mit freundlichen Grüßen,
> ------------------------------------------------------------------------
> *Christian Beikov*
> Am 24.04.2018 um 15:21 schrieb Gail Badner:
> > Yes, that should work with CriteriaQuery as well. It's a reasonable
> > workaround.
> >
> > If JPA doesn't support this now, is it something that should be
> > supported in the future?
> >
> > On Mon, Apr 23, 2018 at 11:45 PM, Christian Beikov
> > <christian.beikov at gmail.com <mailto:christian.beikov at gmail.com>> wrote:
> >
> >     Hey Gail,
> >
> >     I usually register a dedicated SQLFunction for every time value in
> >     the
> >     Dialect and use these functions instead. So in your case, I'd have a
> >     "day" function and use it like
> >
> >     final Expression<Integer> diff = cb.function("DAY", Integer.class,
> >     ... ).as(Integer.class);
> >
> >     The fuction will then render the SQL like it is supposed to be
> >
> >     "select datediff( day, ... ) from ..."
> >
> >     Although I'm not 100% sure that CriteriaBuilder.function properly
> >     resolves the function since I never used custom function with the JPA
> >     Criteria API directly, I'd at least expect it.
> >
> >     Mit freundlichen Grüßen,
> >
>  ------------------------------------------------------------------------
> >     *Christian Beikov*
> >     Am 24.04.2018 um 07:29 schrieb Gail Badner:
> >     > SQL Server defines a function:
> >     >
> >     > DATEDIFF ( datepart , startdate , enddate )  [1]
> >     >
> >     > (This method still needs to be added to SQLServer2012Dialect.)
> >     >
> >     > datepart can be one of a variety of time values, e.g., day,
> >     week, year, etc.
> >     >
> >     > On SQL Server (at least) the value for datepart cannot be
> >     treated as a
> >     > literal (enclosed in quotes) or bound as a parameter.
> >     >
> >     > This causes problems when using DATEDIFF in a CriteriaQuery.
> >     >
> >     > final Expression<Integer> diff = cb.function("DATEDIFF",
> >     Integer.class,
> >     > cb.literal("day"), ... ).as(Integer.class);
> >     >
> >     > SQL Server throws:
> >     > com.microsoft.sqlserver.jdbc.SQLServerException: Invalid parameter
> 1
> >     > specified for datediff.
> >     >
> >     > There is no problem using the function in a query, as long as
> >     datepart is
> >     > not enclosed in quotes or bound to the query:
> >     >
> >     > "select datediff( day, ... ) from ..."
> >     >
> >     > Is there some way to set a literal value using a CriteriaQuery
> >     that will
> >     > not ultimately be enclosed in quotes or bound to a query?
> >     >
> >     > I've also been trying to find an integer constant that would be
> >     equivalent
> >     > to 'day', but haven't had any luck.
> >     >
> >     > Anyone have an idea how this could be done with CriteriaQuery?
> >     >
> >     > Thanks,
> >     > Gail
> >     >
> >     > [1]
> >     >
> >
> https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017
> >     <
> https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017
> >
> >     > _______________________________________________
> >     > hibernate-dev mailing list
> >     > hibernate-dev at lists.jboss.org <mailto:
> hibernate-dev at lists.jboss.org>
> >     > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >     <https://lists.jboss.org/mailman/listinfo/hibernate-dev>
> >
> >     _______________________________________________
> >     hibernate-dev mailing list
> >     hibernate-dev at lists.jboss.org <mailto:hibernate-dev at lists.jboss.org>
> >     https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >     <https://lists.jboss.org/mailman/listinfo/hibernate-dev>
> >
> >
>
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev