Announcement Announcement Module
Collapse
No announcement yet.
problem with one-to-many mapping inserts Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • problem with one-to-many mapping inserts

    I am facing weird problem with inserting data into table at many relation.
    using spring 3.2 with hibernate 3 and mysql

    I am trying to create a user and the user can have a set of preferences.

    Tables structures are like

    Code:
    CREATE TABLE `Users` (
      `userid` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(80) DEFAULT NULL,
      PRIMARY KEY (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    CREATE TABLE `userpreferences` (
      `preferenceid` bigint(20) NOT NULL AUTO_INCREMENT,
      `userid` bigint(20) NOT NULL,
      `prefname` varchar(100) DEFAULT NULL,
      `prefvalue` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`preferenceid`),
      KEY `fk_pref_user_id` (`userid`),
      CONSTRAINT `fk_user_pref_id` FOREIGN KEY (`userid`) REFERENCES `userss` (`userid`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

    and my POJO classes are like

    Code:
    public class user implements Serializable, Comparable<user>{
    private Long userId;
    private String name;
    private Set<UserPreference> preferences;
    }
    
    public class UserPreference{
    public Long preferenceId;
    private String prefName;
    private String prefValue;
    private User user;
    }

    and mapping file for User is

    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">
    <hibernate-mapping>					
    	<class name="User" table="user">
    		<id name="userid" type="java.lang.Long" column="userId">
    			<generator class="native" />
    		</id>
    		<property name="name" type="string" length="80" />
    	
    		<set name="preferences" table="userpreferences" lazy="true" inverse="true" cascade="all-delete-orphan" fetch="select">
    			<key column="userid" not-null="true"/>
    			<one-to-many class="UserPreferences" />
    		</set>
    	</class>
    </hibernate-mapping>
    mapping for preferences


    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">
    <hibernate-mapping>					
    	<class name="UserPreferences" table="userpreferences" >
    		<id name="preferenceId" type="java.lang.Long">
    			<column name="preferenceId" />
    			<generator class="native" />
    		</id>
    		<many-to-one name="user" column="userId" class="User" fetch="select" not-null="true"/>
    		<property name="prefName" type="string" >
    			<column name="prefname" length="100" not-null="true"  />
    		</property>
    		<property name="prefValue" type="string" >
    			<column name="prefvalue" length="100" not-null="true"  />
    		</property>
    	</class>
    </hibernate-mapping>
    Now the java code in DAO is

    Code:
    user.setId(0L);
    user.setName("test");
    session.save(user);
    				
    Map<String, String> defaultPref =  Util.getDefaultPreferences();
    UserPreferences prefs =  null;
    for(String key : defaultPref.keySet()){
        prefs = new UserPreferences();
        prefs.setPreferenceId(0L);
        prefs.setPrefName(key);
        prefs.setPrefValue(defaultPref.get(key));
        prefs.setUser(user);
        session.save(prefs); //error line
    }
    line "session.save(user);" is succeeded and generated user id.
    But "session.save(prefs)" failed with weird error as shown


    Code:
    DEBUG: org.springframework.orm.hibernate3.HibernateTransactionManager - Found thread-bound Session [org.hibernate.impl.SessionImpl@19e6d8ab] for Hibernate transaction
    DEBUG: org.springframework.orm.hibernate3.HibernateTransactionManager - Participating in existing transaction
    DEBUG: org.hibernate.event.def.AbstractSaveEventListener - executing identity-insert immediately
    DEBUG: org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
    DEBUG: org.hibernate.SQL - insert into users(userid, name) values (?, ?)
    Hibernate: insert into users (userid, name) values (?, ?)
    DEBUG: org.hibernate.id.IdentifierGeneratorFactory - Natively generated identity: 32
    DEBUG: org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
    DEBUG: org.hibernate.event.def.AbstractSaveEventListener - executing identity-insert immediately
    DEBUG: org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
    DEBUG: org.hibernate.SQL - insert into userpreferences (userid, prefkey, prefvalue) values (?, ?, ?)
    Hibernate: insert into userpreferences (userid, prefkey, prefvalue) values (?, ?, ?)
    DEBUG: org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
    DEBUG: org.hibernate.util.JDBCExceptionReporter - could not insert: [UserPreferences] [insert into userpreferences (userid, prefkey, prefvalue) values (?, ?, ?)]
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'prefkey, prefvalue) values (32, 'aggregate', '1')' at line 1
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    DEBUG: org.hibernate.SQL - insert into userpreferences (userid, prefkey, prefvalue) values (?, ?, ?)
    Hibernate: insert into userpreferences (userid, prefkey, prefvalue) values (?, ?, ?)

    Why the insert statement for Userpreferences generated excluding preferenceId??

    what is that i am doing wrong.. please help me.
Working...
X