Criteria API and JPA2 GroupBy issue with date

Question

I am writing a query to find dates and their counts for that particular date from my entity table, and running into an issue with the groupBy statement.

Here is my criteria API calls:

    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(Constants.PARAM_DATE), criteriaBuilder.literal("yyyy-MM-dd")
    );

    //create the select statement
    criteriaQuery.select(
            criteriaBuilder.tuple(
                    log_date.alias("log_date"),
                    criteriaBuilder.count(from)));

    criteriaQuery.groupBy(log_date); //ISSUE HERE!!!

    TypedQuery<Tuple> query = em.createQuery(criteriaQuery);
    List<Tuple> results = query.getResultList();

Without trying to cast the date column with to_char and using just the date column it works, but obviously is not aggregated as I want to. I want just the date in yyyy-MM-dd and not the entire timestamp, and I can only see doing this by using groupBy which causes an error

"org.hibernate.exception.SQLGrammarException: could not extract ResultSet"

And there is the query string that is build found from my debugger:

select function('TO_CHAR', generatedAlias0.date, :param0), count(generatedAlias0) from LogMessageEntity as generatedAlias0 group by function('TO_CHAR', generatedAlias0.date, :param1)

Please let me know what I am doing wrong. The corresponding Postgres SQL query should be working for this, as I can test it and see that it would work.

Answer 1

CriteriaApi has a good way of providing me with a headache, but I did end up finding a solution to this a while back, providing the answer for future curious folks:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<MyEntity> from = criteriaQuery.from(classType);

criteriaQuery.multiselect(
    from.get("date").as(java.sql.Date.class),
    criteriaBuilder.count(from)
);

/*Add order by and group by clause*/
criteriaQuery.orderBy(criteriaBuilder.desc(from.get("date").as(java.sql.Date.class)));
criteriaQuery.groupBy(from.get("date").as(java.sql.Date.class));

TypedQuery<Object[]> query = em.createQuery(criteriaQuery);
List<Object[]> results = query.getResultList();

You will notice that I am casting the column projecting in my multi-select statement, and my groupBy as a java.sql.Date type. CriteriaApi has a strict type-safe format such that you cannot mix types for the groupBy and the select statement if you are aggregating on that specific column. My issue was that I was selecting on the date column with a function call, and also trying to apply that to the groupBy.

While the generated query statement works via command line, it does not work for the CriteriaApi. The reason it cannot generate a ResultSet is due to it assuming the projection and aggregation in the groupBy are not the same type (I think) and therefore, if you need to use a function on a column in your select statement and also aggregate on that column you need to either think of a way you can avoid using the function (which is what I did), or generate a native query with the Hibernate EntityManager object you have.

It is unfortunate, but I believe CriteriaApi has a number of limitations with this being one of them. It should probably be submit to their team as a bug, but I'm not sure where to do this.