创建存储过程
IN为输入值,OUT 为输出值
CREATE PROCEDURE STAFF_LOGIN(
IN username_ VARCHAR(32),
IN passowrd_ VARCHAR(32),
OUT id_ INT,
OUT sid_ INT,
OUT name_ VARCHAR(20),
OUT gender_ VARCHAR(1),
OUT age_ INT,
OUT title_ VARCHAR(50),
OUT phone_ VARCHAR(11),
OUT admin_ TINYINT,
OUT lastLogin_ datetime
)
BEGIN
SELECT
id, sid, `name`, gender, age, title, phone, admin, lastLogin INTO
id_,sid_, name_,gender_, age_,title_, phone_, admin_, lastLogin_
FROM
staff
WHERE
username = username_
AND `password` = password_;
UPDATE staff SET lastLogin = now() WHERE username = username_ AND `password` = password_;
END
//INTO id_,sid_, name_,gender_, age_,title_, phone_, admin_, lastLogin_ 是将查询到的值跟输出的值相匹配
--mybatis mapper中编写调用存储过程方法
{call
STAFF_LOGIN(
#{username,mode=IN,jdbcType=VARCHAR},
#{password,mode=IN,jdbcType=VARCHAR},
#{id,mode=OUT,jdbcType=INTEGER},
#{sid,mode=OUT,jdbcType=INTEGER},
#{name,mode=OUT,jdbcType=VARCHAR},
#{gender,mode=OUT,jdbcType=VARCHAR},
#{age,mode=OUT,jdbcType=INTEGER},
#{title,mode=OUT,jdbcType=VARCHAR},
#{phone,mode=OUT,jdbcType=VARCHAR},
#{admin,mode=OUT,jdbcType=TINYINT},
#{lastLogin,mode=OUT,jdbcType=BIGINT}
)}
--Java接口中编写对应方法
public void login(Map map);
--实现类中获取返回值
Map map = new HashMap();
map.put("username", "魔笛");
map.put("password", "qwer");
staffMapper.login(map);
Staff staff = new Staff();
staff.setId((int) map.get("id"));
staff.setSid((int) map.get("sid"));
存储过程返回的值会映射到传递的map中,通过xml中填写的OUT值从map中获取出数据