2

I have two entities, Ablum and Image, which are in many to many relationship. I wanna make a criteria query that to get all Albums and the counts on how many Images they have. I don't want to get all Albums first then loop the result to get the counts as there would be so many sql requests. I've been working for 2 nights and complete lost. If cannot find a way out maybe I need to fallback to use SQL.

hzywind
  • 51
  • 6

2 Answers2

3

Thanks to digitaljoel's inspiration, I found that CriteriaBuilder has a method call "size" that can be put on collections. Below is the code:

    CriteriaBuilder cb = getCriteriaBuilder();
    CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
    Root<AlbumEntity> albums = query.from(AlbumEntity.class);
    query.select(cb.array(albums.get(AlbumEntity_.id), cb.size(albums.get(AlbumEntity_.images))));
    query.groupBy(albums.get(AlbumEntity_.id));

Here the groupBy call is a must otherwise error will occur. But this method is to load the IDs of AlbumEntity, not the entity itself. The Album entity can be load if below code is used:

    query.select(cb.array(albums, cb.size(albums.get(AlbumEntity_.images))));
    query.groupBy(albums.get(AlbumEntity_.id), ...);

The groupBy must include all properites of the album entity. And it still does not work if the album entity has blob type property.

hzywind
  • 51
  • 6
2

I'm going to have to make some assumptions since you haven't posted your JPA mapping, so I'm assuming each album has a List<YourImageClass> images for the many to many mapping. With that, something like this would work.

select a, size(a.images) from Album a

That would return a List<Object[]> where List.get(i)[0] would be the album and List.get(i)[1] would be the corresponding size of the image collection.

Alternately, you could define a simple bean to select into. Something like

public class AlbumResult {
    private Album album;
    private Integer imageCount;
    public AlbumResult( Album a, Integer size ) {
        album = a;
        imageCount = size;
    }
    // getters and setters here
}

Then you could do 

select new AlbumResult(a, size(a.images)) from Album a;

I never deal with criteria queries, but the JPQL is simple enough it should be trivial to translate it into a criteria query.

digitaljoel
  • 26,265
  • 15
  • 89
  • 115
  • Thanks digitaljoel, but i can not use JPQL in the project. But your idea inspired me. Thanks a lot:) – hzywind Aug 26 '11 at 13:33
  • Yeah, you mentioned that you needed a criteria query in your question, that's why in my answer I said that the JPQL should be simple enough to translate into a criteria query, but since I don't ever use criteria queries I figured you would get that done much quicker than I would. Here's where I got the size function from http://www.objectdb.com/java/jpa/query/jpql/collection#Criteria_Query_Collection_Expressions_ – digitaljoel Aug 26 '11 at 15:08