0

i'm dealing with the following scenario:

The Entity Class:

@NamedQuery(
    name = "Table.getSum",
    query = "SELECT SUM(s.price) FROM Table s WHERE 
        (s.openingDate >= :openingDate AND s.closingDate <= :closingDate)"
)

The EJB:

    Calendar openingDate = new GregorianCalendar(year, 1, 1, 0, 0, 0);
    Calendar closingDate = new GregorianCalendar(year, 12, 31, 23, 59, 59);

    BigDecimal salePriceSum = em.createNamedQuery("Table.getSum", BigDecimal.class)
            .setParameter("openingDate", openingDate)
            .setParameter("closingDate", closingDate)
            .getSingleResult();

The native SQL Query:

SELECT SUM(price) FROM Table WHERE openingDate >= 'YYYY-01-01T00:00:00' AND closingDate <= 'YYYY-12-31T23:59:59'

But the two sums are of by a significant amount. That drives me crazy. How can that be?

I'm using hibernate 4.3.7 with wildfly 8.2.0 and the current mariadb on centos 7.

Thank you very much for any suggestion.

user4299567
  • 1
  • You can start debugging this issue by printing out the generated SQL, and comparing it to native SQL – Predrag Maric Nov 27 '14 at 10:25
  • the sum returned by the jpql query is much larger than the sum returned by the sql query. Yes, the latter is a very good suggestion. I figure out, how to do so end report the results. – user4299567 Nov 27 '14 at 10:27
  • The problem is, that the timestamps in the resulting sql query are wrong. Therefore, the sum of something else than intended is calculated. I'm still figuring out why they are wrong and what to do about it. – user4299567 Nov 27 '14 at 11:48

1 Answers1

0

month parameter in the constructor for GregorianCalendar is zero-based, that probably is messing up your results. From the docs:

month - the value used to set the MONTH calendar field in the calendar. Month value is 0-based. e.g., 0 for January.

Predrag Maric
  • 23,938
  • 5
  • 52
  • 68