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

Steve Ebersole steve at hibernate.org
Tue Apr 24 10:16:35 EDT 2018


Section 4.6.17.3 unfortunately also very strictly limits the sources of
values for the arguments that can be passed to the functions using its
`function(name, ...)` syntax.  And unfortunately SQL keywords is not one of
those.

On Tue, Apr 24, 2018 at 9:12 AM Steve Ebersole <steve at hibernate.org> wrote:

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


More information about the hibernate-dev mailing list