EntityExistsException When Attempting Bulk Delete With OpenJPA

Question

I have three classes: Location, MTFCC, and BorderPoint.

Location has a unidirectional @ManyToOne relationship with MTFCC, which is intended only as a Lookup table. No cascading is defined.

Location also has a bidirectional @ManyToOne/@OneToMany with BorderPoint. Since I want all associated BorderPoint objects to delete when I delete a Location, I set cascadetype.ALL on the Location side of the relationship.

Unfortunately, an EntityExistsException is being thrown when I attempt to delete a location:

org.apache.openjpa.persistence.EntityExistsException: Cannot delete or update 
a parent row: a foreign key constraint fails (`mapmaker`.`BORDERPOINT`, 
CONSTRAINT `BORDERPOINT_ibfk_1` FOREIGN KEY (`LOCATIONID`) REFERENCES `LOCATION`
(`LOCATIONID`)) {prepstmnt 21576566 DELETE t0, t1 FROM LOCATION t0 INNER JOIN 
MTFCC t1 ON t0.MTFCCID = t1.MTFCCID WHERE (t0.STATEFP = ? AND t1.MTFCCCODE = ?)
[params=?, ?]} [code=1451, state=23000]

[ERROR] FailedObject: DELETE t0, t1 FROM LOCATION t0 INNER JOIN MTFCC t1 ON 
t0.MTFCCID = t1.MTFCCID WHERE (t0.STATEFP = ? AND t1.MTFCCCODE = ?) 
[java.lang.String]

It looks like it's attempting to delete the associated MTFCC object which I do NOT want to happen. I do, however, want the associated BorderPoint objects to be deleted.

Here is the code (chopped down a bit):

@SuppressWarnings("unused")
@Entity
@Table(name="LOCATION")
@DetachedState(enabled=true)
public class Location implements Serializable, IsSerializable, Cloneable {

private Long id;
private String stateGeoId;
private MTFCC mtfcc;
private List<BorderPoint> borderPointList;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="LOCATIONID")
public Long getId() {
    return id;
}

@ManyToOne
@JoinColumn(name="MTFCCID")
public MTFCC getMtfcc() {
    return mtfcc;
}

@OneToMany(cascade = CascadeType.ALL, mappedBy = "location", fetch = FetchType.EAGER)
public List<BorderPoint> getBorderPointList() {
    return borderPointList;
}

}

@Entity
@Table(name = "BORDERPOINT")
@DetachedState(enabled = true)
public class BorderPoint implements Serializable, IsSerializable {

private Long id;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="BORDERID")
public Long getId() {
    return id;
}

@ManyToOne(targetEntity = Location.class)
@JoinColumn(name="LOCATIONID")
public Location getLocation() {
    return location;
}

}

@Entity
@Table(name = "MTFCC")
public class MTFCC implements Serializable, IsSerializable {

    private Long id;
    private String mtfccCode;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "MTFCCID")
public Long getId() {
    return id;
}

// etc

}

And, for good measure, here is the deletion code:

@Override
@Transactional
public int removeByStateGeoIdAndMtfcc(String stateGeoId, String mtfccCode) throws RepositoryException {

    EntityManager em = entityManagerFactory.createEntityManager();
    String jpaQuery = "DELETE FROM Location L where L.stateFP = ?1 AND L.mtfcc.mtfccCode = ?2";
    int affectedRows = 0;
    Query query = em.createQuery(jpaQuery).setParameter(1, stateGeoId).setParameter(2, mtfccCode);

    try {
        em.getTransaction().begin();
        affectedRows = query.executeUpdate();
        em.getTransaction().commit();
    } catch (Exception e) {
        //log.debug("Exception: ", e);
        throw new RepositoryException(e);
    }
    em.close();

    return affectedRows;
}

Hopefully I copied all relevant parts... can anyone assist?

Answer 1

You aren't reading the error message correctly. It says that the deletion is forbidden because of the foreign key constraint between BorderPoint and Location.

The cascade delete would work if you used em.remove(location) to delete your Location. Using a delete query like you're doing won't automagically delete the BorderPoints before deleting the location.

Either load them and remove them using em.remove, or execute other delete queries before to delete the BorderPoints.