[eclipselink-users] Re: Using count() with Criteria API

[Tim McNerney <mcnerney@xxxxxxxxx>]

>
> If you are going to try using Foo_.id, you need to generate the metamodel classes described here:
> http://wiki.eclipse.org/UserGuide/JPA/Using_the_Canonical_Model_Generator_(ELUG)
> m.entity(Foo.class) returns the EntityType, not a generated Foo_ class, and so will not have 'id' or any other Foo attributes.
> This is why Foo_.getSingularAttribute("id",Integer.class)) will work.

Ahhh. I knew that seemed to be too handy to work so easily.

>
> As for the problem with select, you are not posting any exception so I cant be sure, but the query does not look set up correctly. First, when you call:

Sorry. I guess I should have been clearer. I can't even compile as the
type returned by "f" or "f.get("id")" don't match the argument for
"cb.count()". Specifically, Selection rather than Expression. I don't
see any reasonable way to get it to work.

> CriteriaQuery<Foo> c = cb.createQuery(Foo.class);
> you are stating that the query results will return instances of Foo - a query using count will not, and should return Long instance(s).
>
> The following working example is taken from within the EclipseLink tests that uses the metamodel generation:
> //"SELECT COUNT(DISTINCT phone.owner) FROM PhoneNumber phone";
> CriteriaBuilder qb = em.getCriteriaBuilder();
> CriteriaQuery<Long> cq = qb.createQuery(Long.class);
> cq.select(qb.countDistinct(cq.from(getEntityManagerFactory().getMetamodel().entity(PhoneNumber.class)).get(PhoneNumber_.owner)));
>
> A similar test not using the metamodel generation does the same thing, only much more simply:
> //"SELECT COUNT(DISTINCT phone.owner) FROM PhoneNumber phone";
> CriteriaBuilder qb = em.getCriteriaBuilder();
> CriteriaQuery<Long> cq = qb.createQuery(Long.class);
> cq.select(qb.countDistinct(cq.from(PhoneNumber.class).get("owner")));
>
> Hope this helps

Yeah. I saw that. But I couldn't get it to work with my example.
Basically the same problem with a bad type. I'll take another look,
though.

--Tim

>
> Best Regards,
> Chris
>
>
> On Thu, Apr 8, 2010 at 11:28 AM, Tim McNerney <mcnerney@xxxxxxxxx> wrote:
> > I'm having some trouble getting count() working with Criteria API.
> > What I have right now using JPQL is:
> >
> >  SELECT count(e) FROM Foo e
> >
> > What I'm trying is:
> >
> >  CriteriaBuilder cb = em.getCriteriaBuilder();
> >  CriteriaQuery<Foo> c = cb.createQuery(Foo.class);
> >  Root<Foo> f = c.from(Foo.class);
> >  c.select(cb.count(f));
> >
> > and also:
> >
> >  CriteriaBuilder cb = em.getCriteriaBuilder();
> >  CriteriaQuery<Foo> c = cb.createQuery(Foo.class);
> >  Root<Foo> f = c.from(Foo.class);
> >  c.select(cb.count(f.get("id")));
> >
> > Ideally, I'd like to get it working with Metamodel, but I've run into
> > even more problems with that:
> >
> >  CriteriaBuilder cb = em.getCriteriaBuilder();
> >  CriteriaQuery<Foo> c = cb.createQuery(Foo.class);
> >  Root<Foo> f = c.from(Foo.class);
> >  Metamodel m = em.getMetamodel();
> >  EntityType<Foo> Foo_ = m.entity(Foo.class);
> >  c.select(cb.count(f.get(Foo_.id)));
> >
> > as "id" is not resolved. I do get at least the get part to work with:
> >
> >  c.select(cb.count(q.get(Foo_.getSingularAttribute("id",Integer.class))));
> >
> > I still get a failure on select() as it does not resolve to Selection.
> > I'd still like to use the previous entry as I don't like having use a
> > string for the column name.
> >
> > What am I doing wrong with count()? How can I emulate the JPQL query?
> > Why isn't the Metamodel EntityType not including the attributes? How
> > can I get them there so I can avoid using string names to get them?
> >
> > I'm using Eclipselink 2.0.1.
> >
> > --Tim
> >
>
>