1、创建存储过程
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 use_count;
else
select count() from mybatis.p_user where p_user.sex=‘男’ into use_count;
end if;
end
2、存储过程的调用示例一
set @user_count=0;
call mybatis.get_user_count(1,@user_count);
select @use_count;
sql映射文件中调用存储过程
<select id="getUserCount" paramterMap="getUserCountMap" statementType="CALLABLE">
call mybatis.get_user_count(?,?)
</select>
<paramterMap id="getUserCountMap" type="java.util.Map">
<parameter property="sexid" mode="IN" jdbcType="Integer"/>
<parameter property="usercount" mode="OUT" jdbcType="Integer"/>
</paramterMap>
java测试类
String statement="com.huawei.sdn.userMapper.getUserCount";
Map<String,Integer> paramterMap=new HashMap<String,Integer>();
paramterMap.put("sexid",0);
paramterMap.put("usercout"-1);
session.selectOne(statement,paramterMap);
Integer result=paramterMap.get("usercount");
3、存储过程的调用示例二
1)----不带参数的过程调用
<![CDATA[ {CALL pz_km_deal} ]]>
2)----带参数的过程调用
java中组装参数:
Map<String,Object> map1 = new HashMap<String,Object>();
map1.put{I_PARAM1, “2”);
map1.put{I_PARAM2, “1”);
mapper.xml文件中调用:
<![CDATA[ {CALL nea_budget_webservice.nea_user_dept_dept_select( #{I_PARAM1, mode=IN, jdbcType=VARCHAR}, #{I_PARAM2, mode=IN, jdbcType=VARCHAR}, #{I_PARAM3, mode=IN, jdbcType=VARCHAR}, #{I_PARAM4, mode=IN, jdbcType=VARCHAR}, #{I_PARAM5, mode=IN, jdbcType=VARCHAR}, #{I_PARAM6, mode=IN, jdbcType=VARCHAR}, #{I_PARAM7, mode=IN, jdbcType=VARCHAR}, #{O_RETURN_INT, mode=OUT, jdbcType=INTEGER}, #{O_RETURN_STRING, mode=OUT, jdbcType=VARCHAR} )} ]]>
注意:带参数时,传进来的是map,nea_budget_webservice.nea_user_dept_dept_select是包名.过程名;
注意:需要给pt6用户,创建过程包名和过程名的同义词;
注意: map的key 和mapper.xml中的参数名称 必须和过程的参数名称保持一致;