JPA criteria: count from multiselect query

Question

I want to implement a table component with pagination. The result in the table is retrieved by a multiselect-query like this:

SELECT DISTINCT t0.userId,
            t0.userName,
            t1.rolleName
FROM userTable t0
LEFT OUTER JOIN roleTable t1 ON t0.userId = t1.fkUser
WHERE(t0.userType = 'normalUser' AND t1.roleType = 'loginRole')

This result I can get via a multiselect-query.

Now for the pagination I have to retrieve the total rowcount at first. Is there anybody who can define a criteriaquery for one of this sql? I failed because a subquery does not support multiselects and I do not know how to get this distinct into a count statement.

SELECT COUNT(*) FROM
(
   SELECT DISTINCT t0.userId,
                   t0.userName,
                   t1.rolleName
   FROM userTable t0
   LEFT OUTER JOIN roleTable t1 ON t0.userId = t1.fkUser
   WHERE(t0.userType = 'normalUser' AND t1.roleType = 'loginRole')
)

or

SELECT COUNT(DISTINCT t0.userId || t0.userName || t1.rolleName)
FROM userTable t0
LEFT OUTER JOIN roleTable t1 ON t0.userId = t1.fkUser
WHERE(t0.userType = 'normalUser' AND t1.roleType = 'loginRole')

Thanks in advance!

Btw. I am using OpenJpa on a WebSphere AppServer

This is not a Criteria API question. I am removing that tag. It looks like native SQL, no? — carbontax, Jul 08 '12 at 01:25
@carbontax Thanks for reading my question, but if you read it carefully you can realize that I ask for an criteria query for one of these native sql. So the criteriaApi tag is correct. — Steven Rudolf, Jul 09 '12 at 06:00
What about the JPQL `SELECT COUNT(DISTINCT CONCAT(t0.userId, t0.userName, t1.rolleName) FROM User t0 LEFT OUTER JOIN t0.roles t1 WHERE(t0.userType = 'normalUser' AND t1.roleType = 'loginRole')` ? — Christian Beikov, Mar 03 '13 at 16:13
@ChristianBeikov Thanks for your comment. Unfortunately I am restricted to use the criteria api because in my productive query there are many variable selection parameters which I have to check for null or default values first. — Steven Rudolf, Mar 04 '13 at 06:48

score 0 · Answer 1 · answered Mar 04 '13 at 09:02

The following is not tested but should work:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> query = builder.createQuery(Long.class);
Root<User> t0 = query.from(User.class);
Join<User, Role> t1 = t0.join("roles", JoinType.LEFT); 
query.select(builder.concat(t0.get(User_.userId), builder.concat(t0.get(User_.userName), t1.get(Role_.rolleName))).distinct(true);
query.where(cb.equal(t0.get("userType"), "normalUser"), cb.equal(t1.get("roleType"), "loginRole"));
TypedQuery<Long> tq = em.createQuery(query);

This doesn't work due to a Hibernate bug (https://hibernate.atlassian.net/browse/HHH-9182). I found my solution doing this (which is basically the same, but only works with columns from 1 table): http://stackoverflow.com/a/25549631/1776630 — lopezvit, Aug 26 '16 at 10:48

score 0 · Answer 2 · answered Jul 18 '20 at 03:47

Due to known issue https://jira.spring.io/browse/DATAJPA-1532 Multiselect does not work with repo.findall method. I handled this by autowiring entity manager to service class.

@Autowired
    EntityManager entityManager;
    public List<?> getResults() throws ParseException 
    {
        //ModelSpecification modelSpecification = new ModelSpecification();
        CriteriaQuery<DAO> query = modelSpecification.getSpecQuery();
        TypedQuery<DAO> typedQuery = entityManager.createQuery(query);
          List<?> resultList = typedQuery.getResultList();
        
        //List<DAO> allData  = entityManager.createQuery(query).getResultList();
        return resultList;
    }

You can find working code here https://github.com/bsridharpatnaik/CriteriaMultiselectGroupBy

JPA criteria: count from multiselect query

2 Answers2