java使用Mybatis框架调用mysql存储过程

java使用Mybatis框架调用mysql存储过程

1.mysql创建简单存储过程：

CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`(IN `userName` varchar(50),IN `passWord` varchar(50))

COMMENT '用于登录时添加登录次数和最后登录时间'

BEGIN

#Routine body goes here...

DECLARE temp int;

    DECLARE count int;

    SELECT * FROM sys_user WHERE USER_NAME = userName AND PASSWORD = passWord;

    SELECT count(1) INTO count FROM sys_user WHERE USER_NAME = userName AND PASSWORD = passWord;

    IF (count != 0)THEN

        SELECT LOGIN_TIMES INTO temp FROM sys_user WHERE USER_NAME = userName AND PASSWORD = passWord;

        SET temp := temp+1;

        UPDATE sys_user SET LOGIN_TIMES = temp,LAST_LOGIN_TIME = NOW() WHERE USER_NAME = userName AND PASSWORD = passWord;

    END IF;

END;

使用navicat穿件存储过程要注意：

有参数传入时必须添加设置参数长度varchar(20),不然无法保存

 

 

2.mybatis调用存储过程：

该存储过程会返回

SELECT * FROM sys_user WHERE USER_NAME = userName AND PASSWORD = passWord;

的查询结果