CODEWITHSUNDEEP
jpamany-to-manycriteriaquery
hzywind
hzywind

Reputation: 51

Complex Many-to-Many JPA CriteriaQuery

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.

Upvotes: 2

Views: 1426

Answers (2)

hzywind
hzywind

Reputation: 51

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.

Upvotes: 3

digitaljoel
digitaljoel

Reputation: 26574

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.

Upvotes: 2

Related Questions