I'm trying to write a query with JPA2 that essentially looks at my entity table of log messages, and returns tuples of <date, count> where each day is accompanied with it's corresponding number of messages. This works fine as a POSTGRES SQL query:
SELECT to_char(date, 'yyyy-MM-dd') as log_date, COUNT(0)
FROM my_table
GROUP BY log_date;
But when translated into JPA2 and Criteria API like this:
Code:
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root<HtEntity> from = criteriaQuery.from(entityClassType);
Expression<String> log_date = criteriaBuilder.function("TO_CHAR",
String.class, from.get(PARAM_DATE), criteriaBuilder.literal("yyyy-MM-dd")
);
criteriaQuery.select(
criteriaBuilder.tuple(
log_date.alias("log_date"),
criteriaBuilder.count(from)));
criteriaQuery.groupBy(log_date); //causes exception
The groupBy breaks my query, and has the error: "org.hibernate.exception.SQLGrammarException: could not extract ResultSet".
However, The groupBy isn't an issue if I just say groupBy the column name "date", which doesn't help me because I want to aggregate by date in the form yyyy-MM-dd, and not the timestamp like in my database for the date column.
What am I doing wrong?
Login
FAQ
Search