如何使用 Spring 数据 JPA 选择数据时将可以是"null"的整数参数转换为"0"

Question

假设我们有实体Animal。DB中有'amount的动物' = null，在没有'金额的情况下保存动物是有效的＇．是否有一种方法来转换字段'amount' to0如果是null在查询?

• 最简单的解决方法似乎是将金额null转换为"0"，但不允许。
• 作为另一个解决方案，我们可以在抓取后将此映射到'0'它来自存储库。当按数量按asc顺序排序时，空值将在开头，按desc顺序它们将在末尾。之后,转换为"0"后，所有内容都将位于正确的位置。但这似乎会导致未来的分页问题

在查询中正确的方法是什么?

Spring Data Jpa 2.2.9。发布，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的解决方案是不正确的，因为Postgresql和HSQLDB中的空排序不同。但是，如果您使用的是HSQLDB，它将在测试中工作。

Animal entities in DB test sample: [
Animal(name=Cat, amount=599999.99),
Animal(name=Dog, amount=null),
Animal(name=John, amount=5000)
]

Hsqldb amount desc query result:
[
Animal(name=Cat, amount=599999.99),
Animal(name=John, amount=5000),
Animal(name=Dog, amount=null)
]
Postgresql amount desc query result:
[
Animal(name=Dog, amount=null)
Animal(name=Cat, amount=599999.99),
Animal(name=John, amount=5000)
]

Answer 1

JPA支持COALESCE函数。因此，您可以通过此函数设置所需的值。

SELECT COALESCE(amount,0) AS desiredAmount FROM AnimalEntity animal

代码应该是这样的:

@Entity
@Table(name = "animal")
public class AnimalEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private Integer amount;
public AnimalEntity() {
}
public AnimalEntity(Integer amount, String name) {
this.amount = amount;
this.name = name;
}
public Long getId() {
return id;
}
public Integer getAmount() {
return amount;
}
public void setAmount(Integer amount) {
this.amount = amount;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}

和存储库:

@Repository
public interface AnimalRepository extends JpaRepository<AnimalEntity, Long> {
@Query(
value = "SELECT animal.id AS id, COALESCE(animal.amount,0) AS amount, UPPER(animal.name) AS name FROM animal animal WHERE animal.name = :name",
nativeQuery = true)
Page<AnimalEntity> findAllLikeNameAndOwnerSorted(String name, Pageable pageable);

}

我还准备了测试:

@SpringBootTest
class AnimalRepositoryTest {
@Autowired
private AnimalRepository animalRepository;
@Test
void findAllLikeNameAndOwnerSorted() {
AnimalEntity animalEntity = new AnimalEntity(null, "dog");
animalRepository.save(animalEntity);
AnimalEntity animalEntity2 = new AnimalEntity(1, "CAT");
animalRepository.save(animalEntity2);
System.out.println(animalEntity2.getId());
Pageable sortedByName = PageRequest.of(0, 3, Sort.by("id"));
Page<AnimalEntity> animals = animalRepository.findAllLikeNameAndOwnerSorted("dog", sortedByName);
animals.forEach(System.out::println);
}
}

你可以检查提交:https://gitlab.com/chlupnoha/meth/-/commit/76abbc67c33b2369231ee89e0946cffda0460ec9 -这是一个实验项目。