These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Updating a Join-Table (many-to-many) relationship
PostPosted: Fri May 05, 2006 5:39 am 
Regular
Regular

Joined: Mon Mar 06, 2006 6:18 am
Posts: 95
Location: Bern, Switzerland
Hi all

Hibernate version: 3.1.3

Mapping documents:
Benutzer.hbm.xml

Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 01.05.2006 16:29:30 by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
    <class name="najsre7.model.Benutzer" table="TBENUTZER" schema="NAJSRE7_WEB">
        <id name="id" type="java.lang.Integer">
            <column name="ILAUFNUMMER" precision="9" scale="0" />
            <generator class="sequence">
                <param name="sequence">SEQ_TBENUTZER</param>
            </generator>
        </id>
        <timestamp name="mutDatum" column="DMUTDAT" />
        <many-to-one name="benutzerStatus" class="najsre7.model.BenutzerStatus" fetch="select">
            <column name="FK_TBENUSTAT_ISTATUS" precision="9" scale="0" not-null="true" />
        </many-to-one>
        <property name="benutzerName" type="java.lang.String">
            <column name="SBENUTZERNAME" length="50" not-null="true" />
        </property>
        <property name="passwort" type="java.lang.String">
            <column name="SPASSWORT" length="50" not-null="true" />
        </property>
        <property name="eMail" type="java.lang.String">
            <column name="SEMAIL" length="50" not-null="true" />
        </property>
        <property name="lastLogin" type="timestamp">
            <column name="DLASTLOGIN" length="11" />
        </property>
        <property name="anzahlLogin" type="java.lang.Integer">
            <column name="IANZLOGIN" precision="9" scale="0" not-null="true" />
        </property>
        <property name="mutUser" type="java.lang.String">
            <column name="SMUTUSER" length="10" not-null="true" />
        </property>
        <property name="stvUser" type="java.lang.String">
            <column name="SSTVUSER" length="10" />
        </property>
        <property name="personId" type="java.lang.Integer">
            <column name="FK_TPER_IPERSNR" precision="9" scale="0" not-null="true" />
        </property>
        <set name="zugaenge" inverse="true">
            <key>
                <column name="FK_TBENU_ILAUFNR" precision="22" scale="0" />
            </key>
            <one-to-many class="najsre7.model.Zugang" />
        </set>
      
      <set name="profile" table="TBENU_PROFIL_MATRIX"  fetch="join"  >
          <key column="FK_TBENU_ILAUFNR" />
          <many-to-many column="FK_TPROFIL_ILAUFNR" class="najsre7.model.Profil" />
      </set>
     
    </class>
</hibernate-mapping>


Profil.hbm.xml

Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 01.05.2006 16:29:30 by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
    <class name="najsre7.model.Profil" table="TPROFILE" schema="NAJSRE7_WEB">
        <id name="id" type="java.lang.Integer">
            <column name="ILAUFNUMMER" precision="22" scale="0" />
            <generator class="sequence">
                <param name="sequence">SEQ_TPROFILE</param>
            </generator>
        </id>
        <property name="profilTyp"
              column="SPROFIL"
              not-null="true"
               type="najsre7.dao.hibernate.userType.immutable.benutzer.ProfilTypUT" />
        <property name="beschreibung" type="java.lang.String">
            <column name="SBESCHREIBUNG" length="200" />
        </property>
        <property name="mutUser" type="java.lang.String">
            <column name="SMUTUSER" length="50" not-null="true" />
        </property>
        <property name="stvUser" type="java.lang.String">
            <column name="SSTVUSER" length="50" />
        </property>
        <property name="mutDatum" type="timestamp">
            <column name="DMUTDAT" length="11" not-null="true" />
        </property>
          <set name="benutzer" table="TBENU_PROFIL_MATRIX" fetch="join" inverse="true" >
          <key column="FK_TPROFIL_ILAUFNR" />
          <many-to-many  column="FK_TBENU_ILAUFNR" class="najsre7.model.Benutzer" />
         </set>
          <set name="berechtigungen" table="TPROFIL_BERECHT_MATRIX" inverse="true"  fetch="join" >
          <key column="FK_TPROFIL_ILAUFNR" />
          <many-to-many  column="FK_TBERECHT_ILAUFNR" class="najsre7.model.Berechtigung" />
        </set>
    </class>
</hibernate-mapping>


Name and version of the database you are using: Oracle Express 10

Spring version 1.2.7

I have a many-to-many association between Benutzer and Profile. When i try to add a new Benutzer with an existing Profile an exception occurs. Here is my Manager-Code:

Code:
Benutzer ben = benutzerDao.saveBenutzer(benu);
Profil profil = getProfil(ProfilTyp.USER);
      Set profileSet = ben.getProfile();
      profileSet.add(profil);
      ben.setProfile(profileSet);

      Benutzer tmp = saveBenutzer(ben);


The following exception occurs:

Code:
Hibernate: insert into NAJSRE7_WEB.TBENUTZER (DMUTDAT, FK_TBENUSTAT_ISTATUS, SBENUTZERNAME, SPASSWORT, SEMAIL, DLASTLOGIN, IANZLOGIN, SMUTUSER, SSTVUSER, FK_TPER_IPERSNR, ILAUFNUMMER) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: update NAJSRE7_WEB.TBENUTZER set DMUTDAT=?, FK_TBENUSTAT_ISTATUS=?, SBENUTZERNAME=?, SPASSWORT=?, SEMAIL=?, DLASTLOGIN=?, IANZLOGIN=?, SMUTUSER=?, SSTVUSER=?, FK_TPER_IPERSNR=? where ILAUFNUMMER=? and DMUTDAT=?
2006-05-05 11:35:46,335 ERROR Could not synchronize database state with session in (AbstractFlushingEventListener.java:300)
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [najsre7.model.Benutzer#100]
   at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:1635)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2208)
................................


I also tried the following code (only one save):

Code:
      Profil profil = getProfil(ProfilTyp.USER);
      Set profileSet = benu.getProfile();
      profileSet.add(profil);
      benu.setProfile(profileSet);

      Benutzer ben = benutzerDao.saveBenutzer(benu);


Then I get an other exception, but at least i can see an insert operation into the join table:

Code:
Hibernate operation: Could not execute JDBC batch update; SQL [insert into TBENU_PROFIL_MATRIX (FK_TBENU_ILAUFNR, FK_TPROFIL_ILAUFNR) values (?, ?)]; ORA-02291: Integritäts-Constraint (NAJSRE7_WEB.FK_TBENU_PROFIL_MATRIX) verletzt - übergeordneter Schlüssel nicht gefunden ; nested exception is java.sql.BatchUpdateException: ORA-02291: Integritäts-Constraint (NAJSRE7_WEB.FK_TBENU_PROFIL_MATRIX) verletzt - übergeordneter Schlüssel nicht gefunden


any help appreciated! Thanks!

Kind Regards
Angela


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 08, 2006 1:36 am 
Regular
Regular

Joined: Mon Mar 06, 2006 6:18 am
Posts: 95
Location: Bern, Switzerland
no ideas? :-/


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 08, 2006 9:54 am 
Beginner
Beginner

Joined: Tue Jan 18, 2005 9:37 am
Posts: 29
Location: The Netherlands
Hi Angela,

I think there is a problem in your key-column. The key column should uniquely identify the set, so it is usually the same column as the id-column of the class containing the set.

your code:
Code:
<set name="berechtigungen" table="TPROFIL_BERECHT_MATRIX" inverse="true"  fetch="join" >
          <key column="FK_TPROFIL_ILAUFNR" />
          <many-to-many  column="FK_TBERECHT_ILAUFNR" class="najsre7.model.Berechtigung" />


new code:
Code:
<set name="berechtigungen" table="TPROFIL_BERECHT_MATRIX" inverse="true"  fetch="join" >
          <key column="ILAUFNUMMER" />
          <many-to-many  column="FK_TBERECHT_ILAUFNR" class="najsre7.model.Berechtigung" />


Same for the set in Profil
By the way: FK usually stands for foreign-key (the name of a constraint, not a column).

You should also use the inverse property on only one end of the many-to-many association.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 08, 2006 10:07 am 
Regular
Regular

Joined: Mon Mar 06, 2006 6:18 am
Posts: 95
Location: Bern, Switzerland
Hello!

BasvdB wrote:
Hi Angela,

I think there is a problem in your key-column. The key column should uniquely identify the set, so it is usually the same column as the id-column of the class containing the set.

new code:
Code:
<set name="berechtigungen" table="TPROFIL_BERECHT_MATRIX" inverse="true"  fetch="join" >
          <key column="ILAUFNUMMER" />
          <many-to-many  column="FK_TBERECHT_ILAUFNR" class="najsre7.model.Berechtigung" />


Hmm I'm not sure about this. My TPROFIL_BERECHT_MATRIX table has only two columns which are building a composite id:

Table TPROFIL_BERECHT_MATRIX:
FK_TBENU_ILAUFNR (foreign-key to the TBENUTZER table)
FK_TPROFIL_ILAUFNR (foreign-key to the TPROFIL table)

So this is a mapping table to know which users have which profiles and vice versa.

So i think my mapping should be ok? Or am I wrong?

If i'm doing it the way you say i get an exception, because there is no column named ILAUFNUMMER in the Matrix table:
Code:
java.sql.SQLException: ORA-00904: "PROFILE2_"."ILAUFNUMMER": ungültiger Bezeichner
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)


BasvdB wrote:
You should also use the inverse property on only one end of the many-to-many association.


I already changed this..thanks....Any other ideas?

angela


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 08, 2006 10:23 am 
Beginner
Beginner

Joined: Tue Jan 18, 2005 9:37 am
Posts: 29
Location: The Netherlands
Ok it seems that you have a legacy database schema.

Try this:

Old code:

Code:
<set name="benutzer" table="TBENU_PROFIL_MATRIX" fetch="join" inverse="true" >
          <key column="FK_TPROFIL_ILAUFNR" />
          <many-to-many  column="FK_TBENU_ILAUFNR" class="najsre7.model.Benutzer" /> 


New code:
Code:
<set name="benutzer" table="TBENU_PROFIL_MATRIX" fetch="join" inverse="true" >
          <key column="FK_TPROFIL_ILAUFNR" />
          <many-to-many  column="ILAUFNUMMER" class="najsre7.model.Benutzer" />


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 08, 2006 10:43 am 
Regular
Regular

Joined: Mon Mar 06, 2006 6:18 am
Posts: 95
Location: Bern, Switzerland
BasvdB wrote:
Ok it seems that you have a legacy database schema.

Try this:

Old code:

Code:
<set name="benutzer" table="TBENU_PROFIL_MATRIX" fetch="join" inverse="true" >
          <key column="FK_TPROFIL_ILAUFNR" />
          <many-to-many  column="FK_TBENU_ILAUFNR" class="najsre7.model.Benutzer" /> 


New code:
Code:
<set name="benutzer" table="TBENU_PROFIL_MATRIX" fetch="join" inverse="true" >
          <key column="FK_TPROFIL_ILAUFNR" />
          <many-to-many  column="ILAUFNUMMER" class="najsre7.model.Benutzer" />


I'm using Oracle Database Express 10.

I tried your code and get an exception as well:

Code:
2006-05-08 16:40:53,585 DEBUG could not execute query [select this_.ILAUFNUMMER as ILAUFNUM1_0_2_, this_.DMUTDAT as DMUTDAT0_2_, this_.FK_TBENUSTAT_ISTATUS as FK3_0_2_, this_.SBENUTZERNAME as SBENUTZE4_0_2_, this_.SPASSWORT as SPASSWORT0_2_, this_.SEMAIL as SEMAIL0_2_, this_.DLASTLOGIN as DLASTLOGIN0_2_, this_.IANZLOGIN as IANZLOGIN0_2_, this_.SMUTUSER as SMUTUSER0_2_, this_.SSTVUSER as SSTVUSER0_2_, this_.FK_TPER_IPERSNR as FK11_0_2_, profile2_.FK_TBENU_ILAUFNR as FK1_4_, profil3_.ILAUFNUMMER as ILAUFNUM2_4_, profil3_.ILAUFNUMMER as ILAUFNUM1_7_0_, profil3_.DMUTDAT as DMUTDAT7_0_, profil3_.SPROFIL as SPROFIL7_0_, profil3_.SBESCHREIBUNG as SBESCHRE4_7_0_, profil3_.SMUTUSER as SMUTUSER7_0_, profil3_.SSTVUSER as SSTVUSER7_0_, benutzer4_.FK_TPROFIL_ILAUFNR as FK3_5_, benutzer4_.ILAUFNUMMER as ILAUFNUM2_5_, berechtigu5_.FK_TPROFIL_ILAUFNR as FK1_6_, berechtigu6_.ILAUFNUMMER as FK2_6_, berechtigu6_.ILAUFNUMMER as ILAUFNUM1_9_1_, berechtigu6_.SBERECHTIGUNG as SBERECHT2_9_1_, berechtigu6_.SBESCHREIBUNG as SBESCHRE3_9_1_, berechtigu6_.SMUTUSER as SMUTUSER9_1_, berechtigu6_.SSTVUSER as SSTVUSER9_1_, berechtigu6_.DMUTDAT as DMUTDAT9_1_ from NAJSRE7_WEB.TBENUTZER this_ left outer join TBENU_PROFIL_MATRIX profile2_ on this_.ILAUFNUMMER=profile2_.FK_TBENU_ILAUFNR left outer join NAJSRE7_WEB.TPROFILE profil3_ on profile2_.ILAUFNUMMER=profil3_.ILAUFNUMMER left outer join TBENU_PROFIL_MATRIX benutzer4_ on profil3_.ILAUFNUMMER=benutzer4_.FK_TPROFIL_ILAUFNR left outer join TPROFIL_BERECHT_MATRIX berechtigu5_ on profil3_.ILAUFNUMMER=berechtigu5_.FK_TPROFIL_ILAUFNR left outer join NAJSRE7_WEB.TBERECHTIGUNGEN berechtigu6_ on berechtigu5_.FK_TBERECHT_ILAUFNR=berechtigu6_.ILAUFNUMMER where this_.SBENUTZERNAME=?] in (JDBCExceptionReporter.java:63)
java.sql.SQLException: ORA-00904: "PROFILE2_"."ILAUFNUMMER": ungültiger Bezeichner

   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
   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:743)
   at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)



angela


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 08, 2006 11:02 am 
Beginner
Beginner

Joined: Tue Jan 18, 2005 9:37 am
Posts: 29
Location: The Netherlands
ok, What I wanted to show you is the meaning of the many-to-many column as explained in the reference guide page 11.
This column should map to the primary key of the relating class.

Please check this for every set.
The error refers to PROFILE2_, this is not where I made a change.


Top
 Profile  
 
 Post subject:
PostPosted: Tue May 09, 2006 1:42 am 
Regular
Regular

Joined: Mon Mar 06, 2006 6:18 am
Posts: 95
Location: Bern, Switzerland
BasvdB wrote:
ok, What I wanted to show you is the meaning of the many-to-many column as explained in the reference guide page 11.
This column should map to the primary key of the relating class.

Please check this for every set.
The error refers to PROFILE2_, this is not where I made a change.


The mapping was correctly...i think it was a problem with the constraints in my database.

TBENU_TPROFIL_MATRIX has two columns as a said:
FK_TBENU_ILAUFNR (foreign-key to the TBENUTZER table)
FK_TPROFIL_ILAUFNR (foreign-key to the TPROFIL table)

There were three constraints on this table, one defining the composite Primary key..and one for each foreign key...Now i removed the ones for the foreign keys and its working...

angela


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.