需求：查询得到男性或女性的数量，如果传入的是0就得到男性的数量，如果是1就得到女性的数量
数据库名mybatis 数据库表users
create table users( id int primary key auto_increment, name varchar(10), sex char(2) );
创建存储过程
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.users WHERE users.sex='女' INTO user_count; ELSE SELECT COUNT(*) FROM mybatis.users WHERE users.sex='男' INTO user_count; END IF; END $
MySQL console中调用存储过程
SET @user_count = 0; CALL mybatis.ges_user_count(1,@user_count); SELECT@user_count;
<select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE"> call mybatis.get_user_count(?,?) </select> <parameterMap type="java.util.Map" id="getUserCountMap"> <parameter property="sexid" mode="IN" jdbcType="INTEGER"/> <parameter property="usercount" mode="OUT" jdbcType="INTEGER"/> </parameterMap>
测试
SqlSessionFactory factory = MybatisUtil.getFactory();
SqlSession session = factory.openSession();
String s = "com.mybatis.test06.userMapper.getUserCount";
Map<String, Integer>parameterMap = new HashMap<String, Integer>();
parameterMap.put("sexid", 0);
parameterMap.put("usercount", -1);
session.selectOne(s, parameterMap);
Integer result = parameterMap.get("usercount");
System.out.println(result);
session.close();
