CODEWITHSUNDEEP
jpaejb-3.0bulksql-delete
Tony Eichelberger
Tony Eichelberger

Reputation: 7142

How to execute bulk delete with relationship in EJB3

I am trying to figure out how to execute a bulk delete on a @ManyToOne relationship, so far without success.

Scenario:

Parent has many Downloads, I want to execute a delete of all Downloads where the Parent date is > :some_date. I do not want to delete any of the Parent records, just the Download records. Download has a parent field that is mapped using a @ManyToOne annotation.

I am trying to use a @NamedQuery on the Download entity to accomplish this.

//this OQL tries to delete from both the child and parent tables
//I only want to delete from the Download table
DELETE FROM Download dwn 
    WHERE dwn.acctId = :acctId AND dwn.parent.date > :date

//this OQL is invalid and will keep the bean from deploying
//The example I found used this sub query but with a @OneToMany relationship
//instead of a @ManyToOne relationship
//this is what i get for an error on deployment:
//"Errors in named queries: deleteByAccountIdAndDownloadedDate"
DELETE FROM Download dwn WHERE EXISTS 
    (SELECT p from dwn.parent WHERE p.date > :date) 
    AND dwn.acctId = :acctId

Any suggestions?

Upvotes: 1

Views: 1427

Answers (2)

Tony Eichelberger
Tony Eichelberger

Reputation: 7142

I was able to get the bulk delete to work by taking off the @ManyToOne annotation and replacing it with just the Long parentId. Then I changed the sub select to use the Parent name instead of the field.

Now the OQL looks like this:

DELETE FROM Download dwn WHERE EXISTS 
    (SELECT p FROM Parent p WHERE p.id = dwn.parentId and p.date > :date)

I didn't try it (because I don't need query capabilities for what I am doing) but if may work to leave the @ManyToOne on there and use this OQL:

DELETE FROM Download dwn WHERE EXISTS 
    (SELECT p FROM Parent p WHERE p.id = dwn.parent.id and p.date > :date)

Upvotes: 0

Grzegorz Oledzki
Grzegorz Oledzki

Reputation: 24271

I would stick to the first approach. Is there any error message shown?

When it comes to Parent records being deleted together with Downloads... Are you sure you don't have a cascade on the relation between them?

Upvotes: 1

Related Questions