composite-id problem

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

Hi,

i have two tables: User and survey. Both have the unique key ID. So every user can execute more than one survey and one survey can be executed by more than one users. In the table usersurvey I want to have the UserID, SurveyID (2 FK) and an attribute completed (as date).

now the classes:

Code:

class user {
private int id; //plus getters and setters
...
}

class survey {
private int id; //plus getters and setters
...
}

now the mapping:

Code:

<hibernate-mapping package="core">
  <class name="User">
     <id name="id" column="ID">
        <generator class="native"></generator>
        </id>
        .......
  </class>
</hibernate-mapping>

<hibernate-mapping package="core">
  <class name="Survey" table="Umfrage">
     <id name="id" column="ID">
        <generator class="increment"></generator>
     </id>
        .....
  </class>
</hibernate-mapping>

so now my question: how should the mapping look like for the table usersurvey?

i created a class userSurveyLinking:

Code:

public class UserSurveyLinking implements Serializable{
   
   private static final long serialVersionUID = 1L;
   
   private int userID = -1; //shold be a foreign key
   private int surveyID = -1; //shold be a foreign key
   private Date completed = new Date();
}

But I can't get the mapping. I would be so nice, if some can help me!!!

With best regards

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

by the way this was my appraoch, which is not working:

Code:

<hibernate-mapping package="core">
  <class name="UserSurveyLinking" table="uservorlesung">
     <composite-id>
        <key-many-to-one name="surveyID" column="UmfrageID"></key-many-to-one>
        <key-many-to-one name="userID" column="UserID"></key-many-to-one>
     </composite-id>
     <property name="completed" column="Datum"></property>
     <many-to-one name="surveyID" column="UmfrageID"></many-to-one>
     <many-to-one name="userID" column="UserID"></many-to-one>
  </class>
</hibernate-mapping>

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

Hello,

guys it's very importtant for me! It would be very nice, if someone can help me!!! PLEASE!

With best regards

littypreethkr · **Posted:** Mon Mar 02, 2009 1:58 am

The class attribute is missing in ur key-many-to-one tag. Also the many-to-one tags are not required as you are already mapping those fields in the key-many-to-one.
Try this:

Code:

  <class name="UserSurveyLinking" table="uservorlesung">
     <composite-id>
        <key-many-to-one name="surveyID" class="Survery" column="UmfrageID"></key-many-to-one>
        <key-many-to-one name="userID" class="User" column="UserID"></key-many-to-one>
     </composite-id>
     <property name="completed" column="Datum"></property>
  </class>

If this doesn't work out let us know the exact error which u r getting and we ll try to figure out whats wrong.

mmerder · **Posted:** Mon Mar 02, 2009 4:27 am

You should use an id-class, when using composite ids.
Look hereand here.

littypreethkr · **Posted:** Mon Mar 02, 2009 4:47 am

mmerder wrote:

You should use an id-class, when using composite ids.
Look here and here.

mmerder is right that it is preffered to use an id-class mapping for composite ids. But hibernate supports the other type of mapping also, where the persistent class itself is the identifier class. Its called embedded composite identifier. This also requires that the persistent class should implement Serializable and equals() and hashcode().

But still I also would recommend that you use the id-class method if nothing else is preventing you.

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

jeah, it's working, thank you!!!
But I have a an other question question for this problem. I set an inverse mapping in the user.hbm.xml. Because on delete I want to keep me data.

Code:

     <bag name="UserSurvey" lazy="false" table="usersurveylinking"
        inverse="true">
        <key column="UserID"></key>
        <one-to-many class="UserSurveyLinking"></one-to-many>
     </bag>

But if i want to delete an survey object, all data with the same foreign key should be deleted too. The Table UserSurveyLinking dones not allow a null value to all its attributes UserID, SurveyID and completed. So on survey delete hibernate tries to set the SurveyID null and the progg crashes.

The survey.hbm.xml:

Code:

     <bag name="SurveyUser" lazy="false" table="usersurveylinking">
        <key column="SurveyID"></key>
        <one-to-many class="UserSurveyLinking"></one-to-many>
     </bag>

So my question: which attribute should I use, to delete all rows in the UserSurveyLinking?

With best regards!

littypreethkr · **Posted:** Mon Mar 02, 2009 6:49 am

You can use cascade="all-delete-orphan" inside the <bag> tag.

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

upps wrong text

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

It does not work! :(

hier the bag of the survey.hbm.xml

Code:

        <bag name="SurveyUser" lazy="false" table="usersurveylinking"
        cascade="all-delete-orphan">
        <key column="UmfrageID"></key>
        <one-to-many class="UserSurveyLinking"></one-to-many>
     </bag>

and here what hibernate tries to do:

Code:

Hibernate: delete from usersurveylinking where SurveyID=?
Hibernate: update Survey set SurveyID=null where SurveyID=?
Hibernate: update Group set SurveyID=null, order=null where SurveyID=?
Hibernate: update usersurveylinking set SurveyID=null where SurveyID=?
WARN  - JDBCExceptionReporter      - SQL Error: 1048, SQLState: 23000
ERROR - JDBCExceptionReporter      - Column 'SurveyID' cannot be null
ERROR - tractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

any hints?

littypreethkr · **Posted:** Mon Mar 02, 2009 8:12 am

Can you post the new mapping of UserSurveyLinking and Survery classes and the code which is doing the delete.

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

I post only the minimal things, which are requiered for this prob

Code:

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="core">
  <class name="UserSurveyLinking" table="userumfrage">
     <composite-id>
        <key-property name="userID" column="UserID"></key-property>
        <key-property name="surveyID" column="UmfrageID"></key-property>
     </composite-id>
     <property name="completed" column="Datum"></property>
  </class>
</hibernate-mapping>

Code:

<hibernate-mapping package="core">
  <class name="User">
     <id name="id" column="ID">
        <generator class="native"></generator></id>
     <bag name="lectures" lazy="false" order-by="vorlesungID asc" table="uservorlesung">
        <key column="UserID"></key>
        <many-to-many column="VorlesungID" class="Lecture">
        </many-to-many>
     </bag>
     <bag name="UserSurvey" lazy="false" table="uservorlesung"
        inverse="true">
        <key column="UserID"></key>
        <one-to-many class="UserSurveyLinking"></one-to-many>
     </bag>
  </class>
</hibernate-mapping>

Code:

<hibernate-mapping package="core">
  <class name="Survey" table="Umfrage">
     <id name="id" column="ID">
        <generator class="increment"></generator>
     </id>
     <bag name="SurveyUser" lazy="false" table="uservorlesung"
        cascade="all-delete-orphan">
        <key column="UmfrageID"></key>
        <one-to-many class="UserSurveyLinking"></one-to-many>
     </bag>
  </class>
</hibernate-mapping>

Code:

public class Survey implements Serializable {

   private static final long serialVersionUID = 1L;

   private int id = -1;

   private List<UserSurveyLinking> SurveyUser = null;

       //++ getters and setters

Code:

public class User implements Serializable {

   private static final long serialVersionUID = 1L;

   private int id;

   private List<UserSurveyLinking> UserSurvey = null;

       //+getters and Setters

Code:

public class UserSurveyLinking implements Serializable{
   
   private static final long serialVersionUID = 1L;
   
   private int userID = -1;
   private int surveyID = -1;
   private Date completed = new Date();

littypreethkr · **Posted:** Mon Mar 02, 2009 8:47 am

I spot many in-consistencies in this mapping:

As per your UserSurveyLinking mapping the link table is userumfrage. But in the User and Survey mappings you are using uservorlesung as the linking table.???

Also in the mapping file the Survey PK column is ID. But in the SQL log it says: update Survey set SurveyID=null where SurveyID=? .... ???

UserId also is part of USLink. So null userid is something which is not possible. So I think you should specify all-delete-orphan cascade in the UserSurvey bag of User also.

General_Luzi · **Joined:** Thu Feb 19, 2009 7:15 am **Posts:** 11

I wanted to be consistent with my first post, so i changed the names of the table and so on. the original error message is:

Code:

Hibernate: update Gruppe set UmfrageID=null, Reihenfolge=null where UmfrageID=?
Hibernate: update userumfrage set UmfrageID=null where UmfrageID=?
WARN  - JDBCExceptionReporter      - SQL Error: 1048, SQLState: 23000
ERROR - JDBCExceptionReporter      - Column 'UmfrageID' cannot be null
ERROR - tractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

The problems is hibernate tries to update the uservorlesung table, and sets the UmfrageID (surveyID) = null, thats prohibited by the DB.

I want to keep the data from the users, so if a user is deleted, the table uservorlesung must not be touched. Otherwise if a survey is deleted, the data in this table is not required and has to be deleted.

Conclusion: Do I have to change my DB, so that null is allowed, or is there a way to spezify my an attribute in the survey mapping file, so that all entries from a specific surfvey will be deleted and not updated?

littypreethkr · **Posted:** Mon Mar 02, 2009 9:32 am

If in your domain model the UserSurveyLinking has an existance without a User entity then you must remove the userId FK from the PK. Better have a surrogate generated PK column for the Link table. I think that will solve all your problems.