Cannot get parameter from oracle procedure performed by mybatis

Question

I am developing a Java application using Spring 3.0.5, and I am working with an Oracle database using mybatis-spring.

I have an interface for mybatis:

public interface SubscriberMapper { Subscriber getSubscriberByMsisdn(String msisdn); void insertSubscriber(Subscriber subscriber); void updateSubscriber(Subscriber subscriber); void canCustomerSubscribe(@Param("msisdn") String msisdn, @Param("responseCode") Integer responseCode); 

}

mybatis xml content for canCustomerSubscribe:

 <parameterMap id="canCustomerSubscribeParams" type="map"> <parameter property="msisdn" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/> <parameter property="responseCode" jdbcType="NUMERIC" javaType="java.lang.Integer" mode="OUT"/> </parameterMap> <select id="canCustomerSubscribe" parameterMap="canCustomerSubscribeParams" statementType="CALLABLE"> CALL wallet.pkg_wallet_validation.can_customer_subscribe(#{msisdn}, #{responseCode}) </select> 

and code to execute:

 public void subscribe(String msisdn) throws InvalidArgumentException { Integer responseCode = 0; subscriberMapper.canCustomerSubscribe(msisdn, responseCode); System.out.println("msisdn: " + msisdn + ", responseCode: " + responseCode); } 

When I execute the "subscribe" method with an invalid "msisdn", I do not get the actual value from the procedure. Performing this procedure in the database returns reponseValue = 1001, but in Java code I get 0. I turned on debugging logging in stout for mybatis, and the output is:

  2011-10-19 10: 32: 46,732 DEBUG [main] (Slf4jImpl.java:28) ooo Connection Opened
 2011-10-19 10: 32: 46,909 DEBUG [main] (Slf4jImpl.java:28) ==> Executing: CALL wallet.pkg_wallet_validation.can_customer_subscribe (?,?) 
 2011-10-19 10: 32: 46,911 DEBUG [main] (Slf4jImpl.java:28) ==> Parameters: 509999999 (String), 0 (Integer)
 msisdn: 509999999, responseCode: 0

When I change the "subscribe" method responseCode = null, I get and mistakenly:

  org.springframework.jdbc.UncategorizedSQLException: Error setting null parameter.  Most JDBC drivers require that the JdbcType must be specified for all nullable parameters.  Cause: java.sql.SQLException: Invalid column type
 ;  uncategorized SQLException for SQL [];  SQL state [null];  error code [17004];  Invalid column type;  nested exception is java.sql.SQLException: Invalid column type

Answer 1

I have found a solution. The map must be a user instead of two parameters in the canCustomerSubscribe method.

  void canCustomerSubscribe(Map<String,Object> params); 

mybatis xml content:

 <select id="canCustomerSubscribe" parameterType="java.util.HashMap" statementType="CALLABLE"> CALL wallet.pkg_wallet_validation.can_customer_subscribe( #{msisdn, jdbcType=VARCHAR, javaType=java.lang.String, mode=IN}, #{responseCode,jdbcType=NUMERIC, javaType=java.lang.Integer, mode=OUT}) </select> 

(I need to add commas between the argument attributes)

calling it from a subscription service method:

 public void subscribe(String msisdn) throws InvalidArgumentException { Map<String, Object> params = new HashMap<String, Object>(); params.put("msisdn", msisdn); params.put("responseCode", null); subscriberMapper.canCustomerSubscribe(params); System.out.println(params.get("responseCode")); }