myBatsi调用存储过程

1.结构

　　

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',"男");

　　创建存储过程

DELIMITER $
CREATE PROCEDURE mybatis.get_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
$

　　

3.调用存储过程

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

在mysql中运行得：

4.新建实体类

package com.cao.bean;

public class PUser {
    private String id;
    private String name;
    private String sex;
    public PUser() {}
    public PUser(String id,String name,String sex) {
        this.id=id;
        this.name=name;
        this.sex=sex;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", sex=" + sex + "]";
    }
    

}

4.映射文件

<?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="procedure">
        <!-- 查询男性或者女性的数量，如果输入的是0，则是女性 -->
        <select id="getCount" parameterMap="getUserCount" statementType="CALLABLE">
            CALL mybatis.get_user_count(?,?)
        </select>
        <parameterMap type="java.util.Map" id="getUserCount">
            <parameter property="SexId" mode="IN" jdbcType="INTEGER"/>
            <parameter property="UserCount" mode="OUT" jdbcType="INTEGER"/>
        </parameterMap>
    </mapper>

5.配置文件

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

<configuration>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC">
        <property name="" value=""/>
      </transactionManager>
      <dataSource type="UNPOOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3308/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
      </dataSource>
    </environment>
  </environments>

   <mappers>
      <mapper resource="com/jun/sql/config/user.xml"/>
   </mappers>

</configuration>

6.测试类

package com.jun.main;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
public class MainTest {
    /**
     * 方式一
     * @throws Exception
     */
    @Test
    public void test1() throws Exception {
        Reader reader=Resources.getResourceAsReader("com/cao/config/Configuration.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession=sqlSessionFactory.openSession(true);                //true后是自动提交
        
        String statement="procedure.getCount";
        
        Map<String,Integer> parameterMap=new HashMap<>();
        parameterMap.put("SexId", 1);
        parameterMap.put("UserCount", 0);
        sqlSession.selectOne(statement, parameterMap);
        Integer result=parameterMap.get("UserCount");
        System.out.println("result="+result);
        sqlSession.close();
    }
    
    
}

7.效果