Post details: Hibernate many-to-many relationships and Criteria queries

Oct 20, 2005 : Hibernate many-to-many relationships and Criteria queries

In hibernate, if you have a many-to-many relationship and you try to query it using a Criteria query you need to add:

criteria.setResultTransformer(Criteria.ROOT_ENTITY);

If you don't, you get a full cartesian join between the primary object and the related objects. So you get all the combinations listed, not just one per object like you'd like.

For example, using annotations I have a class like this:


@Entity(access = AccessType.FIELD)
public class PrintOrder {
	...
	
	@ManyToMany(fetch = FetchType.EAGER)
	@JoinTable(table = @Table(name="tblPrintOrderProcesses"))
	protected List<PaperProcess> paperProcesses;

	...
}

Then the Criteria Query should go something like this:


/** HibernateDaoSupport is a spring class and in this example is only relevant for the getSession() call */
public class MyDAOClass extends HibernateDaoSupport
	public .. some method() {
		Criteria criteria = getSession().createCriteria(PrintOrder.class);
		criteria.setResultTransformer(Criteria.ROOT_ENTITY);
		Property sort_property = Property.forName(sortField);
		Order sort_order;
		if (asc) {
			sort_order = sort_property.asc();
		} else {
			sort_order = sort_property.desc();
		}
		criteria.addOrder(sort_order);
		List list = criteria.list();
	...

So now you get only unique PrintOrders instead of multiple PrintOrders when the order has multiple paperProcesses.

Comments, Pingbacks:

Comment from: spring-user [Visitor]
A quick correction if you have a manyToMany relationship and actually have more than a single joining row.

The result transformer should in fact be:
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Otherwise, you just get duplicate ROOT_ENTITY classes where the Cartesian join occurred rather than the expected result of eager fetched collection attributes and a single entry of each ROOT_ENTITY.
Permalink 01/24/07 @ 22:09
Comment from: Romulo Rocha [Visitor]
Congratulations for your article. Its work excelently. But I'd like to do the left join contrary to inner join. How can I do it?
Thanks!!
Permalink 06/29/07 @ 10:59
Comment from: Abe [Visitor]
Thank you so much for writing this article. I was chasing down the exact same problem and came across this article and what worked for me was the solution in the response from spring-user. So thanks a lot to you both.
Permalink 08/15/07 @ 15:20
Comment from: Arin [Visitor]
Hi, I have joined two tables user and customer having many-to-many relationship. Now I need to fetch user by customer id. The hibernate created new table with user_id and customer_id. I have tried to join the two tables, but query didn't work. Can you tell me how to proceed with the same..
Permalink 06/18/08 @ 09:19
Comment from: Andrew [Visitor]
Thank you! I spent quite a bit of time running down this problem and this solved my problem in 3 minutes. Thanks also to spring-user for the clarifying comment, which was necessary for me. Much appreciated!
Permalink 08/30/08 @ 21:12
Comment from: mibo [Visitor]
Hi,
I wanted to know how you can extend this to return x # of unique item
Permalink 12/15/08 @ 03:22
Comment from: David Schmitz [Visitor]
How is this any help to ANYONE, he's not even doing adding any criteria to the statement and why muck up the example with all of that sort order BS. Completely garbage and of course it's the first thing google returns for an example of a many-to-many criteria query
Permalink 01/21/09 @ 09:29

Leave a comment:

Your email address will not be displayed on this site.
Your URL will be displayed.
What is the 2-letter abbreviation for Michigan?
Allowed XHTML tags: <p, ul, ol, li, dl, dt, dd, address, blockquote, ins, del, span, bdo, br, em, strong, dfn, code, samp, kdb, var, cite, abbr, acronym, q, sub, sup, tt, i, b, big, small>
Options:
 
(Line breaks become <br />)
(Set cookies for name, email & url)

<  June 2019  >
Mon Tue Wed Thu Fri Sat Sun
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Categories


Archives

Misc

Syndicate this blog XML

powered by
b2evolution