4 Replies Latest reply on Nov 23, 2005 9:37 AM by Martin Ganserer

    Why does EJB QL between not work?

    kourosh bagheri Newbie

      Hi!,

      could someone please give an example of a properly working query, using the EJB QL with

      between java.util.Date() START and java.util.Date() END
      .

      My problem:
      I woul like to use this, to compute the sales of an year like this:

      public Collection<Order> getAllOrdersOfYear(String year) {
      
      float totalSales = 0.00F;
      String firstDayOfYear = year + "-01-01";
      String lastDayOfYear = year + "-12-31";
      try {
       //select sum(o.total)
       Query query = manager.createQuery("from Order o where o.orderDate between "
       + ":firstDayOfYear and :lastDayOfYear");
       query.setParameter("first", "'" + firstDayOfYear + "'");
       query.setParameter("last", "'" +lastDayOfYear + "'");
      
       return query.getResultList()) ;
      ...


      But it doesn't work!


        • 1. Re: Why does EJB QL between not work?
          Emmanuel Bernard Master

          Hum, I already answered the same question somewhere. Guys try not to crosspost.

          • 2. Re: Why does EJB QL between not work?
            kourosh bagheri Newbie

            Thanks for your response.
            I had to answer the question, because nobody answered to it.
            Sorry.
            I have tried to build date objects with the GregorialCalendar like this:

            Calendar c = new GregorianCalendar()
            c.set(paramYear, 1, 1);
            java.util.Date start = c.getTime();
            c.set(paramYear, 12, 31);
            java.util.Date end = c.getTime();


            and here is my query:
            Query q = manager.createQuery("from Order o where o.orderDate netween start and end");
            q.setParameter("start", start);
            q.setParameter("end", end);
            ...


            but it doesn't work!



            • 3. Re: Why does EJB QL between not work?
              Emmanuel Bernard Master

              q.setParameter("start", start, TemporalType.DATE);

              • 4. Re: Why does EJB QL between not work?
                Martin Ganserer Novice

                Hello,

                I have the same problem. Please take a look at my code:

                Calendar startDate = new GregorianCalendar(2005,1,1);
                Calendar endDate = new GregorianCalendar(2005,1,1);
                
                // Set range of twelve month
                endDate.add(GregorianCalendar.MONTH,12);
                
                System.out.println(endDate.get(Calendar.DATE) + "." + endDate.get(Calendar.MONTH) + "." + endDate.get(Calendar.YEAR));
                
                buf.append("select distinct o from CustomerOrder o join o.lineItems l ");
                buf.append("where (l.wantedDeliveryDate >= :paramStartDate ");
                buf.append("and l.wantedDeliveryDate < :paramEndDate) ");
                buf.append("and o.enteredBy = :paramSalesMan ");
                
                Query query = em.createQuery(buf.toString());
                query.setParameter("paramStartDate",startDate,TemporalType.DATE);
                query.setParameter("paramEndDate",endDate,TemporalType.DATE);
                query.setParameter("paramSalesMan",salesMan);
                


                I can execute the query. But I do not get the proper resultset. What should I do?
                I have already tried to use Date instead of Calendar. And I have even tried the between operator. I always get more records than I expect!

                Thank you!