TypeORM数组参数查询CONFIGURATIONS实体返回空数组问题排查

我们定义了CONFIGURATIONS实体（代码如下），通过TypeScript代码生成SQL查询后，configuration变量始终为空数组。但在SQL Developer中将参数占位符:1替换为'TEST'执行相同查询时，能够正常返回记录，请协助排查问题原因。

实体代码 #

import { Column, Entity, Index, PrimaryGeneratedColumn } from "typeorm";

@Entity({ name: "CONFIGURATIONS" })
export class Configuration {
  @PrimaryGeneratedColumn({
    name: "ID",
    primaryKeyConstraintName: "configurations_pk",
  })
  id?: number;

  @Column({
    name: "CONFCATEGORY",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  @Index("configurations_idx1_confcategory")
  confCategory!: string;

  @Column({ name: "CONFID", nullable: true, type: "varchar2", length: 4000 })
  confID!: string;

  @Column({
    name: "CONFLABEL",
    nullable: false,
    type: "varchar2",
    length: 4000,
  })
  confLabel!: string;

  @Column({ name: "CONFVALUE", nullable: false, type: "clob" })
  confValue!: string;

  @Column({
    default: "NO",
    name: "ISACTIVE",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  isActive!: string;

  @Column({
    name: "CREATEDBY",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  createdBy?: string;

  @Column({
    default: () =>
      "CAST(systimestamp AT TIME ZONE 'UTC' AS TIMESTAMP WITH TIME ZONE)",
    name: "CREATIONDATE",
    nullable: false,
    type: "timestamp with time zone",
  })
  creationDate?: Date;

  @Column({
    default: "0.0.0.0",
    name: "SOURCEIP",
    nullable: false,
    type: "varchar2",
    length: 256,
  })
  sourceIP?: string;

  @Column({
    name: "LASTUPDATEDBY",
    nullable: true,
    type: "varchar2",
    length: 256,
  })
  lastUpdatedBy?: string;

  @Column({
    name: "LASTUPDATEDDATE",
    nullable: true,
    type: "timestamp with time zone",
  })
  lastUpdatedDate?: Date;
}

TypeScript查询代码 #

if (datasource.isInitialized) {
    const configurations: Configuration[] = await datasource
      .getRepository(Configuration)
      .createQueryBuilder("configuration")
      .where(`"configuration"."CONFCATEGORY" IN (:...categories)`, {
        categories: confCategory,
      })
      .select([
        `"configuration"."CONFCATEGORY"`,
        `"configuration"."CONFID"`,
        `"configuration"."CONFLABEL"`,
        `"configuration"."CONFVALUE"`,
        `"configuration"."ISACTIVE"`,
      ])
      .orderBy(`"configuration".CONFCATEGORY`, "ASC")
      .getMany();

      response.configurations = {...configurations}
      response.totalRecords = configurations.length;
}

生成的SQL查询 #

SELECT "configuration"."CONFCATEGORY", "configuration"."CONFID", "configuration"."CONFLABEL", "configuration"."CONFVALUE", "configuration"."ISACTIVE" FROM "CONFIGURATIONS" "configuration" WHERE "configuration"."CONFCATEGORY" IN (:1) ORDER BY "configuration".CONFCATEGORY ASC -- PARAMETERS: ["TEST"]

排查方向及解决方案 #

• 参数格式问题
  IN (:...categories)语法要求传入的categories必须是数组类型。如果confCategory是单个字符串（比如直接是"TEST"而非["TEST"]），TypeORM的参数绑定逻辑可能出现异常，导致Oracle无法正确匹配。
  解决：确保参数为数组，可在代码中做兼容处理：

  categories: Array.isArray(confCategory) ? confCategory : [confCategory]
  

• 大小写匹配问题
  Oracle字符串默认区分大小写，你在SQL Developer中手动添加了单引号执行'TEST'，但TypeORM绑定的参数是纯字符串值。如果数据库中CONFCATEGORY的实际值是小写（如'test'），就会出现匹配失败。此外，检查数据库表的字符集是否设置了大小写敏感。

• 引号导致的列名匹配问题
  你在查询中用双引号包裹表和列名（如"configuration"."CONFCATEGORY"），Oracle中双引号会强制严格匹配对象名称的大小写。而Oracle默认创建的表/列名是大写，不加双引号时会自动转为大写匹配，加双引号后如果实体映射的名称大小写不一致，就会找不到对应列。
  解决：改用TypeORM的链式API，避免手动写带双引号的SQL：

  // 推荐写法，自动映射列名
  .where("configuration.confCategory IN (:...categories)", { categories })
  // 或者更安全的whereIn方法
  .whereIn("configuration.confCategory", categories)
  
  // select部分改用实体属性名
  .select([
    "configuration.confCategory",
    "configuration.confID",
    "configuration.confLabel",
    "configuration.confValue",
    "configuration.isActive",
  ])
  

• CLOB列映射问题
  实体中confValue是CLOB类型，TypeORM对Oracle CLOB的处理偶尔会出现映射异常，导致查询结果无法正确转为实体对象。可以临时去掉confValue的查询，验证是否能返回其他字段的结果，以此排除该因素。

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