Re: How to use aggregate functions with JPA

[Ricardo Nascimento]

Hello,

It's not object but tables, JPA return a list of tables :

[id,name,count]
[id,name,count]
...

On 25 nov, 14:53, sun <goo...@suncom.de> wrote:
> Hi, I am new to JPA and Play and if this is a plain JPA question
> excuse me please and send me away, I am unsure about this.
>
> I want to know how many Ads are in a Category and try to use this
> query:
>
>     System.out.println("noOfCats: " +
> JPA.em().createNativeQuery("select count(id) from
> MainCategory").getSingleResult());
>     System.out.println("noOfAds: " +
> JPA.em().createNativeQuery("select count(id) from
> Ad").getSingleResult());
>     Query query = JPA.em().createNativeQuery("select cat.id, cat.name,
> count(ad.id) from MainCategory as cat "
>         + "join Ad as ad group by cat.id, cat.name");
>     List res = query.getResultList();
>     System.out.println("size:" + res.size());
>     for(int i=0; i<res.size(); i++) {
>       System.out.println(res.get(0));
>     }
>
> This is written to the console:
>
> 15:59:37,214 DEBUG ~ select count(id) from MainCategory
> noOfCats: 7
> 15:59:37,214 DEBUG ~ select count(id) from Ad
> noOfAds: 1
> 15:59:37,214 DEBUG ~ select cat.id, cat.name, count(ad.id) from
> MainCategory as
> cat join Ad as ad group by cat.id, cat.name
> size:7
> [Ljava.lang.Object;@1cf8595
> [Ljava.lang.Object;@1cf8595
> [Ljava.lang.Object;@1cf8595
> [Ljava.lang.Object;@1cf8595
> [Ljava.lang.Object;@1cf8595
> [Ljava.lang.Object;@1cf8595
> [Ljava.lang.Object;@1cf8595
>
> What I get are 7 results, one for each category, seems correct. The
> problem is I get Objects, I do not get Integers (id and count(id) and
> String (name)) but plain Objects - I have no idea what to do with
> them.
>
> I am using plain old JDBC now for my query and it works fine, but I
> have the feeling it should be possible to query aggregate functions
> with JPA too.

[dirk]

Instead of doing this:

JPA.em().createNativeQuery("select count(id) from MainCategory").getSingleResult());

Do this:
MainCategory.count();

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.

[Ricardo Nascimento]

By table i mean Object tab[], example :

for(int i=0; i<res.size(); i++) {

Object tab[] = res.get(i);

System.out.println("id : "+tab[0]);
System.out.println("name : "+tab[1]);
System.out.println("count : "+tab[2]);
}

On 25 nov, 15:41, sun <goo...@suncom.de> wrote:
> What do you mean by table? I do not understand how to make use of the
> object returned. getClass() on the returned object returns object. I
> tried to cast it to (almost) all classes I know of in Java but get
> only ClassCastExceptions. Can you probably point me to some place how
> to work with list of tables or tell what class exactly that is?

[The Dave]

When you issue this query:

select cat.id, cat.name, count(ad.id) from MainCategory as cat
join Ad as ad group by cat.id, cat.name

JPA is returning an object of type: Object[]. This is an array, whose
indexes refer to the three columns you requested in your query. In
many ways this is inconvenient, however, there are a couple of other
more useful ways you could get the same information:

1) Use the 'new map' statement to get the query results in a map:

select new map ( cat.id as categoryId, cat.name as categoryName,
count(distinct ad.id) as adCount ) from MainCategory cat join Ad ad
group by cat.id, cat.name

Note that to use this query, you should call
JPA.em().createQuery("select new map( ..... "), since this is not a
native query.

2) Create a "view" object, which holds the three pieces of info you
want:

view class:
package models.category;

public class CategoryStats {
public Long categoryId;
public String categoryName;
public Long adCount;

// default constructor, just in case we want to instantiate this
without parameters
public CategoryStats() {}
public CategoryStats( Number categoryId, String categoryName,
Number adCount ) {
this.categoryId = categoryId.longValue();
this.categoryName = categoryName;
this.adCount = adCount.longValue();
}
}

Query code:

select new models.category.CategoryStats( cat.id, cat.name,
count(distinct ad.id) ) from MainCategory cat join Ad ad group by
cat.id, cat.name

Again, just use JPA.em().createQuery("select new
models.category.CategoryStats ..."); to run the query. The result
list of this query will be a list of CategoryStats objects. There are
a few important things to note about this technique: a) the name of
the view class needs to be fully qualified when you instantiate it
from within your query and b) the data type of the variables it
returns are not very predictable. This is why I use the very generic
"Number" type in the CategoryStats constructor, since BigInteger,
Integer, Long and BigDecimal all extend the Number class.

Hope this helps.

--Dave

On Nov 25, 7:45 am, dirk <australiandevelo...@gmail.com> wrote:
> Instead of doing this:
> JPA.em().createNativeQuery("select count(id) from
> MainCategory").getSingleResult());
> Do this:
> MainCategory.count();
>
> On Thu, Nov 25, 2010 at 11:31 AM, Ricardo Nascimento <
>

> > play-framewor...@googlegroups.com<play-framework%2Bunsu...@googlegroups.com>
> > .

[The Dave]

I should also mention that the "new map" method (number 1 in my list),
returns a list of Map<String, Object>. You can retrieve columns like
like:

List<Map<String, Object>> result = query.getResultList();

for (Map<String, Object> row : result)
{
System.out.println("categoryId: " + row.get("categoryId"));
System.out.println("categoryName: " + row.get("categoryName"));
System.out.println("adCount: " + row.get("adCount"));
}

i.e., the key in the maps created is the column name specified for the
columns in the query.

--Dave