Backup a database using JPA

Question

I'm working on a database backup engine using JPA. Everything is working fine but I'm having issues figuring this issue. I know is related to how I defined the Entities.

First here's a brief on how the system works:

1. Create a Derby database instance in a selected directory
2. Connect to the source database and scrol thru the tables fetching all entities and copying them over to the destination database (the Derby instance)
3. Zip the destination database and store the zip.

To restore basically unzip the database and make the backup the source and the live db the target (after deleting all entities in the new target database)

Everything is working fine until I noticed I was leaving out a table from the backup. When I added it it's complaining with the following:

    javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`xinco`.`xinco_core_ace`, CONSTRAINT `FK_xinco_core_ace_xinco_core_user_id` FOREIGN KEY (`xinco_core_user_id`) REFERENCES `xinco_core_user` (`id`))
Error Code: 1451
Call: DELETE FROM xinco_core_user WHERE (id = ?)
        bind => [1]

Here's the Entity I'm copying when I get the error:

    package com.bluecubs.xinco.core.server.persistence;

import com.bluecubs.xinco.core.server.AuditedEntityListener;
import com.bluecubs.xinco.core.server.XincoAuditedObject;
import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.CascadeType;

/**
 *
 * @author Javier A. Ortiz Bultrón <[email protected]>
 */
@Entity
@Table(name = "xinco_core_ace")
@EntityListeners(AuditedEntityListener.class)
@NamedQueries({
    @NamedQuery(name = "XincoCoreAce.findAll",
    query = "SELECT x FROM XincoCoreAce x"),
    @NamedQuery(name = "XincoCoreAce.findById",
    query = "SELECT x FROM XincoCoreAce x WHERE x.id = :id"),
    @NamedQuery(name = "XincoCoreAce.findByReadPermission",
    query = "SELECT x FROM XincoCoreAce x WHERE x.readPermission = :readPermission"),
    @NamedQuery(name = "XincoCoreAce.findByWritePermission",
    query = "SELECT x FROM XincoCoreAce x WHERE x.writePermission = :writePermission"),
    @NamedQuery(name = "XincoCoreAce.findByExecutePermission",
    query = "SELECT x FROM XincoCoreAce x WHERE x.executePermission = :executePermission"),
    @NamedQuery(name = "XincoCoreAce.findByAdminPermission",
    query = "SELECT x FROM XincoCoreAce x WHERE x.adminPermission = :adminPermission")})
public class XincoCoreAce extends XincoAuditedObject implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    private Integer id;
    @Basic(optional = false)
    @Column(name = "read_permission", nullable = false)
    private boolean readPermission;
    @Basic(optional = false)
    @Column(name = "write_permission", nullable = false)
    private boolean writePermission;
    @Basic(optional = false)
    @Column(name = "execute_permission", nullable = false)
    private boolean executePermission;
    @Basic(optional = false)
    @Column(name = "admin_permission", nullable = false)
    private boolean adminPermission;
    @JoinColumn(name = "xinco_core_data_id", referencedColumnName = "id", nullable = true)
    @ManyToOne(fetch = FetchType.LAZY)
    private XincoCoreData xincoCoreDataId;
    @JoinColumn(name = "xinco_core_group_id", referencedColumnName = "id", nullable = true)
    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
    private XincoCoreGroup xincoCoreGroupId;
    @JoinColumn(name = "xinco_core_node_id", referencedColumnName = "id", nullable = true)
    @ManyToOne(fetch = FetchType.LAZY)
    private XincoCoreNode xincoCoreNodeId;
    @JoinColumn(name = "xinco_core_user_id", referencedColumnName = "id", nullable = true)
    @ManyToOne(fetch = FetchType.LAZY)
    private XincoCoreUser xincoCoreUserId;

    public XincoCoreAce() {
    }

    public XincoCoreAce(Integer id) {
        this.id = id;
    }

    public XincoCoreAce(Integer id, boolean readPermission, boolean writePermission, boolean executePermission, boolean adminPermission) {
        this.id = id;
        this.readPermission = readPermission;
        this.writePermission = writePermission;
        this.executePermission = executePermission;
        this.adminPermission = adminPermission;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public boolean getReadPermission() {
        return readPermission;
    }

    public void setReadPermission(boolean readPermission) {
        this.readPermission = readPermission;
    }

    public boolean getWritePermission() {
        return writePermission;
    }

    public void setWritePermission(boolean writePermission) {
        this.writePermission = writePermission;
    }

    public boolean getExecutePermission() {
        return executePermission;
    }

    public void setExecutePermission(boolean executePermission) {
        this.executePermission = executePermission;
    }

    public boolean getAdminPermission() {
        return adminPermission;
    }

    public void setAdminPermission(boolean adminPermission) {
        this.adminPermission = adminPermission;
    }

    public XincoCoreData getXincoCoreDataId() {
        return xincoCoreDataId;
    }

    public void setXincoCoreDataId(XincoCoreData xincoCoreDataId) {
        this.xincoCoreDataId = xincoCoreDataId;
    }

    public XincoCoreGroup getXincoCoreGroupId() {
        return xincoCoreGroupId;
    }

    public void setXincoCoreGroupId(XincoCoreGroup xincoCoreGroupId) {
        this.xincoCoreGroupId = xincoCoreGroupId;
    }

    public XincoCoreNode getXincoCoreNodeId() {
        return xincoCoreNodeId;
    }

    public void setXincoCoreNodeId(XincoCoreNode xincoCoreNodeId) {
        this.xincoCoreNodeId = xincoCoreNodeId;
    }

    public XincoCoreUser getXincoCoreUserId() {
        return xincoCoreUserId;
    }

    public void setXincoCoreUserId(XincoCoreUser xincoCoreUserId) {
        this.xincoCoreUserId = xincoCoreUserId;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof XincoCoreAce)) {
            return false;
        }
        XincoCoreAce other = (XincoCoreAce) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "com.bluecubs.xinco.core.server.persistence.XincoCoreAce[id=" + id + "]";
    }
}

I tried changing the cascade type for the xincoCoreDataId but it didn't work. I'll keep working on it but any feedback is welcomed.

Edit: Seems I copied the wrong exception. Sorry.

Here's the actual exception

    javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL091217135308520' defined on 'XINCO_CORE_GROUP'.
Error Code: 20000
Call: INSERT INTO xinco_core_group (id, designation, status_number) VALUES (?, ?, ?)
        bind => [1, general.group.admin, 1]

Answer 1

What I see here is that the database is preventing you from deleting a record which is referenced by another table using a foreign key constraint. Specifically it looks like the table xinco_core_ace has a column named xinco_core_user_id which is a foreign key into your xinco_core_user table.

You cannot delete that user object unless the row in xinco_core_ace is deleted first (referential integrity).

You can change this in your JPA mappings to cascade DELETE from your user object to your XincoCoreAce object if that is what you want.

Answer 2

Found it! I had to remove the CascadeType.PERSISTENCE from xincoCoreGroupId. Found this after writing my comment to your post.