How to delete multiple records (entity beans)?

[cs710]

Ladies/Gentlemen,

I am not sure what is the best way to delete multiple records from database through CMP.

Assuming I have a entity bean Customer which has the following structure.
Customer
{
id int (primary key)
firstName String (non unique)
lastName String
}

In the home interface, I have a findByFirstName() which returns a collection. How do I delete those entity beans which has firstName="TOM"? In sql, I could do "delete customer where firstname='TOM'. In EJB, I am doing something like this:

Collection c = home.findByFirstName().
Go through each element in the collection and call remove().

This works. However the server log shows a delete statement for every record which has firstname="TOM". I guess this mean JBoss has to go to database many times. Is there an alternative in addition to using direct JDBC?

If I use c.clear(), should this delete all the records from database? I tried this, it doesn't work.

Your comments are appreciated.

[srinij77]

Hi,
Did you find any solution to this issue?

Thanks
Sreeni

[dsundstrom]

Unfortunately, that is the way EJB works. May be we (JBoss) could add support for a special remove method in the home interface that takes a collection. Would that work for you? If so, can you post a feature request at source forge.

[srinij77]

Thanks for your response.

So, in this case, mass deletion will become JBOSS specific. Is there any way, which works in most of the application servers.

-Sreeni

[dsundstrom]

There are now ways that I know of other then cascade delete. If you do find one, I'd like to know.

[dsundstrom]

Actually now that I think about it. From the application perspective you have to delete the entities one by one, but the application server can wait until the end of the transaction and delete all rows in a single delete statement.

Delayed deletes (and inserts) is planned for JBoss 4.0.

[cs710]

Thanks for your response.

My solution to this problem is to code JDBC directly using command pattern (which is called from session beans) as suggested in J2EE pattern book.

I had been doing Pro*C for a couple of years before I started to do J2EE. The biggest problem I have is how entity bean works. I am not complaining about JBoss, which in fact performs very decently, but about how entity bean works generally. It is nice to map object to tables through entity beans. However for a relatively complicated system, entity bean doesn't provide enough mechanism to do batch processing (for example, insert 5 hundred records or delete 5 hundred). More importantly, joining tables seems a big hassle to me. EJB-QL doesn't seem to address the real problem, for example, if tables don't have a FK relationship.

Delayed delete and insert would definitely be helpful. I assume the container is smart enough to do the delete in one sql and do batch insert instead of many sql as well. I guess the same applies to finder methods. It would be nice for container to do one sql instead of one for each row. Or does EJB say that container has exactly to fetch each row at a time?

Thanks.

[dsundstrom]

The EJB specification does not day anything about the persistence mapping other than you must store it persistently.

All of what you suggest here is planned for JBoss 4.0.

[cs710]

Thanks for your response.

My solution to this problem is to code JDBC directly using command pattern (which is called from session beans) as suggested in J2EE pattern book.

I had been doing Pro*C for a couple of years before I started to do J2EE. The biggest problem I have is how entity bean works. I am not complaining about JBoss, which in fact performs very decently, but about how entity bean works generally. It is nice to map object to tables through entity beans. However for a relatively complicated system, entity bean doesn't provide enough mechanism to do batch processing (for example, insert 5 hundred records or delete 5 hundred). More importantly, joining tables seems a big hassle to me. EJB-QL doesn't seem to address the real problem, for example, if tables don't have a FK relationship.

Delayed delete and insert would definitely be helpful. I assume the container is smart enough to do the delete in one sql and do batch insert instead of many sql as well. I guess the same applies to finder methods. It would be nice for container to do one sql instead of one for each row. Or does EJB say that container has exactly to fetch each row at a time?

Thanks.

[cosmic_crusader]

Heh... being a developer I know the pain of being asked this question, but I'm only asking out of curioisity not out of pressure.

When is JBOSS 4.0 expected to hit the world?

1 year? 18 months?
(I honestly have no idea)

[dsundstrom]

This is targeted for JavaOne next year, but features may get bumped. We tend to bump features instead of pushing dates way out.

[sonwh98]

Regarding your question about building relationships without FK between tables, use ejbSelect methods and wrapper methods that delegates to the ejbSelect methods.

[srinij77]

"Delayed deletes (and inserts) is planned for JBoss 4.0."

Is this taken care in JBOSS 4?

Thanks
Sreeni