java - 在mybatis中映射结果集(两个整数)？

Question

我有以下存储过程 - 现在只返回两个整数:

USE [DB_NAME_HERE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[PR_PROCEDURE_NAME]
SELECT 0 AS ValueA,0 AS ValueB

我有以下映射器 XML:

  <resultMap id="BaseResultMap" type="com.companyname.service.mybatis.model.modelClass">
    <result column="ValueA" jdbcType="INTEGER" property="valueA" />
    <result column="ValueB" jdbcType="INTEGER" property="valueB" />
  </resultMap>

  <select id="getPurgedTokens" resultMap="BaseResultMap" statementType="CALLABLE">
      {call PR_PROCEDURE_NAME(
          #{resultContainer.valueA, mode=OUT, jdbcType=INTEGER},
          #{resultContainer.valueB, mode=OUT, jdbcType=INTEGER}
      )}
  </select>

然后我有以下用于映射的 java 类:

public class modelClass implements Serializable {

    private int valueA;
    private int valueB;

    public int getValueA() { return this.valueA; }
    public void setValueA(int valueA) { this.valueA = valueA; }

    public int getValueB() { return this.valueB; }
    public void setValueB(int valueB) { this.valueB= valueB; }
}

但是当我运行我的应用程序并尝试获取任何类型的数据时，我收到以下错误:

code=DataIntegrityViolationException,messages=[SqlSession operation; SQL []; Invalid JDBC call escape at line position 10.; nested exception is java.sql.SQLException: Invalid JDBC call escape at line position 10., Invalid JDBC call escape at line position 10.]]

非常感谢这里的所有帮助 - 我已经尝试了好几天了!

编辑:使用最新代码的精确副本进行更新!

MapperXML

    <?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.company.product.mybatis.mappers.MapperXML">

    <!-- Commented out the base result map because now we link directly in the paramter type? -->

    <!--
    <resultMap id="BaseResultMap" type="com.companyname.service.mybatis.model.modelClass">
    <result column="ValueA" javaType="java.lang.Integer" jdbcType="INTEGER" property="valueA"/>
    <result column="ValueB" javaType="java.lang.Integer" jdbcType="INTEGER" property="valueB"/>
  </resultMap>-->

  <!-- -------------------------------------------- -->

  <select id="getPurgedTokens" parameterType="com.companyname.service.mybatis.model.ModelClass" statementType="CALLABLE">
    {call PR_TokenPurge_Clean(
      #{dvalueA, mode=OUT, jdbcType=INTEGER},
      #{valueB, mode=OUT, jdbcType=INTEGER}
    )}
  </select>
</mapper>

模型类

package com.companyname.service.mybatis.model;

import java.io.Serializable;

public class ModelClass implements Serializable {

    private int valueA;
    private int valueB;


    public int getVavlueA() { return this.valueA; }
    public void setValueA(int valueA) { this.valueA = valueA; }

    public int getValueB() { return this.valueB; }
    public void setValueB(int valueB) { this.valueB = valueB; }
}

DAO

package com.company.product.dao.mybatis;

[imports (removed for brevity and security)...]


@Repository
public class ModelClassDaoMybatis implements ModelClassDao {

    private TokenPurgeModelMapper tokenPurgeModelMapper;
    private SqlSessionOperations sqlSessionOperations;

    @Override
    public TokenPurgeModel purgeTokens()
    {
        if (this.sqlSessionOperations.selectOne(".getPurgedTokens") != null)
        {
            System.out.println("I FOUND A VALUE!!!!!!");
            return (TokenPurgeModel) sqlSessionOperations.selectOne(testing);
        }
        else
        {
            System.out.println("No value found for select statement");
            return null;
        }
    }

    @Override
    public List<TokenPurgeModel> getPurgedTokens() {
        return tokenPurgeModelMapper.getPurgedTokens();
    }

    @Required
    public void setTokenPurgeModelMapper(final TokenPurgeModelMapper tokenPurgeMapper) {
        this.tokenPurgeModelMapper = tokenPurgeMapper;
    }
    @Required
    public void setSqlSessionOperations(SqlSessionOperations sqlSessionOperations) {
        this.sqlSessionOperations = sqlSessionOperations;
    }
}

模型映射器类

package com.company.product.mybatis.mappers;


[imports (removed for brevity and security)...]

public interface TokenPurgeModelMapper {

    // This is the one I am using right now. (as seen in the DAO!)
    TokenPurgeModel purgeTokens();

    // Get the list of tokens which have been removed from the PAN table.
    List<TokenPurgeModel> getPurgedTokens(); // This is actually never used - will be used in later funcationality!
}

感谢您为我查看此内容!

Answer 1

事实上，第一件事是使用语法{call PR_PROCEDURE_NAME()}

那就要看程序是怎么写的以及用在什么SGBD上。有多种可能性，包括:

将值作为过程 OUT 参数返回:

<select id="getPurgedTokens" parameterType="com.companyname.service.mybatis.model.modelClass" statementType="CALLABLE">
    {call PR_PROCEDURE_NAME(
        #{valueA, mode=OUT, jdbcType=INTEGER},
        #{valueB, mode=OUT, jdbcType=INTEGER}
    )}
</select>

在本例中，modelClass 参数实际上并不提供输入参数，这里仅用作 OUT 参数的目标容器；然后你就不需要resultMap。

如果结果变得更复杂，则在选择上返回光标:

{call PR_PROCEDURE_NAME(
    #{resultContainer.resultList, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=BaseResultMap}
)}

或者一个函数(而不是过程)返回获取 Select 的游标(某些 SGBD 可能允许只在主体中写入 SELECT)，然后只需:

SELECT function_name from DUAL

与resultMap绑定(bind)