users@jpa-spec.java.net

[jpa-spec users] Re: JPA_SPEC-25: map back and forth between CriteriaQueries and JPQL queries

From: Steve Ebersole <steve_at_hibernate.org>
Date: Thu, 04 May 2017 23:46:18 +0000

More I was thinking a tie-in with the flow between Query and criteria and
adding support to the BNF for allowing subqueries as "from elements". This
is something we are experimenting with in Hibernate 6.0. But imo that
would be beyond the scope of an MR, especially an MR we are trying to
finish scoping.

But again, I'd personally be against exposing the SQL.

On Thu, May 4, 2017, 6:36 PM arjan tijms <arjan.tijms_at_gmail.com> wrote:

> Hi,
>
> The problem is that JPA is not rich enough to allow a couple of well known
> constructs, like the count over the entire query.
>
> The idea is to get the SQL that the persistence provider would execute,
> then manually manipulate that (for the count example, wrap the count around
> it), then execute the result as a native query.
>
> And yes, the params have to be taken into account. The SQL would not be
> expanded, but would contain their placeholders, which (if I'm not mistaken)
> is the kind of SQL persistence providers in practice all generate.
>
> See this code example for Hibernate:
>
>
> https://github.com/omnifaces/omnipersistence/blob/develop/src/main/java/org/omnifaces/persistence/service/GenericEntityService.java#L348
>
> I know it's not super pretty, and it's definitely one of those lower level
> things for advanced users or libraries (you could say native queries are
> that too), but for things like count * there are not many alternatives.
> Closest comes e.g. querying just all IDs and counting that, but depending
> on the situation that's not super pretty either.
>
> Kind regards,
> Arjan Tijms
>
>
>
>
>
>
>
> On Fri, May 5, 2017 at 1:25 AM, Steve Ebersole <steve_at_hibernate.org>
> wrote:
>
>> I'm not understanding. If the JPA provider can't generate SQL that it
>> can execute, how would someone execute it?
>>
>> Not to mention you'd have to expose other things like JDBC params to have
>> this doable. And what about multi-valued parameters? Would the SQL
>> already be expanded to account for them? If so, now this #getSqlString
>> becomes time-dependent in terms of when it can be called. Etc... Just a
>> lot of questions. Which is why I said I would not expose it.
>>
>> On Thu, May 4, 2017, 5:58 PM arjan tijms <arjan.tijms_at_gmail.com> wrote:
>>
>>> Hi,
>>>
>>> On Fri, May 5, 2017 at 12:53 AM, Steve Ebersole <steve_at_hibernate.org>
>>> wrote:
>>>
>>>> Can definitely see the usefulness of this.
>>>>
>>> Thanks
>>>
>>>
>>>> Whether it happens in this MR is a different question.
>>>>
>>>> I'd personally avoid exposing the SQL.
>>>>
>>> Sometimes it's a necessity for things that JPA just can't do. For
>>> instance, JPA can't universally do a count over any random query, but in
>>> SQL this is trivial (for most databases, that is).
>>>
>>> For a longer explanation see:
>>> http://arjan-tijms.omnifaces.org/2012/06/counting-rows-returned-from-jpa-query.html
>>>
>>> Kind regards,
>>> Arjan Tijms
>>>
>>>
>>>
>>>
>>>> I'd also suggest adding a note that getting the JPQL from a Query and
>>>> especially from a criteria query is a potentially expensive operation, for
>>>> those considering using it in a toString e.g.
>>>>
>>>> On Thu, May 4, 2017, 5:27 PM arjan tijms <arjan.tijms_at_gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> See https://java.net/jira/browse/JPA_SPEC-25
>>>>>
>>>>> This would be an incredible useful feature to have and largely would
>>>>> "just" be exposing methods that all persistence providers already seem to
>>>>> have internally.
>>>>>
>>>>> Linda herself said:
>>>>>
>>>>> "*Being able to map back and forth between CriteriaQueries and JPQL
>>>>> queries would be useful to have. This is something that should be
>>>>> considered in a future release. See also
>>>>> http://java.net/jira/browse/JPA_SPEC-29
>>>>> <http://java.net/jira/browse/JPA_SPEC-29>*"
>>>>>
>>>>> Wonder if this could be considered for the MR.
>>>>>
>>>>> Kind regards,
>>>>> Arjan Tijms
>>>>>
>>>>>
>