Friday, 23 October 2009

Cannot Simultaneously Fetch Multiple Bags

It's a gem of an error message, isn't it? I suspect that most people, on encountering it for the first time, wonder if it applies to them. "Did I ask for multiple bags?" or "What the hell is a bag?". It would be cheap to dismiss it as meaningless but, once you investigate it, you realise that the issues behind it are complex and varied. I don't think we can blame the Hibernate developers for adopting an error message that describes the problem that Hibernate is wrestling with internally, rather than trying to second-guess what caused it.

The purpose of this post is not to explain the reasons behind why it happens. Googling the error message will lead you to that. What I want to do is step through some solutions beyond the "use a Set" or "specify the index column" answers.

Let's start with a simple example that I think is typical of how people might run into this error. You want to maintain countries and regions in an application. A classic one-to-many relationship where country is the parent or master and region is the child or detail. The obvious representation in the database is two tables with a foreign key from the child to the parent:

For many people, their natural inclination will be to map this using Hibernate like this:

public class Country implements Serializable, Comparable {

    private long id;
    private String name;
    private List regions = new ArrayList();

    ...

    @OneToMany(mappedBy="country", cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    public ListgetRegions() {
        return regions;
    }

    ...

}

I'm using JPA annotations here. The mappedBy attribute is the equivalent of inverse=true on the collection in a Hibernate schema-based mapping.

public class Region implements Serializable, Comparable {

    private long id;
    private String name;
    private Country country;

    ...

    @OneToMany(mappedBy="region", cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    public Country getCountry() {
        return country;
    }

}

I'm not suggesting you would always use FetchType.EAGER of course. But in this case, let's assume that we always want the regions populated when we get the country and vice versa.

I'm thinking of this in a Spring project, probably with a DAO based on Spring's support classes. So something like this:

public class CountryDAOImpl extends HibernateDaoSupport implements CountryDAO {

    @Override
    public Country createCountry(Country country) {
        country.setId((Long)getHibernateTemplate().save(country));
        return country;
    }

    @Override
    public Country findCountryByPrimaryKey(long id) {
        return (Country)getHibernateTemplate().get(Country.class, id);
    }

    @Override
    public List findAllCountries() {
        return getHibernateTemplate().find("from Country");
    }

    ...

And you might have this exposed via a service facade, perhaps combined with calls to other DAOs as part of a transaction, and so on. All wired together as usual with Spring. You put it all together, ask Hibernate hbm2ddl to create your tables, run a simple test case and it works. Tables look OK. Life is good.

Then you decide to add another level. Say you need areas within regions. Same idea as before, it just becomes a three-tiered database structure with area having a foreign key into region.

After a judicious cut & paste session (resolving to try abstract DAOs when your boss isn't breathing down your neck) you end up with everything wired together and you run again. The database looks OK but lo and behold ... Hibernate hits you with "Cannot simultaneously fetch multiple bags".

That might be how you ended up here, when you Googled the error message. You may have read the suggestions to use @IndexColumn on the collections, or perhaps read that using List is just a habit and you should really be using a Set? So let's explore these solutions:

@IndexColumn

The idea here is that you annotate the collection mappings with @IndexColumn. Note that this is a Hibernate annotation, not a JPA annotation, which might start alarm bells ringing for you if the boss mumbled something about not tying yourself to specific technologies.

public class Region implements Serializable, Comparable {

    private long id;
    private String name;
    private Country country;

    ...

    @OneToMany(mappedBy="region", cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    @IndexColumn(name="id")
    public Country getCountry() {
        return country;
    }

}

You run again and all is well. Unless you don't care about null entries in your collection of countries. Hmm. Something is not quite right here. You might dig around for another solution and find the suggestion that you remove the mappedBy attribute? So you try that and it works. Deep joy. Or maybe not - have a look at the database structure that Hibernate has created - join tables between the expected tables that is going to take some explaining to your DBA, who is already mistrustful of the idea of having Hibernate create a database schema.

It is possible to get back to a more logical database design, but perhaps we should put a hold on this solution and try the other option that was suggested. After all, this should allow us to avoid the Hibernate-specific @IndexColumn annotation ...

Set

OK. A set is just a collection so this shouldn't be that difficult. Global search and replace on the project, changing all references of List to Set and ArrayList to HashSet. Also make sure you've removed those @IndexColumn annotations and reintroduced the mappedBy attribute, otherwise you'll still get those join tables.

The first problem is that your DAO complains. The find method on Spring's Hibernate template wants to return a List rather than a Set. Maybe that global search and replace wasn't such a good idea? When the solution said you should use a Set, it didn't mean everywhere. ;) It's perfectly OK to have your finder methods in your DAOs and facade return List:

    @Override
    public List findAllCountries() {
        return getHibernateTemplate().find("from Country");
    }

What happens now depends on whether you were diligent enough to create equals() and hashcode() methods on your entities. It's common and sounds perfectly logical to create these based on your primary key column. The IDEs even do the work for you. The problem is that when you create the entity, it won't have an id (assuming we are letting the database generate them). It only gets the id when Hibernate saves it. If you create a series of regions in your country and try to save the country, you'll find that there's only one region. The HashSet thought they were all the same entry (because the ids were all null).

You could be tempted to try a TreeSet instead of a HashSet at this point. It would work, but the HashSet is generally better performing and it's not a good idea to get yourself forced into a choice like this.

The database generated "surrogate" ids are fine, but what you really need to do is create the hashcode() function based on a "real" primary key. In the case of country for example, this would be the name. In the case of an invoice, it would be the invoice number as used by accounts staff. So go back to your equals() and hashcode() methods and make them use the "real" primary key, not the surrogate one that could be null before the record is inserted.

At this point, it wouldn't be a bad idea to add this annotation to your entity so that the database creates indexes on these "real" primary keys.

@Entity
@Table(uniqueConstraints=@UniqueConstraint(columnNames="name"))
public class Country implements Serializable, Comparable {

    ...

With the region table, you might allow the same region to appear in two countries. It might be "North", "Central", etc. In that case, you'd specify a composite:

@Entity
@Table(uniqueConstraints=@UniqueConstraint(columnNames={"fk_country_id", "name"}))
public class Region implements Comparable {

And you should find that's a decent solution.

Summary

  1. Use java.util.Set for collection mappings (but not on your DAO and facade methods)
  2. Use mappedBy to make the collection end of the relationship the inverse end
  3. Create equals() and hashcode() based on a "real" primary key (with indexes as appropriate)

If you are using JPA/EJB3 of course, you could just switch provider to Toplink and it would be quite happy with the original code using List. But that's another story.

3 comments:

Anonymous said...

Clear, concise explanation Richard...Thank You....you could make a small fortune writing an e-book. Thanks again

Anonymous said...

"Unless you don't care about null entries in your collection of countries. Hmm. Something is not quite right here. "

I did not understand, this was the solution adopted by me. The collection does not return null values​​. Could you explain more about this part.

tanks. Giovanni

Anonymous said...

Hibernate is janky at best. Use EclipseLink and these amateur problems go away.

Followers