Hibernate Criteria API - Filtering collection property

Question

I have such entity:

@Entity
public class Album {

    private Integer id;
    private Integer ownerId;
    private String name;
    private String description;
    private Date created;
    @OneToMany @JoinColumn(name = "albumId")
    private Set<AlbumUser> users = new HashSet<AlbumUser>();
    @OneToMany @JoinColumn(name = "albumId")
    private Set<Picture> pictures = new HashSet<Picture>();
}

and another one:

@Entity
public class Picture {

    private Integer id;
    private Integer creatorId;
    private Integer albumId;
    private Date created;
    private String title;
    private String description; 
    @ManyToOne @JoinColumn(name = "eventId")
    private Event event;
}

Using Criteria API I want to get unique AlbumDs with filtered set of Picturs. I try something like this:

public Album read(Integer albumId, Set<Integer> picFilter) {
        Criteria crit = getCurrentSession().createCriteria(Album.class, "album");
        crit.add(Restrictions.idEq(albumId));
        if (picFilter != null && !picFilter.isEmpty()) {
            crit = crit.createAlias("album.pictures", "picture");
            crit.add(Restrictions.in("picture.event.id", picFilter));
            crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);   
        }       
        Album resultDs = (Album) crit.uniqueResult();       
        return resultDs;
}

And here I get Album with all pictures associated. They're not filtered at all. When I try to execute query printed by a logger, I get only four rows wich is the number of pictures with given eventId, but in the Album I get all pictures.

I also tried other ResultTransformers, but eventualy got many result (4) not distinct one.

What do I miss or do wrong?

I wrote it already. There is one SQL query retrieving only 4 pictures, but I found later, there's also another one retrieving all of them. I start to believe my case is not implementable with Criteria API :( — Patryk Dobrowolski, May 23 '11 at 21:34
I came to some conclusion. Filtering the collection associated with entity is unsafe. There will be an update during transaction exit. So by definition, some pictures will be removed from album. It's wrong behavior. So... collection should be read only (not insertable nor updatable) or this case could not be implemented because of security reasons. Unfortunetlny, currently I don't have my code now and can't check it. — Patryk Dobrowolski, May 24 '11 at 09:07

score 5 · Accepted Answer · answered May 24 '11 at 20:40

You can not filter the content of Collections associated with an entity by including Restrictions on the Collection in the query. The query will only fetch the Albums. The content of the Collection can be fetched later, when the Collection is accessed. All you do is filter the Albums to retrieve only those Albums that contain the Pictures with the event ids.

If the Collection would only contain the Pictures that match your Criteria and you would get a partial Collection it would cause problems on updates, because Hibernate then think the filtered items have been removed and would update the database to reflect that change, actually removing the items from the Collection.

If you want to receive only some items from a Collection you can use the Session.createFilter() method. The only problem is, that it only supports HQL queries currently.

Yes you can using subcriteria as explained below. But it's somehow convoluted, I prefer using HQL and JOIN. — Lluis Martinez, May 08 '14 at 13:30

score 1 · Answer 2 · answered May 24 '11 at 19:35

1

I recall this being an issue for something I did recently. Have you tried this:

if (picFilter != null && !picFilter.isEmpty()) {
    Criteria subCriteria = crit.createCriteria("album.pictures"); // Or just 'pictures?'
    Disjunction or = Restrictions.disjunction();

    for (Integer id : picFilter)
        or.add(Restrictions.idEq(id));
    subCriteria.add(or);
    crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); 
 }

answered May 24 '11 at 19:35

jbrookover

5,100
1
23
23

can you please help me with http://stackoverflow.com/questions/7687409/hibernate-criteria-query-for-collection-table/7693793#7693793 I attached a bounty there.. Badly need help – Anupam Gupta Oct 10 '11 at 06:18

score 0 · Answer 3 · answered Jan 10 '18 at 06:17

If you use alias with left_join, it will return just sub object which satisfy related condition. Otherwise it returns main object which satisfy conditions but with all of sub object set.

crit = crit.createAlias("album.pictures", "picture", CriteriaSpecification.LEFT_JOIN);

This method is deprrecated in some hibernate version, if so you can use below solution for it too: criteria with filtered complex set

score 0 · Answer 4 · answered May 24 '11 at 20:44

try this:

    Criteria crit = getCurrentSession().createCriteria(Album.class, "album");
    crit.add(Restrictions.idEq(albumId));
    if (picFilter != null && !picFilter.isEmpty()) {
        crit.createAlias("album.pictures", "picture");
        crit.createAlias("picture.event", "event");
        crit.add(Restrictions.in("event.id", picFilter));
        crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);   
    }

Hibernate Criteria API - Filtering collection property

4 Answers4

Linked