myBatsi调用存储过程

1.结构

2.准备数据

　　建表和插入数据

 1 CREATE TABLE p_user(
 2     id INT PRIMARY KEY AUTO_INCREMENT,
 3     name VARCHAR(10),
 4     sex CHAR(2)
 5 );
 6 INSERT INTO p_user(name,sex) VALUES('A',"男");
 7 INSERT INTO p_user(name,sex) VALUES('B',"女");
 8 INSERT INTO p_user(name,sex) VALUES('C',"男");

　　创建存储过程

 1 DELIMITER $
 2 CREATE PROCEDURE mybatis.get_user_count(IN sex_id INT,OUT user_count INT)
 3 BEGIN
 4 IF sex_id=0 THEN
 5 SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='女' INTO user_count;
 6 ELSE
 7 SELECT COUNT(*) FROM mybatis.p_user WHERE p_user.sex='男' INTO user_count;
 8 END IF;
 9 END
10 $

3.调用存储过程

SET @user_count=0;
CALL mybatis.get_user_count(1, @user_count);
SELECT @user_count;

在mysql中运行得：

4.新建实体类

 1 package com.cao.bean;
 2 
 3 public class PUser {
 4     private String id;
 5     private String name;
 6     private String sex;
 7     public PUser() {}
 8     public PUser(String id,String name,String sex) {
 9         this.id=id;
10         this.name=name;
11         this.sex=sex;
12     }
13     public String getId() {
14         return id;
15     }
16     public void setId(String id) {
17         this.id = id;
18     }
19     public String getName() {
20         return name;
21     }
22     public void setName(String name) {
23         this.name = name;
24     }
25     public String getSex() {
26         return sex;
27     }
28     public void setSex(String sex) {
29         this.sex = sex;
30     }
31     @Override
32     public String toString() {
33         return "User [id=" + id + ", name=" + name + ", sex=" + sex + "]";
34     }
35     
36 
37 }

4.映射文件

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper
 3     PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5     <mapper namespace="procedure">
 6         <!-- 查询男性或者女性的数量，如果输入的是0，则是女性 -->
 7         <select id="getCount" parameterMap="getUserCount" statementType="CALLABLE">
 8             CALL mybatis.get_user_count(?,?)
 9         </select>
10         <parameterMap type="java.util.Map" id="getUserCount">
11             <parameter property="SexId" mode="IN" jdbcType="INTEGER"/>
12             <parameter property="UserCount" mode="OUT" jdbcType="INTEGER"/>
13         </parameterMap>
14     </mapper>

5.配置文件

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
 3 
 4 <configuration>
 5   <environments default="development">
 6     <environment id="development">
 7       <transactionManager type="JDBC">
 8         <property name="" value=""/>
 9       </transactionManager>
10       <dataSource type="UNPOOLED">
11         <property name="driver" value="com.mysql.jdbc.Driver"/>
12         <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
13         <property name="username" value="root"/>
14         <property name="password" value="123456"/>
15       </dataSource>
16     </environment>
17   </environments>
18 
19    <mappers>
20       <mapper resource="com/jun/sql/config/user.xml"/>
21    </mappers>
22 
23 </configuration>

6.测试类

 1 package com.jun.main;
 2 
 3 import java.io.IOException;
 4 import java.io.Reader;
 5 import java.util.HashMap;
 6 import java.util.Map;
 7 
 8 import org.apache.ibatis.io.Resources;
 9 import org.apache.ibatis.session.SqlSession;
10 import org.apache.ibatis.session.SqlSessionFactory;
11 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
12 import org.junit.Test;
13 public class MainTest {
14     /**
15      * 方式一
16      * @throws Exception
17      */
18     @Test
19     public void test1() throws Exception {
20         Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
21         SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
22         SqlSession sqlSession=sqlSessionFactory.openSession(true);                //true后是自动提交
23         
24         String statement="procedure.getCount";
25         
26         Map<String,Integer> parameterMap=new HashMap<>();
27         parameterMap.put("SexId", 1);
28         parameterMap.put("UserCount", 0);
29         sqlSession.selectOne(statement, parameterMap);
30         Integer result=parameterMap.get("UserCount");
31         System.out.println("result="+result);
32         sqlSession.close();
33     }
34     
35     
36 }

7.效果