0

I have two entities:

class A {
@OneToMany(mappedBy = "a")
List<B> bs;
// getter/ setter
}
class B {
@ManyToOne
A a;
// getter/ setter
}

To delete one b, I first need to invalidate that relationship. "Traditionally" I would do something like that:

A a = em.getReference(A.class, entityIdA)
B b = em.getReference(B.class, entityIdB);
a.getBs().remove(b);
b.setA(null);
em.remove(b);

This is not very performant, if the List of a's is getting large (a few hundreds in my case).

I know I can also use JPQL to create an update query.

something like this:

Query q = em.createQuery("UPDATE B b SET b.a = NULL");
q.executeUpdate();

Question: What would be the corresponding JPQL query to remove one b from a's list of bs?

In short: How to translate

a.getBs().remove(b);

into a JPQL query?

EDIT: the mentioned update query translates to

UPDATE B SET A_ID = ? WHERE (ID = ?)
    bind => [null, 2]

Tables look like this:

A
ID

B
ID    A_ID
kerner1000
  • 3,382
  • 1
  • 37
  • 57
  • An `A` doesn't have a List of `B`s in the database; it is just the FK you need to null out, as you did. What you then need to do is clear any `A` objects from the cache(s), and/or call `em.refresh()` on the `A`, so the `A` is reloaded (without the other `B`) –  Feb 08 '18 at 18:18
  • I understand. But `a` still has a join table that includes a key to the deleted `b`. If I do only the query I posted, `a` will still have an FK to the deleted `b`. – kerner1000 Feb 08 '18 at 18:21
  • If you are using a Join table INSTEAD OF a foreign key, then what are you getting updated with the JPQL you mentioned? No FK means it has to update the join table when updating the `B` side –  Feb 08 '18 at 18:37
  • I am not sure if I understand. Are you saying I need only one update query, namely `"UPDATE B b SET b.a = NULL"` to update both sides? – kerner1000 Feb 08 '18 at 18:46
  • errm, why not try it? If you execute that JPQL you quote, why not look at what SQL is actually executed? The fact remains though that JPQL UPDATE cannot update multi-valued fields ... –  Feb 08 '18 at 18:46
  • I tested it again. `b.getA() == null`, but `a.getBs().size() == 1` – kerner1000 Feb 08 '18 at 21:50
  • If I understand your question correctly, it looks like you should set orphanRemoval and then delete your item in any way you like. On flush link will be deleted automatically. – asm0dey Feb 09 '18 at 04:26
  • I said look at the sql invoked. And what is it????? Without understanding what happens you are blundering around in the dark –  Feb 09 '18 at 05:55
  • @asm0dey Ophan removal will not do, since sometimes I just want to release the bidi-relationship but keep the entities and not delete them. – kerner1000 Feb 09 '18 at 09:04
  • @DN1, what do you suggest? should I try to make the question more clear? – kerner1000 Feb 09 '18 at 09:05
  • 1
    I've already "suggested" that you LOOK AT THE SQL INVOKED when you do that JPQL. And then you PUT IT IN THE QUESTION. It would then tell you what is happening with that JPQL, so then you can understand why you get that result –  Feb 09 '18 at 09:08
  • This is not the question. It is obvious that this query only updates one site. Everything else would be very unintuitive. The question is, how to formulate `a.getBs().remove(b)` as a JPQL query. – kerner1000 Feb 09 '18 at 09:12
  • I added the corresponding SQL query. – kerner1000 Feb 09 '18 at 09:28
  • So if you are using a fk then there is no join table. So go back to my original comment –  Feb 09 '18 at 12:43
  • A has a join table, B uses FK. A_B is not affected by the query. – kerner1000 Feb 09 '18 at 13:34
  • So you mean the 2 relations are totally UNRELATED?? i.e 2 UNIDIRECTIONAL relations, instead of the more sensible 1 BIDIRECTIONAL relation? –  Feb 09 '18 at 15:06
  • They are not supposed to be unrelated. Its a bidi relationship. This table layout is generated. What is the difference between a bidi relationship and two unidirectional relationships? – kerner1000 Feb 09 '18 at 15:16
  • If it is a BIDIRECTIONAL relation then you need to put `mappedBy`. And if they are bidirectional then there is EITHER a join table OR a foreign key, as said earlier. –  Feb 09 '18 at 15:53
  • See the edited question, `@OneToMany` has a `mappedBy` instruction. – kerner1000 Feb 09 '18 at 16:06
  • 1
    As said ... IF you have mapped a 1-N BIDIRECTIONAL relation then there is EITHER a JOIN TABLE OR a FOREIGN KEY. You still have BOTH so not, it is utterly wrong –  Feb 09 '18 at 16:11
  • You are right! In my mwe I did not have `mappedBy` And it seems to work like you told, but somehow only after I re-establishing the database connection. `em.commit()` should flush everything, not? – kerner1000 Feb 09 '18 at 16:16

1 Answers1

0

From the comments and from this question, changing the owning side of the relationship is sufficient.

Therefore, to do 

a.getBs().remove(b);

as an jpql query, one can do

"UPDATE B b SET b.a = NULL"

This will release the bidirectional relationship between a and b.

Note that you might need to clear the L2 cache or close the EntitiyManagerFactory for this to take effect.

factory.getCache().evictAll();
kerner1000
  • 3,382
  • 1
  • 37
  • 57