HQL delete associations from many to many mapping

Question

I Have two entities CRImageType & CRVariable with a many to many relation as follows:

CRImageType entity:

@Entity
@Table(name = "imageviewer_crimagetype")
public class CRImageType implements Serializable   {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    @Column(name = "ImTypeId")
    private Long imTypeId;

    @Column(name = "ImTypeName")
    private String imTypeName;

    @Column(name = "ImTypeDescription")
    private String imTypeDescription;

    @ManyToMany(cascade = {CascadeType.ALL})
    @JoinTable(name="imageviewer_imtype_variable", 
    joinColumns={@JoinColumn(name="ImTypeId")}, 
    inverseJoinColumns={@JoinColumn(name="VarId")})
    private Set<CRVariable> crvariables = new HashSet<CRVariable>();

CRVariable entity:

@Entity
@Table(name = "imageviewer_crvariable")
public class CRVariable implements Serializable   {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    @Column(name = "VarId")
    private Long varId;

    @Column(name = "VarName")
    private String varName;

    @Column(name = "VarDescription")
    private String varDescription;

    @ManyToMany(mappedBy="crvariables")
    private Set<CRImageType> crimagetypes = new HashSet<CRImageType>();

In my database the relation is mapped by two tables "imageviewer_crimagetype" & "imageviewer_crvariable" and a third one "imageviewer_imtype_variable" for their many to many relation.

I would like only to DELETE association records from table "imageviewer_imtype_variable". How can be done using an HQL query since i can not directly access "imageviewer_imtype_variable table.

I would like the HQL equivalent of an SQL query like

delete from imageviewer_imtype_variable where ImTypeId='%%%'

score 3 · Accepted Answer · answered Nov 08 '12 at 16:17

3

This is JPA, not Hibernate specifically. The fact that you have a standardized API on top here makes it easier to find answers if you search in the context of the API, not the implementation.

The way to do it (as far as I remember, I don't use many to many relationships that often) is to remove the related entities from each other's collection mapping fields. So if you have EntityA and EntityB, you remove EntityA from EntityB and EntityB from EntityA. The persistence provider should then be triggered to remove the record from the join table.

Native queries should only be a last resort IMO.

answered Nov 08 '12 at 16:17

Gimby

5,095
2
35
47

You are correct that the mappings will be deleted when the associated entities are deleted. But the OP wants to delete **only** the mapping records here. You can do it using the Hibernate API or a native query. – Daniel F. Thornton Nov 08 '12 at 16:23
@Gimby is not talking about deleting entities, but just removing then from each other mapping collection and that's definitely correct. – remigio Nov 08 '12 at 16:29
NOTE: Using native SQL API does invalidates the L2 cache for DML operations. This is a bummer! I wonder if there is any way to instruct nhibernate which entity will be effected in response to DML command instead of it throwing away entire cache. – Mazhar Qayyum Apr 11 '18 at 12:14

Daniel F. Thornton · Answer 2 · 2012-11-08T16:15:59.427

1

You can execute a native SQL query:

http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch18.html#querysql-creating

So in your case, something like:

session.createSQLQuery("DELETE FROM imageviewer_imtype_variable").executeUpdate();

You can also specify a custom native SQL DELETE query within your CrImageType entity:

http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html/ch18.html#querysql-cud

edited Nov 08 '12 at 16:15

answered Nov 08 '12 at 15:53

Daniel F. Thornton

3,687
2
28
41

Thanks for your reply! I am going to use it ... But i am wondering if Hibernate has an internal mechanism (native) for dealing with such a case (delete records from many to many table) – thanili Nov 08 '12 at 16:11

score 1 · Answer 3 · answered Dec 02 '14 at 11:05

From my experience, handling the ManyToMany relation is one of the few case where getting out of ORM integrism is the best option for our mind's sake. Particularly, when you use a two way navigation (i.e. when the relation and the inverse relation are usefull).

@Gimby is correct in his answer, the thing is that with a complex cross referencing it is far harder to make it work, than doing a simple native query.

So:

session.createSQLQuery("DELETE FROM imageviewer_imtype_variable").executeUpdate();

is easier, if it still does not work because of cross referencing, you migh even add a :

session.clear();

OK. This is another ORM integrism infringment, but get you out of the cesspit in two lines ;-) .

This is something that i was also thinking about (and start implementing). Managing referential integrity on my own... I think is going to save me some time :) than trying to map it on Hibernate — thanili, Dec 02 '14 at 12:05

score 0 · Answer 4 · answered Nov 08 '12 at 16:53

0

The answer from @Gimby is the correct one, in a many to many relationship removing related instances from each other mapping collection triggers a delete from the relationship mapping table.

answered Nov 08 '12 at 16:53

remigio

4,101
1
26
28

score 0 · Answer 5 · answered Nov 08 '17 at 16:38

In the entity class CRVariable you add :

@PreRemove
private void removeCRVariableFromCRImageType() {
    for (CRImageType crImageType: CRImageType) {
        crImageType.getCrvariables ().remove(this);
    }
}

This method will override the JPA action PreRemove in order to detach the CRVariable object to be removed from the set crvariables (technically the table imageviewer_imtype_variable)

Hope this helps you !

HQL delete associations from many to many mapping

5 Answers5