MyBatis:java.sql.SQLException:无效的java.sql.Types常量值-9传递给set或update方法

本文介绍了MyBatis:java.sql.SQLException:无效的java.sql.Types常量值-9传递给set或update方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

从MyBatis调用SqlServer中的Stored_Procedure时出现此错误:

Im getting this error when calling a Stored_Procedure in SqlServer from MyBatis:

> ### Error querying database.  Cause: java.sql.SQLException: Invalid java.sql.Types constant value -9 passed to set or update method.
> ### The error may exist in mybatis-mapper/UpstreamMapper.xml
> ### The error may involve callStoredProcedure
> ### The error occurred while executing a query
> ### SQL: {call dbo.get_discount_value(?,?,       ?,?,?,?)}
> ### Cause: java.sql.SQLException: Invalid java.sql.Types constant value -9 passed to set or update method.

我是否需要将Stored_Procedure中的变量类型与POJO中的变量类型进行匹配?

Do I need to match the types of the variable in my Stored_Procedure with that of my POJO?

因此，如果这些是Stored_Procedure的变量:

So if these are the variables of Stored_Procedure:

CREATE PROCEDURE [dbo].[get_discount_value]
    @firstname nvarchar(50),
    @lastname nvarchar(10),
    @gender nvarchar(1),
    @dateOfBirth date,
    @age bigint
AS
BEGIN
 .. SP BODY..

POJO:StoredProcRef.java

POJO: StoredProcRef.java

public class StoredProcRef {

    String  SP_FirstName= "";
    String  SP_LastName= "";
    String  SP_Gender = "";
    Date    SP_Birthday = null;
    Integer SP_Age = 0;
    String  Output = "";
    ..GETTERS AND SETTERS..
}

MapperXML.xml

<resultMap id = "ReferenceValueParams"  type="StoredProcRef" autoMapping="false" >
      <result property = "SP_FirstName"         column = "SP_FirstName"/>
      <result property = "SP_LastName"          column = "SP_LastName"/>
      <result property = "SP_Gender"            column = "SP_Gender"/>
      <result property = "SP_Birthday"          column = "SP_Birthday"/>
      <result property = "SP_Age"               column = "SP_Age"/>
      <result property = "Output"               column = "DiscountValue"/>
   </resultMap>

        <select id = "callStoredProcedure" statementType = "CALLABLE" resultMap="ReferenceValueParams">
              {call dbo.lab_get_result_form_field(
     #{SP_FirstName,mode=IN,jdbcType=NVARCHAR},
     #{SP_LastName,mode=IN,jdbcType=NVARCHAR},
     #{SP_Gender,mode=IN,jdbcType=NVARCHAR},
     #{SP_Birthday,mode=IN,jdbcType=DATE},
     #{SP_Age,mode=IN,jdbcType=BIGINT},
     #{Output,mode=OUT,jdbcType=NVARCHAR}
    )}
      </select>

Interfacemapper.java

public interface UpstreamXmlMapper {
         public List<Map<String, ?>> callStoredProcedure(String FirstName, String LastName, String gender, Date dateOfBirth, Integer age);
}

Service.java

    public List<Map<String, ?>> getDiscountValue( StoredProcRef storedProcRef ) {

                List<Map<String, ?>> referenceValue = new ArrayList<>();
                SqlSession session = MyBatisUtil.getSqlSessionFactory().openSession();
                try {
                    UpstreamXmlMapper applicationMapper = session.getMapper(UpstreamXmlMapper.class);
                    referenceValue = applicationMapper.callStoredProcedure(storedProcRef.getSP_FirstName(), storedProcRef.getLastName(), storedProcRef.getSP_Gender(), storedProcRef.getSP_Birthday(), storedProcRef.getSP_Age()) ;

                    session.commit();
                } catch (Exception e) {
                    LOGGER.error(e);
                } finally {
                    session.close();
                }
                return referenceValue;
        }

Main.java

        SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
        sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
        Date date;
        date = sdf.parse("01-Oct-1984");

        List<StoredProcRef> storedProcRefList = new ArrayList<>();
        StoredProcRef storedProcRefObj = new StoredProcRef();
        storedProcRefObj.setSP_Age(28);
        storedProcRefObj.setSP_Birthday(date);
        storedProcRefObj.setSP_Gender("M");
        storedProcRefObj.setSP_FirstName("Joe");
        storedProcRefObj.setSP_LastName("Higashi");
        storedProcRefList.add(storedProcRefObj);

        List<Map<String, ?>> referenceValue = new ArrayList<>();
        referenceValue = service.getDiscountvalue(storedProcRefList.get(0));

推荐答案

您似乎对输入和输出参数感到困惑.
由于该过程未声明任何OUT参数，因此结果将映射到其他对象.
我将在下面使用新类进行解释.

You seem to be confused about input and output parameters.
As the procedure declares no OUT parameter, the result will be mapped to a different object.
I'll use the new class below for this explanation.

public class StoredProcOutput {
  private String Output;
  // getter/setter
}

由于用五个参数声明了该过程，所以call语句中应该有五个参数，而不是六个.即

As the procedure is declared with five parameters, there should be five parameters in the call statement, not six. i.e.

<select id="callStoredProcedure" statementType="CALLABLE"
    resultMap="outputResultMap">
  {call MY_PROC(
  #{SP_FirstName,mode=IN,jdbcType=NVARCHAR},
  #{SP_LastName,mode=IN,jdbcType=NVARCHAR},
  #{SP_Gender,mode=IN,jdbcType=NVARCHAR},
  #{SP_Birthday,mode=IN,jdbcType=DATE},
  #{SP_Age,mode=IN,jdbcType=BIGINT}
  )}
</select>

结果映射用于映射查询结果(您的过程中存在查询，对吧?).
正如您只想要DiscountValue一样，它很简单.

The result map is used to map the result of a query (there is a query in your procedure, right?).
As you just want DiscountValue, it is simple.

<resultMap id="outputResultMap" type="test.StoredProcOutput">
  <result property="Output" column="DiscountValue" />
</resultMap>

您的映射器方法应如下所示:

And your mapper method should look as follows:

List<StoredProcOutput> callStoredProcedure(StoredProcInput inParam);

我也更新了演示项目.
https://github.com/harawata/mybatis-issues/tree/master/so-58802623

I have updated the demo project as well.
https://github.com/harawata/mybatis-issues/tree/master/so-58802623

如果您需要进一步的帮助，我可能会在一段时间内进行聊天(不过，我不确定聊天的工作原理).

I may be available for a chat for a while if you need further help (I'm not sure how the chat works, though).

这篇关于MyBatis:java.sql.SQLException:无效的java.sql.Types常量值-9传递给set或update方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！