Mybatis Oracle Stored Procedure

Question

I am having problem calling oracle Stored Procedure and getting Output parameters from Oracle Stored Procedure. Here is my mapper AppUserMapper.java

public interface AppUserMapper {
    @Select(value= "{ CALL sp_check_user(    #{userId, mode=IN, jdbcType=VARCHAR },  #{userPwd, mode=IN, jdbcType=VARCHAR},  #{userType, jdbcType=VARCHAR, mode=OUT} )}")
    @Options(statementType = StatementType.CALLABLE)
    Object getUserType(String userId,String UserPwd);
}

Dao call
    Map<String, Object> retrurnStatus = (Map<String, Object>) appUserMapper.getUserType(userId,UserPwd);

Here is Stored Procedure code

CREATE OR REPLACE PROCEDURE HR.sp_check_user (userId VARCHAR,userPwd VARCHAR, userType OUT VARCHAR )
AS 
BEGIN
  BEGIN

  select user_type into userType from appuser where USER_ID = userId and USER_PWD = userPwd ;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      userType := 'Invalid';      
  END ;
END ;
/

It is executing stored procedure but giving error

DEBUG 2014-04-12 09:51:56,948 org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl: ==>  Preparing: { CALL sp_check_user( ?, ?, ? ) } 
DEBUG 2014-04-12 09:51:57,103 org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl: ==> Parameters: abc(String), abc1(String)

Could not complete request
    java.lang.NullPointerException
        at com.appuser.dao.AppUserDaoImpl.getUserType(AppUserDaoImpl.java:33)

I had been trying multiple options but could not find sample which I can take as is and try.

I will relay appropriate help on this as I have been trying to use oracle stored procs in mybatis with multiple input and output parameters.

Can someone provide simple example for calling Oracle stored procedure using mybatis for multiple input output parameters?

Answer 1

Jeff Butler says in mybatis-user group:

For stored procedure parameters, MyBatis will map both input and output parameters to properties in the parameterType.

You can try sending a Map, POJO or three annotated variables having userId, userPwd and userType. After the SP is called, MyBatis will set the OUT parameter to userType.

I cannot try it now, I'll try to edit the answer later. But can you try these,

1) Define your function as follows, and check if userType is updated after you call the SP.

void getUserType(Param("userId") String userId, Param("userPwd") String userPwd, Param("userType") String userType);

or

2) Create a Map, add 3 key-value pairs, i.e. ("userId", 1234), ("userPwd", 666), ("userType, null) tuples. After you call the SP, you can try getting the userType value.

void getUserType(Map<String, Object> myMap);

or

3) Create a class with 3 variables (userId, userPwd, userType), getters, setters. Set userId and userPwd values. Call the SP. Then, userObj.getUserType().

void getUserType(UserClass userObj);