all,

i have read the documentation but it is not apparant to me how i create a many-to-many relationship on a column other than the primary key. my problem stems from the fact that i am using tomcat container authentication and thus must follow the directions for the tables it needs. so what i have is a user, role and user_role table. the tables are as follow:

user
====
id
user_name

role
===
id
role_name

user_role
=======
user_name
role_name

so the collection table of user_role uses the names for both items instead of the primary keys (id). if it is possible, can someone help me with the many-to-many mapping i need to get a user.getRoles and a role.getUsers?

thanks very much.

-peter

Hibernate version: 3

Name and version of the database you are using: postgresql 8.1

Use the property-ref attributes of the key and many-to-many elements, as appropriate. So User will haveAnd Role would have the same, with "role" and "user" swapped.

first off thanks very much for the help. i have implemented the relationship as you directed but now hibernate says the mapping is invalid. it complains with the following message:

Error parsing XML: XML InputStream(32) Attribute "property-ref" must be declared for element type "many-to-many".

it is complaining about the user mapping file. here is the relevant portion of that file:

<set name="roles" inverse="true">
<key column="user_name" property-ref="UserName"/>
<many-to-many property-ref="RoleName" column="role_name" class="Role" />
</set>

there is property-ref attribute and i've tried moving it around to no avail. any ideas?

thanks again.

-peter

Obviously many-to-many doesn't use property-ref :) It is declared in the DTD, so you must be using a strange, modified DTD. Check your DTD and ensure that the <!ELEMENT many-to-many> has a <!ATTLIST many-to-many property-ref>.

BTW you omitted the table="user_role" on your <set>. That's mandatory for a many-to-many.

the dtd in the mapping file is:

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

and the many-to-many element is:

<!ELEMENT many-to-many (meta*,(column|formula)*,filter*)>
<!ATTLIST many-to-many class CDATA #IMPLIED>
<!ATTLIST many-to-many node CDATA #IMPLIED>
<!ATTLIST many-to-many embed-xml (true|false) "true">
<!ATTLIST many-to-many entity-name CDATA #IMPLIED>
<!ATTLIST many-to-many column CDATA #IMPLIED>
<!ATTLIST many-to-many formula CDATA #IMPLIED>
<!ATTLIST many-to-many not-found (exception|ignore) "exception">
<!ATTLIST many-to-many outer-join (true|false|auto) #IMPLIED>
<!ATTLIST many-to-many fetch (join|select) #IMPLIED>
<!ATTLIST many-to-many lazy (false|proxy) #IMPLIED>
<!ATTLIST many-to-many foreign-key CDATA #IMPLIED>
<!ATTLIST many-to-many unique (true|false) "false">
<!ATTLIST many-to-many where CDATA #IMPLIED>
<!ATTLIST many-to-many property-ref CDATA #IMPLIED>

property-ref is in there it seems. it seems to me that the dtd is fine and the mapping is fine as well (my editor validates it) but the xml parser is not picking up the property-ref that is in there. i'm a bit confused to be honest.

on the table item, it is in my file but i must have made a copy paste error when i put it in my post. sorry.

thanks again.

-peter

Check for strange characters in your .hbm.xml file? Maybe you've used two single quotes instead of a double quote? Try viewing the file in a different editor, instead of an XML or IDE editor. vi or notepad, something simple like that.

no special characters. i checked in vi with :set list.

thanks.

No idea then. Check that there aren't other DTDs or jars on your class path. Try a different mapping file, with just the bare minimum. It does work, you just have to find out what's broken in your current configuration.

ok, thanks for all the help. i spent the last day or so getting the user and role classes to work by themselves. i got them working, copied them into my project, rebuilt and i still have the same problem. what else might be screwed up aside from the user and role mapping files that might cause this problem?

thanks,

-peter

All I can think of is that the DTD that hibernate is using isn't the one that you're looking at. Despite the reference to sourceforge, hibernate actually uses the one in the jar, so you might open up the jar and check that the DTD in there is correct. No idea why it would be wrong, though.

You can work around it by making the collection a one-to-many. Use <join> to map the user_role table into the Role class, and put a one-to-many collection in User to Role (which now includes user_name, because of the <join>). That should be functional, if not ideal.

i would just like to thank you very much for your help. you were exactly correct on everything. i unjarred what i had in lib, compared it to the dtd in the url i posted and lo and behold, the many-to-many description in the jarred dtd was missing property-ref. so i put the dtd from the url in that jar and now it all works. thanks again for all your help.

How can this be mapped without using a set and in the user class?

I want to load the data for the roles only so it's light weight.

i'm not sure i understand the question. do you mean you want to roles to be available from the user but without a set? i don't think this is possible. i'm not sure it is more "lightweight" but you could just map the user to hold a role_id attribute and then if you need to use that to build an hsql query to get the role only when necessary. if you mean something else, let me know.

Or you could just get an Object[] of scalars, without instantiating a mapped object. Use an SQLQuery. See refdocs section 16, look for "addScalar" and "return-scalar". It's not much more lightweight than a normal mapping though, hibernate is pretty efficient.

Yes thanks guys ... duh.

The problem I was having was that since my table didn't have a primary key, I was telling it that the user_id was the primary.

Well that was no good since there are multiple rows per user. I was then getting duplicate role names for the same user_id instead of differing user roles.

Mark