最近用MyBatis调用存储过程返回结果集遇到了很多问题,特地总结一下
完整的存储过程如下所示:
在这里插入代码片USE [BSAsset_NewTest]
GO
/****** Object: StoredProcedure [dbo].[DeleteAssetBarCode] Script Date: 06/16/2021 16:43:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: bwch
-- Create date: 2013-11-28 9:15:38
-- Description:删除条码
-- =============================================
ALTER PROCEDURE [dbo].[DeleteAssetBarCode]
@Id INT,
@errMsg VARCHAR(100) OUTPUT
AS
BEGIN
--验证是否已打印
DECLARE @isPrint BIT;
SET @isPrint = (SELECT CAST((CASE WHEN dbo.AssetBarCode.PrintTime IS NOT NULL THEN 1 ELSE 0 END) AS bit) FROM dbo.AssetBarCode WHERE Id = @Id)
IF @isPrint = 1
BEGIN
--验证是否已入库
DECLARE @stockCount INT;
SET @stockCount = (SELECT COUNT(*) FROM dbo.AssetInScanLog WHERE AssetBarCodeId = @Id)
IF @stockCount > 0
BEGIN
SET @errMsg = '设备已入库'
END
ELSE
BEGIN
--验证是否已领用
DECLARE @collerCount INT;
SET @collerCount = (SELECT COUNT(*) FROM dbo.AssetColler WHERE BarCodeId = @Id)
IF @collerCount > 0
BEGIN
SET @errMsg = '设备使用中'
END
ELSE
BEGIN
--是否已销毁
DECLARE @disposeCount INT;
SET @disposeCount = (SELECT COUNT(*) FROM dbo.AssetDispose WHERE BarCodeId = @Id)
IF @disposeCount > 0
BEGIN
SET @errMsg='设备已报废'
END
END
END
END
IF @errMsg = ''
BEGIN
DECLARE @assetRecordId INT;
SET @assetRecordId = (SELECT AssetRecordId FROM dbo.AssetBarCode WHERE Id = @Id)
UPDATE dbo.AssetRecord SET Amount = (Amount - 1) WHERE Id = @assetRecordId
DELETE FROM dbo.AssetBarCode WHERE Id = @Id
END
END
实现调用存储过程实现删除条码信息,已入库,使用中,已报废的不能删除,并返回提示信息。参数如下所示:
解释说明,Id,输入参数,要删除条码的ID,errMsg,输入/输出/参数,返回的信息
Controller层
//删除资产条码信息
@PostMapping("/deleteAssetBarCode")
@ResponseBody
public BaseResponse deleteAssetBarCode(PhoneCodeRecord phoneCodeRecord){
BaseResponse response=null;
try{
Integer integer = assetRegisterService.deleteAssetBarCode(phoneCodeRecord);
if("设备已入库".equals(phoneCodeRecord.getErrMsg()) || "设备使用中".equals(phoneCodeRecord.getErrMsg()) || "设备已报废".equals(phoneCodeRecord.getErrMsg())){
response = new BaseResponse(StatusCode.Fail);
response.setMsg(phoneCodeRecord.getErrMsg());
}else{
response = new BaseResponse(StatusCode.Success);
}
}catch (Exception e){
response=new BaseResponse(StatusCode.SysBusy);
}
return response;
}
参数为POJO,实体类
public class PhoneCodeRecord {
private int Id;
private String errMsg;
//省略get/set方法
}
Service层
//删除条码信息
Integer deleteAssetBarCode(PhoneCodeRecord phoneCodeRecord);
Mapper(xml文件)
<!--删除条码信息-->
<parameterMap id="pm" type="com.example.phonegd.register.pojo.PhoneCodeRecord">
<parameter property="Id" mode="IN" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<parameter property="errMsg" mode="INOUT" javaType="java.lang.String" jdbcType="VARCHAR"/>
</parameterMap>
<select id="deleteAssetBarCode" parameterType="com.example.phonegd.register.pojo.PhoneCodeRecord" statementType="CALLABLE" resultType="java.lang.Integer">
<![CDATA[
{
call dbo.DeleteAssetBarCode(
#{Id,mode=IN, jdbcType=INTEGER},
#{errMsg,mode=INOUT, jdbcType=VARCHAR}
)
}
]]>
</select>
PostMan测试
备注:
1.output参数会自动将值映射到请求参数的对象属性中去,所以可以不需要resultType。
2.mode=IN为输入,mode=OUT为输出,mode=INOUT为输入输出,上述例子errMsg就用到了输入输出,可根据实际情况选择。
3.数据类型对应的javaType和jdbcType要一致
需要注意的事项:
1、 存储过程的参数和名称无关,只和顺序有关系
2、 存储过程的output参数,只能通过传入的map获取
3、 存储过程返回的结果集可直接用返回的map接收
4、 存储过程的return结果需要使用?=call procName(?,?)的第一个参数接收,需要指定对应的mode为OUT类型
5、 存储过程对应的数据类型为枚举类型,需要使用大写,如VARCHAR