How does mybatis call mysql stored procedure and get the return value

mybatis calls mysql stored procedure and gets the return value

1, mysql creates stored procedure

#结束符号默认;, delimiter $$语句表示结束符号变更为$$
delimiter $$
CREATE PROCEDURE `demo`(IN inStr VARCHAR(100), out ourStr VARCHAR(4000))
BEGIN

SET ourStr = '01';
if (inStr == '02') then
    set ourStr = '02';
end if;

END$$
#结束符号修改
delimiter ;

2, mybatis calls

(1) Annotation method

// key1和key2都是参数map中的key
// key1是需要传入存储过程的值
// key2是用于接收存储过程返回的值，跟获取自增主键类似
@Select("call demo('${key1}', #{key2, mode=OUT, jdbcType=VARCHAR})")
@Options(statementType = StatementType.CALLABLE)
String getDemoValue(Map<String, Object> map);

(2)xml method

 <select id="getDemoValue" resultType="java.lang.String" parameterType="java.util.Map" statementType="CALLABLE">
     call demo(&#39;${key1}&#39;, #{key2, mode=OUT, jdbcType=VARCHAR})
 </select>

Mybatis encounters the pitfall of returning a null value when calling a stored procedure

Mybatis returns a null value when calling a stored procedure

It’s a simple one Stored procedure

BEGIN
   #SELECT COUNT(*) scount FROM test_user t WHERE t.user_sex=sex;
   SELECT user_name userName FROM test_user t WHERE t.user_sex=sex;
END

Then in the Mybatis Mapper configuration file

 <!--存储过程  输入参数用Map -->
  <select id="queryCountBySexWithProcedure" statementType="CALLABLE" parameterType="java.util.Map" resultType="java.util.HashMap">
      {
           CALL queryCountBySexWithProcedure(
              #{sex,jdbcType=VARCHAR,mode=IN},
              #{userName,jdbcType=VARCHAR,mode=OUT}
           )
    }
  </select>

then the userName value is null. Why is this? Mapping is performed in the mapper.xml file, and the attribute user_name marked in red below in the stored procedure is not automatically mapped to the userName value, so calling the userName value must return null.

BEGIN
   #SELECT COUNT(*) scount FROM test_user t WHERE t.user_sex=sex;
   SELECT user_name userName FROM test_user t WHERE t.user_sex=sex;
END

So how to deal with this problem? That is to add the INTO keyword

BEGIN
    #SELECT COUNT(*) scount FROM test_user t WHERE t.user_sex=sex;
         SELECT user_name INTO userName FROM test_user t WHERE t.user_sex=sex;
END

The above is the detailed content of How does mybatis call mysql stored procedure and get the return value. For more information, please follow other related articles on the PHP Chinese website!