04、调用存储过程抛：The parameter '@JLBH' in the procedure 'SAVE_TEST' was not declared as an OUTPUT paramete

最新推荐文章于 2022-02-08 10:32:00 发布

yandype

最新推荐文章于 2022-02-08 10:32:00 发布

阅读量509

点赞数

本文链接：https://blog.csdn.net/yandype/article/details/100877069

版权

异常集专栏收录该内容

12 篇文章 0 订阅

订阅专栏

一、异常描述：

在mybatis中调用存储过程抛：

The parameter '@JLBH' in the procedure 'SAVE_TEST' was not declared as an OUTPUT parameter

存储过程SQL语句：

Create Procedure dbo.SAVE_TEST @NEW_JLBH int OutPut,
                                     @JLBH int

二、Java代码：

对象pojo:

package com.yandype.domain.jinli.hnfb;

import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
public class CashRegister {
    // 入参
    private Integer jlbh;
    // 出参
    private Integer newJlbh;

    public CashRegister(Integer jlbh) {
        this.jlbh = jlbh;
    }
}

mapper接口：

package com.yandype.jinli.dao.hnfb;

import com.yandype.common.mappers.MyMapper;
import com.yandype.domain.jinli.hnfb.CashRegister;
import com.yandype.domain.jinli.hnfb.CashRegisterParam;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Repository
@Mapper
public interface CashRegisterParamMapper extends MyMapper<CashRegisterParam> {

    /**
     * 调用过程：
     *    Create Procedure dbo.SAVE_TEST @NEW_JLBH int OutPut, @JLBH int
     */
    void createCashRegister(CashRegister cashRegister);
}

mapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.yandype.dao.hnfb.CashRegisterParamMapper">

    <!-- 调用存储过程：Create Procedure dbo.SAVE_TEST @NEW_JLBH int OutPut, @JLBH int -->
    <select id="createCashRegister" parameterType="com.yandype.domain.jinli.hnfb.CashRegister" statementType="CALLABLE">
       {call SAVE_TEST(
           #{jlbh,mode=IN,jdbcType=INTEGER},
           #{newJlbh,mode=OUT,jdbcType=INTEGER}
           
       )}
    </select>

</mapper>

测试：

@Test
public void createCashRegisterTest(){
    CashRegister cashRegister = new CashRegister(32);
    cashRegisterParamMapper.createCashRegister(cashRegister);
    System.out.println(cashRegister);
}

运行测试就抛：The parameter '@JLBH' in the procedure 'SAVE_TEST' was not declared as an OUTPUT parameter.

根据这个异常提示，说 JLBH 它是个未声明的输出参数。可是，JLBH 它明明是输入参数。到了这里怎么会是输出参数呢？

原来，创建存储过程的SQL语句，输出和输入参数和mapper.xml 里面的配置顺序不一样。你可以发现，在 mapper.xml配置

里面 JLBH 放在第一个位置了。这样，就难怪每次运行测试抛 JLBH 未声明它是个输出参数了。

三、解决方案：

存储过程应该，严格按照存储过程的SQL语句入参，出参顺序一致！！！把mapper.xml 参数位置保持和存储过程的SQL位置一致就可以了：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.yandype.dao.hnfb.CashRegisterParamMapper">

    <!-- 调用存储过程：Create Procedure dbo.SAVE_TEST @NEW_JLBH int OutPut, @JLBH int -->
    <select id="createCashRegister" parameterType="com.yandype.domain.jinli.hnfb.CashRegister" statementType="CALLABLE">
       {call SAVE_TEST(     
           #{newJlbh,mode=OUT,jdbcType=INTEGER},
           #{jlbh,mode=IN,jdbcType=INTEGER}
           
       )}
    </select>

</mapper>