[hibernate-dev] Statements leaks when using JPA StoredProcedureQuery API

[Robert Marcano]

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/main/java/org/hibernate/procedure/internal/ProcedureCallImpl.java#L437