MyBatis调用存储过程_mybatis call produces-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_29290295/article/details/87634835

本文介绍如何使用SQL存储过程统计特定状态的流水记录总金额，并通过MyBatis在Java应用中调用该存储过程。示例展示了创建存储过程、编写对应的MyBatis XML配置以及Controller代码实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

举个栗子：

统计删除（-1）和正常（0）流水的总金额。

创建表：

CREATE TABLE `t_pay_detail_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fee` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '费用金额',
  `record_status` int(2) NOT NULL DEFAULT '0' COMMENT '流水记录状态：0、有效；-1、删除',
  `created_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='流水表';

创建存储过程：

DELIMITER $
CREATE PROCEDURE `mytable`.getCountMoney ( IN recordStatus INT, OUT totalMoney DECIMAL ) 
BEGIN 
IF recordStatus = 0 THEN 
SELECT SUM(r.fee) AS totalMoney FROM t_pay_detail_record r WHERE r.record_status = 0 INTO totalMoney; 
ELSE 
SELECT SUM(r.fee) AS totalMoney FROM t_pay_detail_record r WHERE r.record_status = 1 INTO totalMoney; 
END IF ; 
END
$

mybatis对应xml，调用存储过程

	<parameterMap type="java.util.Map" id="getTotalMoneyByStatus">
		<parameter property="recordStatus" mode="IN" jdbcType="INTEGER"/>
		<parameter property="totalMoney" mode="OUT" jdbcType="DECIMAL"/>
	</parameterMap>

	<select id="getTest" statementType="CALLABLE" parameterMap="getTotalMoneyByStatus">
		CALL getCountMoney(?, ?)
	</select>

Controller代码

    @RequestMapping(value = "/get/{recordStatus}", method = { RequestMethod.GET},produces={MediaType.APPLICATION_JSON_UTF8_VALUE})
	public void get(@PathVariable int recordStatus){
    	BigDecimal totalMoney=BigDecimal.ZERO;
        Map<String,Object> parameterMap = new HashMap<String, Object>();
         parameterMap.put("recordStatus", recordStatus);
         parameterMap.put("totalMoney", totalMoney);
         payDetailRecordService.getTest(parameterMap);
         Object result = parameterMap.get("totalMoney").toString();
         System.out.println(result);
	}