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