CODEWITHSUNDEEP
javasqlspringcaching
Tastybrownies
Tastybrownies

Reputation: 937

Caching in an enterprise web application

I'm relatively new to using caching in larger programs intended to be used by a large number of people. I know what caching is and why its beneficial in general and I've started to integrate EHCache into my application which uses JSP and Spring MVC. In my application the user selects an ID from a drop down list and this uses a java class to grab data from DB according to the ID picked. First the query is executed and it returns a ResultSet object. At this point I am confused at what to do and feel like I'm missing something.

I know I want the object to go into cache if it's not already in there and if it's already in cache then just continue with the loop. But doing things this way requires me to iterate over the whole returned result set from the DB query, which is obviously not the way things are supposed to be done?

So, would you recommend that I just try to cache the whole result set returned? If I did this I guess I could update the list in the cache if the DB table is updated with a new record? Any suggestions on how to proceed and correctly put into ecache what is returned from the DB?

I know I'm throwing out a lot of questions and I certainly appreciate it if someone could offer some help! Here is a snippet of my code so you see what I mean.

rs = sta.executeQuery(QUERYBRANCHES + specifier);

          while (rs.next())
            {

              //for each set of fields retrieved, use those to create a branch object.
                //String brName = rs.getString("NAME");
                String compareID = rs.getString("ID");
                String fixedRegID = rs.getString("REGIONID").replace("0", "").trim();

                //CHECKING IF BRANCH IS ALREADY IN THE CACHE. IF IT IS NOT CREATE
                //THE NEW OBJECT AND ADD IT TO CACHE. IF THE BRANCH IS IN CACHE THEN CONTINUE
                if(!cacheManager.isInMemory(compareID))
                {

                Branch branch = 
                        new Branch(fixedRegID, rs.getString("ID"), rs.getString("NAME"), rs.getString("ADDR1"), rs.getString("CITY"), rs.getString("ST"), rs.getString("ZIP"));




                cacheManager.addBranch(rs.getString("ID"), branch);
                }
                else
                {
                    continue;
                }
            }

          retData = cacheManager.getAllBranches();

Upvotes: 1

Views: 115

Answers (1)

Evandro Pomatti
Evandro Pomatti

Reputation: 15144

But doing things this way requires me to iterate over the whole returned result set from the DB query, which is obviously not the way things are supposed to be done?

You need to iterate in order to fetch the results.

To avoid iteration on all elements you need to exclude the already cached values that are returned in the select.

What I mean is, add to your select exclusion clause the values you dont want, in this case the values already cached. (not like, <>, etc). This will reduce the iteration time.

Otherwise yes, Im afraid you will have to iterate over all returns if your SQL filter is not complete.

So, would you recommend that I just try to cache the whole result set returned? If I did this I guess I could update the list in the cache if the DB table is updated with a new record? Any suggestions on how to proceed and correctly put into ecache what is returned from the DB?

You should not cache highly dynamic business information.

What I recommend is that you use database indexes so that would dramatically increase your performance, and get your values from there. Use pure native SQL if needed.

If you are going to work with a lot of users you will need a lot of memory to keep all those objects in memory.

As you start scaling horizontally caching management is going to be a challenge this way.

If you can, only cache values that wont change or that change very few times, like in the application start up or application parameters.

If you really need to cache business information, please let us know the specifics, like what is the hardware, platform, database, landscape, peak of access, etc.

Upvotes: 1

Related Questions