java.sql.SQLException: Cannot add or update a child row

skipskipping · **Joined:** Tue Aug 24, 2010 7:24 am **Posts:** 9

Hi

Could someone help me understand that why am I getting the following error when I am trying to persist ServiceProvider entity?

Code:

java.sql.SQLException: Cannot add or update a child row: a foreign key constraint fails (`springmvc/businesslocation`, CONSTRAINT `FK5CB747B5A26ED80E` FOREIGN KEY (`state_id`) REFERENCES `state` (`id`))
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
   at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1129)
   at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:681)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1368)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1283)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1268)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
   at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:72)
   at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:33)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2163)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2643)
   at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
   at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298)
   at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
   at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
   at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:511)
   at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:503)
   at org.hibernate.engine.CascadingAction$5.cascade(CascadingAction.java:218)
   at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:268)
   at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:216)
   at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:169)
   at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:296)
   at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:242)
   at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:219)
   at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:169)
   at org.hibernate.engine.Cascade.cascade(Cascade.java:130)
   at org.hibernate.event.def.AbstractSaveEventListener.cascadeAfterSave(AbstractSaveEventListener.java:456)
   at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:334)
   at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
   at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
   at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
   at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
   at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:539)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:527)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
   at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:642)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
   at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:639)
   at com.inception.dao.ServiceProviderDAOImpl.save(ServiceProviderDAOImpl.java:23)
   at com.inception.service.ServiceProviderServiceImpl.addServiceProvider(ServiceProviderServiceImpl.java:21)
   at com.inception.web.RegisterServiceProviderController.processFinish(RegisterServiceProviderController.java:68)
   at org.springframework.web.servlet.mvc.AbstractWizardFormController.validatePagesAndFinish(AbstractWizardFormController.java:642)
   at org.springframework.web.servlet.mvc.AbstractWizardFormController.processFormSubmission(AbstractWizardFormController.java:492)
   at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:265)
   at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
   at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
   at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
   at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
   at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:476)
   at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:441)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
   at java.lang.Thread.run(Thread.java:619)

The situation is like this: a ServiceProvider has a List of BusinessLocation and a State has a list of BusinessLocation.

Following annotated classes that I want to be persisted:
ServiceProvider.java

Code:

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class ServiceProvider implements Serializable{
   
   private Long id;   
   private Set<BusinessLocation> businessLocations = new HashSet<BusinessLocation>();

   @Id
   @GeneratedValue
   public Long getId() {
      return id;
   }
   public void setId(Long id) {
      this.id = id;
   }

   @OneToMany(mappedBy="serviceProvider", targetEntity=BusinessLocation.class, cascade=CascadeType.ALL, fetch=FetchType.EAGER)
   public Set<BusinessLocation> getBusinessLocations() {
      return businessLocations;
   }

   public void setBusinessLocations(Set<BusinessLocation> businessLocations) {
      this.businessLocations = businessLocations;
   }

}

BusinessLocation.java

Code:

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

@Entity
public class BusinessLocation implements Serializable{
   
   private Long id;
   private String address;
   private String city;
   private State state;
   private String pincode;
   private ServiceProvider serviceProvider;
   
   public BusinessLocation() {      
   }
   
   @Id
   @GeneratedValue
   public Long getId() {
      return id;
   }
   public void setId(Long id) {
      this.id = id;
   }
   public String getAddress() {
      return address;
   }
   public void setAddress(String address) {
      this.address = address;
   }
   public String getCity() {
      return city;
   }
   public void setCity(String city) {
      this.city = city;
   }
   
   @ManyToOne
   @JoinColumn(name="state_id")
   public State getState() {
      return state;
   }
   public void setState(State state) {
      this.state = state;
   }   
   public void setPincode(String pincode) {
      this.pincode = pincode;
   }

   public String getPincode() {
      return pincode;
   }
   
   @ManyToOne
   @JoinColumn(name="serviceProvider_id")
   public ServiceProvider getServiceProvider() {
      return serviceProvider;
   }
   public void setServiceProvider(ServiceProvider serviceProvider) {
      this.serviceProvider = serviceProvider;
   }
}

State.java

Code:

@Entity
public class State implements Serializable {
   
   private Long id;
   private String name;
   private List<BusinessLocation> businessLocations;
   
   @Id
   @GeneratedValue   
   public Long getId() {
      return id;
   }
   public void setId(Long id) {
      this.id = id;
   }
   public void setName(String name) {
      this.name = name;
   }
   
   public String getName() {
      return name;
   }

   
   @OneToMany(mappedBy="state", targetEntity=BusinessLocation.class, cascade=CascadeType.ALL, fetch=FetchType.EAGER)
   public List<BusinessLocation> getBusinessLocations() {
      return businessLocations;
   }
   public void setBusinessLocations(List<BusinessLocation> businessLocations) {
      this.businessLocations = businessLocations;
   }   

}

Looking forward to someone waking me up of the blunder I might be commiting here.
Thanks.

saltnlight5 · **Posted:** Tue Aug 24, 2010 9:31 am

The SQL error is telling you that you are trying to insert "location" records with a state_id column value that is NOT in the "state" table, therefore violating the constraints.

To have this happened in Hibernate, my best guess is that you have detached State object, and some how changed the ID value afterward, then trying to set in a Location.setState(), which then you trying to persist through the a ServiceProvider object via cascading. Can't tell without seeing your code.

Try removing the State.setId() method, and don't set this value on your own. Let hibernate load/set it since it's a generated ID anyway. User app have no business in setting this value. This should be rule of thumb for all general entity ID property anyway.

skipskipping · **Joined:** Tue Aug 24, 2010 7:24 am **Posts:** 9

Hello saltnlight5, you're a life saver :)

I did was overriding the auto-generated id generated by Hibernate thinking that it wouldnt mind other int type values being set on them, I was doing it through a drop-down select tag.

I did leave Ids created by Hibernate alone and have another attribute set on my State.java entity and now things are making its way into data base but the attributes of State.java entity. So the error I am getting now is..

Code:

org.springframework.dao.InvalidDataAccessApiUsageException: object references an unsaved transient instance - save the transient instance before flushing: com.inception.domain.State; nested exception is org.hibernate.TransientObjectException: object references an unsaved transient instance - save the transient instance before flushing: com.inception.domain.State
   at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:634)
   at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:378)
   at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:639)
   at com.inception.dao.ServiceProviderDAOImpl.save(ServiceProviderDAOImpl.java:23)
   at com.inception.service.ServiceProviderServiceImpl.addServiceProvider(ServiceProviderServiceImpl.java:21)
   at com.inception.web.RegisterServiceProviderController.processFinish(RegisterServiceProviderController.java:68)
   at org.springframework.web.servlet.mvc.AbstractWizardFormController.validatePagesAndFinish(AbstractWizardFormController.java:642)
   at org.springframework.web.servlet.mvc.AbstractWizardFormController.processFormSubmission(AbstractWizardFormController.java:492)
   at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:265)
   at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
   at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
   at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
   at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
   at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:476)
   at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:441)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
   at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.TransientObjectException: object references an unsaved transient instance - save the transient instance before flushing: com.inception.domain.State
   at org.hibernate.engine.ForeignKeys.getEntityIdentifierIfNotUnsaved(ForeignKeys.java:219)
   at org.hibernate.type.EntityType.getIdentifier(EntityType.java:397)
   at org.hibernate.type.ManyToOneType.isDirty(ManyToOneType.java:242)
   at org.hibernate.type.TypeFactory.findDirty(TypeFactory.java:596)
   at org.hibernate.persister.entity.AbstractEntityPersister.findDirty(AbstractEntityPersister.java:3128)
   at org.hibernate.event.def.DefaultFlushEntityEventListener.dirtyCheck(DefaultFlushEntityEventListener.java:478)
   at org.hibernate.event.def.DefaultFlushEntityEventListener.isUpdateNecessary(DefaultFlushEntityEventListener.java:204)
   at org.hibernate.event.def.DefaultFlushEntityEventListener.onFlushEntity(DefaultFlushEntityEventListener.java:127)
   at org.hibernate.event.def.AbstractFlushingEventListener.flushEntities(AbstractFlushingEventListener.java:196)
   at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:76)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:26)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1004)
   at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:374)
   ... 27 more

Here is the code for my new State.java entity..

Code:

import java.io.Serializable;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class State implements Serializable {
   
   private Long id;
   private String abbreviatedName;
   private String name;
   private List<BusinessLocation> businessLocations;
   
   @Id
   @GeneratedValue   
   public Long getId() {
      return id;
   }
   public void setId(Long id) {
      this.id = id;
   }
   public String getAbbreviatedName() {
      return abbreviatedName;
   }
   public void setAbbreviatedName(String abbreviatedName) {
      this.abbreviatedName = abbreviatedName;
   }
   public void setName(String name) {
      this.name = name;
   }
   
   public String getName() {
      return name;
   }

   
   @OneToMany(mappedBy="state", targetEntity=BusinessLocation.class, cascade=CascadeType.ALL, fetch=FetchType.EAGER)
   public List<BusinessLocation> getBusinessLocations() {
      return businessLocations;
   }
   public void setBusinessLocations(List<BusinessLocation> businessLocations) {
      this.businessLocations = businessLocations;
   }   

}

Below is the code for my Service and DAO that I am using to persist ServiceProvider.java entity.
ServiceProviderServiceImpl.java

Code:

import java.util.List;

import org.springframework.transaction.annotation.Transactional;

import com.inception.bean.ExtendedServiceProvider;
import com.inception.dao.ServiceProviderDAO;
import com.inception.domain.BusinessLocation;
import com.inception.domain.ServiceProvider;

@Transactional
public class ServiceProviderServiceImpl implements ServiceProviderService{
   
   private ServiceProviderDAO serviceProviderDAO;
   public void setServiceProviderDAO(ServiceProviderDAO serviceProviderDAO) {
      this.serviceProviderDAO = serviceProviderDAO;
   }

   public void addServiceProvider(ServiceProvider serviceProvider) {
      serviceProviderDAO.save(serviceProvider);
      for(BusinessLocation businessLocation: serviceProvider.getBusinessLocations()) {
         businessLocation.setServiceProvider(serviceProvider);
         serviceProviderDAO.save(businessLocation);
      }
      
   }

}

ServiceProviderDAOImpl.java

Code:

import java.util.List;

import org.springframework.orm.hibernate3.HibernateTemplate;

import com.inception.bean.ExtendedServiceProvider;
import com.inception.domain.BusinessLocation;
import com.inception.domain.ServiceProvider;

public class ServiceProviderDAOImpl implements ServiceProviderDAO{
   
   private HibernateTemplate hibernateTemplate;
   public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
      this.hibernateTemplate = hibernateTemplate;
   }

   public void save(ServiceProvider serviceProvider) {
      hibernateTemplate.save(serviceProvider);      
   }

   public void save(BusinessLocation businessLocation) {
      hibernateTemplate.save(businessLocation);      
   }

}

Could you help me understand the blunder I am commiting now?
Thanks

skipskipping · **Joined:** Tue Aug 24, 2010 7:24 am **Posts:** 9

Hi saltnlight5
I've had this problem resolved by setting cascade property to (cascade=CascadeType.ALL) as below in the entity BusinessLocation.java.

Code:

@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="state_id")
public State getState() {
    return state;
}

Many thanks :)