Oracle数据库WHERE子句参数化引发ORA-00920无效关系运算符错误

问题背景 #

通过POST请求传入字符串列表，手动拼接SQL查询条件后传入Spring Data JPA的原生@Query查询，触发Oracle ORA-00920错误。

POST请求内容 #

{
  "list": ["002-02-0284","029-00-0100"]
}

相关代码 #

Controller层 #

@PostMapping("/fetch")
fun sync(@RequestBody @Valid request: DataRequest): ResponseEntity<SyncResponse> {
   val list = request.list
   return ResponseEntity(service.getContainers(list), HttpStatus.ACCEPTED)
}

Service层 #

fun getContainers(list: List<String>){
  val conditions = mutableListOf<String>()
  for (item in list) {
    val values = extractValues(item)
    val departmentId = values[0]
    val classId = values[1]
    val itemId = values[2]
    conditions.add("(DEPT_I = :departmentId AND CLASS_I = :classId AND ITEM_I = :itemId)")
  }
   val whereClause = conditions.joinToString(" OR ")
   println("--< whereClause --> $whereClause")
   val responseList = service.getContainers(whereClause)
}

fun extractValues(item: String): Array<Int> {
   return item.split("-").map { it.toInt() }.toTypedArray()
}

fun getContainers(whereClause: String): List<YourClass> {
  return repository.getContainers(queryString) // 此处存在笔误：应传入whereClause而非queryString
}

Repository层 #

@Query(
nativeQuery = true,
value = "SELECT PALT_I " +
    "FROM tableName " +
    "WHERE (:whereClause) AND STAT_C NOT IN ('PL', 'CN') " +
    "ORDER BY PALT_I "
)
fun getContainers(@Param("whereClause") whereClause: String): List<TableName>

错误信息 #

Hibernate: SELECT PALT_I, SIZE_C, AREA_C, DEPT_I, CLASS_I, ITEM_I, AISLE_I, BIN_I, LVL_I, PALT_UNIT_Q, AVAIL_PALT_UNIT_Q, PALT_PEND_POST_Q,PALT_PEND_PULL_Q, PALT_PEND_PTWY_Q, PULL_PALT_UNIT_Q, VCP_Q, SSP_Q, PALT_Q, CTN_PER_PALT_Q, EXPIRE_D, LOT_I, HOLD_STAT_C, STAT_C, MODF_USER_ID, MODF_PGM_N, MODF_TS FROM RSV_PALT WHERE (?) AND STAT_C NOT IN ('PL', 'CN') ORDER BY PALT_I

[WARN] 2023-11-22 11:25:42,993 http-nio-8080-exec-1 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - {X-API-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, X-CORRELATION-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, team=Ole} - SQL Error: 920, SQLState: 42000

[ERROR] 2023-11-22 11:25:42,994 http-nio-8080-exec-1 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - {X-API-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, X-CORRELATION-ID=3283db6f-ef50-4d04-be5b-45ed5751432d, team=Ole} - ORA-00920: invalid relational operator

问题原因 #

1. 参数绑定错误：拼接好的条件字符串被Hibernate当作普通字符串参数，用?占位符替换后，SQL变成WHERE ('(DEPT_I = 2 AND ...) OR (...)') AND ...，括号内是一个无关系运算符的字符串，触发Oracle语法错误。
2. 参数占位符无效：拼接条件时使用的:departmentId等占位符，不会被Hibernate解析，因为整个条件是作为单个参数传入的。
3. 代码笔误：Service层调用repository时传入了未定义的queryString，应为whereClause。

解决方案 #

方案1：使用Spring Data JPA Specification（推荐，防SQL注入） #

通过Specification动态构建查询条件，无需手动拼接SQL：

修改Repository层

interface YourRepository : JpaRepository<TableName, Long>, JpaSpecificationExecutor<TableName>

修改Service层

fun getContainers(list: List<String>): List<TableName> {
    // 构建每个字符串对应的查询条件
    val itemSpecs = list.map { item ->
        val values = extractValues(item)
        Specification<TableName> { root, _, cb ->
            cb.and(
                cb.equal(root.get<Int>("DEPT_I"), values[0]),
                cb.equal(root.get<Int>("CLASS_I"), values[1]),
                cb.equal(root.get<Int>("ITEM_I"), values[2])
            )
        }
    }.reduce { spec1, spec2 -> spec1.or(spec2) }

    // 组合最终查询条件（OR条件 + STAT_C过滤）
    val finalSpec = Specification<TableName> { root, _, cb ->
        cb.and(
            itemSpecs,
            cb.not(root.get<String>("STAT_C").`in`("PL", "CN"))
        )
    }

    return repository.findAll(finalSpec, Sort.by("PALT_I"))
}

方案2：使用Oracle多列IN子句（简单场景适用） #

利用Oracle支持的多列IN语法，直接传入参数列表：

修改Repository层

@Query(
    nativeQuery = true,
    value = """
        SELECT PALT_I 
        FROM tableName 
        WHERE (DEPT_I, CLASS_I, ITEM_I) IN (:conditions) 
          AND STAT_C NOT IN ('PL', 'CN') 
        ORDER BY PALT_I
    """
)
fun getContainers(@Param("conditions") conditions: List<Array<Int>>): List<TableName>

修改Service层

fun getContainers(list: List<String>): List<TableName> {
    val conditions = list.map { extractValues(it) }
    return repository.getContainers(conditions)
}

方案3：EntityManager动态构建查询（需注意SQL注入） #

若必须手动拼接SQL，使用EntityManager创建查询并绑定参数：

修改Service层

@Autowired
private lateinit var entityManager: EntityManager

fun getContainers(list: List<String>): List<TableName> {
    val conditions = mutableListOf<String>()
    val params = mutableMapOf<String, Any>()
    
    list.forEachIndexed { index, item ->
        val values = extractValues(item)
        val paramPrefix = "param$index"
        // 为每个条件生成唯一参数名
        conditions.add("(DEPT_I = :${paramPrefix}_dept AND CLASS_I = :${paramPrefix}_class AND ITEM_I = :${paramPrefix}_item)")
        params["${paramPrefix}_dept"] = values[0]
        params["${paramPrefix}_class"] = values[1]
        params["${paramPrefix}_item"] = values[2]
    }

    val whereClause = conditions.joinToString(" OR ")
    val sql = """
        SELECT PALT_I 
        FROM tableName 
        WHERE $whereClause 
          AND STAT_C NOT IN ('PL', 'CN') 
        ORDER BY PALT_I
    """.trimIndent()

    val query = entityManager.createNativeQuery(sql, TableName::class.java)
    params.forEach { (key, value) -> query.setParameter(key, value) }
    
    return query.resultList as List<TableName>
}

关键注意事项 #

• 优先使用方案1或2，避免手动拼接SQL，防止SQL注入风险
• 修复代码中的笔误：将Service层的queryString替换为whereClause

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