|Criteria API + countDistinct + composite primary key [message #540813]
||Thu, 17 June 2010 08:39
| Radek Hodain
Registered: February 2010
we use criteria API from JPA 2.0. We want to perform count with distinct.
In JPQL something like this:
SELECT count distinct (entity) from Entity entity.
With criteria API this:
CriteriaBuilder b = entityManager.getCriteriaBuilder();
CriteriaQuery q = b.createQuery(Long.class);
Root root = q.from(Entity.class);
If we use entity with simple primary key, it works correctly but we have
composite primary key and there is some problem.
We have Entity which is association Entity between Group and User. We can called it
GroupUser. GroupUser has composite primary key composed by primary key of Group and User.
One User has many GroupUser. We try to find count distinct of GroupUser which belongs to one User.
If we perform our query we get this SQL command in console:
FROM (SELECT COUNT(t0.USER_CODE) FROM GROUPUSER t3
LEFT OUTER JOIN USER t1 ON (t1.USER_CODE = t3.USER_CODE), GROUPUSER t0
WHERE EXISTS (SELECT t3.USER_CODE FROM GROUPUSER t3 WHERE (((t0.USER_CODE = t3.USER_CODE) AND (t0.GROUP_CODE = t3.GROUP_CODE)) AND (t3.GROUP_CODE = 7000))) )
This generated SQL is wrong because it founds how many GroupUser have some user.
Do you have any idea where is problem?
Powered by FUDForum
. Page generated in 0.01681 seconds