4 Replies Latest reply on Jun 1, 2009 10:58 AM by rafael ruiz

    EntityQuery between filter dates.

    rafael ruiz Newbie

      I would like filter by date range in EntityQuery.


      Something like this:


      migsm.procdatetime between   #{component1.dateIni} and #{component1.dateEnd} in the RESTRICTIONS variable


      The class is as follows:


      @Name(component1)
      public class Component1 extends EntityQuery<Migsm> {
             



              private static final String EJBQL = select migsm from Migsm migsm;


              private static final String[] RESTRICTIONS = {
                              lower(migsm.messtype) like concat(lower(#{component1.migsm.messtype}),'%'),                  
                              migsm.procdatetime between #{component1.fechaInicio} and #{component1.fechaFin},
                              };
             
              private Migsm migsm = new Migsm();


              private Date dateIni;
             
              private Date fateEnd;


              public Component1() {
                     
                      setEjbql(EJBQL);
                      setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));          
                      setMaxResults(25);
              }


              public Migsm getMigsm() {
                      return migsm;
              }


              public Date getDateIni() {
                      return dateIni;
              }


              public void setDateIni(Date dateIni) {
                      this.dateIni = dateIni;
              }


              public Date getFateEnd() {
                      return fateEnd;
              }


              public void setFateEnd(Date fateEnd) {
                      this.fateEnd = fateEnd;
              }



      }



      Which is the best way to filter dates in this class



      At the present, the method parseEjbql of class  Query throw new IllegalArgumentException ( there should be exactly one value binding in a restriction: + restriction);


      Moreover I don't know if this sentence - migsm.procdatetime between #{component1.fechaInicio} and #{component1.fechaFin}-            
      work well if one of its values have null value.





      Thank you very much for your suggestions       
      and sorry for my poor knowledge in English and seam.

        • 1. Re: EntityQuery between filter dates.
          Zoltan Tiringer Newbie

          Restrictions support only one binding per restriction. Just separate 'between' into 2 different restriction (less than maxDate, more than MinDate). This way besides the 'between' effect, you will handle even open-ended queries if user specifies only one of the filters.

          • 2. Re: EntityQuery between filter dates.
            rafael ruiz Newbie

            Thank you very much for your answer.



            The problem with this solution is that I can't control the
            execution plan for the select by the database - In this case oracle -.


            This case fails with this solution:



                    "msgim.procdatetime >= #{msgimList.date1}",
                    "msgim.procdatetime <= #{msgimList.date2}",
                    "msgim.regdatetime >= #{msgimList.datea3}",
                    "msgim.regdatetime <= #{msgimList.datea4}",


            The result is the following:


                    select ... where msgim.procdatetime >= msgimList.date1
                                    and  (msgim.procdatetime >= msgimList.date2
                                            and msgim.regdatetime >= msgimList.datea3)
                                    and msgim.regdatetime <= msgimList.datea4




            Thanks again

            • 3. Re: EntityQuery between filter dates.
              rafael ruiz Newbie

              What happened is that the joins between the filter dates haven't been done as I want

              • 4. Re: EntityQuery between filter dates.
                rafael ruiz Newbie

                Sorry for my English.
                I explain better my previous post.
                What happens is that the joins between the filter dates is not as I would like


                Thanks