Spring Data JPA查询阶段如何将值为null的Integer字段转换为0

假设存在实体类 Animal，数据库中部分动物记录的 amount 字段值为null，业务上允许保存未设置amount字段的动物记录。现需要在查询逻辑中实现：当amount字段值为null时，自动将其转换为0。

• 保存阶段将null的amount值转为0：不符合业务要求，禁止使用。
• 查询获取结果后手动将null的amount映射为0：存在排序、分页异常。按amount升序排序时null值会排在结果最前端，降序排序时null值会排在结果末尾，和null转0后的预期排序位置不符；且内存中修改值无法修正数据库层面已经完成的分页逻辑，会导致分页数据顺序错乱。

当前需要在查询语句层面实现兼容不同数据库、排序分页逻辑正确的方案。

Spring Data Jpa 2.2.9.RELEASE, Postgresql 42.2.16。

@Repository
public interface AnimalRepository extends JpaRepository<AnimalEntity, Long> {

    @Query(value = "SELECT animal FROM AnimalEntity animal" +
            " WHERE animal.ownerId = :ownerId" +
            " and function('replace', upper(animal.name), '.', ' ') like function('replace', upper(concat('%', :name,'%')), '.', ' ') "
    )
    Page<AnimalEntity> findAllLikeNameAndOwnerSorted(String ownerId, String name, Pageable pageable);

}

@Entity
@Table(name = "animal")
public class AnimalEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Integer amount;

    private String name;
    
    private String ownerId;

}

直接替换null为0的方案存在兼容性缺陷：Postgresql和HSQLDB的null值默认排序规则存在差异，HSQLDB环境测试可正常通过，但Postgresql实际环境排序结果不符合预期。

排序结果示例 #

数据库测试Animal实体数据：

[
  {"name":"Cat", "amount":599999.99},
  {"name":"Dog", "amount":null},
  {"name":"John", "amount":5000}
]

HSQLDB按amount降序查询结果：

[
  {"name":"Cat", "amount":599999.99},
  {"name":"John", "amount":5000},
  {"name":"Dog", "amount":null}
]

Postgresql按amount降序查询结果：

[
  {"name":"Dog", "amount":null},
  {"name":"Cat", "amount":599999.99},
  {"name":"John", "amount":5000}
]

方案核心是将null值转0的逻辑同时应用到数据库排序阶段和实体加载阶段，从根源上避免数据库默认null排序规则差异、内存修改导致的分页异常问题，且完全不影响保存逻辑。

步骤1：实体加载时自动转null为0 #

在AnimalEntity类中添加JPA生命周期回调方法@PostLoad，该方法会在实体从数据库加载完成后自动执行，将null的amount设置为0，不影响保存时的null存储逻辑：

@PostLoad
public void fillDefaultAmount() {
    if (this.amount == null) {
        this.amount = 0;
    }
}

步骤2：排序时统一将null按0处理 #

使用JPA标准函数COALESCE(字段名, 默认值)构造排序规则，该函数为JPQL标准函数，兼容Postgresql、HSQLDB等所有支持JPA的数据库，排序时会自动将null值转为0参与排序，彻底解决不同数据库默认null排序规则不一致的问题，保证分页逻辑基于正确的排序结果执行。
调用Repository方法构造Pageable时，通过JpaSort.unsafe传入带函数的排序表达式（注意排序规则需后端白名单控制，避免前端恶意传入导致SQL注入）：

// 按amount降序场景，null值等价于0参与排序
Sort amountSort = JpaSort.unsafe(Sort.Direction.DESC, "COALESCE(amount, 0)");
Pageable pageable = PageRequest.of(pageIndex, pageSize, amountSort);
// 执行查询
Page<AnimalEntity> result = animalRepository.findAllLikeNameAndOwnerSorted(ownerId, name, pageable);

如果需要动态切换升降序、或组合其他字段排序，只需对应调整Sort构造逻辑即可，例如按amount升序+id升序：

Sort sort = JpaSort.unsafe(Sort.Direction.ASC, "COALESCE(amount, 0)")
        .and(Sort.by(Sort.Direction.ASC, "id"));

方案验证 #

针对示例测试数据，按amount降序查询时，COALESCE(amount,0)计算后的值分别为Cat=599999.99、Dog=0、John=5000，排序结果固定为Cat > John > Dog，和数据库类型无关；分页在数据库排序完成后执行，不会出现数据错乱；实体加载完成后Dog的amount自动被设为0，完全符合业务要求。

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