[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