Spring Boot存储过程调用时JdbcTemplate输出参数未清空问题

DevOps 小满

2026-8-25

问题描述

服务循环处理传入参数项时，每个项需执行两次存储过程调用：

第一次循环：checkprice返回9999（正常无数据情况），checkstock返回正确库存值150；
第二次循环：checkprice调用返回了上一次checkstock的结果150。

疑惑点：price的输出参数是第6位，stock的输出参数是第8位，不同位置参数却保留旧值，怀疑JdbcTemplate未清空或缓存输出参数。

相关代码

MyService.java

@Service
public class MyService extends BaseService implements MyInterface{
    protected static final Logger logger = LoggerFactory.getLogger(MyService.class);

    @Autowired
    private MyDAO myDAO;
    public myResponse checkOrder(args...)  
    {  
        for(something in args){
                // PRICE
                // Grab price data
                LinkedHashMap<String, Object> priceCallInParams = new LinkedHashMap<String, Object>();
                priceCallInParams .put("param1", "param1val");
                priceCallInParams .put("param2", "param2val");
                priceCallInParams .put("param3", "param3val");
                priceCallInParams .put("param4", "param4val");
                priceCallInParams .put("param5", "param5val");

                LinkedHashMap<String, Object> priceCallOutParams = new LinkedHashMap<String, Object>();
                priceCallOutParams .put("price", "0");

                
                logger.debug("Getting data...");
                Map<String, Object> priceData = new HashMap<String, Object>();

            
                priceData = myDAO.checkPrice(priceCallInParams , priceCallOutParams );
                
                BigDecimal unitPrice = new BigDecimal(9999);
                if (!priceData .get("PRCE").toString().trim().equals("")){
                    unitPrice = new BigDecimal(priceData.get("PRCE").toString().trim());
                }
                System.out.println("PRC - "+unitPrice);
                
                
                // AVAILABLE STOCK
                // Grab check stock data
                LinkedHashMap<String, Object> checkStockInParms = new LinkedHashMap<String, Object>();
                checkStockInParms.put("param1", "param1val");
                checkStockInParms.put("param2", "param2val");
                checkStockInParms.put("param3", "param3val");
                checkStockInParms.put("param4", "param4val");
                checkStockInParms.put("param5", "param5val");
                checkStockInParms.put("param6", "param6val");
                checkStockInParms.put("REQQTY", "123");

                LinkedHashMap<String, Object> checkStockOutParms = new LinkedHashMap<String, Object>();
                checkStockOutParms .put("AVAILQTY", "0");
                checkStockOutParms .put("NEXTDUE"," ");
                checkStockOutParms .put("NEXTQTY","0");

                
                logger.debug("Getting data...");
                Map<String, Object> checkStockDat = new HashMap<String, Object>();

                checkStockDat = myDAO.checkStock(checkStockInParms , checkStockOutParms );


                
                // Output quantity
                int AvailQTY = Integer.valueOf(checkStockDat.get("AVAILQTY").toString().trim());
                if (reqBIT.getRequestedQuantity()>AvailQTY) {
                    resBIT.setConfirmedQuantity(AvailQTY);
                }
                else {
                    resBIT.setConfirmedQuantity(reqBIT.getRequestedQuantity());
                }
            }

    }


}

MyDAO.java

@Component
public class MyDAO extends BaseDAO{
    protected static final Logger logger = LoggerFactory.getLogger(OrderDAO.class);

    
    public Map<String, Object> checkStock(LinkedHashMap<String, Object> inparms, LinkedHashMap<String, Object> outparms){
        StringBuilder builtSQL = new StringBuilder();
        builtSQL.append("CALL ");
        builtSQL.append("checkstock ");
        
// just generates our param string (?,?,?...) 
builtSQL.append(DataUtilities.genParmPlaceholderStringFromTotal(inparms.size()+outparms.size()));
        
        return executeStoredProcedure(builtSQL.toString(), inparms, outparms);
    }
    
    public Map<String, Object> checkPrice(LinkedHashMap<String, Object> inparms, LinkedHashMap<String, Object> outparms){
        logger.debug("CheckPrcc Initiated");
        StringBuilder builtSQL = new StringBuilder();
        builtSQL.append("CALL ");
        builtSQL.append("checkprice ");

// just generates our param string (?,?,?...) 
builtSQL.append(DataUtilities.genParmPlaceholderStringFromTotal(inparms.size()+outparms.size()));

        return executeStoredProcedure(builtSQL.toString(), inparms, outparms);
    }

}

BaseDAO.java

public class BaseDAO{
    @Autowired
    protected JdbcTemplate jdbcTemplate;
        
    protected static final Logger logger = LoggerFactory.getLogger(BaseDAO.class);

    protected Map<String, Object> executeStoredProcedure(String SQL, LinkedHashMap<String, Object> inParams, LinkedHashMap<String, Object> outParams){
        
        Map<String, Object> result = new HashMap<String, Object>();
            List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>();
            for (Map.Entry<String, Object> entry : inParams.entrySet()) {
                declaredParameters.add(new SqlParameter(entry.getKey().toString(), Types.CHAR));
            }
            for (Map.Entry<String, Object> entry : outParams.entrySet()) {
                declaredParameters.add(new SqlOutParameter(entry.getKey().toString(), Types.CHAR));
            }
            
           result = jdbcTemplate.call(new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection connection)
                throws SQLException {
                    CallableStatement callableStatement = connection.prepareCall(SQL);

                    int index = 0;
                    for (Map.Entry<String, Object> entry : inParams.entrySet()) {
                        index++;
                        callableStatement.setString(index, entry.getValue().toString());
                    }

                    for (Map.Entry<String, Object> entry : outParams.entrySet()) {
                        index++;
                        callableStatement.registerOutParameter(index, Types.CHAR);  
                    }
                    return callableStatement;
    
                    }
                }, declaredParameters);

        return result;
    }
    
}

问题诊断与修复

核心问题

问题出在BaseDAO的executeStoredProcedure方法中：你提前初始化了一个result HashMap，并且复用该对象接收存储过程返回值。jdbcTemplate.call方法会往这个Map中累积添加新的输出参数，而非覆盖清空。

第一次循环调用checkPrice后，result中存入PRCE:9999；
调用checkStock时，jdbcTemplate.call会把AVAILQTY:150等参数加入同一个result；
第二次循环调用checkPrice时，如果存储过程返回空的PRCE值，Map中会保留上一次checkStock留下的AVAILQTY值，导致你错误读取到150。

另外存在键名不匹配问题：priceCallOutParams定义的键是price，但读取时用的是PRCE，会导致读取值异常。

修复方案

每次调用生成新的结果Map
移除提前初始化的result，直接返回jdbcTemplate.call的返回值，确保每次调用都是全新的Map：

protected Map<String, Object> executeStoredProcedure(String SQL, LinkedHashMap<String, Object> inParams, LinkedHashMap<String, Object> outParams){
    List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>();
    for (Map.Entry<String, Object> entry : inParams.entrySet()) {
        declaredParameters.add(new SqlParameter(entry.getKey().toString(), Types.CHAR));
    }
    for (Map.Entry<String, Object> entry : outParams.entrySet()) {
        declaredParameters.add(new SqlOutParameter(entry.getKey().toString(), Types.CHAR));
    }
    
    // 直接返回新的结果Map，不再复用旧对象
    return jdbcTemplate.call(new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection connection)
        throws SQLException {
            CallableStatement callableStatement = connection.prepareCall(SQL);

            int index = 0;
            for (Map.Entry<String, Object> entry : inParams.entrySet()) {
                index++;
                callableStatement.setString(index, entry.getValue().toString());
            }

            for (Map.Entry<String, Object> entry : outParams.entrySet()) {
                index++;
                callableStatement.registerOutParameter(index, Types.CHAR);  
            }
            return callableStatement;
        }
    }, declaredParameters);
}

修正输出参数键名匹配问题
保持输出参数定义和读取的键名一致：

// 修改输出参数键名为PRCE，和读取逻辑匹配
LinkedHashMap<String, Object> priceCallOutParams = new LinkedHashMap<String, Object>();
priceCallOutParams.put("PRCE", "0");

// 读取逻辑无需修改，键名统一后即可正确获取值
if (!priceData.get("PRCE").toString().trim().equals("")){
    unitPrice = new BigDecimal(priceData.get("PRCE").toString().trim());
}

内容的提问来源于stack exchange，提问作者Codesidian