[FIXED] Using Hibernate @Where annotation to show non null deleted timestamp

Issue

Hello I am using Spring Boot and JPA. My entity has a deleted_at timestamp in the DB. How do I correctly use the @Where annotation to show only entities that the deleted_at column is NOT NULL.

I tried using the @Where annotation but when it equals null I get an empty array as a response. I have tried

clause = "deleted_at=null"
clause = "deleted_at=NULL"
clause = "deleted_at='NULL'"

None of them worked

@Data
@Entity
@Table(name="examples")
@NamedQuery(name="Example.findAll", query="SELECT l FROM Example l")
@SQLDelete(sql = "UPDATE examples SET deleted_at = CURRENT_TIMESTAMP() WHERE id = ?")
@Where(clause = "deleted_at=null")
public class Example {
  @Id
  @GeneratedValue(strategy= GenerationType.IDENTITY)
  Long id;
  String name;
  Timestamp deletedAt;
}

Solution

For anyone looking into this in the future, the solution is to use

@Where(clause = "deleted_at IS NULL")

Answered By - Karlo Gašpar
Answer Checked By - Senaida (JavaFixing Volunteer)