MyBatis调用存储过程

忘是亡心i 2022-06-10 13:38 296阅读 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 mybatis.ges_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 
    $
    
    #调用存储过程
    DELIMITER ;
    SET @user_count = 0;
    CALL mybatis.ges_user_count(1, @user_count);
    SELECT @user_count;

# 二、实现过程 #

1.创建表的实体类

2.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.sms.mybatis.test8.userMapper"> 
    	
    	<select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE">
    		CALL  mybatis.ges_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>
    	
    </mapper>

3.conf.xml

<mapper resource="com/sms/mybatis/test8/userMapper.xml"/

4.测试

package com.sms.mybatis.test8;
    
    import java.util.HashMap;
    
    import java.util.Map;
    
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    
    
    import com.sms.mybatis.util.MybatisUtils;
    
    public class Test8 {
    
    	public static void main(String[] args){
    		  SqlSessionFactory factory =MybatisUtils.getFactory();
    		  SqlSession session = factory.openSession();
    		  
    		  String statement="com.sms.mybatis.test8.userMapper.getUserCount";
    		  
    		  Map<String,Integer>  parameterMap = new HashMap<String,Integer>();
    		  parameterMap.put("sexid", 0);
    		  parameterMap.put("usercount", -1);
    		 
    		  session.selectOne(statement,parameterMap);
    		  
    		  Integer result = parameterMap.get("usercount");
    		  System.out.println(result);	  
    		  
    		  session.close();
    		}
    }

# 三、总结 #

在实现的过程中，测试的时候结果一直是null，最后发现是自己把usercount这个词给写错了。