I have a problem with a many-to-many association with Strings, whose not the id of the classes.

My hbm-config;

<hibernate-mapping>
<class name="com.Benutzer" table="BENUTZER">

<id name="benutzerId" column="BENUTZER_ID" type="long">
<generator class="seqhilo">
<param name="sequence">SEQ_BENUTZER</param>
<param name="max_lo">10</param>
</generator>
</id>

<set name="rollen" table="BENUTZER_ROLLE" lazy="true" cascade="none" sort="unsorted">
<key column="NAME"></key>
<many-to-many class="com.Rolle" column="NAME1" outer-join="auto" />
</set>

<property name="name" type="java.lang.String" update="true" insert="true" column="NAME" not-null="true"/>
</class>

<class name="com.Rolle" table="ROLLE">

<id name="rolleId" column="ROLLE_ID" type="long">
<generator class="seqhilo">
<param name="sequence">SEQ_ROLLE</param>
<param name="max_lo">10</param>
</generator>
</id>

<set name="benutzer" table="BENUTZER_ROLLE" lazy="true"
cascade="none" sort="unsorted" >
<key column="NAME1" > </key>
<many-to-many class="com.Benutzer" column="NAME" outer-join="auto" />
</set>

<property name="name" type="java.lang.String" update="true" insert="true" column="NAME1"/>
</class>
</hibernate-mapping>


My db:

create table benutzer (benutzer_id number(38) not null, name VARCHAR2(50) not null CONSTRAINT PK_BENUTZER PRIMARY KEY (benutzer_id) )

create table rolle (rolle_id number(38) not null, name1 VARCHAR2(50) not null CONSTRAINT PK_ROLLE PRIMARY KEY (rolle_id) )

create table benutzer_rolle (name VARCHAR2(50) not null, name1 VARCHAR2(50) not null, CONSTRAINT PK_BENUTZER_rolle PRIMARY KEY (name, rolle_name) )


I got following error:
DEBUG: select rollen0_.NAME as NAME0_, rollen0_.NAME1 as NAME2_0_ from BENUTZER_ROLLE rollen0_ where rollen0_.NAME=?
2009-02-26 11:56:12,453 main, org.hibernate.util.JDBCExceptionReporter(77)
WARN : SQL Error: 1722, SQLState: 42000
Exception in thread "main" 2009-02-26 11:56:12,454 main, org.hibernate.util.JDBCExceptionReporter(78)
ERROR: ORA-01722: invalid number

org.hibernate.exception.SQLGrammarException: could not initialize a collection: [com.claas.ccs.ts.base.model.Benutzer.rollen#2673]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadCollection(Loader.java:2001)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:36)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:565)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:63)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1716)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:86)
at org.hibernate.collection.PersistentSet.iterator(PersistentSet.java:163)
at com.claas.ccs.ts.base.model.Test.main(Test.java:23)
Caused by: java.sql.SQLException: ORA-01722: invalid number

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:75)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1994)
... 8 more

But why havnt you declared name and name1 of link table as the foreignkeys? Try that.

Please give me an example

create table benutzer_rolle (name VARCHAR2(50) not null references benutzer(name), name1 VARCHAR2(50) not null references rolle(name1), CONSTRAINT PK_BENUTZER_rolle PRIMARY KEY (name, rolle_name) )

For this I think u ll have to make name n name1 unique

I was wrong. I tried this and found that in the DB you dont need a foreign key reference for the columns, but in the role and benutzer mappings the name1 and name properties need to be declared unique. Then inside the many-to-many mapping you can use property-ref to refer to the name and name1 properties instead of the primary keys.

Ie

<class name="com.Benutzer" table="BENUTZER">

.......
.......

<property name="name" type="java.lang.String" update="true" insert="true" column="NAME" not-null="true" unique="true"/>

<set name="rollen" table="BENUTZER_ROLLE" lazy="true" cascade="none" sort="unsorted">
<key column="NAME"></key>
<many-to-many class="com.Rolle" property-ref="name1" column="NAME1" outer-join="auto" />
</set>
</class>

<class name="com.Rolle" table="ROLLE">

.......
.......

<property name="name" type="java.lang.String" update="true" insert="true" column="NAME1" unique="true"/>

<set name="benutzer" table="BENUTZER_ROLLE" lazy="true"
cascade="none" sort="unsorted" >
<key column="NAME1"> </key>
<many-to-many class="com.Benutzer" property-ref="name" column="NAME" outer-join="auto" />
</set>
</class>

Great! Thanks for your help!

Rating is welcome..... :-)