Hi,
On Fri, May 5, 2017 at 1:46 AM, Steve Ebersole <steve_at_hibernate.org> wrote:
> 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.
>
I can see that, if Hibernate is still experimenting with it this is surely
not the time to standardise it.
So exposing the SQL is kind of a somewhat neater escape hatch for in the
meantime. It's just like given access to the native Hibernate session from
JPA.
You *know* you're going into low-level (for JPA/EE) and non-standard
territory, and just being able to count from JPA would *of course* be much
neater, but often if the latter is not available and the first is, the
choice, given there are business requirements where you must be able to
count, is not that hard.
Kind regards,
Arjan Tijms
> 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
>>>>>>
>>>>>>
>>