cannot receive out parameter from oracle procedure executed by mybatis
13,551
I found solution. Map must be user instead of two parameter in 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 the commas between arguments attributes)
calling it from subscribe 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"));
}
Author by
Tural
Updated on June 16, 2022Comments
-
Tural almost 2 years
I develop java application using Spring 3.0.5 and I work with database Oracle using mybatis-spring.
I've 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 "subscribe" method with invalid "msisdn", I do not receive real out value from procedure. Execution of this procedure in the database returns reponseValue = 1001, but in Java code I receive 0. I turned on debug logging to stout for mybatis and 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 in "subscribe" method responseCode = null, I receive and error:
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
-
Panciz about 9 yearsI correct the syntax of the XML file, I need to add the commas between arguments attributes!