I have a table named MEMBER_DETAILS with fields - LOGIN_ID,PASSWORD,IS_ACTIVE,LAST_LOGIN_TIME.
Due to high traffic and frequent login , I have to proposed a best solution.
Solution 1: Need to interact every time a DB to do a select query with where clause for loginid,password and active.
Solution 2: Load member login details as soon as container gets up and put in to local data structure like. a bean object for member details and a hashmap with login id as Key and bean object as value. As soon as user submits details, it will be validated by local data structure. Off course I need to provide a method which will sync the local data structure with DB when ever a new user registered.
Please suggest which one is good or any other idea.
Solution 1 requires one select query and 1 update query - 2 database round-trips on every login.
Solution 2 requires update of DB on every login (fields: IS_ACTIVE, LASTLOGIN) - only 1 database round-trip, but consumes some resources (memory) on caller's computer.
Solution 3: create stored procedure on DB that validates user, update DB if validation succeed and return true/false to the caller with only 1 database round-trip,
and no resources consumed on caller's computer.
Caching in Java has the added problem that you cannot change the data in the database and see the changes in the application. Depending on your app this may or may not be an issue. Just keep in mind that caches need purging and refreshing mechanisms and they cost time to maintain.