[hibernate-dev] Stored procedure improvement

Vlad Mihalcea mihalcea.vlad at gmail.com
Mon Feb 15 11:16:31 EST 2016


The problem with naming the method as
"ProcedureCall#setTreatAsFunction(boolean isFunction)" is that the term
"function" is very leaky.

PostgreSQL uses functions only even when using OUT or REF_CURSOR parameter
types:

statement.executeUpdate(
    "CREATE OR REPLACE FUNCTION post_comments(postId BIGINT) " +
    "   RETURNS REFCURSOR AS " +
    "$BODY$ " +
    "    DECLARE " +
    "        postComments REFCURSOR; " +
    "    BEGIN " +
    "        OPEN postComments FOR  " +
    "            SELECT *  " +
    "            FROM post_comment   " +
    "            WHERE post_id = postId;  " +
    "        RETURN postComments; " +
    "    END; " +
    "$BODY$ " +
    "LANGUAGE plpgsql"
);

Which even works with the JPA syntax:

StoredProcedureQuery query =
entityManager.createStoredProcedureQuery("post_comments");
query.registerStoredProcedureParameter(1, void.class,
ParameterMode.REF_CURSOR);
query.registerStoredProcedureParameter(2, Long.class, ParameterMode.IN);

query.setParameter(2, 1L);

List<Object[]> postComments = query.getResultList();
assertEquals(2, postComments.size());

In fact, PostgreSQL is the only one where REF_CURSOR worked for the
database that I tested.
For SQL Server and MySQL, I got: "Dialect .*? not known to support
REF_CURSOR parameters",
while for Oracle I got: ".*?Dialect does not support resultsets via stored
procedures".

To summarize, we can have the discriminator method for knowing we should
handle a return-like SQL function:

1. ProcedureCall#returnResultSet(boolean isReturn)

Now, considering the options that you proposed, I'd go for the 2nd one:

"2. use the existing ProcedureCall param methods and just assume that in
the case of a function that the first parameter represents the function
return"

This is actually very close to JDBC too, so it would be easier for a
developer to recall the syntax because in JDBC the syntax is:

try (CallableStatement function = connection.prepareCall(
        "{ ? = call fn_count_comments(?) }" )) {
    function.registerOutParameter( 1, Types.INTEGER );
    function.setInt( 2, 1 );
    function.execute();
    int result = function.getInt( 1 );
}

But then, it means that we register the return type and the fact that we
use the first index like with a stored procedure:

query.registerStoredProcedureParameter(1, Integer.class, ParameterMode.OUT);

This way we have only a new methods being added (e.g. returnResultSet) and
we alter the callable statement syntax based on it.
When it comes to fetching the result set, we need to do it just like in the
JDBC example.

Vlad

On Mon, Feb 15, 2016 at 5:29 PM, Steve Ebersole <steve at hibernate.org> wrote:

> A function can also define (IN/)OUT arguments depending on database, so
> I'd rather not get into validating that.  I have no idea whether any
> databases support REFCURSOR arguments for a function.
>
> I think the registerFunctionReturnType suggestion works on this
> assumptions that (1) we are only returning the function RETURN (aka, not
> allowing OUT args) and (2) that the function only returns "simple" types.
> I think that the normal parameter registration could work for the function
> return.  The only thing I worry about is whether it is better to handle the
> param registration representing the return separately (see below).
>
> So I'd suggest the following...
>
> For the Hibernate native ProcedureCall I would add a method to indicate
> whether the call is a procedure or a function.  Just a simple boolean,
> something like:
>
> ProcedureCall#setTreatAsFunction(boolean isFunction)
>
> The the question becomes how to deal with the function-return.  To me it
> makes the most sense to reuse the notion of a ParameterRegistration,
> although I can see 2 specific options:
>
>    1. hold a separate "ProcedureCallImpl#functionReturnParameterRegistration"
>    field with appropriate ProcedureCall methods exposed
>    2. use the existing ProcedureCall param methods and just assume that
>    in the case of a function that the first parameter represents the function
>    return
>
>
> Another, more drastic, option on the native side is a
> specialized ProcedureCall type for functions: `FunctionCall
> extends ProcedureCall`.  But that effectively requires a new method (set
> of?) on Session to create the FunctionCall.
>
> And, unless I am mistaken, drivers support calling functions differently
> in terms of the "call string" we pass it so we'd also have to have a
> Dialect/CallableStatementSupport hook here.  This is the place where the
> difference in how we handle the ParameterRegistration for the function
> return is important.
>
> On a completely separate tangent... I wonder if we maybe want to wander
> into trying to
> leverage java.sql.DatabaseMetaData#getFunctions/java.sql.DatabaseMetaData#getProcedures
> to just automatically handle the difference (as opposed to the need for the
> user to explicitly hand us a boolean)?
>
> On Mon, Feb 15, 2016 at 8:58 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
> wrote:
>
>> Hi Steve,
>>
>> I'm glad we have plans to improve this, so let's use this conversation to
>> gather as much info as we need to open a JIRA issue for this task.
>>
>> For the Hibernate-specific ProcedureCall, should add a method like this:
>>
>> ProcedureCall#registerFunctionReturnType(Class type)
>>
>> This way we can define the result type and also know that we should
>> expect a function and not a stored procedure.
>> We can add a validation so that we disallow registering an OUT/REF_CURSOR
>> and a function-return-type for the same ProcedureCall instance.
>>
>> For @javax.persistence.NamedStoredProcedureQuery and
>> StoredProcedureQuery, we could add the following hint:
>>
>> org.hibernate.registerFunctionReturnType
>>
>> The logic should be just like for the
>> ProcedureCall#registerFunctionReturnType(Class type).
>>
>> Does it sound reasonable?
>>
>> Vlad
>>
>> On Mon, Feb 15, 2016 at 4:34 PM, Steve Ebersole <steve at hibernate.org>
>> wrote:
>>
>>> So to be clear...
>>>
>>> I absolutely think we should add support for this.  The question really
>>> is how to expose this, both in the native API
>>> (org.hibernate.procedure.ProcedureCall) and the JPA API
>>> (javax.persistence.StoredProcedureQuery), as well as
>>> @javax.persistence.NamedStoredProcedureQuery.  As far as the JPA contracts,
>>> obviously this requires a hint since we cannot change them (of course we
>>> could offer an extension to build a StoredProcedureQuery that models a
>>> function rather than a procedure.
>>>
>>> Notice too that there is another concern though: namely defining the
>>> spec for the output parameter.
>>>
>>> On Mon, Feb 15, 2016 at 8:27 AM Steve Ebersole <steve at hibernate.org>
>>> wrote:
>>>
>>>> Well as my todo comment says:
>>>>
>>>> // todo : how to identify calls which should be in the form `{? = call
>>>> procName...}` ??? (note leading param marker)
>>>>
>>>> //        more than likely this will need to be a method on the native API.  I can see this as a trigger to
>>>> //    both: (1) add the `? = ` part and also (2) register a REFCURSOR parameter for DBs (Oracle, PGSQL) that
>>>> //    need it.
>>>>
>>>> :)
>>>>
>>>>
>>>> On Mon, Feb 15, 2016 at 7:54 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> While writing the stored procedure section, I found a way to improve
>>>>> the
>>>>> current implementation to FUNCTIONS as well.
>>>>>
>>>>> Considering the following function:
>>>>>
>>>>> CREATE FUNCTION fn_post_comments(postId integer)
>>>>> RETURNS integer
>>>>> DETERMINISTIC
>>>>> READS SQL DATA
>>>>> BEGIN
>>>>>     DECLARE commentCount integer;
>>>>>     SELECT COUNT(*) INTO commentCount
>>>>>     FROM post_comment
>>>>>     WHERE post_comment.post_id = postId;
>>>>>     RETURN commentCount;
>>>>> END
>>>>>
>>>>> We could call this function and fetch the result ith plain-old JDBC:
>>>>>
>>>>> session.doWork(connection -> {
>>>>>     try (CallableStatement function = connection.prepareCall("{ ? =
>>>>> call fn_count_comments(?) }")) {
>>>>>         function.registerOutParameter(1, Types.INTEGER);
>>>>>         function.setInt(2, 1);
>>>>>         function.execute();
>>>>>         int commentCount = function.getInt(1);
>>>>>         assertEquals(2, commentCount);
>>>>>     }
>>>>> });
>>>>>
>>>>> When using the JPA 2.1 API:
>>>>>
>>>>> StoredProcedureQuery query =
>>>>> entityManager.createStoredProcedureQuery("fn_count_comments");
>>>>> query.registerStoredProcedureParameter("postId", Long.class,
>>>>> ParameterMode.IN);
>>>>>
>>>>> query.setParameter("postId", 1L);
>>>>>
>>>>> Long commentCount = (Long) query.getSingleResult();
>>>>>
>>>>> We get a "PROCEDURE fn_count_comments does not exist" exception
>>>>> because the
>>>>> SQL statement is built as "{call fn_count_comments(?)}" instead of "{
>>>>> ? =
>>>>> call fn_count_comments(?) }".
>>>>> I think we could define a hint like this:
>>>>>
>>>>> query.setHint(QueryHints.HINT_CALL_FUNCTION, true);
>>>>>
>>>>> So we could adjust the callable statement to work like a function.
>>>>>
>>>>> What do you think of this?
>>>>>
>>>>> Vlad
>>>>> _______________________________________________
>>>>> hibernate-dev mailing list
>>>>> hibernate-dev at lists.jboss.org
>>>>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>>>>>
>>>>
>>


More information about the hibernate-dev mailing list