0

We have a many to many relation with let's say Entity1 and Entity2.

Now when we delete an element of Entity2, we don't in fact remove it from database but simply have a field "isActive" which we set to false.

When that happens, we would like however to remove all relations between any element of Entity1 to the element of Entity2 which was "deleted".

In SQL this transforms to a entity1 table, entity2 table, and entity1TOentity2 table. In SQL we can simply do a single update,

delete from entity1TOentity2 where entity2ID = :entity2Id

In JPA, we could load all elements of Entity1 as objects, with a join fetch on the relation, then remove one by one in Java the connections, but it's terribly wasteful...

Is there any way to formulate that SQL query with JPAQL?

I know it's possible to explicitly define a link entity, and make JPA use it, then I guess I could run a query basically identical to the SQL one on that link entity, but it seems a bit overkill.

am I missing a solution as nice as the raw SQL, but using the JPA API/JPAQL?

Community
  • 1
  • 1
Emmanuel Touzery
  • 9,008
  • 3
  • 65
  • 81

2 Answers2

1

Given your context, I see only one option: native queries.

V G
  • 18,822
  • 6
  • 51
  • 89
  • what do you call the owner of a many-to-many entity? Here I have Entity1 and Entity2. I didn't explicitly define an entity Entity1toEntity2 if that's what you mean? – Emmanuel Touzery Mar 13 '14 at 14:50
  • The owner of the relationship is the Entity that has a `@ManyToMany` annotation, without a `mappedBy` element. Yes, that is what I mean (i.e you do not need that Entity1toEntity2 entity). – V G Mar 13 '14 at 14:53
  • I have Entity1. And there are many elements, many records in DB. I mentioned the possibility of fetching them all and removing the connections one by one. But you say it's not what you suggest. There is the entity, but I need to work on elements, on records. So I don't follow. – Emmanuel Touzery Mar 13 '14 at 14:56
  • Oh, you have many other relationships in Entity2 to other entities than Entity1 that you do not want to load AND that you do not want to touch (free)? – V G Mar 13 '14 at 15:01
  • In fact yes, entity2 has relations to other entities than Entity1 but I'm not sure it's relevant. Yes, I can see the SQL query and it's really free. So I'm wondering if something similar is achievable with JPAQL, seems like a waste to load all elements from Entity1. I think it's achievable with an explicit link table as I said but it seems overkill. Ultimately with my current options I'll go for loading all of Entity1 elements or running the raw SQL, neither options really appealing. – Emmanuel Touzery Mar 13 '14 at 15:04
  • Well, beside the Nativequery option, you have also the BULK UPDATE option, that I did not mention, but you should check whether it works with element collection. Basically you create a (Named)Query `UPDATE Entity2 e SET e.entities1=NULL WHERE e.ID=:id` Also note that with this option or Native queries, the active entities in your persistence context are not refreshed when the DB update is made. – V G Mar 13 '14 at 15:06
  • Hmm currently I don't have the reverse mapping set up on Entity2 (exactly because it could be linked to several entity types on the other side, that's why I have a link table). I would have to add the reverse mapping for all the entity types it may be linked to and I like it that it doesn't know what may be linked to it. So probably no option there. – Emmanuel Touzery Mar 13 '14 at 15:11
  • I see your problem now. Probably a native query would be your only solution (given you don't to load the rest of relationships). – V G Mar 13 '14 at 15:14
1

On pure JPA, I don't know a clean way to do it.
If you're using Hibernate behind the scenes, there is a high-level solution:

@Entity
@Where(clause='isActive=true')
public class Entity2{
}
Shay Elkayam
  • 4,128
  • 1
  • 22
  • 19
  • Thank you that's very interesting. We are using hibernate under JPA but trying not to depend on it so we probably won't take advantage of it. I'm not sure but it could maybe even be used only in the context of the relation which would be really nice, something like that: http://stackoverflow.com/a/7701696/516188 though that one is OneToMany, not ManyToMany. – Emmanuel Touzery Mar 13 '14 at 19:16
  • It can be used like you want. I even included it in my answer before editing it. But I thought if you set isActive=false you don't want this entity to fetched in any of its relations, so it's better to put it on top of the entity. – Shay Elkayam Mar 13 '14 at 19:18