MyBatis调用存储过程

MySQL存储过程

DROP PROCEDURE IF EXISTS transferMoney;
-- 实现转账功能的存储过程
CREATE PROCEDURE transferMoney (
    IN fromUserId INT,    -- 付款方
    IN toUserId INT,    -- 收款方
    IN money DOUBLE,        -- 转账金额
        OUT state INT,            -- 状态
        OUT errorMsg VARCHAR(40)    -- 异常信息
)    
BEGIN
        SET state = 0;        -- 0表示正常，99表示异常
        START TRANSACTION;    -- 启用事务

        -- 先扣除付款人的金额
        UPDATE USER u SET u.money = u.money-money WHERE id = fromUserId;
            IF ROW_COUNT()=0 then  -- 如果影响记录为0，表示异常，标示为99
                set state = 99;
                                set errorMsg = CONCAT('付款人金额更新影响行数为0,fromUserId:',fromUserId);
            END IF;

        -- 再增加收款人的金额
        UPDATE USER u SET u.money = u.money+money WHERE id = toUserId;
            IF ROW_COUNT()=0 then  -- 如果影响记录为0，表示异常，标示为99
                set state = 99;
                                set errorMsg = CONCAT('收款人金额更新影响行数为0,toUserId:',toUserId);
            END IF;
        
        -- 如果运行正常则提交，否则，回滚
        IF state=0 then 
            COMMIT;
        ELSE 
            ROLLBACK;
        END IF;
END;

 MyBatis映射文件UserMapper.xml

<select id="transferMoney" statementType="CALLABLE" parameterType="java.util.HashMap">
        {
          call transferMoney (#{fromUserId,mode=IN,jdbcType=INTEGER},
                            #{toUserId,mode=IN,jdbcType=INTEGER},
                            #{money,mode=IN,jdbcType=DOUBLE},
                            #{state,mode=OUT,jdbcType=INTEGER},
                            #{errorMsg,mode=OUT,jdbcType=VARCHAR})
        }
</select>

 UserServiceImpl.java代码

@Override
     public void transferMoneyByProcedure(int fromUserId, int toUserId, double money) {
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("fromUserId", fromUserId);
        map.put("toUserId", toUserId);
        map.put("money", money);
        userDao.transferMoney(map);
        //存储过程完成转账，打印执行结果，存储过程返回的out参数state, errorMsg值会保存在map中。
        Logger.info(JSON.toJSONString(map));
        int state = Integer.parseInt(map.get("state").toString());
        if(state != 0) {
            System.out.println("转账异常："+map.get("errorMsg"));
        }

    }

UserServiceTest.java代码

    @Test
    public void testTransferMoneyByProcedure() {
        int fromUserId = 1;
        int toUserId = 3;
        userService.transferMoneyByProcedure(fromUserId, toUserId, 1001);
    }

控制台结果

[com.ssm.dao.UserDao.transferMoney] - ==>  Preparing: { call transferMoney (?, ?, ?, ?, ?) } 
[com.ssm.dao.UserDao.transferMoney] - ==> Parameters: 1(Integer), 3(Integer), 1001.0(Double)
[com.ssm.common.Logger] - {"toUserId":3,"state":99,"money":1001,"errorMsg":"收款人金额更新影响行数为0,toUserId:3","fromUserId":1}
转账异常：收款人金额更新影响行数为0,toUserId:3
[org.springframework.context.support.GenericApplicationContext] - Closing org.springframework.context.support.GenericApplicationContext@7da3f9e4: startup date [Sat Oct 24 14:50:09 GMT+08:00 2015]; root of context hierarchy

 数据库User表

 Spring事务配置

<!-- 第一种配置事务的方式 ，tx-->
    <tx:advice id="txadvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="add*" propagation="REQUIRED" rollback-for="Exception" />
            <tx:method name="update*" propagation="REQUIRED" rollback-for="Exception" />
            <tx:method name="del*" propagation="REQUIRED" rollback-for="Exception"/>
            <tx:method name="*TX" propagation="REQUIRED" rollback-for="Exception"/>
            <!-- 存储过程都是自带了事务处理，所以这里配置NEVER了 -->
            <tx:method name="*Procedure" propagation="NEVER" rollback-for="Exception"/>
            <tx:method name="*" propagation="REQUIRED" read-only="true"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="serviceMethod" expression="execution(* com.ssm.service.*.*(..))"/>
        <aop:advisor pointcut-ref="serviceMethod" advice-ref="txadvice"/>
    </aop:config>