I am not getting Expected results using spring Data JPA

Question

I have a repository: MEOfferRepository

@Repository
public interface MEOfferRepository extends JpaRepository<MEOfferDetailEntity, Long> {

  @Query("select carrierId, carrierName from MEOfferDetailEntity entity where "
      + "(:countryCode is null or entity.carrierGeoAreaCode =:countryCode)")
  CompletableFuture<Set<MEOfferDetail>> findCarrierByCountryCode(
      final @Param("countryCode") String countryCode);

}

where MEOfferDetailEntity is entity and MEOfferDetail is projection of that entity.

this is working fine if I provide a valid countryCode and I am getting the expected result out of this query, the issue is when I pass country code as null.

I am expecting :countryCode is null to run as null is null and the query would fetch the result without the where clause.

This query is giving expected result in MySql workbench but in Spring boot JPA I am getting an empty set as a response which should not be the case.

Answer 1

I had the same problem once, then I changed the syntax and it worked.

@Repository
public interface MEOfferRepository extends JpaRepository<MEOfferDetailEntity, Long> {

  @Query("select carrierId, carrierName from MEOfferDetailEntity entity where " +
      "entity.carrierGeoAreaCode = coalesce(:countryCode, entity.carrierGeoAreaCode)")
  CompletableFuture<Set<MEOfferDetail>> findCarrierByCountryCode(
      final @Param("countryCode") String countryCode);

}