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

Christian Beikov christian.beikov at gmail.com
Tue Apr 24 10:28:59 EDT 2018


I'm speaking about a mechanism to define what Hibernate calls 
`SQLFunction` in a JPA standard way. Every JPA provider has this 
concept. I even implemented a JPA provider abstraction for this in 
Blaze-Persistence and it works pretty well.

How does the specification process in the Eclipse Foundation work, or is 
it not yet defined? I'd be curious what it takes for an idea or a 
feature to be considered.


Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 24.04.2018 um 16:12 schrieb 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 <mailto: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>
>     <mailto: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>
>     <mailto: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>
>     <mailto: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
>



More information about the hibernate-dev mailing list