[hibernate-dev] Stored procedure improvement

[Vlad Mihalcea]

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