1

I wrote the following code to get the count of "ExampleEntity":

cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
root = cq.from(ExampleEntity);
cq.select(cb.count(root));
return entityManager.createQuery(cq).getSingleResult();

Generated sql: Hibernate: select count(exampleen0_.id) as col_0_0_ from EXAMPLEENTITY exampleen0_

But, for performance reasons (using a Oracle 11g database),I need to generate the following sql:

Desired sql: select count(*) as col_0_0_ from EXAMPLEENTITY exampleen0_

It's quite simple to do creating JPQL queries, but I would have to rewrite a lot of existing code for filters.

How can I generate "count(*)" instead of "count(exampleen0_.id)" using CriteriaQuery?

vduk225
  • 31
  • 6

2 Answers2

0

It's extremely hard to force the hand of the SQL generated by Hibernate. Instead, I would consider writing a native query.

Query query = entityManager.createNativeQuery(
        "SELECT COUNT(*) FROM EXAMPLEENTITY", Long.class);
return query.getSingleResult();

Note: Native queries can get messy when using more complex logic, but this particular one complies to the ANSI standard and should run without issue against any commonly used DB without having to worry about any db-specific syntax issues.

Dean Clark
  • 3,770
  • 1
  • 11
  • 26
  • Correct, It's an alternative, like the jpql queries... but as I said in the description I would have to rewrite a lote of existing code for the filtering – vduk225 May 03 '16 at 20:44
0

It works in Oracle...

BigDecimal count = (BigDecimal) manager.createNativeQuery(query).getSingleResult();
Marcel
  • 2,810
  • 2
  • 26
  • 46