How to convert Integer param which can be 'null' to '0' when selecting data with Spring Data JPA

Question

Assume we have entity Animal. There are animals in DB with 'amount' = null, it's a valid case to save animal without the 'amount'. Is there a way to convert field 'amount' to 0 in case it's null in query?

• The simplest workaround seems to convert amount null to '0' earlier when saving, but it's not allowed.
• As another workaround we can do this mapping to '0' after fetching it from the repository. When sorting by amount in asc order, null values will be at the beginning, in desc order they will be at the end. And after converting to '0' everything will be at the right place. But it seems that can cause problems with pagination in future

What is the proper way to do it in Query?

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;

}

---

UPDATE

Also important to mention. The solution I suggested with replacing nulls with zero is incorrect, because of the different null ordering in Postgresql and HSQLDB. But it will work in tests, if you're using 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

The JPA supports the COALESCE function. Thus you can set up the desired value via this function.

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

The code should look like this:

@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;
}
}

And the repository:

@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);


}

Also I have prepared the test:

@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);
}

}

You can check the commit: https://gitlab.com/chlupnoha/meth/-/commit/76abbc67c33b2369231ee89e0946cffda0460ec9 - it is experiment project.