JPQL deleting from self referencing table

Question

The scenario is that I have a class that represents a node in a folder structure, which has an ID, a name and a link to its parent. I'm trying to write some code that deletes all the folders, however the query "DELETE FROM Folder" no longer works because of integrity constraints if the folders are not deleted in exactly the right order - ie a folder cannot be deleted if it has a child that references it.

So what needs to happen is that the folders with no children need to be deleted first, and then the folders that are subsequently left with no children can be deleted, and so on iteratively until the root folders can be deleted.

I've tried writing a loop to this effect but I'm struggling with the query, the code is like this:

int rowsAffected = -1;

while(rowsAffected != 0) {
    query = entityManager.createQuery(
            "DELETE FROM MessageFolder mfparent"
            + "WHERE NOT EXISTS ("
            + "FROM MessageFolder mfchild"
            + "WHERE mfchild.parentId = mfparent.id"
            + ") ");
    rowsAffected = query.executeUpdate();
}

The error I get from this is:

10:56:00,988 INFO [stdout] (default task-9) Hibernate: delete from PT_MESSAGE 10:56:01,004 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (default task-9) line 1:72: unexpec ted token: NOT 10:56:01,004 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (default task-9) line 1:72: unexpec ted token: NOT: line 1:72: unexpected token: NOT at org.hibernate.hql.internal.antlr.HqlBaseParser.deleteStatement(HqlBaseParser.java:296) [h ibernate-core-4.3.1.Final.jar:4.3.1.Final]

Which I don't quite understand as "NOT EXISTS" seems to be valid as part of the JPA spec. What am I doing wrong? Also to note that my persistence provider is Hibernate, so if I can do this in a better way with either JPA or Hibernate, that is also an option.

For reference the MessageFolder class is here:

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;

@Entity
@Table(name="PT_MESSAGE_FOLDER")
public class MessageFolder {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private long id;
    private String name;

    @OneToMany(mappedBy="messageFolder", targetEntity=Message.class,
                    cascade=CascadeType.ALL, fetch=FetchType.EAGER)
    private List<Message> messages;

    @OneToOne
    @JoinColumn(name="parentId", referencedColumnName="id")
    private MessageFolder parentFolder;

    public MessageFolder() {
        messages = new ArrayList<>();
    }

//getters.. setters...
}

score 2 · Answer 1 · answered Jul 30 '14 at 10:06

2

Your main problem is String concatenation, for instance:

mfparent"
+ "WHERE

would produce invalid statement because of missing spacebar character

mfparentWHERE

My recommendation is to stop spliting String in JPQL as it brings more harm than good.

answered Jul 30 '14 at 10:06

abc

2,371
3
25
36

Urgh, what a beginners mistake. Ironically I'd probably never have made that mistake writing a SQL query. – Patata Pequeña Jul 30 '14 at 10:13
1

@PotatoMan well... happens. Try using NamedQueries, which are stored on class level annotation, or held them in external file (persistance.xml) that will be less error prone as in code you will only reference the name. Scalability will also increase. – abc Jul 30 '14 at 10:27

JamesB · Answer 2 · 2014-07-30T10:49:28.090

1

I think you could achieve this using the in clause:

query = entityManager.createQuery(
        "DELETE FROM MessageFolder "
        + "WHERE id NOT in ("
        + "SELECT distinct parentFolder.id FROM MessageFolder"
        + ") ");
rowsAffected = query.executeUpdate();

edited Jul 30 '14 at 10:49

answered Jul 30 '14 at 10:11

JamesB

7,774
2
22
21

Trying this I get: Caused by: javax.ejb.EJBException: java.lang.IllegalStateException: No data type for node: org.hiber nate.hql.internal.ast.tree.IdentNode \-[IDENT] IdentNode: 'parentId' {originalText=parentId} .... Which I suppose is because there is no property "parentId" on the class, although it is the name of the column in the database. – Patata Pequeña Jul 30 '14 at 10:18
Just one minor thing, it should be "SELECT distinct parentFolder. **id** ", otherwise you have the same problem. Which almost works, except that it turns out in MYSQL which I am using it doesn't allow you to subquery the table you are deleting from! http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause For now I'll just execute this as two separate queries, not exactly brilliant but this isn't for a production system so it'll do. – Patata Pequeña Jul 30 '14 at 10:44

JPQL deleting from self referencing table

2 Answers2