[OJB-developers] Delete by query?

[John F. <jfr...@so...>]

I used to use Castor before OJB.  One area where Castor performance
killed me was in deleting objects that had a lot of other relationships.
In this case, I had to force Castor object caching off and intermix
straight JDBC calls (made the difference between 15 minutes and about 20
seconds in terms of delete time).

From what I can discern so far, OJB is not any better in this regard.
In order to delete objects from the database, I have to fully
materialize them (which can be cost prohibitive if you don't care about
them).

For example, suppose I have a Customer.  The Customer has a 1:m
relationship to Orders.  Orders have a 1:m relationship to OrderItems.
If I want to delete a Customer (and all the Orders and OrderItems that
Customer has) what happens if the Customer has 1000 Orders and each
Order has 1000 OrderItems?  Major ouch.

One area that seemed natural to me is to extend the PersistenceBroker
interface to implement something like deleteByQuery() and then possibly
use the QueryBySQL() object to setup a few SQL statements which
efficiently delete all the rows in the proper order so that FK
relationships aren't violated.  Of course this sort of thing would have
to interact with the object cache also somehow (the easiest being to
simply flush the whole thing, but it could be more sophisticated
perhaps).

Thoughts?  Has anyone else solved this sort of problem in a better way?

Thanks,=20
 - John