I am working on a trade's appointments agenda. The question is that what is better. When application initialize, dump data from database to an ArrayList and iterate over ArrayList whenever that I want appointments of a day OR execute one query everytime that I want appointments of a day? The database is on localhost.
Java querys are PreparedStatement.
I ask this because when appointments will increase, Iterate over ArrayList is going to be less and less efficient.
You have different options based on how big your tables and how scalable you need your system to be.
If you have a pattern in showing the daily appointments, then I suggest you extract that pattern to have a generic solution to show the appointments of a day.
On top of this if you have validations to be running for a selected day, keep it simple and safe in your database, that way the whole point of persistence is not lost also.
Have a look at in memory databases as well.
Generally speaking I would leave database processing and the cache the database. I have done some measurements, and these show that processing records in a JVM is quite slow (filter, sort, distinct, group).
This is especially the case, if you are using higher level collections like ArrayList in contrast to arrays (like Customer[]).
I would not even worry with "remote" databases; in case of localhost databases just give the database more RAM for its cache or work memory, and it's hard to beat the database access planning/cache of a database with Java.
But you should follow the rule to query your database with a single SQL statement only. It doesn't really matter how complex that query is, but it's important that your request is done in a single query.
There is another adavantage: You have no cache to deal with explicitly. It's not hard to build caches in Java, but cache synchronization/invalidation can be difficult. So the codebase you have to maintain is much smaller.
