Java JPA OQL recursive filter

Question

Is there any way in OQL (Object Query Language) to select an object, by filtering out all objects matching a certain property?

To access Category objects recursively I just retrieve the root-Category. Later I access its children-property through FetchType.EAGER.

Now, when I delete a Category, I in reality don't delete it, but set the deleted-property to true. This works well with the modified/deleted category, but when I access the children-property, I still get the deleted Category objects.

My current OQL-Select to get the root-Category looks like this:

SELECT c FROM Category c WHERE c.name = 'Root'

Is there any way to filter out the all the Category-objects that have Category.deleted = true? I mean recursively, so that I will not find deleted-true-Categories within the children-property?

The entity looks like this:

@Entity
@NamedQueries({
    @NamedQuery(name = Category.FIND_CATEGORY_ROOT,
                query = "SELECT c FROM Category c WHERE c.name = 'Root'")
})
public class Category implements Serializable {
    private static final long serialVersionUID = 1L;
    private static final String PREFIX = "Category.";
    public static final String FIND_CATEGORY_ROOT = PREFIX + "findCategoryRoot";
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private boolean deleted;
    @OneToMany(fetch = FetchType.EAGER)
    private List<Category> children;

    // More code here
}

A good tutorial on **JPQL Bi-Directional Recursion** would help as well. Regettably, I wasn't quite so successful in finding one yet. — Socrates, Sep 26 '15 at 18:16

score 0 · Accepted Answer · answered Sep 22 '15 at 05:18

0

I think you have to options:

Add named query with c.name = 'Root' and c.deleted = false
Do not use named queries, just use normal entityManager.createNamedQuery and add filtering there.

If your JPA provider is hibernate you can add annotation @Where(clause = 'c.deleted = false') and it will be added to all queries.

answered Sep 22 '15 at 05:18

asm0dey

2,841
2
20
33

Yes, I use Hibernate as JPA provider. Well, number 1 of yours suggestion would now work as it only filters the first object, but not its children. I didn't quite understand your 2nd suggestion. How would I have to add filtering to `entityManager.createNamedQuery`? – Socrates Sep 25 '15 at 17:48
You can build JPQL dynamically. i.e `String queryStr = "SELECT c FROM Category c WHERE c.name=:categoryName"; if(filterDeleted) queryStr += " AND c.isDeleted = FALSE"; entityManager.createTypedQuery(queryStr, Category.class);` Of course logic can be somewhat more complex. – asm0dey Sep 25 '15 at 18:29
Well, my current JPQL query is like this: `SELECT c FROM Category c WHERE c.name = 'Root' AND c.deleted = FALSE`, but all the objects with `deleted = TRUE` and `deleted = FALSE` are still loaded. Is there a problem in the syntax? To get the data I use `em.createNamedQuery(...).getSingleResult()`. The rest ist then loaded lazily using `Collection.size()`. – Socrates Sep 25 '15 at 20:05
Sorry, I do not understand. `getSingleResult` returns first row if it's the only row for your query. Otherwise you should use `.setMaxResults(1).getResultList()`. The size of list will be 1 or 0. – asm0dey Sep 25 '15 at 20:37
What I do in the first step, is load the root Category with `FetchType.Lazy`. In the second step I get the child Categories from this root Category. Due to lazy-loading, I first call `childCategory.getChildren().size();` to load them, and then walk through the child Categories in a loop. I continue to do this all the way down recursively. If I hit `SELECT c FROM Category c WHERE c.name = 'Root' AND c.deleted = FALSE` it only checks `c.deleted = FALSE` for the root Category, but not for its children. – Socrates Sep 25 '15 at 21:32
So you need to filter out deleted objects from category children too? It's not clear from your question. You still have 2 options: use hibernate-specific `@Where` annotation or add `FROM Category c JOIN c.child cc WHERE c.deleted=FALSE AND cc.deleted=FALSE`. – asm0dey Sep 26 '15 at 04:37
Well, I wrote about recursion and children-properties in the above posting. I tried out to add the JOIN you suggested, but it won't work. My JPQL now is `SELECT c FROM Category c JOIN c.children cc WHERE c.name = 'Root' AND c.deleted = FALSE AND cc.deleted = FALSE` and the error that is raised is `ERROR [org.jboss.as.ejb3] (default task-12) javax.ejb.EJBTransactionRolledbackException: No entity found for query`. – Socrates Sep 26 '15 at 09:56

Java JPA OQL recursive filter

1 Answers1