Mybatis 存储过程 利用SQL Server与Mybatis集成调用存储过程并获取结果集

Mybatis实战：SQL Server存储过程调用与结果集处理全攻略

场景引入：当报表遇上存储过程

"小王啊，财务部那个月度销售报表系统又卡死了！" 技术主管老张皱着眉头走过来，"现在每次生成报表要30多秒，财务部的同事已经投诉好几次了..."

小王心里一紧，这个报表系统是他半年前用MyBatis+SQL Server开发的，当时为了赶工期，直接在Java代码里拼装了复杂的SQL查询，随着数据量增长,性能问题越来越明显。

"我建议把核心查询逻辑移到SQL Server的存储过程中，"老张拍拍小王肩膀，"存储过程在数据库端预编译，还能减少网络传输，性能至少能提升50%。..用MyBatis调用存储过程并处理结果集，你之前没做过吧？"

确实，小王虽然熟悉MyBatis的基本CRUD操作，但对存储过程调用这块还真不太熟，让我们跟着小王的探索过程，一起看看如何用MyBatis优雅地调用SQL Server存储过程并处理各种返回结果。

基础准备：SQL Server存储过程创建

我们需要在SQL Server中创建一个示例存储过程，假设我们要处理的就是财务销售报表,创建一个能按时间段查询销售汇总的存储过程：

CREATE PROCEDURE sp_get_sales_report
    @start_date DATE,
    @end_date DATE,
    @min_amount DECIMAL(18,2) = 0
AS
BEGIN
    -- 结果集1：按产品分类统计
    SELECT 
        p.category,
        COUNT(*) as order_count,
        SUM(od.quantity * od.unit_price) as total_amount
    FROM 
        orders o
        JOIN order_details od ON o.order_id = od.order_id
        JOIN products p ON od.product_id = p.product_id
    WHERE 
        o.order_date BETWEEN @start_date AND @end_date
    GROUP BY 
        p.category
    HAVING 
        SUM(od.quantity * od.unit_price) >= @min_amount;
    -- 结果集2：按地区统计
    SELECT 
        c.region,
        COUNT(*) as order_count,
        SUM(od.quantity * od.unit_price) as total_amount
    FROM 
        orders o
        JOIN order_details od ON o.order_id = od.order_id
        JOIN customers c ON o.customer_id = c.customer_id
    WHERE 
        o.order_date BETWEEN @start_date AND @end_date
    GROUP BY 
        c.region;
    -- 返回影响行数
    RETURN @@ROWCOUNT;
END

这个存储过程有几个特点：

1. 接收输入参数（包括一个带默认值的参数）
2. 返回多个结果集
3. 最后还通过RETURN返回一个整数值

MyBatis配置与映射

Mapper接口定义

首先在Mapper接口中定义调用存储过程的方法：

public interface SalesReportMapper {
    @Options(statementType = StatementType.CALLABLE)
    @Select("{CALL sp_get_sales_report(#{startDate,jdbcType=DATE}, #{endDate,jdbcType=DATE}, #{minAmount,jdbcType=DECIMAL})}")
    void callSalesReport(Map<String, Object> params);
}

关键点说明：

• @Options(statementType = StatementType.CALLABLE) 声明这是一个存储过程调用
• {CALL ...} 是标准的JDBC调用存储过程语法
• 使用Map作为参数容器，方便获取各种返回结果

结果集映射

对于存储过程返回的多个结果集,我们需要定义对应的ResultMap：

<resultMap id="categoryResultMap" type="com.example.CategorySales">
    <result property="category" column="category"/>
    <result property="orderCount" column="order_count"/>
    <result property="totalAmount" column="total_amount"/>
</resultMap>
<resultMap id="regionResultMap" type="com.example.RegionSales">
    <result property="region" column="region"/>
    <result property="orderCount" column="order_count"/>
    <result property="totalAmount" column="total_amount"/>
</resultMap>

Java服务层实现

在服务层,我们需要处理完整的调用逻辑：

@Service
public class SalesReportService {
    @Autowired
    private SalesReportMapper salesReportMapper;
    public SalesReportResult generateReport(LocalDate startDate, LocalDate endDate, BigDecimal minAmount) {
        Map<String, Object> params = new HashMap<>();
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        params.put("minAmount", minAmount);
        // 调用存储过程
        salesReportMapper.callSalesReport(params);
        // 获取第一个结果集
        List<CategorySales> categorySales = (List<CategorySales>) params.get("result1");
        // 获取第二个结果集
        List<RegionSales> regionSales = (List<RegionSales>) params.get("result2");
        // 获取返回参数
        Integer affectedRows = (Integer) params.get("return");
        // 构建最终结果
        SalesReportResult result = new SalesReportResult();
        result.setCategorySales(categorySales);
        result.setRegionSales(regionSales);
        result.setAffectedRows(affectedRows);
        return result;
    }
}

进阶技巧：处理复杂场景

处理输出参数

如果存储过程有输出参数,可以这样处理：

-- 修改存储过程，增加输出参数
ALTER PROCEDURE sp_get_sales_report
    @start_date DATE,
    @end_date DATE,
    @min_amount DECIMAL(18,2) = 0,
    @total_sales DECIMAL(18,2) OUTPUT
AS
BEGIN
    -- ...原有逻辑...
    -- 设置输出参数值
    SELECT @total_sales = SUM(od.quantity * od.unit_price)
    FROM order_details od
    JOIN orders o ON od.order_id = o.order_id
    WHERE o.order_date BETWEEN @start_date AND @end_date;
END

Mapper接口调整：

@Options(statementType = StatementType.CALLABLE)
@Select("{CALL sp_get_sales_report(#{startDate,jdbcType=DATE}, #{endDate,jdbcType=DATE}, #{minAmount,jdbcType=DECIMAL}, #{totalSales,jdbcType=DECIMAL,mode=OUT})}")
void callSalesReportWithOutput(Map<String, Object> params);

使用XML方式调用存储过程

有些开发者更喜欢用XML配置方式：

<select id="callSalesReportXml" statementType="CALLABLE" parameterType="map">
    {CALL sp_get_sales_report(
        #{startDate,jdbcType=DATE},
        #{endDate,jdbcType=DATE},
        #{minAmount,jdbcType=DECIMAL},
        #{totalSales,jdbcType=DECIMAL,mode=OUT}
    )}
</select>

处理游标返回

SQL Server存储过程有时会返回游标：

@Select("{CALL sp_get_sales_report_cursor(#{startDate,jdbcType=DATE}, #{endDate,jdbcType=DATE}, #{minAmount,jdbcType=DECIMAL}, #{result1,jdbcType=CURSOR,resultMap=categoryResultMap,mode=OUT})}")
void callSalesReportWithCursor(Map<String, Object> params);

性能优化与最佳实践

1. 参数处理：对于NULL值处理，明确指定jdbcType
2. 结果集命名：建议为每个结果集指定明确的名称而不是依赖默认的result1, result2
3. 连接池配置：存储过程调用通常耗时较长，适当增加连接池超时设置
4. 日志记录：开启MyBatis的debug日志，方便调试存储过程调用

# application.properties配置示例
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

常见问题排查

1. 参数顺序错误：确保Java代码中的参数顺序与存储过程定义一致
2. 参数类型不匹配：特别注意日期、Decimal等类型的处理
3. 结果集获取失败：检查是否所有结果集都被正确处理
4. 权限问题：确保应用数据库账号有执行存储过程的权限

回到场景：问题解决

小王按照上述方法重构了报表系统，将核心查询逻辑移到了存储过程中,经过测试：

1. 响应时间从30秒降低到8秒
2. 数据库服务器CPU使用率下降40%
3. 网络传输量减少60%
4. 代码更加简洁，业务逻辑更清晰

"干得漂亮！"老张看着监控数据满意地说，"不过记住，存储过程虽然强大，但也不要滥用，业务规则经常变化的部分还是放在Java这边更灵活。"

小王点点头，在笔记本上又记下了一条经验法则："合理使用存储过程处理复杂数据操作，但保持业务逻辑在应用层的可维护性。"