Mybatis调用MySQL存储过程

存储过程

• myprocedure1加法计算，使用IN/OUT传递参数和结果；myprocedure2，无参数，打印hello；myprocedure3依次调用myprocedure1和myprocedure2。

create procedure myprocedure1(IN a int, IN b int, OUT sum int)
BEGIN
    set sum = a+b;
END;

create procedure myprocedure2()
BEGIN
    declare tmp varchar(16);
    set tmp='hello';
    select tmp;
END;

create procedure myprocedure3(IN a int, IN b int, OUT sum int)
BEGIN
    CALL myprocedure1(a,b,sum);
    CALL myprocedure2();
END;
           

Dao

• Dao层分别声明调用上述三个存储过程。

@Resource
public interface MyDao {

    void call1(Map<String,Object> map);

    String call2();

    String call3(Map<String,Object> map);

}
           

Mapper.xml

• 实现Dao的声明。

<!--    通过OUT型参数返回-->
    <select id="call1"  statementType="CALLABLE">
         call myprocedure1(
             #{x,mode=IN},
             #{y,mode=IN},
             #{sum,mode=OUT,jdbcType=INTEGER}
             );
    </select>

<!--    通过RESULT返回-->
    <select id="call2" resultType="String" statementType="CALLABLE">
        call myprocedure2();
    </select>

<!--    通过OUT型参数和RESULT返回-->
    <select id="call3" resultType="String" statementType="CALLABLE">
        call myprocedure3(
                #{x,mode=IN},
                #{y,mode=IN},
                #{sum,mode=OUT,jdbcType=INTEGER}
            );
    </select>
           

注意OUT型参数必须指定mode=OUT和jdbcType

测试调用

• 使用Map可以将OUT型参数传递出来

@Test
void Text() {
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("x",1);
    paramMap.put("y",2);

    myDao.call1(paramMap);
    System.out.println(paramMap.get("sum"));

    System.out.println(myDao.call2());
    paramMap.put("x",4);

    String c3 = myDao.call3(paramMap);
    System.out.println(c3);
    System.out.println(paramMap.get("sum"));

}