Integrity constraint violated just when I commit the transaction

Question

I'm using Hibernate 4.0 Final and ojdbc6 to develop my web application. Everything is ok except when I try to insert a new parent/child relationship. First of all, these are the entities:

@Entity
@Table(name = "EMPLOYEE")
public class Employee implements Serializable, Cloneable {
    @Id
    @SequenceGenerator(name = "seq", sequenceName = "P_SEQ")
    @GeneratedValue(generator = "seq")
    @Column(name = "ID_EMPLOYEE")
    private long idEmployee;
    ......
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "employee", orphanRemoval = true)
    @Fetch(FetchMode.SELECT)
    @BatchSize(size = 10)
    private Set<Address> addresses;
    ......
}

@Entity
@Table(name = "ADDRESS")
public class Address implements Serializable, Cloneable, Comparable {    
    @Id
    @SequenceGenerator(name = "seq", sequenceName = "P_SEQ")
    @GeneratedValue(generator = "seq")
    @Column(name = "ID_ADDRESS")
    private long idAddress;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_EMPLOYEE")
    private Employee employee;
    .......
}

Let's see these two scenarios:

1. An employee already exists and I try to add a new address to it --> It works properly.
2. An employee doesn't exist yet and I try to create a new one. Two diferent cases:
   
   • a) I only insert an employee (with no address) --> It works properly.
   • b) I insert and employee and its address --> It fails. I have to say that this must be an atomic transaction. I mean, I need to create (save) an employee and one address for it at once.

This is the transaction handler:

public static void save(Employee employee) throws HibernateException, Exception {
    Session session = HibernateUtil.getCurrentSession();
    session.beginTransaction();
    try {
        session.saveOrUpdate(employee);
    } catch (Exception ex) {
        session.refresh(employee);
        HibernateUtil.closeSession();
        throw ex;
    }
    HibernateUtil.commitTransaction();
}
public static void commitTransaction() throws Exception {
    Transaction tx = getSessionFactory().getCurrentSession().getTransaction();
    try {
        if (tx != null && !tx.wasCommitted() && !tx.wasRolledBack()) {
            tx.commit();
        }
    } catch (Exception ex) {
        tx.rollback();
        throw ex;
    } finally {
        closeSession();
    }
}

As you can imagine, the 2.b case is the one I'm concerned about. I've debugged the transaction and this is what I get when I call the save() method (which is in a DAO class):

1. The session.saveOrUpdate(employee) method executes successfully (I can check that the saved employee has an address). Besides, its idEmployee is properly set (taken from the sequence) and the address is binded to the employee and has a valid idAddress (taken from the sequence, as well).
2. During the execution of the commitTransaction() method I get an org.hibernate.exception.ConstraintViolationException, although both idEmployee and idAddress has been previously properly set.

In short, the Exception come out just during the commit process. It's like if this began commiting the child (address) instead the parent (employee).

What am I doing wrong? Can anybody help me? Thanks in advance.

UPDATED. Above, you can see the main parts of the two classes involved in the problem. Now, here you are the methods that call them besides the trace of exception. They are in the order they are called.

Belonging to the DataBacking class:

public void save(ActionEvent event) {
    try {
        EmployeeDAO.save(selectedEmployee);
        newEmployee();  //reset the employee and its collections
    } catch (ConstraintViolationException ex) {
        Utilities.addFacesMessage(FacesMessage.SEVERITY_WARN, ex.getMessage(), "");
    } catch (Exception ex) {
        Utilities.log("error", ex.getCause().toString());
        Utilities.addFacesMessage(FacesMessage.SEVERITY_WARN, ex.getMessage(), "");
    }
}

Belonging to the EmployeeDAO class:

public static void save(Employee employee) throws HibernateException, Exception {
    Session session = HibernateUtil.getCurrentSession();
    session.beginTransaction();
    try {
        session.saveOrUpdate(employee);
    } catch (Exception ex) {
        session.refresh(employee);
        HibernateUtil.closeSession();
        throw ex;
    }
    HibernateUtil.commitTransaction();
}

Belonging to the HibernateUtil class:

public static void commitTransaction() throws Exception {
    Transaction tx = getSessionFactory().getCurrentSession().getTransaction();
    try {
        if (tx != null && !tx.wasCommitted() && !tx.wasRolledBack()) {
            tx.commit();
        }
    } catch (Exception ex) {
        tx.rollback();
        throw ex;
    } finally {
        closeSession();
    }
}

Right after that the EmployeeDAO.save() method calls to session.SaveOrUpdate(employee), I get the following trace:

2013-03-12 07:22:55,958 [DEBUG, org.hibernate.internal.SessionImpl] Opened session at timestamp: 13630693759 
2013-03-12 07:22:57,584 [DEBUG, org.hibernate.engine.transaction.spi.AbstractTransactionImpl] begin 
2013-03-12 07:22:57,585 [DEBUG, org.hibernate.engine.jdbc.internal.LogicalConnectionImpl] Obtaining JDBC connection 
2013-03-12 07:22:57,586 [DEBUG, org.hibernate.engine.jdbc.internal.LogicalConnectionImpl] Obtained JDBC connection 
2013-03-12 07:22:57,587 [DEBUG, org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction] initial autocommit status: true 
2013-03-12 07:22:57,587 [DEBUG, org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction] disabling autocommit 
2013-03-12 07:23:00,285 [DEBUG, org.hibernate.SQL] 
    select
        P_SEQ.nextval 
    from
        dual 
2013-03-12 07:23:00,361 [DEBUG, org.hibernate.id.SequenceGenerator] Sequence identifier generated: BasicHolder[java.lang.Long[5665]] 
2013-03-12 07:23:00,365 [DEBUG, org.hibernate.event.internal.AbstractSaveEventListener] Generated identifier: 5665, using strategy: org.hibernate.id.SequenceGenerator 
2013-03-12 07:23:00,411 [DEBUG, org.hibernate.SQL] 
    select
        P_SEQ.nextval 
    from
        dual 
2013-03-12 07:23:00,417 [DEBUG, org.hibernate.id.SequenceGenerator] Sequence identifier generated: BasicHolder[java.lang.Long[5666]] 
2013-03-12 07:23:00,421 [DEBUG, org.hibernate.event.internal.AbstractSaveEventListener] Generated identifier: 5666, using strategy: org.hibernate.id.SequenceGenerator 

And after the commit has been invoked:

2013-03-12 07:24:53,288 [DEBUG, org.hibernate.engine.transaction.spi.AbstractTransactionImpl] committing 
2013-03-12 07:24:53,336 [DEBUG, org.hibernate.event.internal.AbstractFlushingEventListener] Processing flush-time cascades 
2013-03-12 07:24:53,343 [DEBUG, org.hibernate.event.internal.AbstractFlushingEventListener] Dirty checking collections 
2013-03-12 07:24:53,403 [DEBUG, org.hibernate.engine.internal.Collections] Collection found: [org.svq.pol.gesper.bean.Employee.addresses#5665], was: [<unreferenced>] (initialized) 
2013-03-12 07:24:53,439 [DEBUG, org.hibernate.event.internal.AbstractFlushingEventListener] Flushed: 2 insertions, 0 updates, 0 deletions to 2 objects 
2013-03-12 07:24:53,440 [DEBUG, org.hibernate.event.internal.AbstractFlushingEventListener] Flushed: 1 (re)creations, 0 updates, 0 removals to 1 collections 
2013-03-12 07:24:53,453 [DEBUG, org.hibernate.internal.util.EntityPrinter] Listing entities: 
2013-03-12 07:24:53,454 [DEBUG, org.hibernate.internal.util.EntityPrinter] org.svq.pol.gesper.bean.Address{address=fasdf, pc=, city=fadsf, idAddress=5666, operator=xxxxx, province=, telef2=, movDate=Tue Mar 12 07:21:15 CET 2013, telef1=, employee=org.svq.pol.gesper.bean.Employee#5665, version=0} 
2013-03-12 07:24:53,456 [DEBUG, org.hibernate.internal.util.EntityPrinter] org.svq.pol.gesper.bean.Employee{surname=fadsf, user=null, dob=Tue Jan 01 00:00:00 CET 1980, address=[org.svq.pol.gesper.bean.Address#5666], pob=fadsf, operator=xxxxx, movDate=Tue Mar 12 07:21:15 CET 2013, version=0, name=fasdf, gender=H, idEmployee=5665, id=12345678} 
2013-03-12 07:24:53,572 [DEBUG, org.hibernate.SQL] 
    insert 
    into
        EMPLOYEE    
    (SURNAME, ID, MOV_DATE, DOB, GENDER, POB, OPERATOR, USER, VERSION, ID_EMPLOYEE)
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
2013-03-12 07:24:53,793 [DEBUG, org.hibernate.SQL] 
    insert 
    into
        ADDRESS
    (pc, address, MOV_DATE, OPERATOR, ID_EMPLOYEE, city, province, telef_1, telef_2, version, ID_ADDRESS)        
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
2013-03-12 07:24:53,943 [DEBUG, org.hibernate.engine.jdbc.spi.SqlExceptionHelper] ORA-02291: integrity constraint (PERPLADM.ADDRESS_EMPLOYEE_FK) violated - parent key not found
 [n/a] 
java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (PERPLADM.ADDRESS_EMPLOYEE_FK) violated - parent key not found

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at $Proxy46.executeUpdate(Unknown Source)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:56)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2849)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3290)
    at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:80)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:186)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:323)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1081)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:315)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175)
    at org.svq.pol.gesper.utility.HibernateUtil.commitTransaction(HibernateUtil.java:57)
    at org.svq.pol.gesper.dao.EmployeeDAO.save(EmployeeDAO.java:110)
    at org.svq.pol.gesper.backing.DataBacking.save(DataBacking.java:498)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
    at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:148)
    at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
    at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:769)
    at javax.faces.component.UICommand.broadcast(UICommand.java:300)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:409)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:964)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:722)
2013-03-12 07:24:54,027 [WARN, org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 2291, SQLState: 23000 
2013-03-12 07:24:54,027 [ERROR, org.hibernate.engine.jdbc.spi.SqlExceptionHelper] ORA-02291: integrity constraint (PERPLADM.ADDRESS_EMPLOYEE_FK) violated - parent key not found

Equiprobable

Show us the code, and paste the complete stack trace of the exception. – Shaunshauna

I've updated my question with the code involved and the stack trace of the exception. Sorry the delay. – Equiprobable

I've eventually found a partial answer for my own question which I added at the end. – Equiprobable

@Julio: Could you please let me know how did you check that :new.ID value is NULL? As even I'm facing this issue. First I was working with MySQL and just for testing purpose I switch to Oracle10g and this issue prompt up. So even I think there is some issue related to the sequence which Hibernate generate. Could you please let me know how did you resolve it? – Excel

@asifsid88. First, sorry for the delay. I usually work with Oracle 11g, so my solution works on Oracle although I guess is valid for MySQL. What I did was to put an IF clause before getting a value from the sequence. This way I could decide what to do. If the new value (:new.ID) was not null meant that Hibernate was operating and assigning its own value so I didn't have to do anything. Else, the trigger had to assign the value from the sequence. I could give you the exact code for my trigger, but you'll have to wait until next Monday. – Equiprobable

@Julio: Thank you so much for your reply :) I'll manage that way. Thanks a lot for your help – Excel

I was faced with the same issue on Oracle 10g as well. All my triggers had the if statement, except one, which blew up all right. – Kerchief

Answer 1

After few days struggling with this, I eventually found what the problem was and, obviously, the solution. As far as I know, the mapping shown above is correct (at least, the application works properly). The only problem was in the database, where there was a trigger for inserting the sequence. This way, everytime I tried to insert an employee (and its address), Hibernate gave me two sequence numbers (one for the parent and another for the child), which were correctly set. However, at the precise moment of doing the commit, Oracle gave me two more sequence numbers, and this time incorrectly set, I mean, the foreign key didn't match the parent's primary key.

But as I need triggers, I have to do one more step: I have to modify these ones so they check whether the :new.ID value is NULL or not. If it is, it means that the trigger has been triggered from outside Hibernate (i.e. another application), and therefore I take the nextval from the sequence, otherwise I leave the value that comes from Hibernate.

Equiprobable

Excellent point! Just I don't understand why the before insert trigger fires two times ?! – Radiotelephone

Because DBMS (Oracle) is independent of Hibernate. Using just Hibernate, you usually get the trigger fired only one time because Hibernate is in charge of inserting, modifying and deleting records. In my case, I needed a trigger to do the role of Hibernate for those records manipulations done without Hibernate. – Equiprobable