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

    Why does EJB QL between not work?

    kourosh bagheri Newbie


      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.
            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


                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
                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());

                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!