JPA - Improving Actionbazzar code

[John Stone]

Hello rangers,

I found this code on page 330 of EJB 3 in Action:

List<Category> categories = getAllCategories();
List<Item> items = seller.getItems();
for (Item item: items) {
  for (Category category: categories) {
    category.getItems().remove(item);
  }
}

I'm concerned about efficiency of this code. As I understand it, all items of seller are fetched from DB, and all items of categories are fetched from DB, then made into java classes, some change is made to them, and then again they all need to be saved to DB.

Is this really how it works behind the scenes?
Is there any better way to delete these items more efficiently? I know you can write your own query, but then what's the point of having JPA?

Thank you.

[Mark Garland]

Hi John,

I'm still learning EJB3/JPA, so I'm not sure how much I'll be able to help you having not reached p330 yet.

I see your point entirely. My initial thoughts (which perhaps others more experienced may be able to expand upon) would be
- is the list of categories fixed and therefore read only? If so, I suspect JPA can be configured to keep this list of categories cached. Looking at the code, the list isn't being modified, just the items assigned to it - correct?
- you *could* write an SQL query, but SQL can be non-standard so you could never guarentee that it would run on all db's. I'll accept that the SQL delete command is probably one of the simplest, but I think the point still stands. Unless you meant writing a JPA-QL query...?
- perhaps this code could be modified, and instead of using getAllCategories(), you could use a method that would get all the categories of items relating to a particular seller - this could be implemented in JPA-QL, which would generate joins in the SQL, pushing the load to the db server. That way, the list in the code could be significantly cut down.

Hope that helps in some way - as I say, I'm still learning and would be keen to hear the opinions of experienced JPA'ers.

MG