Problem in Foreign Keys in a simple one to many relation!

sltorrent · **Joined:** Fri May 16, 2008 2:36 am **Posts:** 2

I am a newbie to Hibernate so please forgive me if this kind of question was asked on the forum before.
I tried to find a similar post but was not successful.

What I am trying to do:
I am trying to implement a one-to-many relationship using an owner and accounts scenario. An owner can have many accounts. The idea is to when an owner is added with a set of account objects, the account table gets the new rows automatically. This works fine when there are no specified foreign keys in the database. Whenever I add a foreign key constraint in the
database it gives an error. (The error message is shown below). But I guess this should not happen in a correct solution.

Please give me some help and it would be appreciated a lot!

Hibernate Version 3
Database Used: MySQL 5.0
Java 6
IDE : ObjectWeb Lomboz 3.3
OS: Win XP Pro

Here are the relevant files:

[u]Config File[/u]

hibernate.cfg.xml

Code:

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> 
<hibernate-configuration>
<session-factory>
<property name="show_sql">true</property>
<property name="hibernate.connection.autocommit">true</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost/bank</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
<mapping resource="hibernate/mapping/Account.hbm.xml"/>
<mapping resource="hibernate/mapping/Owner.hbm.xml"/>
</session-factory>
</hibernate-configuration>

Mapping Files

Account.hbm.xml

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="hibernate.obj.Account" table="accounts">
   <id name="accountNumber" type="integer" column="accountNumber">
   <generator class="increment"/>
   </id>
</class>
</hibernate-mapping>

Owner.hbm.xml

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="hibernate.obj.Owner" table="owners" >
   <id name="ownerId" type="integer" column="ownerId">
   <generator class="increment"/>
   </id>
   <property name="ownerName" type="string" column="ownerName"/>
   <set name="accounts" cascade="all">
        <key column="ownerId"/>
        <one-to-many class="hibernate.obj.Account"/>
    </set>
</class>
</hibernate-mapping>

Java Classes:

Account.java

Code:

package hibernate.obj;

public class Account {
   
   private int accountNumber;

   public Account(){}
   
   public int getAccountNumber() {
      return accountNumber;
   }
   public void setAccountNumber(int accountNumber) {
      this.accountNumber = accountNumber;
   }
}

Owner.java

Code:

package hibernate.obj;

import java.util.*;

public class Owner {
   private int ownerId;
   private String ownerName;
   private Set accounts = new HashSet();
   
   public Owner(){}
   
   public int getOwnerId() {
      return ownerId;
   }
   public void setOwnerId(int ownerId) {
      this.ownerId = ownerId;
   }
   public String getOwnerName() {
      return ownerName;
   }
   public void setOwnerName(String ownerName) {
      this.ownerName = ownerName;
   }

   public Set getAccounts() {
      return accounts;
   }

   public void setAccounts(Set accounts) {
      this.accounts = accounts;
   }
}

Main Application :

MyBank.java

Code:

package hibernate.app;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import com.fast.hibernate.obj.*;
import java.util.*;

public class MyBank {

   public static void main (String args[]){
      
      Session session = null;
      
      try{
         SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
         session =sessionFactory.openSession();
         Owner owner = new Owner();
         Set accountSet= new HashSet();
         Account acc1 = new Account();
         Account acc2 = new Account();
         accountSet.add(acc1);
         accountSet.add(acc2);
         owner.setOwnerName("SomeName");
         owner.setAccounts(accountSet);
         session.saveOrUpdate(owner);   
          System.out.println("Done");
      }catch(Exception ex){
         System.out.println(ex.getMessage());
      }
      finally{
         session.flush();
          session.close(); 
      }
   }
}

DATABASE

Code:

CREATE TABLE owners
(
  ownerId INT NOT NULL default '0',
  ownerName VARCHAR(50),
  PRIMARY KEY (ownerId)
)ENGINE=InnoDB;


CREATE TABLE accounts
(
  accountNumber INT default '0',
  ownerId INT NOT NULL default '0',
  PRIMARY KEY (accountNumber),
  KEY (ownerId)
)ENGINE=InnoDB;

ALTER TABLE accounts ADD CONSTRAINT FK_ownerId 
   FOREIGN KEY (ownerId) REFERENCES owners(ownerId) ON UPDATE CASCADE ON DELETE CASCADE;

The error::

Code:

14:08:16,993  INFO Environment:456 - Hibernate 3.0rc1
14:08:16,993  INFO Environment:469 - hibernate.properties not found
14:08:16,993  INFO Environment:502 - using CGLIB reflection optimizer
14:08:16,993  INFO Environment:532 - using JDK 1.4 java.sql.Timestamp handling
14:08:16,993  INFO Configuration:1228 - configuring from resource: /hibernate.cfg.xml
14:08:16,993  INFO Configuration:1199 - Configuration resource: /hibernate.cfg.xml
14:08:17,149  INFO Configuration:439 - Mapping resource: hibernate/mapping/Account.hbm.xml
14:08:17,211  INFO HbmBinder:256 - Mapping class: hibernate.obj.Account -> accounts
14:08:17,211  INFO Configuration:439 - Mapping resource: hibernate/mapping/Owner.hbm.xml
14:08:17,227  INFO HbmBinder:256 - Mapping class: hibernate.obj.Owner -> owners
14:08:17,227  INFO Configuration:1340 - Configured SessionFactory: null
14:08:17,227  INFO Configuration:844 - processing extends queue
14:08:17,243  INFO Configuration:848 - processing collection mappings
14:08:17,243  INFO HbmBinder:1951 - Mapping collection: hibernate.obj.Owner.accounts -> accounts
14:08:17,243  INFO Configuration:857 - processing association property references
14:08:17,243  INFO Configuration:884 - processing foreign key constraints
14:08:17,290  INFO Dialect:89 - Using dialect: org.hibernate.dialect.MySQLDialect
14:08:17,290  INFO SettingsFactory:87 - Maximum outer join fetch depth: 2
14:08:17,290  INFO SettingsFactory:90 - Default batch fetch size: 1
14:08:17,290  INFO SettingsFactory:94 - Generate SQL with comments: disabled
14:08:17,290  INFO SettingsFactory:98 - Order SQL updates by primary key: disabled
14:08:17,290  INFO SettingsFactory:273 - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
14:08:17,290  INFO ASTQueryTranslatorFactory:21 - Using ASTQueryTranslatorFactory
14:08:17,290  INFO SettingsFactory:106 - Query language substitutions: {}
14:08:17,290  INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
14:08:17,290  INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
14:08:17,290  INFO DriverManagerConnectionProvider:45 - autocommit mode: true
14:08:17,305  INFO DriverManagerConnectionProvider:80 - using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost/bank
14:08:17,305  INFO DriverManagerConnectionProvider:86 - connection properties: {user=root, password=****, autocommit=true}
14:08:17,430  INFO SettingsFactory:148 - JDBC batch size: 15
14:08:17,430  INFO SettingsFactory:151 - JDBC batch updates for versioned data: disabled
14:08:17,430  INFO SettingsFactory:156 - Scrollable result sets: enabled
14:08:17,430  INFO SettingsFactory:164 - JDBC3 getGeneratedKeys(): enabled
14:08:17,430  INFO TransactionFactoryFactory:31 - Using default transaction strategy (direct JDBC transactions)
14:08:17,430  INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
14:08:17,430  INFO SettingsFactory:176 - Automatic flush during beforeCompletion(): disabled
14:08:17,430  INFO SettingsFactory:179 - Automatic session close at end of transaction: disabled
14:08:17,430  INFO SettingsFactory:260 - Cache provider: org.hibernate.cache.EhCacheProvider
14:08:17,430  INFO SettingsFactory:187 - Second-level cache: enabled
14:08:17,430  INFO SettingsFactory:192 - Optimize cache for minimal puts: disabled
14:08:17,430  INFO SettingsFactory:199 - Structured second-level cache entries: enabled
14:08:17,430  INFO SettingsFactory:203 - Query cache: disabled
14:08:17,430  INFO SettingsFactory:210 - Echoing all SQL to stdout
14:08:17,430  INFO SettingsFactory:214 - Statistics: disabled
14:08:17,430  INFO SettingsFactory:218 - Deleted entity synthetic identifier rollback: disabled
14:08:17,446  INFO SettingsFactory:232 - Default entity-mode: pojo
14:08:17,524  INFO SessionFactoryImpl:140 - building session factory
14:08:17,524 DEBUG CacheManager:191 - Creating new CacheManager with default config
14:08:17,524 DEBUG CacheManager:164 - Configuring ehcache from classpath.
14:08:17,540  WARN Configurator:126 - No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/D:/My%20Workspace/Hibernate%20Exercise/libs/ehcache-1.1.jar!/ehcache-failsafe.xml
14:08:17,540 DEBUG Configuration$DiskStore:185 - Disk Store Path: C:\DOCUME~1\xxxx\LOCALS~1\Temp\
14:08:17,727  INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
14:08:17,727  INFO SessionFactoryImpl:366 - Checking 0 named queries
Done
14:08:17,774 DEBUG SQL:292 - insert into owners (ownerName, ownerId) values (?, ?)
Hibernate: insert into owners (ownerName, ownerId) values (?, ?)
14:08:17,805 DEBUG SQL:292 - insert into accounts (accountNumber) values (?)
Hibernate: insert into accounts (accountNumber) values (?)
14:08:17,805 DEBUG SQL:292 - insert into accounts (accountNumber) values (?)
Hibernate: insert into accounts (accountNumber) values (?)
14:08:17,821  WARN JDBCExceptionReporter:57 - SQL Error: 1452, SQLState: 23000
14:08:17,821 ERROR JDBCExceptionReporter:58 - Duplicate key or integrity constraint violation message from server: "Cannot add or update a child row: a foreign key constraint fails (`bank/accounts`, CONSTRAINT `FK_ownerId` FOREIGN KEY (`ownerId`) REFERENCES `owners` (`ownerId`) ON DELETE CASCADE ON UPDATE CASCADE)"
14:08:17,821 ERROR AbstractFlushingEventListener:277 - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
   at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
   at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:161)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669)
   at com.fast.hibernate.app.MyBank.main(MyBank.java:32)
Caused by: java.sql.BatchUpdateException: Duplicate key or integrity constraint violation message from server: "Cannot add or update a child row: a foreign key constraint fails (`bank/accounts`, CONSTRAINT `FK_ownerId` FOREIGN KEY (`ownerId`) REFERENCES `owners` (`ownerId`) ON DELETE CASCADE ON UPDATE CASCADE)"
   at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1492)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:154)
   ... 6 more
Exception in thread "main" org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
   at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
   at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:161)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669)
   at hibernate.app.MyBank.main(MyBank.java:32)
Caused by: java.sql.BatchUpdateException: Duplicate key or integrity constraint violation message from server: "Cannot add or update a child row: a foreign key constraint fails (`bank/accounts`, CONSTRAINT `FK_ownerId` FOREIGN KEY (`ownerId`) REFERENCES `owners` (`ownerId`) ON DELETE CASCADE ON UPDATE CASCADE)"
   at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1492)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:154)
   ... 6 more

Note:
When the foreign key constraint is removed from the table, no error is given and the database is added with records correctly.

Thank you!

sltorrent · **Joined:** Fri May 16, 2008 2:36 am **Posts:** 2

Update:

when I add the following two lines to the hibernate.cfg.xml
it is solved.

Code:

<property name="hibernate.hbm2ddl.auto">create</property>
<property name="hibernate.connection.pool_size">10</property>

But the problem is I think a new schema is created because of this. But I still have the problem what should I do if I need to access an already created database?

mhatreakshay · **Joined:** Mon Feb 23, 2009 1:26 am **Posts:** 13

hi,
Was this problem resolved.Since I am facing a similar problem.

thank you in advance.
regards
akshay

incarnate · **Joined:** Sat Mar 07, 2009 4:24 am **Posts:** 10

I don't know if it will help you solve your problem, but since it is bi-directional Many-To-One mapping, it should: try including Owner field in your Account class and map it as <many-to-many ...>. The other side may be mapped with inverse="true".

incarnate · **Joined:** Sat Mar 07, 2009 4:24 am **Posts:** 10

Example mappings should look like this:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="hibernate.obj.Account" table="accounts">
   <id name="accountNumber" type="integer" column="accountNumber">
   <generator class="increment"/>
   </id>
   <many-to-one name="owner" column="ownerId"/>
</class>

<class name="hibernate.obj.Owner" table="owners" >
   <id name="ownerId" type="integer" column="ownerId">
   <generator class="increment"/>
   </id>
   <property name="ownerName" type="string" column="ownerName"/>
   <set table="accounts" name="accounts" cascade="all">
        <key column="ownerId"/>
        <one-to-many class="hibernate.obj.Account"/>
    </set>
</class>
</hibernate-mapping> 