0

Hibernate V.4.1.8 Final

Spring: 3.1.3 Release

Scenario: During customer registration process, user will fill complete from. He is allowed to save form multiple times before finalizing form and clicks submit to finalize contents. Application will create customer id only after final submit. To achieve this functionality, I am creating 2 tables based on same POJO. Ref: Mapping same POJO to more than one table

Problem: While Saving data into Customer Table. hibernate inserts records into Address table and directly into family table but ignoring customer table. Hence throwing exception:Cannot add or update a child row: a foreign key constraint fails

Please see sql sequence while custDetail object into backup table and in customer table.

Inserting into backup table

Hibernate: insert into mypack.address_bk (address1, address2, city, emailAddress, faxNo, landLineNo, mobileNo, pincode, state, verified) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into mypack.address_bk (address1, address2, city, emailAddress, faxNo, landLineNo, mobileNo, pincode, state, verified) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into mypack.address_bk (address1, address2, city, emailAddress, faxNo, landLineNo, mobileNo, pincode, state, verified) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into mypack.customer_bk (version, custId, custBirthDate, custEducation, custFirstName, custGender, custLastName, custMiddleName, custTitle, exServiceManFlag, fatherFullName, handicappedFlag, motherFullName, staffFlag, status, userId, bankCode, branchCode, customerPhoto, currentAddress, permanentAddress, officeAddress) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into mypack.family_bk (custId, memberName, relation, birthDate, gender, occupation) values (?, ?, ?, ?, ?, ?)

Inserting into customer Table

Hibernate: insert into mypack.address (address1, address2, city, emailAddress, faxNo, landLineNo, mobileNo, pincode, state, verified) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into mypack.address (address1, address2, city, emailAddress, faxNo, landLineNo, mobileNo, pincode, state, verified) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into mypack.address (address1, address2, city, emailAddress, faxNo, landLineNo, mobileNo, pincode, state, verified) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into mypack.family (custId, memberName, relation, birthDate, gender, occupation) values (?, ?, ?, ?, ?, ?)
SQL Error: 1452, SQLState: 23000 Cannot add or update a child row: a foreign key constraint fails

Table Defination:

<class entity-name="com.boss.mypack.model.Customer" lazy="true"
    name="com.boss.mypack.model.Customer" polymorphism="explicit"
    schema="mypack" table="customer">
    <id length="15" name="custId" type="string">
        <column length="20" name="custId" />
        <generator class="assigned" />
    </id>
    <version column="version" generated="never" name="version"
        type="integer" unsaved-value="null" />
    <property generated="never" lazy="false" name="tempId" type="java.lang.Integer"> 
        <column name="tempId" not-null="false" /> 
    </property>
    <many-to-one cascade="all" class="com.boss.mypack.model.Address"
        entity-name="com.boss.mypack.model.Address" fetch="select" lazy="false"
        name="OfficeAddress">
        <column name="officeAddress" />
    </many-to-one>
    <set cascade="all" fetch="select" inverse="true" lazy="true" name="family"
        sort="unsorted" table="family">
        <key>
            <column length="20" name="custId" />
        </key>
        <one-to-many class="com.boss.mypack.model.Family"
            entity-name="com.boss.mypack.model.Family" />
    </set>
</class>

Back Table:

<class entity-name="bkCustomer" lazy="true"
    name="com.boss.mypack.model.Customer" polymorphism="explicit"
    schema="mypack" table="customer_bk">
    <id length="4" name="tempId" type="java.lang.Integer">
        <column length="20" name="tempId" />
        <generator class="identity" />
    </id>
    <version column="version" generated="never" name="version"
        type="integer" unsaved-value="null" />
    <property generated="never" lazy="false" name="custId" type="string">
        <column length="20" name="custId" />
    </property>
    <many-to-one cascade="all" class="com.boss.mypack.model.Address"
        entity-name="bkAddress" fetch="select" lazy="false" name="OfficeAddress">
        <column name="officeAddress" />
    </many-to-one>
    <set cascade="all" fetch="select" inverse="true" lazy="true" name="family"
        sort="unsorted" table="bkfamily">
        <key>
            <column length="20" name="custId" />
        </key>
        <one-to-many class="com.boss.mypack.model.Family"
            entity-name="bkFamily" />
    </set>
</class>

Save method:

public void saveCustomer(Customer custDetails) {
    Session session;
    session = getSessionFactory().getCurrentSession();  
    session.beginTransaction();

    try {
        session.saveOrUpdate("bkCustomer", custDetails);
        session.getTransaction().commit();
    } catch (Exception e) {
        session.getTransaction().rollback();
        e.printStackTrace();
    }
}

Approve Customer Method

@Override
public void approveCustomer(Customer custDetails) {
    Session session = null;
    // Saving Customer details to Database
    try {
        //TODO:Need to this in single session, In failure, every changes should get rolled back.
        //Removing from backup Table
        session = getSessionFactory().getCurrentSession();  
        session.beginTransaction();
        session.delete("bkCustomer", custDetails);
        session.getTransaction().commit();
        System.out.println("**** Record Deleted ****");

        session = getSessionFactory().getCurrentSession();  
        session.beginTransaction();

        // check to see whether this is new customer.
        if (custDetails.getCustId() == null) {
            // New Customer - Generate Customer ID
            custDetails.setTempId(null);
            custDetails.setCustId(getNextCustomerCode(session));

            // Clear all Auto Generated IDs
            custDetails.getCurrentAddress().setAddressId(null);
            custDetails.getOfficeAddress().setAddressId(null);
            custDetails.getPermanentAddress().setAddressId(null);
            // custDetails.getPhotoMaster().setPhotoId(0);

            Set<Family> familyMembers = custDetails.getFamily();

            for (Family member : familyMembers) {
                member.setMemberId(null);
            }
        } else {
            System.out.println("**** This customer is already present in Database");
        }

        custDetails.setStatus("A");

        session.saveOrUpdate("com.boss.mypack.model.Customer", custDetails);

        //Following works properly, updates Backup tables
        //session.saveOrUpdate("bkCustomer", custDetails);

        session.getTransaction().commit();

        // Saving Customer details to LDAP.
        // ldapDAO.saveOrUpdate(custDetails);
        // session.getTransaction().commit();
    } catch (Exception e) {
        session.getTransaction().rollback();
        e.printStackTrace();
    }
}

I am not getting any exception on customer table. I have tried to detach object using session.clear/flash/evict/merge/replicate etc, Using DOZER but no progress. Even I have tried to remove cascade option.

I am not sure where it is going wrong. Please help.

Regards,

Shirish

Shirish Bathe
  • 627
  • 9
  • 22

1 Answers1

0

Are your constraints immediate? Make them deferred. That way, hibernate can write all tables without getting a constraint violation before the transaction ends.

If you're working with MySQL, you can't defer constraints to the end of the transaction. You then have to make sure you're always inserting a consistent state into the db. For you that'd mean rewriting some of the code so that you're effectively issueing two saves, one that saves only the customer and one that saves the customer with the family attribute.

Anyway, it's interesting to find out why Hibernate tried to insert the family first, while on the bk tables the order of the inserts is as expected. Just a guess: try to move the statement

 custDetails.setStatus("A");

above the if block

// check to see whether this is new customer.
    if (custDetails.getCustId() == null) {
        // New Customer - Generate Customer ID
        custDetails.setTempId(null);
        custDetails.setCustId(getNextCustomerCode(session));

        // Clear all Auto Generated IDs
        custDetails.getCurrentAddress().setAddressId(null);
        custDetails.getOfficeAddress().setAddressId(null);
        custDetails.getPer...

That way, Hibernate may reorder the inserts, because family is the last edited entity, and your code will work. Anyway, that'd rely heavily on Hibernate internals. I recommend using two saves, as explained before.

sorencito
  • 2,517
  • 20
  • 21