mybatis结合mysql，调用含有入参和出参的存储过程的方式

先看看存储过程，存储过程名叫get_serial_no：

BEGIN
  DECLARE v_category_code VARCHAR(20);
  DECLARE v_serial_no INT(5);
  DECLARE v_serial_code VARCHAR(20);
  DECLARE v_existsed TINYINT(1);
	
  START TRANSACTION;

  SELECT serial_no,serial_code INTO v_serial_no,v_serial_code FROM t_bm_serial_no WHERE category_id = p_category_id FOR UPDATE;

  SELECT category_code INTO v_category_code FROM t_bm_group_category WHERE category_id = p_category_id;
  IF v_category_code IS NOT NULL THEN
		IF v_serial_no IS NULL THEN
			SET p_serial_code = CONCAT(v_category_code,'0001');
			INSERT INTO t_bm_serial_no
				(category_id,serial_no,serial_code,oper_id,oper_time)
			VALUES
				(p_category_id,1,p_serial_code,p_oper_id,NOW());
		ELSE
			SET v_existsed = 1;
			cal_v_increase: WHILE v_existsed > 0 DO 
				SET v_serial_no = v_serial_no+1;
				SELECT count(check_in_no) INTO v_existsed FROM t_bm_checkin c
				WHERE c.DISEASE_CATEGORY_ID = p_category_id
				AND c.CHECK_IN_STATUS = '200102'
				AND RIGHT(check_in_no,5)+ 0 = v_serial_no;
			END WHILE cal_v_increase;  

			SET p_serial_code = CONCAT(v_category_code,RIGHT(CONCAT('0000',v_serial_no),4));      
			UPDATE t_bm_serial_no 
			SET serial_no = v_serial_no,
					serial_code = p_serial_code,
					oper_id = p_oper_id,
					oper_time = NOW()
			WHERE category_id = p_category_id;
		END IF;
  END IF;
  COMMIT;
END

参数：IN p_category_id varchar(36),IN p_oper_id varchar(36),OUT p_serial_code varchar(20)

第一种实现方式，参数为map：

java实现：

HashMap<String,Object> paramMap = new HashMap<String,Object>();
paramMap.put("diseaseCategoryId",checkin.getDiseaseCategoryId());
paramMap.put("operId", checkin.getOperId());
checkInDAO.getCheckInNoByCheckIn(paramMap);//出参存放于paramMap中
checkin.setCheckInNo(checkin.getCheckInNo());

mybatis中配置实现：

配置paramMap，其中diseaseCategoryId和operId为入参，checkInNo为出参

 <parameterMap id="CheckinParameterMap" type="java.util.Map">  
 	<parameter property="diseaseCategoryId" jdbcType="VARCHAR" javaType="String" mode="IN"/>    
    	<parameter property="operId" jdbcType="VARCHAR" javaType="String" mode="IN"/>       
    	<parameter property="checkInNo" jdbcType="VARCHAR" javaType="String" mode="OUT"/>  
 </parameterMap>

调用sql语句，statementType为callable表示调用存储过程：

<select id="getCheckInNoByCheckIn" parameterMap="CheckinParameterMap" statementType="CALLABLE" resultType="java.util.Map">
	{call get_serial_no(?,?,?)}
</select>

第二种实现方式，参数为javabean：

这里javabean名为checkin，checkin中含有diseaseCategoryId、operId和checkInNo三个参数：

checkInDAO.getCheckInNoByCheckIn(checkin);

这种方式，在mybatis中就不需要配置返回的结果集，同map方式一样，其中diseaseCategoryId和operId为入参，checkInNo为出参

<select id="getCheckInNoByCheckIn" parameterType="com.yantu.bean.BmCheckin" statementType="CALLABLE" resultType="com.yantu.bean.BmCheckin">
      {call get_serial_no(
            #{diseaseCategoryId,jdbcType=VARCHAR,mode=IN},
            #{operId,jdbcType=VARCHAR,mode=IN},
            #{checkInNo,jdbcType=VARCHAR,mode=OUT}
      )}
</select>