iBatis调用存储过程以及MySQL创建存储过程

完整版见https://jadyer.github.io/2014/05/29/ibatis-procedure/

首先是MySQL中创建存储过程的SQL

-- 列出所有的存储过程
SHOW PROCEDURE STATUS;


-- 查看一个已存在的存储过程的创建语句,如果此存储过程不存在,会提示SQL错误（1305）：PROCEDURE pro_init does not exist
SHOW CREATE PROCEDURE pro_init;


-- 创建存储过程
DROP PROCEDURE IF EXISTS pro_init; -- 删除一个已存在的存储过程
DELIMITER //                       -- 声明当前MySQL分隔符为//
CREATE PROCEDURE pro_init(username VARCHAR(60), OUT userId INT)
BEGIN
SELECT user_id INTO userId FROM user_info ui WHERE ui.username=username;
IF userId IS NULL OR userId='' THEN
SELECT 0 INTO userId;
ELSE
INSERT INTO t_worldcup_team(userId, teamAA, teamBB, updateTime, createTime)
VALUES(userId, '巴西', '俄罗斯', NULL, now());
INSERT INTO t_worldcup_team(userId, teamAA, teamBB, updateTime, createTime)
VALUES(userId, '法国', '西班牙', NULL, now());
INSERT INTO t_worldcup_team(userId, teamAA, teamBB, updateTime, createTime)
VALUES(userId, '荷兰', '英格兰', NULL, now());
INSERT INTO t_worldcup_team(userId, teamAA, teamBB, updateTime, createTime)
VALUES(userId, '智利', '意大利', NULL, now());
INSERT INTO t_worldcup_team(userId, teamAA, teamBB, updateTime, createTime)
VALUES(userId, '伊朗', '葡萄牙', NULL, now());
INSERT INTO t_worldcup_team(userId, teamAA, teamBB, updateTime, createTime)
VALUES(userId, '希腊', '阿根廷', NULL, now());
END IF;
END
//                                 -- 分隔符,表示此SQL语句结束


-- 调用存储过程
CALL pro_init('hongyu', @userId);
SELECT @userId;

下面是iBatis配置文件的写法

<parameterMap id="pro_init_Map" class="java.util.Map">
<parameter property="username" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>
<parameter property="userId" javaType="java.lang.Integer" jdbcType="INT" mode="OUT"/>
</parameterMap>
<procedure id="pro_init" parameterMap="pro_init_Map">
{call pro_init(?, ?)}
</procedure>

最后是Java调用类的写法

public int teamInit(String username) {
int userId = 0;
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("username", username);
paramMap.put("userId", userId);
this.getSqlMapClientTemplate().queryForObject("worldcup_guess.pro_init", paramMap);
return (Integer)paramMap.get("userId");
}