Query database with JPA for null values

Question

The value in database can be sometimes NULL and sometimes not. How can I retrieve it? This is my try which makes me suprised:

@Repository
public interface AddressRepo extends JpaRepository<Address, Long>{

    @Query("select count(a) > 0 from Address a where a.street = :street")
    boolean testtest(@Param("street") String street);
}

test OK:

// given
address = new Address("WIELKA WARSZAAAWA", "Bokserska", "xxx", "50-500");
// when
addressRepo.save(address);
// then
assertTrue(addressRepo.testtest("Bokserska")); // OK

test fails:

// given
address = new Address("WIELKA WARSZAAAWA", null, "xxx", "50-500");
// when
addressRepo.save(address);
// then
assertTrue(addressRepo.testtest(null)); // cuz false!

but then normal Queries when street is not null won't work! — stakowerflol
– stakowerflol, Commented Dec 8, 2018 at 17:53
You're allowed to use more than a single query. Use the right one depending on what you want to query. — JB Nizet
– JB Nizet, Commented Dec 8, 2018 at 17:58
this will be very uncomfortable to have 12 ifs for this example... if(street ==null) {addressRepo.testtest2} ... if(city == null) {..} if(street == null && city == null) {...} etc. — stakowerflol
– stakowerflol, Commented Dec 8, 2018 at 18:35
That's why the criteria API, or QueryDSL, exist: to dynamically create queries. — JB Nizet
– JB Nizet, Commented Dec 8, 2018 at 18:39

Dherik · Accepted Answer · 2018-12-20 13:05:52Z

2

The JPQL is not able to translate this statement:

WHERE a.street = null

To this SQL:

WHERE a.street IS null

So, you need to create a new @Query:

select count(a) > 0 from Address a where a.street IS NULL

Mount manually the JPQL string or use Criteria to create a dynamic query are also good options.

answered Dec 20, 2018 at 13:05

Dherik

19.4k15 gold badges132 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query database with JPA for null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related