File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes To check if failed attempts is more than 5 within 24hrs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "To check if failed attempts is more than 5 within 24hrs" Watch "To check if failed attempts is more than 5 within 24hrs" New topic
Author

To check if failed attempts is more than 5 within 24hrs

kamabotti khan
Greenhorn

Joined: Jul 13, 2010
Posts: 22

hi

I am trying to stop from unlocking an account for a domain if the user has made attempts more than 5 times within 24hrs.I have added the check for five times taking the count from the table where failure or success log is stored but I want an additional condition that is ,if the five attempts is within period of 24hrs .I am using ORACLE DB.

The SQL query I am using is

select count(*) from test where acct_name='1234' and domain_name='d' and log_time<= SYSDATE-1.

This gives me the count the user has tried to unlock an account name "1234" on domain name "d" .If this count is more than 5 then I display a error message to user,but know I have to check if the count is more than 5 with 24hrs then display a error message or else the user can unlock.

For example the user named "K" has tried his fifth attempt to unlock today at 10:00 AM ,so until tommorrow 10:00 AM he will be displayed that message,And after tommorrow 10:00 am ,he can start unlocking.

Here is my java implementation for that ,But checking for overall five times,not within 24hrs ,should add that,please help me on this


final String CHECK_UNLOCK_ATTEMPTS = "SELECT count(*) FROM test WHERE acct_name = '"
+ accntname
+ "' AND domain_name = '"
+ domainName
+ "' "
+ "and log_time <= SYSDATE - 1";

// check if the session user has reached more than 5 attempts of unlock
SqlRowSet rs_check_unlock_attempts = jt.queryForRowSet(CHECK_UNLOCK_ATTEMPTS);
int unlock_attempts = 0;

while (rs_check_unlock_attempts.next())
{
unlock_attempts = rs_check_unlock_attempts.getInt(1);
}

if (unlock_attempts > 5)
model.put("errorStr", "You have reached Maxium Attempts of unlock");

}

Please help me on how to do this.


Thanks
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3436
    
  47

First and foremost, you should learn how to use bind variables and use PreparedStatement for your code. It shields you from SQL injection attacks and saves precious database resources (very true for Oracle). Any decent JDBC tutorial deals with bind variables and the topic has been discussed countless times here on Javaranch too.

I'd say your approach should generally work, you've just a small mistake in the time condition. You want to count records which are younger than one day. Your current condition (log_time<= SYSDATE-1) matches records that are older than one day.

Just reverting the condition should do the trick.

You should also create an index on acct_name, domain_name and log_time to speed up the query. And unless you want to keep the history, you should delete records older than on day periodically.
kamabotti khan
Greenhorn

Joined: Jul 13, 2010
Posts: 22
Thanks!!!1 It worked like charmmm
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3436
    
  47

You're welcome
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: To check if failed attempts is more than 5 within 24hrs
 
Similar Threads
To Terry McKee, Rasika Chitnis and others
How to remove locks if client has failed to do so?
Thoughts on synchronizing, implementing delete and on locking
New to Board & Questions about 2 .java errs
to stop unlocking an account from if failed attempts is more than 5 within 24hrs