[hibernate-dev] Stored procedure improvement
Vlad Mihalcea
mihalcea.vlad at gmail.com
Mon Feb 15 11:23:18 EST 2016
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