查询Oracle db时意外的IncorrectResultSizeDataAccessException

Question

我有下表：

  create table INTERNATIONALIZATION (
    ID number not null unique,
    LANG char(2) not null,
    EXT_ID number not null,
    EXT_COLUMN char(32) not null,
    EXT_NAME char(32) not null,
    TRANS_VAL nvarchar2(512) not null
  );

public Optional<String> getTranslation(long idSkill, Locale lang, String extColumn, String extName) {
    try {
        return Optional.of(jdbcTemplate.queryForObject("select TRANS_VAL from INTERNATIONALIZATION where ext_id = ? and lang = ? and ext_column = ? and ext_name = ?", String.class, idSkill, lang.toLanguageTag(), extColumn, extName));
    } catch (IncorrectResultSizeDataAccessException ex) {
        return Optional.empty();
    }
}

Answer 1

您可能需要验证传递给查询的参数。它们可能与您在Oracle SQL Developer中使用的不同。

为了调试，在捕获异常时，打印所使用的值。

    try {
            return Optional.of(jdbcTemplate.queryForObject("select TRANS_VAL from INTERNATIONALIZATION where ext_id = ? and lang = ? and ext_column = ? and ext_name = ?", String.class, idSkill, lang.toLanguageTag(), extColumn, extName));
        } catch (IncorrectResultSizeDataAccessException ex) {
            return Optional.empty();
          // print idSkill, lang.toLanguageTag(), extColumn, extName here
        }

另外，由于使用的是CHAR列，所以在比较时必须注意填充的空格。当您使用绑定变量时，将char与varchar进行比较时可能会出现问题。

    Table created.

    SQL> insert into t1 values ('A');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select count(*) from t1 where c1 = 'A'; 

      COUNT(*)
    ----------
         1

    SQL> select count(*) from t1 where c1 = 'A ';

      COUNT(*)
    ----------
         1

    SQL> select '<'|| c1 || '>' from t1;

    '<'||C1
    -------
    <A    >

    SQL> var s varchar2(5)
    SQL> exec :s := 'A' ;

    PL/SQL procedure successfully completed.

    SQL> select count(*) from t1 where c1 = :s;

      COUNT(*)
    ----------
         0

数据必须相等（5个特征--一个字母，4个空格）：

    SQL> exec :s :='A    '

    PL/SQL procedure successfully completed.

    SQL> select count(*) from t1 where c1 = :s;

      COUNT(*)
    ----------
         1

这只是一个提示，但我认为您最好使用varchar2列：

      create table INTERNATIONALIZATION (
        ID number not null unique,
        LANG varchar2(2) not null,
        EXT_ID number not null,
        EXT_COLUMN varchar2(32) not null,
        EXT_NAME varchar2(32) not null,
        TRANS_VAL nvarchar2(512) not null
      );