Hi,

I have been banging my head on the wall for some time trying to figure out how to do this. Here is the setup:

2 tables + association join table
Contacts
Companies
Contact_Companies_Map

Primary key in each table is an ID field. The Contact_Companies_Map table has two columns. They have foreign key constraints to the Contacts and Companies tables to the id column respectively.

Here is my dilemma. When I retrieve a specific contact, I would like to display the associated companies. However, the companies list is returned sorted by the id column in the association table and not the company name.

Here is part of my .hbm.xml files:

Contact.hbm.xml
<set name="companies" table="contact_company_map">
<key column="contact_id"/>
<many-to-many column="company_id" class="beans.CompanyBean"/>
</set>

Company.hbm.xml
<set name="contacts" table="contact_company_map">
<key column="company_id"/>
<many-to-many column="contact_id" class="beans.ContactBean"/>
</set>

I retrieve the companies using the following piece of code once I retrieve the Contact object:

Hibernate.initialize( contact.getCompanies());
Set companies = contact.getCompanies();

Everything works well, except not being sorted by company_name. I found that the following code worked:

List companyList = sess.createFilter(companies, "order by company_name" ).list();

but I have already started using a Set in my bean and not a List. Can someone help give me some guidance here on what I should do?

Thanks,
Eric

I'm no Hibernate expert at this point, so someone may know better. But Java Sets aren't going to keep a specific order by default.

One thing I could think of doing is to use specifically a TreeSet inside the class declaration... e.g. private Set xs = new TreeSet(). The default constructor TreeSet will order by the natural ordering of its contents.

Then make the Company object implement Comparable and have a natural ordering of the company name.

E.g.

public int compareTo(Object o) {
return this.name.compareTo(((Company) o).name);
}

This should have the effect of making any TreeSet of Companies be sorted by name.

If you want to get fancier, you can construct the TreeSet with an arbitrary Comparator to have it sorted however you want it in a particular method.

I'll try that... thanks!

Eric

in the set tag, you can specify the "sort" attribute and put your comparator class in there.

order-by="company_name asc"

put that in the collection definition, hibernate will tag that on the end of the sql query and use a LinkedHashSet, which preserves order but still keeps most of the properties of a Set

check out page 82 of the ref doc for further details on each..

http://www.hibernate.org/hib_docs/v3/reference/en/pdf/hibernate_reference.pdf

I thought I had tried this one before... I receive the error 'Invalid column name 'company_name'

Here is what I changed the <set> element too.

<set name="companies" table="contact_company_map" order-by="company_name">
<key column="contact_id"/>
<many-to-many column="company_id" class="beans.CompanyBean"/>
</set>

Thanks,
Eric

DEBUG [http-8080-Processor24] 434807ms 2006-Mar-16 21:16:11 org.hibernate.util.JDBCExceptionReporter - could not initialize a collection: [com.spx.itcontacts.beans.ContactBean.companies#182] [select companies0_.contact_id as contact2_1_, companies0_.company_id as company1_1_, companybea1_.company_id as company1_0_, companybea1_.COMPANY_NAME as COMPANY2_0_0_, companybea1_.address1 as address3_0_0_, companybea1_.address2 as address4_0_0_, companybea1_.city as city0_0_, companybea1_.stateOrProvince as stateOrP6_0_0_, companybea1_.postalCode as postalCode0_0_, companybea1_.country as country0_0_, companybea1_.telephoneNumber as telephon9_0_0_ from contact_company_map companies0_ inner join company companybea1_ on companies0_.company_id=companybea1_.company_id where companies0_.contact_id=? order by companies0_.company_name]
java.sql.SQLException: Invalid column name 'company_name'.

Could I bother you for a small example of what this class would look like? Would you just create a generic class that implements comparator and just have the compareTo test the different properties of the CompanyBean?

Thanks a lot for your help,
Eric

I think the problem here is that the 'order-by' can only order by columns in the mapping table but it makes much more sense to be able to order by a column in beans.CompanyBean.

Is this actually possible, or can we only order by the mapping table?

It is true that we can use a comparator, or set up a natural ordering on the CompanyBean, however this will perform the ordering in memory within hibernate. I'd much rather let the database do this sorting since this is what it is good at!

Any pointers here would really help,

George

I'm also looking to order things using Criteria or something without having to resort to specifying the column in the mapping files(hbm.xml).

-Avinash