myBatis学习笔记（7）——调用存储过程

超、凢脫俗 2022-08-08 07:01 84阅读 0赞

# 创建表 #

在数据库中执行

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 bank.ges_user_count(IN sex_id INT, OUT user_count INT) BEGIN IF sex_id=0 THEN SELECT COUNT(*) FROM bank.p_user WHERE p_user.sex='女' INTO user_count;
    ELSE
    SELECT COUNT(*) FROM bank.p_user WHERE p_user.sex='男' INTO user_count;
    END IF;
    END $

> sex\_id作为传入参数，user\_count 是接收结果值的参数

# 调用存储过程 #

## 映射文件 ##

<select id="getCount" parameterMap="paramMap" statementType="CALLABLE">
            CALL bank.ges_user_count(?, ?);
        </select>
        <parameterMap type="Map" id="paramMap">
            
            <parameter property="sexId" jdbcType="INTEGER" mode="IN"/>
            <parameter property="count" jdbcType="INTEGER" mode="OUT"/>
        </parameterMap>

## java调用 ##

SqlSessionFactory factory = MyBatisUtil.getFactory();
            SqlSession session = factory.openSession(true);
            String statement = "com.bank.orderMapper.getCount";
            Map<String,Object> map = new HashMap<String, Object>();
            map.put("sexId", 1);
            map.put("count", -1);
            map.get("count");
            session.selectOne(statement, map);
            //获取返回值
            System.out.println("结果:"+map.get("count"));
            session.close();