Re: [hibernate-dev] Statements leaks when using JPA StoredProcedureQuery API
The conceptualization of a Query for both Hibernate and JPA is that you
execute it and get back the result of that execution: a List, a single
result, a ScrollableResult, a Stream, etc.
Yet then JPA throws in StoredProcedureQuery which is completely different
conceptually. Here you execute and then (statefully!!!) access the
individual pieces of that execution's results.
Sadly the JPA StoredProcedureQuery API is just messed up. In my opinion
(yes, of course I'm biased) our ProcedureCall + ProcedureOutputs model is
MUCH better. It is more in line with that conceptualization of Query ->
Result as opposed to StoredProcedureQuery's Query -> Query-as-Result.
All that said...
What you are asking for here is to implicitly close the JDBC
CallableStatement when we recognize <some condition> that signifies
the exhaustion
of the execution results. What is that condition? Can't be just when
there are no more ResultSets in the CallableStatement's pipeline. Consider
calling StoredProcedureQuery#getOutputParameterValue (which would require
accessing the CallableStatement) *after* we have implicitly closed the
CallableStatement.
So what is that condition? That's the rub...
On Wed, Oct 11, 2017 at 1:07 PM Robert Marcano <robert(a)marcanoonline.com>
wrote:
Migrating code from the Hibernate API to JPA, I found a stored
procedure
being called on a loop that was generating DB2 errors [1] on tests. This
error is caused in this case for having a lot of not closed statements.
The problem didn't happen using ProcedureCall Hibernate API because the
method getOutputs() and release() from the Outputs instance are available.
StoredProcedureQuery JPA API doesn't have any way to "close" the query
and by that, the statement. Reusing the same instance of
StoredProcedureQuery trying to only leak an unclosed statement but that
doesn't help either, ProcedureCallImpl is creating a new statement for
every call [2].
The only option is to unwrap the StoredProcedureQuery to an
StoredProcedureQuery and release the Outputs instance.
I don't think there is a way to avoid this without enhancing the JPA
API. Client code can call an store procedure and in some cases not
caring about all the results, so there is no way for a JPA
implementation to know when to close the statement.
Making StoredProcedureQuery an AutoCloseable may help, but it will
contrast with other Query types that don't need to be closed.
Note: It is not frequent to call many store procedures on a loop, I
would have preferred to just create a new procedure with the loop, but
for this application the conditions about when to call the procedure for
each iteration are outside the database.
[1] http://www-01.ibm.com/support/docview.wss?uid=swg21504334
[2]
https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src...
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev