8.调用存储过程
8.1. 提出需求:
查询得到男性或女性的数量, 如果传入的是0就女性否则是男性
8.2. 准备数据库表和存储过程:
create table p_user( id int primary key auto_increment, name varchar(10), sex char(2) );
insert into p_user(name,sex) values('A',"男"); insert into p_user(name,sex) values('B',"女"); insert into p_user(name,sex) values('C',"男");
#创建存储过程(查询得到男性或女性的数量, 如果传入的是0就女性否则是男性) DELIMITER $ CREATE PROCEDURE mybatis.ges_user_count(IN sex_id INT, OUT user_count INT) BEGIN IF sex_id=0 THEN SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='女' INTO user_count; ELSE SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='男' INTO user_count; END IF; END $
#调用存储过程 DELIMITER ; SET @user_count = 0; CALL mybatis.ges_user_count(1, @user_count); SELECT @user_count; |
8.3. 创建表的实体类
public class User { private String id; private String name; private String sex; } |
8.4. userMapper.xml
<mapper namespace="com.atguigu.day03_mybatis.test7.userMapper"> <select id="getCount" resultType="java.util.Map" statementType="CALLABLE"> {call ges_user_count(#{sex_id,mode=IN,jdbcType=INTEGER},#{result,mode=OUT,jdbcType=INTEGER})} </select> </mapper> |
8.5. 测试调用:
Map<String, Integer> paramMap = new HashMap<>(); paramMap.put("sex_id", 1); Object returnValue = sqlSession.selectOne(statement, paramMap); System.out.println("result="+paramMap.get("result")); System.out.println("sex_id="+paramMap.get("sex_id")); System.out.println("returnValue="+returnValue); |
本教程由尚硅谷教育大数据研究院出品,如需转载请注明来源。