What is the benefit of holding table rows in Collection?

Question

I've seen some Java codes in which the rows in database table are being held in a collections (usually ArrayList or HashMap).

• What is the benefit of this approach?
• How do you keep the collection and table synced?
• Why not sending a query to database for each retrieval?

Is it a good practice at all?

Answer 1

The benefit is performance. Querying a database is resource and time intensive. If your tables are small enough that you can hold the items in memory, simple reference to local memory is orders of magnitude faster.

As far as keeping them in sync, that's a more difficult answer and would depend on the use case. In most cases, unless you've set up some good custom architecture, there will be no way to guarantee that the database and the in-memory collection are synchronized once you've retrieved it into memory.

If you wanted to take this approach and have the collection and the database be in sync (kind of like having your cake and eating it, too), you could do something like the following:

1. Set up database triggers on your table for any Create, Insert, Update, or Delete.
2. Have the triggers run a script which notifies your application somehow (monitoring thread, service, whatever).
3. Have the application, once notified, update the collection by reading the database.

Of course, whether this would even give you a performance improvement would depend on how often your database is getting modified by other programs.

You could also in your program simply maintain a lock on the database so that no one else could modify it for the duration of your processing (allowing you to keep the items in memory and guarantee that the database is unchanged), but this is extremely bad practice, because you will essentially break any other application using that table at the same time (for anything other than reading).

Answer 2

If you are constantly reading the same data that will never be changed, it makes sense to keep that data within a Java Collection, like a List or a Set. It is all about performance, making database calls and carrying out database transactions through Java does take time (My thesis in the University of London was all about this issue). However, if you have that data within a Java collection, you do not have to keep communicating with the database which has an 'impedance mismatch' as they are two separate entities; one using the Java paradigm and the other using the database paradigm.

As for keeping them in sync, that is whole different beast altogether.