8.调用存储过程

8.调用存储过程

8.1. 提出需求:

         查询得到男性或女性的数量, 如果传入的是0就女性否则是男性

8.2. 准备数据库表和存储过程:

create table p_user( 

         idint primary key auto_increment, 

         namevarchar(10),

         sexchar(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(INsex_id INT, OUT user_count INT)

BEGIN 

IF sex_id=0 THEN

SELECT COUNT(*) FROM mybatis.p_user WHEREp_user.sex='女' INTO user_count;

ELSE

SELECT COUNT(*) FROM mybatis.p_user WHEREp_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 {

         privateString id;

         privateString name;

         privateString sex;

}

8.4. userMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">




<mapper namespace="com.mybatis.test8.userMapper">


   <!-- 查询得到男性或女性的数量, 如果传入的是0就女性否则是男性 -->
   
   <select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE">
      CALL mybatis.ges_user_count(?, ?);
   </select>
   
   <!-- parameterMap.put("sexid",0);
parameterMap.put("usercount", -1); -->

   <parameterMap type="java.util.Map" id="getUserCountMap">
      <parameter property="sexid" mode="IN" jdbcType="INTEGER"/>
      <parameter property="usercount" mode="OUT" jdbcType="INTEGER"/>
   </parameterMap>


</mapper>

8.5. 测试调用:

public class Test8 {


@Test
public void getUser() throws IOException {

SqlSessionFactory factory = MybatisUtils.getFactory();
SqlSession session = factory.openSession();

String statement = "com.mybatis.test8.userMapper.getUserCount";

Map<String,Integer> parameterMap = new HashMap<>();
parameterMap.put("sexid",0);
parameterMap.put("usercount", -1);

session.selectOne(statement, parameterMap);

Integer result = parameterMap.get("usercount");
System.out.println(result);
}


}