CODEWITHSUNDEEP
oracle-databasemybatis
user1071914
user1071914

Reputation: 3393

Return value from Oracle function using Mybatis

I have looked at a number of StackOverflow questions on this issue but can't find one that makes any sense. This one comes closest but doesn't show how to get the return value from the function.

Here's my mapper call:

public Long callMyFunction(@Param("recordId") Long recordId, @Param("otherId") Long otherId, @Param("date") Date date, @Param("comments") String comments);

Here's the mapper XML: 

<select id="callMyFunction" parameterType="map" statementType="CALLABLE"  resultType="java.lang.Long">
    {  #{resultId,javaType=java.lang.Long,jdbcType=NUMERIC,mode=OUT} = call MYSCHEMA.MYPACKAGE.my_function(
    #{recordId,jdbcType=NUMERIC,mode=IN},
    #{otherId,jdbcType=NUMERIC,mode=IN},
    #{date,jdbcType=DATE,mode=IN},
    #{comments,jdbcType=VARCHAR,mode=IN})}
</select>

The call works, but the return value (resultId) is always null.

Can anybody spot the problem?

Upvotes: 1

Views: 5524

Answers (1)

blackwizard
blackwizard

Reputation: 2044

If you want to return directly the result, then the SQL call must be: SELECT MYSCHEMA.MYPACKAGE.my_function(...) FROM DUAL

If you want to keep with calling the function in the procedure call style, that means the result is an OUT parameter (you env declared it OUT). The minimum change would consist in adding a parameter to the mapper method signature: public Long callMyFunction(@Param("recordId") Long recordId, @Param("otherId") Long otherId, @Param("date") Date date, @Param("comments") String comments, @Param("resultIdContainer") Map<String, Long> resultIdContainer);

In the XML: forget the resultType, this is for selects. And the call: { #{resultIdContainer.resultId, javaType=java.lang.Long,jdbcType=NUMERIC,mode=OUT} = call ...

Not that I use here a map to contain the resutlId: an indirection is required: the function will write the parameter 'result' value somewhere you can read later (after your mapper call), you can also use a class with a resultId property.

Upvotes: 3

Related Questions