jpa criteria group by aggregates values

Question

I need a query to convert time (milisec) to an hourly distribution.

my table has a colum called timestamp : value in milisec. to group on hours I need to do the folowing devide by 1000 -> sec devide by 3600 -> hours -> mod 24 -> get the hour of day the records was created.

timestamp is a long:

    public static volatile SingularAttribute<VersionJpa, Long> timestamp;

I can then display the results in a in day distribution. However, this doesn't work, the group by not working and I get the full list not grouped and not counted.

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<VersionJpa> root = cq.from(VersionJpa.class);
    cq.where(p);
    Expression<Integer> group = cb.mod(cb.toInteger(cb.quot(root.get(VersionJpa_.timestamp), 3600000)),24);
    cq.multiselect(group, cb.count(root));
    cq.groupBy(group);
    TypedQuery<Tuple> tq = em.createQuery(cq);
    return tq.getResultList();

this SQL (mysql) generate the right results. is there equivalent floor in JPA? Tried as.(Integer) || toInteger()

    select mod(floor(stamp/3600000), 24) , count(*) from versions group by mod(floor(stamp/3600000), 24);

I found that during unit test (on Derby) this runs well, but in more production like setup (MySQL) it doesn't cast the inner most aggregate to integer, so the group is done on a floating values and not on 24 (0, 1, .. 23) potential values (return thousand of groups).

    Expression<Integer> hours = cb.mod(cb.quot(root.get(VersionJpa_.timestamp).as(Integer.class), 3600000).as(Integer.class),24).as(Integer.class);
    cq.multiselect(hours, cb.count(root));
    cq.groupBy(hours);

adding PostgreSQL support and testing lead to yet another error which enforce my thinking that my query is wrong:

    RuntimeException Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: column "versions.stamp" must appear in the GROUP BY clause or be used in an aggregate function Position: 13 Error Code: 0 Call: SELECT MOD((STAMP / ?), ?), COUNT(ID) FROM VERSIONS GROUP BY MOD((STAMP / ?), ?) LIMIT ? OFFSET ? bind => [6 parameters bound] Query: TupleQuery(referenceClass=VersionJpa sql="SELECT MOD((STAMP / ?), ?), COUNT(ID) FROM VERSIONS GROUP BY MOD((STAMP / ?), ?) LIMIT ? OFFSET ?")

any help is appreciated.

score 2 · Answer 1 · answered Aug 07 '13 at 13:13

2

You can use the function() method to call any database function.

See, http://java-persistence-performance.blogspot.com/2012/05/jpql-vs-sql-have-both-with-eclipselink.html

Postgres seems to be more complaining about use a function in the group by, in JPQL you could try,

Select mod((v.timestamp / 3600000), 24) h, count(v) from Version v group by h

answered Aug 07 '13 at 13:13

James

17,965
11
91
146

thank you, I did try it with "floor" and it didn't work. I need to work with criteria because I add more filtering/select more dynamically. is it an eclipse bug or I just use wrong syntax? – Gadi Aug 08 '13 at 08:38

score 0 · Answer 2 · answered Aug 09 '13 at 18:25

for anyone ever going to have this problem, I found a workaround based on database columns and not complicated queries. a reminder, the issue was the different vendor support is not guaranteed. it works on Derby but failed on MySQL.

I created a new column per each metric I am interested in:

year
month
day-of-week
day-of-month
hour-of-day (24)

and then populated it with a calendar initialized with localize settings and system time.

    Calendar now = Calendar.getInstance();
    now.setTimeInMillis(timestamp);
    this.year = now.get(Calendar.YEAR);
    logger.trace("Year : [{}]", year);
    this.month = now.get(Calendar.MONTH);
    logger.trace("Month : [{}]", month);
    this.weekOfYear = now.get(Calendar.WEEK_OF_YEAR);
    logger.trace("Week of Year : [{}]", weekOfYear);
    this.dayOfMonth = now.get(Calendar.DAY_OF_MONTH);
    logger.trace("Day of Month : [{}]", dayOfMonth);
    this.dayOfWeek = now.get(Calendar.DAY_OF_WEEK);
    logger.trace("Day of Week : [{}]", dayOfWeek);
    this.hourOfDay = now.get(Calendar.HOUR_OF_DAY);
    logger.trace("Hour of Day : [{}]", hourOfDay);

hope this helps someone. cheers.

jpa criteria group by aggregates values

2 Answers2