mybatis调用sqlserver存储过程

1.无参返回多条结果

mapper

	@Select("EXEC csp_wait_packing_new")
    @ResultMap({"WaitPackingNew"})
    @Options(statementType = StatementType.CALLABLE)
    public List<WaitPackingNew> getPackData();

mapper.xml

    <resultMap id="WaitPackingNew" type="com.yunker.bjpacking.entity.WaitPackingNew">
        <result column="fcounts" property="fcounts"></result>
        <result column="status" property="status"></result>
        ...
    </resultMap>

2.无参返回多个结果集列表

存储过程csp_cut_dashboard

	...
	//返回两个列表结果
	select ...
	select ...
	...

service

	public HashMap<String, List<?>> getDashBoard() {
        List<List<Object>> dashBoard = boardMapper.getDashBoard();
        List<CutDashBoardA> cutDashBoardAList = new ArrayList<>();
        List<CutDashBoardB> cutDashBoardBList =new ArrayList<>();
        for (Object o : dashBoard.get(0)) {
            if (o instanceof CutDashBoardA) {
                cutDashBoardAList.add((CutDashBoardA) o);
            }
        }
        for (Object o : dashBoard.get(1)) {
            if (o instanceof CutDashBoardB) {
                cutDashBoardBList.add((CutDashBoardB) o);
            }
        }
        HashMap<String, List<?>> map = new HashMap<>();
        map.put("BoardA", cutDashBoardAList);
        map.put("BoardB", cutDashBoardBList);
        return map;
    }

mapper

	@Select("EXEC csp_cut_dashboard")
    @ResultMap({"CutDashBoardA","CutDashBoardB"})
    @Options(statementType = StatementType.CALLABLE)
    public List<List<Object>> getDashBoard();

mapper.xml

	<resultMap id="CutDashBoardA" type="com.yunker.bjpacking.entity.cutDashBoard.CutDashBoardA">
        ...
    </resultMap>
    <resultMap id="CutDashBoardB" type="com.yunker.bjpacking.entity.cutDashBoard.CutDashBoardB">
        ...
    </resultMap>

3.有参返回一行结果

存储过程csp_packing_Action

	ALTER PROC [dbo].[csp_packing_Action]
		 @order_number NVARCHAR(30),
		 @lot_number   NVARCHAR(30),
		 ...
	 AS

service

		Map<String, Object> parameterMap = new HashMap<>();
        parameterMap.put("orderNumber", packingActiondto.getOrderNumber());
        parameterMap.put("lotNumber", packingActiondto.getLotNumber());
        ...
        Map<String, Object> packingInfo = packingActionMapper.getPackingInfo(parameterMap);

mapper

    public Map<String, Object> getPackingInfo(Map<String, Object> params);

mapper.xml

	<select id="getPackingInfo" statementType="CALLABLE" parameterType="map" resultType="map">
	        {call csp_packing_Action(
	                #{orderNumber, mode=IN, jdbcType=VARCHAR},
	                #{lotNumber, mode=IN, jdbcType=VARCHAR},
	                ...
	            )}
	    </select>

4.有参返回值在声明中的存储过程

存储过程csp_update_shipping_status

	ALTER PROCEDURE [dbo].[csp_update_shipping_status]
		@order_number			    NVARCHAR(30), 
		@out_result					NVARCHAR(30) OUTPUT,
		@out_msg				    NVARCHAR(200) OUTPUT
	AS

service

        Map<String, Object> parameterMap = new HashMap<>();
        parameterMap.put("orderNumber", shippingStatusUpdatedto.getOrderNumber());
   		shippingMapper.updateShippingStatus(parameterMap);
        String outResult = (String) parameterMap.get("outResult");
        String outMsg = (String) parameterMap.get("outMsg");

mapper

	public void updateShippingStatus(Map<String, Object> params);

mapper.xml

    <resultMap id="updateShippingStatusResultMap" type="java.util.HashMap">
        <result property="outResult" column="@out_result"/>
        <result property="outMsg" column="@out_msg"/>
    </resultMap>

    <select id="updateShippingStatus" statementType="CALLABLE" parameterType="map" resultMap="updateShippingStatusResultMap">
        {call csp_update_shipping_status(
                #{orderNumber, mode=IN, jdbcType=VARCHAR},
                #{outResult, mode=OUT, jdbcType=NVARCHAR},
                #{outMsg, mode=OUT, jdbcType=NVARCHAR}
            )}
    </select>