Spring Data JPA Native Query 中处理 IN 列表为 null 的情况

声明：个人笔记，仅供参考，谢谢

处理方法

在使用原生 SQL 查询(Native Query)时，当传入的 IN 列表参数可能为 null 时，需要特别处理以避免 SQL 语法错误或意外结果。以下是几种不同的处理方式：

正确：使用 COALESCE 或 CASE WHEN 处理

    @Query(value = "SELECT * " +
            "FROM " +
            "   users " +
            "WHERE " +
            "   id IN (COALESCE(:ids, NULL)) OR :ids IS NULL",
            nativeQuery = true)
    List<User> findByIds(@Param("ids") List<Long> ids);

---

错误：使用动态 SQL 条件 

@Query(value = """
    SELECT * FROM users 
    WHERE (:ids IS NULL OR id IN (:ids))
    """, 
    nativeQuery = true)
List<User> findByIds(@Param("ids") List<Long> ids);

会报错如下：

[ERROR: operator does not exist: bigint = text 建议：No operator matches the given name and argument types. You might need to add explicit type casts.]

---

补充资料：

COALESCE 函数详解

COALESCE 是 SQL 中的一个标准函数，用于处理 NULL 值。它在大多数关系型数据库中都可用，包括 MySQL、PostgreSQL、Oracle、SQL Server 等

基本语法

COALESCE(expression1, expression2, ..., expressionN)

功能说明

1. 从左到右依次检查每个参数

2. 返回第一个非 NULL 的值

3. 如果所有参数都是 NULL，则返回 NULL

与 IN 结合使用的场景

在 Spring Data JPA 的 Native Query 中，我们使用 COALESCE 来处理可能为 NULL 的 IN 列表参数：

与其他类似函数的比较

1. COALESCE vs ISNULL (SQL Server):

   • COALESCE 是标准 SQL，可接受多个参数

   • ISNULL 是 SQL Server 特有，只接受两个参数

2. COALESCE vs NVL (Oracle):

   • COALESCE 是标准 SQL

   • NVL 是 Oracle 特有

3. COALESCE vs IFNULL (MySQL):

   • COALESCE 可处理多个参数

   • IFNULL 只能处理两个参数