IS NOT NULL always return true JPA query

Question

I have Two Entity User and Account. Both have one to one mapping. Here is the Entity Classes : User:

@Entity
@Table(name = 'docutools_users')
public class DocutoolsUser {
   @Id
   @Type(type = "pg-uuid")
   UUID id = UUID.randomUUID();

   @OneToOne(mappedBy = "user", cascade = ALL)
   Account account;}

Account

@Entity
@Table(name = "accounts")
public class Account {

  @Id
  @Type(type = "pg-uuid")
  private UUID id = UUID.randomUUID();
  @Column(nullable = false)
  private LocalDate activated = LocalDate.now();
  @OneToOne
  private DocutoolsUser user;
}

Query

@Query("SELECT u FROM DocutoolsUser u WHERE u.account IS NOT NULL")
Page<DocutoolsUser> findByAccountNotNull()

I am using JPA repositery. The expression u.account IS NOT NULL always return true even there is no account in user.

Thanks

Exact Query is here

 @Query("""SELECT u FROM DocutoolsUser u WHERE u.organisation = :org AND UPPER(CONCAT(u.name.firstName, ' ', u.name.lastName)) LIKE :search AND ((CASE WHEN ( (u.id = u.organisation.owner.id AND u.account IS NULL) OR ( u.account IS NOT NULL AND (u.account.subscription.until IS NULL or u.account.subscription.until > :currentDate)) ) THEN true ELSE false END) IS :isLicensed )""")
Page<DocutoolsUser> findByOrganisationAndLicense(@Param('org') Organisation organisation, @Param('search') String search, @Param('currentDate') LocalDate currentDate, @Param('isLicensed') Boolean isLicensed, Pageable pageable)

Thanks, But My whole query has some complex logic so I can not use this one, here is the whole query — Vikram Singh Shekhawat, Feb 23 '18 at 10:50
Hi Sighn, I think that you will need to use `LEFT JOIN FETCH` to make sure that relationships are properly loaded. Otherwise Hibernate may keep intermediate proxy objects to lazy load `Account` on demand. Have a look at: https://stackoverflow.com/questions/43879474/jpa-join-fetch-results-to-null-on-empty-many-side. — Anthony Accioly, Feb 23 '18 at 11:00
Thanks Anthony, LEFT JOIN FETCH is throwing error, but only LEFT JOIN to Account works for me. — Vikram Singh Shekhawat, Feb 25 '18 at 17:54

score 10 · Accepted Answer · answered Feb 23 '18 at 10:55

you can do this without @Query using JpaRepository and IsNotNull

@Repository
public interface DocutoolsUserRepository  extends JpaRepository<DocutoolsUser, Long> {

// where Account not null
 public List<DocutoolsUser> findByAccountIsNotNull();

}

for more informations : https://docs.spring.io/spring-data/jpa/docs/1.5.0.RELEASE/reference/html/jpa.repositories.html

for the whole query you can combine many jpaRepository services

IS NOT NULL always return true JPA query

1 Answers1