[hibernate-dev] Stored procedure improvement
Steve Ebersole
steve at hibernate.org
Mon Feb 15 12:44:54 EST 2016
IMO that is actually worse. So if we returnOutParameter==false, does that
mean we ignore all (IN/)OUT parameters? Because that's what that name
implies to me
On Mon, Feb 15, 2016 at 10:23 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
wrote:
> After giving it more thought, I think the method should be called:
>
> ProcedureCall#returnOutParameter(boolean returnOut)
>
> On Mon, Feb 15, 2016 at 6:16 PM, Vlad Mihalcea <mihalcea.vlad at gmail.com>
> wrote:
>
>> 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