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

首先是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配置文件的写法

{call pro_init(?, ?)}

最后是Java调用类的写法

public int teamInit(String username) {

int userId = 0;

Map paramMap = new HashMap();

paramMap.put("username", username);

paramMap.put("userId", userId);

this.getSqlMapClientTemplate().queryForObject("worldcup_guess.pro_init", paramMap);

return (Integer)paramMap.get("userId");

}