Specific Caching Scenario for Web Application

Question

I am making a web application that involves being able to grab specific computer logs from multiple department stores. The application is getting data from a SQL Server DB. At the start of the app I ask the user to select a region number to narrow down their choices. After the region selection is made I make a call to the DB to retrieve the different stores in that area (I have made a Store class so I can create objects easily). I display them to the user so they can make their choice of which store they want. After the user chooses their store, I have to call the DB again to retrieve a list of workstations that belong to that store (again I have a class defined called Workstation). Then the user can select a workstation, pick a day and month they want a log from and hit a submit button.

When it comes to caching results from the DB I am confused on how to do this. Ideally I'd be caching Store and Workstation objects with some cache manager like JCS or EHCACHE. My first SQL statement to get the stores has a WHERE clause checking the REGION the user selected. So, As I get these results back in a ResultSet object do I iterate through them one by one and check if they're already in the cache? If they aren't in there then create a Store object and add it. If the object is already in cache then use a continue statement to skip to the next iteration of the ResultSet?

And do you think it would even be wise to consider caching log info that was asked for on a certain day and month if it's in the past?

I'm looking for some guidance on how to continue this project and I would really appreciate it if someone could help me out or give me a general walk through or recommendation on how to proceed. The programming itself is not the problem. It's just my unfamiliarity with caching itself.

Thank you very much!

Answer 1

Then the user can select a workstation, pick a day and month they want a log from and hit a submit button.

Based on this statement, I am assuming that the log for a particular day and month will not be updated once it has been written to the database.

So, As I get these results back in a ResultSet object do I iterate through them one by one and check if they're already in the cache?

It looks like using Hibernate with a second level cache and query caching would be a better option for you instead of using just a cache. Since your question is a high level question, I am not going to get into the details of what is Hibernate and how to us Hibernate and configure a second level cache and a query cache.

See this for more details on query cache. The article is a bit old but it did help me understand the basics of query caching.

And do you think it would even be wise to consider caching log info that was asked for on a certain day and month if it's in the past?

I don't think there is anything wrong in caching past data. The primary purpose of caching data is to make data required by your application available quickly when it is required. If accessing this data requires you to hit the database or read from a file everytime the data is required, a cache makes sense.