mybatis操作mysql存储过程

之前没有用mybatis操作mysql存储过程，一般都是用ibatis操作oracle存储过程，两者的差距还是蛮大的，mysql的存储过程语法和oracle都不一样。以下为代码片断（spring MVC+mybatis+MySQL）：

package com.letu.dao;

import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.letu.domain.Payments;

/**
 * 支付 - DAO层
 * 
 * @author Dwen
 * @version v 0.1 2013-9-12 下午10:08:15
 */
@Repository
public interface IPaymentsDao {

	/**
	 * 商品支付
	 * @param payments
	 * @return
	 */
	void pay(Map payMap);
	
	
}

 

   payments-mapper.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.letu.dao.IPaymentsDao">
	<parameterMap type="java.util.Map" id="payMap">
        <parameter property="userId" jdbcType="INTEGER" mode="IN"/>
        <parameter property="payPassword" jdbcType="VARCHAR" mode="IN"/>
        <parameter property="goodsId" jdbcType="INTEGER" mode="IN"/>
        <parameter property="payMoney" jdbcType="DOUBLE" mode="IN"/>
        <parameter property="resultStatus" jdbcType="INTEGER" mode="INOUT"/>
    </parameterMap>
    <resultMap type="java.util.Map" id="payResultMap">  
    		<result column="RESULT_STATUS" property="resultStatus"  jdbcType="INTEGER" javaType="java.lang.Integer" />   
  	</resultMap>
    <!-- 商品支付 -->
    <update id="pay" parameterMap="payMap" statementType="CALLABLE">
        {CALL P_PAY(?,?,?,?,?)}
    </update>
    
</mapper> 

 

    P_PAY存储过程：

/** 支付存储过程 */
CREATE  PROCEDURE P_PAY(IN p_user_id INTEGER,IN p_pay_password  VARCHAR(100),IN p_goods_id INTEGER,IN p_goods_price  DOUBLE,INOUT RESULT_STATUS INT) 
label_a:BEGIN
	declare v_account DOUBLE;
	declare v_total_account DOUBLE;
  	declare v_pay_password  VARCHAR(1000);
  	declare v_user_name     VARCHAR(1000);
  	declare v_goods_no      INTEGER;
	declare v_goods_name    VARCHAR(1000);
	declare v_market_price  DOUBLE;
	declare v_goods_price   DOUBLE;
  	declare v_goods_num     INT;
  	declare v_payments_id   INTEGER;
  	/*declare exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND*/
	/* 判断用户账户金额*/
  	SELECT ACCOUNT,TOTAL_ACCOUNT,USER_NAME,PAY_PASSWORD INTO v_account,v_total_account,v_user_name,v_pay_password 
  	FROM USER WHERE ID=p_user_id;
  	
  	/* 检查用户支付密码 ,INOUT RESULT_STATUS INT*/
  	BEGIN
  	IF (v_pay_password != p_pay_password) THEN
      /*SET RESULT_STATUS = 1;*/
      /*SELECT 1 INTO RESULT_STATUS;*/
      leave label_a;
  	END IF;
  	END;
  	
  	 /* 检查用户余额是否足够 */
  	 BEGIN
  	IF (v_account < p_goods_price) THEN
    		/*SET RESULT_STATUS = 2;*/
    		/*SELECT 2 INTO RESULT_STATUS;*/
    		leave label_a;
  	END IF;
  	END;
  	
  	/* 用户余额支付商品 */
  	BEGIN
  	IF (v_account >= p_goods_price) THEN
    		UPDATE USER SET ACCOUNT=ACCOUNT-p_goods_price,TOTAL_ACCOUNT=TOTAL_ACCOUNT-p_goods_price 
    		WHERE ID=p_user_id;
    	END IF;
    	END;
    	
    	/* 查询购物车*/
	BEGIN
	  SELECT GOODS_NO,GOODS_NAME,MARKET_PRICE,GOODS_PRICE,GOODS_NUM 
	  INTO v_goods_no,v_goods_name,v_market_price,v_goods_price,v_goods_num 
	  FROM GOODS_CART WHERE USER_ID=p_user_id AND GOODS_ID=p_goods_id;
	  /* 修改购物车商品状为已支付*/
	  UPDATE GOODS_CART SET STATUS='1',UPDATE_AT=now() WHERE  USER_ID=p_user_id AND GOODS_ID=p_goods_id;
	END;
	
	/* 修改商品库存*/
    	BEGIN
    		UPDATE GOODS SET STORAGE_NUM=STORAGE_NUM-v_goods_num,UPDATE_AT=now() WHERE ID=p_goods_id;
    	END;
	
    /* 支付单*/
  	BEGIN
    		INSERT INTO PAYMENTS(USER_ID,USER_NAME,PAY_MONEY,GOODS_ID,GOODS_NO,GOODS_NUM,PAY_TYPE,PAY_DATE,PAY_STATUS,CREATE_AT) 
    		VALUES(p_user_id,v_user_name,p_goods_price,p_goods_id,v_goods_no,v_goods_num,'1',now(),'1',now());
  		/* 获得支付单id*/
  		SET v_payments_id = LAST_INSERT_ID();
  		/* 用户账户流水*/
    		INSERT INTO USER_ACCOUNT(USER_ID,SOURCE_ID,SOURCE_NAME,DIRECTION,MONEY,ACCOUNT_OLD,ACCOUNT_NEW,STATUS,PAY_TYPE,CREATE_AT) 
    		VALUES(p_user_id,v_payments_id,'PAYMENTS','1',p_goods_price,v_account-p_goods_price,v_account,'1','1',now());
  	END;
  
  	/* 生成订单*/
  	BEGIN
    		INSERT INTO ORDER_GOODS(GOODS_NAME,GOODS_ID,GOODS_NO,MARKET_PRICE,ORDER_PRICE,SHIPPING_NUM,USER_ID,USER_NAME,PAYMENT_ID,ORDER_STATUS,CREATE_AT) 
    		VALUES(v_goods_name,p_goods_id,v_goods_no,v_market_price,v_goods_price,v_goods_num,p_user_id,v_user_name,v_payments_id,'1',now());
  	END;
  	
  	/*SELECT RESULT_STATUS; */
  
END;