[#HHH-374] EJB3 example delete query doesn't work in Hibernate.

Hibernate3

EJB3 example delete query doesn't work in Hibernate.

Created: 19/Apr/05 04:40 PM Updated: 15/Jul/05 09:10 AM

Component/s:

core

Affects Version/s:

3.0 final

Fix Version/s:

3.1 beta 1

Original Estimate:

Unknown

Remaining Estimate:

Unknown

Time Spent:

Unknown

Description

« Hide

Here's the first example of a delete query in the EJB3 Persistence API spec (second public draft):

DELETE
FROM Customer c
WHERE c.status = 'inactive'
AND (NOT EXISTS
(SELECT c FROM Customer c JOIN c.orders))

This query does not work in Hibernate 3 because support for aliases in bulk queries were dropped during development. This similar query probably would work:

DELETE
FROM Customer
WHERE status = 'inactive'
AND (NOT EXISTS
(SELECT c FROM Customer c JOIN c.orders))

All

Comments

Work Log

Change History

FishEye

Sort Order:

[ Permlink | « Hide ]

Gavin King - [19/Apr/05 05:01 PM ]

OK, I thought this was a buggy example until I checked the BNF, and there is indeed an optional alias in the definition of DELETE. I'm not sure how that snuck in there. Since that is not a standard part of SQL, we should change the EJB-QL spec.

I'll see about getting that changed in the spec.

[ Permlink | « Hide ]

Robert Watkins - [19/Apr/05 06:03 PM ]

*rofl* Fair enough. :)

[ Permlink | « Hide ]

Christian Bauer - [20/Apr/05 05:25 AM ]

It would be great if you'd stop linking to your blog on our systems.

[ Permlink | « Hide ]

Christian Bauer - [20/Apr/05 05:33 AM ]

Actually, how can I turn this feature of in JIRA? I've never seen it before...

[ Permlink | « Hide ]

Christian Bauer - [20/Apr/05 05:37 AM ]

OK, got it. Stupid kiddie trackback stuff. I wonder what they'll invent next year... newsgroups maybe.

[ Permlink | « Hide ]

Robert Watkins - [20/Apr/05 06:03 AM ]

Tell me about it; it's a combination of the trackback feature of JIRA, and the auto-discover-trackbacks-to-ping feature of Moveable Type.

Nuke the links if you want; I'll try and stop them in the future.

[ Permlink | « Hide ]

Gavin King - [20/Apr/05 10:07 AM ]

I've gone back to the EJB group on this one, and apparently no-one agrees with me that aliases should not be supported in DELETE and UPDATE. So we need to add support for this.

[ Permlink | « Hide ]

Steve Ebersole - [20/Apr/05 12:19 PM ]

Another thing we need to consider is deletes against an entity participating in a many-to-many. There'd never be a way to bulk delete these (provided the association table contained actual FKs)...

[ Permlink | « Hide ]

Gavin King - [20/Apr/05 02:19 PM ]

I agree that this is difficult in general, and my understanding is that everyone considers this is out of the scope of the bulk delete feature. There is now some active discussion on this stuff in the group.

[ Permlink | « Hide ]

Robert Watkins - [20/Apr/05 04:31 PM ]

At the least, Gavin, I think the lifecycle needs to become explicit. From a Hibernate perspective, I've also got concerns (though I haven't had a chance to prove or disprove) about the impact on objects in the second-level cache; we already have issues where external database calls can corrupt our cache because the objects are deleted, and I'd hate to think that the bulk delete has this effect as well.

[ Permlink | « Hide ]

Steve Ebersole - [20/Apr/05 05:16 PM ]

Currently it does not clean up the second level cache. However, I have a JIRA task already to implement this.

http://opensource.atlassian.com/projects/hibernate/browse/HHH-352

[ Permlink | « Hide ]

Steve Ebersole - [20/Apr/05 05:18 PM ]

Also note that its easy enough for you to do this manually in the meantime, using SessionFactory.evict()

[ Permlink | « Hide ]

Steve Ebersole - [27/Jun/05 09:39 AM ]

from-element aliases are now supported as optional in the bulk dml operations

[ Permlink | « Hide ]

Steve Ebersole - [15/Jul/05 09:10 AM ]

Just to clarify a bit further. As long as only one "from element" appears in the from clause, aliases are now completely optional in any hql statement, whether DML-style statement or select statement.