JPQL BETWEEN Date Interval

Question

I tried to get all MainIntervals in a certain date interval. But I get always null.

Here is the JPA Entity

@Table(name="MAIN_INTERVAL")
 @NamedQueries({
 @NamedQuery(name = MainInterval.FIND_ALL, query = " select m from MainInterval m"),
 @NamedQuery(name = MainInterval.FIND_BETWEEN, 
    query = "select m from MainInterval m where m.mainIntervalStart 
    BETWEEN :startDate AND :endDate 
    AND m.mainIntervalEnd BETWEEN :startDate AND :endDate"
   )  }) 


public class MainInterval implements Serializable {

public static final String FIND_ALL = "MainInterval.findAll";
 public static final String FIND_BETWEEN = "MainInterval.findBetween";

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="MAIN_INTERVAL_ID")
private Long id;

@Column(name="MAIN_INTERVAL_START")
@Temporal(javax.persistence.TemporalType.DATE)
private Date mainIntervalStart;

@Column(name="MAIN_INTERVAL_END")
@Temporal(javax.persistence.TemporalType.DATE)
private Date mainIntervalEnd; }

And in the EJB SessionBeans I have the method:

public List<MainInterval> findMainIntervalsBetween(Date startDate, Date endDate){
      List<MainInterval> resultList = em.createNamedQuery(MainInterval.FIND_BETWEEN, MainInterval.class)
              .setParameter("startDate", startDate, TemporalType.DATE).setParameter("endDate", endDate, TemporalType.DATE).getResultList();
      return resultList;
}

But when I call it from JSF with CDI the resultList is always null. Although I have some MainIntervals that meet the conditions between startDate and endDate.

I would be very grateful to every answer or links for tutorial. Best regards!

Answer 1

Your query is:

select m from MainInterval m where m.mainIntervalStart 
BETWEEN :startDate AND :endDate 
AND m.mainIntervalEnd BETWEEN :startDate AND :endDate

Your first row's interval is [01.05.2012, 31.05.2012], and your second row's interval is [01.05.2012, 01.08.2012]. The arguments of the query are 10.05.2012, 20.05.2012.

So, for your first row:

m.mainIntervalStart BETWEEN :startDate AND :endDate 
01.05.2012          BETWEEN 10.05.2012 AND 20.05.2012 : false

So the first row is not returned.

For your second row:

m.mainIntervalStart BETWEEN :startDate AND :endDate 
01.05.2012          BETWEEN 10.05.2012 AND 20.05.2012 : false

So the second row isn't returned either.

Everything looks normal to me.