Mybatis使用注解的方式执行存储过程并获取返回值

开始

通过搜索引擎搜索了获取返回值的Mybatis注解配置方式，但是都搜索不到，都是xml配置方式，尝试自己写出来了。

过程

1. 首先要有一个存储过程，in，out值。
2. 配置mapper：
   部分代码：

       //mybatis 注解 调用存储过程
       @Select({
               "call execute_seckill(",
               "#{map.seckillId,mode=IN,jdbcType=BIGINT},",
               "#{map.userPhone,mode=IN,jdbcType=BIGINT},",
               "#{map.killTime,mode=IN,jdbcType=TIMESTAMP},",
               "#{map.result,mode=OUT,jdbcType=INTEGER});"
       })
       @Results({
               @Result(column="result", property="result", jdbcType= JdbcType.INTEGER)
       })
       @Options(statementType = StatementType.CALLABLE)
       void killByProcedure(@Param("map") Map map);

   StatementType.CALLABLE 表示 存储过程

   3. 配置service
      部分代码：

           Map<String, Object> map = new HashMap<>();
           map.put("seckillId", seckillId);
           map.put("userPhone", userPhone);
           map.put("killTime", killTime);
           map.put("result", null);
           try {
               seckillCustomMapper.killByProcedure(map);
               // 获取result
               System.out.println(map.get("result"));
           } catch (Exception e) {
               logger.error(e.getMessage(), e);
           }

   通过map相对应的可以获取到result值。

 

@select 添加判断

添加两个 使用例子, 供参考:
@Select({"<script>",
        "select",
        " seatName,SUM(profit) as profit",
        "from td_seat_single_code_profit",
        "where code = #{queryType,jdbcType=VARCHAR}  ",
        "<![CDATA[ and tradeDate >= #{startDate,jdbcType=INTEGER} and tradeDate <= #{endDate,jdbcType=INTEGER} ]]>",
        "GROUP BY seatName ",
        "<if test='!flag'>ORDER BY  profit </if>  ",
        "<if test='flag'>ORDER BY  profit desc</if>  ",
        "LIMIT #{rankSize,jdbcType=INTEGER}",
        "</script> "})
List<TdSeadeProfit> getSeatProfitListByParam(@Param("rankSize") int rankSize, @Param("startDate") int startDate, @Param("endDate") int endDate, @Param("queryType") String queryType, @Param("flag") boolean flag);

@Select({"<script>",
        "SELECT  seatName,SUM(profit) as profit FROM td_seat_code_chain_profit  where  ",
        "  type = #{queryFlag,jdbcType=INTEGER}",
        " <if test='queryFlag ==1'>and tradingCode =#{queryType,jdbcType=VARCHAR} </if>",
        " <if test='queryFlag ==2'>and industrialChain =#{queryType,jdbcType=VARCHAR} </if>",
        "<![CDATA[ and tradeDate >= #{startDate,jdbcType=INTEGER} and tradeDate <= #{endDate,jdbcType=INTEGER} ]]>",
        "GROUP BY seatName ",
        "<if test='!flag'>ORDER BY  profit </if>  ",
        "<if test='flag'>ORDER BY  profit desc</if>  ",
        "LIMIT #{rankSize,jdbcType=INTEGER}",
        "</script> "})
List<TdSeatCinProfit> getSeatProfitListByParam(@Param("rankSize") int rankSize, @Param("startDate") int startDate, @Param("endDate") int endDate, @Param("queryFlag") int queryFlag, @Param("queryType")String queryType,@Param("flag") boolean flag);

注意大于小于的转义或者使用 <![CDATA[ ]]>包裹表达式

今天要获得存储过程的返回值，但不想用call方法感觉太麻烦， 

<select id="getUpdateHumanDisease" resultType="java.lang.String">
    declare @result varchar(50)
    exec updateHumanDisease #{0},#{1},#{2},@result output
    select @result
</select>