Eclipse Community Forums: EclipseLink » Criteria API + countDistinct + composite primary key

Help

Home

Home » Eclipse Projects » EclipseLink » Criteria API + countDistinct + composite primary key

Show: Today's Messages :: Show Polls :: Message Navigator

Criteria API + countDistinct + composite primary key [message #540813]

Thu, 17 June 2010 12:39

Radek Hodain

Messages: 16
Registered: February 2010

Junior Member

Hi,

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);
root.alias("entity");
q.select(b.countDistinct(root));
entityManager.createQuery(q).getSimpleResult();

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?

thx.

Report message to a moderator

Re: Criteria API + countDistinct + composite primary key [message #540917 is a reply to message #540813]

Thu, 17 June 2010 15:38

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

You SQL seems to be for a much more complex query, could you please include the code for the query, and the full SQL. Your SQL also starts with FROM??

Why do you need to use a distinct, does it work correctly if you remove the distinct?

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: Criteria API + countDistinct + composite primary key [message #541109 is a reply to message #540917]

Fri, 18 June 2010 11:54

Radek Hodain

Messages: 16
Registered: February 2010

Junior Member

Hi,

here is my code:

CriteriaBuilder b = manager.getCriteriaBuilder();
CriteriaQuery<Long> q = b.createQuery(Long.class);
Root<UserGroupAsoc> identificationVariable = q.from(UserGroupAsoc.class);
identificationVariable.join("group", JoinType.LEFT);
q.select(b.countDistinct(identificationVariable));
q.where(b.equal(identificationVariable.get("user"), user));
TypedQuery<Long> createQuery = manager.createQuery(q);
Long count = createQuery.getSingleResult();

SQL:

SELECT COUNT(t0.user_code) FROM GROUPUSER t2 LEFT OUTER JOIN GROUP t1 ON (t1.group_code = t2.group_code), GROUPUSER t0 WHERE EXISTS (SELECT t2.user_code FROM GROUPUSER t2 WHERE (((t0.user_code = t2.user_code) AND (t0.group_code = t2.group_code)) AND (t2.user_code = 1)))

If I use the method count, it works correct.

I tried to remove identificationVariable.join("group", JoinType.LEFT); and the result was similar with EXISTS part but the result of the SQL command was correct.

SQL without join:
SELECT COUNT(t0.user_code) FROM GROUPUSER t0 WHERE EXISTS (SELECT t1.user_code FROM GROUPUSER t1 WHERE (((t0.user_code = t1.user_code) AND (t0.group_code = t1.group_code)) AND (t1.user_code = 1))) .

We need distinct because we use left outer joins.

Report message to a moderator

Re: Criteria API + countDistinct + composite primary key [message #541732 is a reply to message #541109]

Tue, 22 June 2010 11:12

Radek Hodain

Messages: 16
Registered: February 2010

Junior Member

I haven't written yet that I use eclipselink the version 2.1.0-RC4.2 with Oracle database.

RH

Report message to a moderator

Re: Criteria API + countDistinct + composite primary key [message #541842 is a reply to message #541109]

Tue, 22 June 2010 17:32

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

The issue is that COUNT can only operate on a single field, so DISTINCT cannot work with composite primary keys. What EclipseLink does, is to use a EXISTS sub-selects instead of the distinct.

The SQL seems correct without the join, the join seems to be confusing things. Do you need the join, you don't seem to be using it?

The join issue seems to be a bug, please log the bug and vote for it.

As a workaround either avoid the join, or avoid the distinct, or use native SQL.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Previous Topic:	EclipseLink, JTA, GlassFish Embedded - No transaction is currently active exception
Next Topic:	IN_OUT Parameters not working for NamedStoredProcedureQuery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 24 15:51:36 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter