jpql query without params

Question

I have a SpringBoot 2.6.14 WebApp using JPA 2.2 and Hibernate 5.

now suppose I have a database table like follow:

---

id start_date end_date 1 null 2024-06-14 2 null 2023-02-16

with the follow Entity:

@Getter
@Setter
@Entity
@Table(name = "entita")
@NoArgsConstructor
public class Entita implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Temporal(TemporalType.DATE)
  private Date startDate;
  @Temporal(TemporalType.DATE)
  private Date endDate;
}

now I want to retrieve all the data that meet this criterion: start_date < current_date < end_date

I wrote a ugly query like follow:

@Query("select e from Entity e where current_date >= "
        + " CASE "
        + " WHEN e.start_date is not null THEN e.start_date "
        + " WHEN e.start is null THEN '1970-01-01' " // a date in the past
        + " END"
        + " AND current_date <= "
        + " CASE "
        + " WHEN e.end_date is not null THEN e.end_date "
        + " WHEN e.end_date is null THEN '2999-01-01' " // a date in the future
        + " END")
public List<Entity> findAllActive();

Now is there a way to write it in JPA? Moreover is it possible to wirte a JPA query without parameters to find some? For example:

public List<Entity> findAllBetweenStartDateAndEndDate();

Is something like that possible?

Answer 1

You should whitelist the two endpoint null edge cases using IS NULL as follows:

select e
from Entity e
where (e.start_date < current_date or e.start_date is null) and
      (e.end_date > current_date or e.end_date is null);

According to the above logic, if either of the start_date or end_date be null, then that particular criterion(a) would not apply in the where clause.