Hi,
I really appreciate anyone taking the time to read through this question. I've tried to make it as distilled as possible:
I am trying to work out an efficient way of adding / deleting a single item to/from a large collection.
The application in question is designed to allow lots of people within an organisation manage their own 'address book', which, for the purposes of this question, just contains 'person's'.
I have a big pile of 'person's', and the idea is that people can browse/search through them, adding people to their own address book as required.
I got it all working with sample data and it's all fine until I start working with the real dataset of around 10,000 people... ;-(
Stuff like pagination is fine, because I am using stuff like:
Code:
criteria.setFirstResult((int)startRecord);
criteria.setMaxResults(itemsPerPage);
But when it comes to adding/deleting to/from an individual addressbook, it gets a bit terminal (i.e. taking around 30 seconds to delete a single person from an addressbook with 3,000 people in it.)
My mapping file for the addressbook has a bit that looks like this:
Code:
<list name="people" lazy="true" cascade="save-update">
<key column="addressBookId" not-null="true"/>
<index column="personIdx" />
<many-to-many class="Person" />
</list>
When I start using it, hibernate creates me a nice 'people' table that looks like this:
Code:
addresssBookId elt personIdx
===================================
260 34177 0
260 34186 1
260 34191 2
260 34195 3
260 34215 4
260 34216 5
260 34218 6
260 34229 7
260 34230 8
260 34232 9
...
In other words, in this example, addressBook with identifier 260 contains 'person's' with identifiers 34177, 34186, 34191, 34195... etc.
Which is exactly what I want.
To delete, I started out doing the classic:
Code:
addressBook.getPeople().remove(personToRemoveFromList);
But that took forever, as it loads the whole list first.
From a purely database point of view (I'm finding it hard to wrench myself away from it!), the query I want to perform is very simple, just:
Code:
delete from people where addressBookId = ? and elt = ?
But I can't do that, because people is not a mapped entity - it is only created to facilitate the collection.
So, what do I have to do to remove that little row in the database? ;-)
Many thanks for reading this far - any comments very welcome.
James.
Login
FAQ
Search