实现逻辑:
登录时首先判断账号是否被冻结(直接查询最新一条数据,如果最新一条数据没有冻结时间意味着之前的冻结时间肯定是过了的,否则不可能往下执行)
|
|____如果被冻结且时间未过,不允许登录,返回剩余冻结时间信息
|
|____如果没有被冻结,进入正常登录判断
|
|____登录成功,返回首页
|
|____密码错误,插入数据库(登录账号、密码、登录时间)
|____查询2分钟内登录错误记录数 > 5条 ? 直接冻结15分钟 : 返回登录页面
postrgres数据库建表语句:
CREATE TABLE public.dongjie (
id serial2 NOT NULL,
loginid varchar(20) NULL,
loginpwd varchar(20) NULL,
errortime timestamp NULL,
djtime timestamp NULL,
ip varchar(20) NULL
);
COMMENT ON COLUMN dongjie.loginid IS '登录账号';
COMMENT ON COLUMN dongjie.loginpwd IS '登录密码';
COMMENT ON COLUMN dongjie.errortime IS '登录错误的时间';
COMMENT ON COLUMN dongjie.djtime IS '冻结时间';
COMMENT ON COLUMN dongjie.ip IS '登录错误电脑ip';
原始JDBC实现方法:
/***********************JDBC方法示例**********************************************************/
// 查询是否被冻结 ---返回冻结时间就是还被冻结中,返回null就是没被冻结,要么就是冻结了但是已经超过15分钟了,两种情况都可以登录
@SuppressWarnings("finally")
public static Date isDJ(String loginId) throws Exception { // operationLog.jsp
Connection conn = null;
Statement stmtSQL = null;
Date djtime = null;
try {
conn = Common.getConnection("PG");
stmtSQL = conn.createStatement();
//String sql = "SELECT id, loginid, loginpwd, errortime, djtime, ip FROM public.dongjie where loginid = '" + loginId + "'";
//查询最新的一条错误记录是否有冻结时间,如果有就判断时间过了没,如果没有就不允许登录返回信息
String sql = "SELECT djtime FROM dongjie where loginid = '" + loginId + "' order by errortime desc limit 1 ";
ResultSet rs = stmtSQL.executeQuery(sql);
if (rs.next()) {
djtime = rs.getTimestamp(1);
if(djtime != null && diffMinute(djtime, new Date()) < 15) {
return djtime ;//如果有冻结时间,且小于15分钟,就把冻结时间返回页面
}
djtime = null; // 否则就是没冻结直接返回null
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmtSQL != null)
stmtSQL.close();
if (conn != null)
conn.close();
return djtime;
}
}
// 保存登录密码错误并查询2分钟内是否有5条输错密码的,如果有更新最新一条数据的冻结时间
@SuppressWarnings("finally")
public static void savaPwdInputError(LoginForm user, HttpServletRequest request) {
Connection conn = null;
Statement stmtSQL = null;
String loginId = user.getTxt_uid();
int count =0;
try {
conn = Common.getConnection("PG");
stmtSQL = conn.createStatement();
//新增登录密码错误
String strSQL = "INSERT INTO dongjie (loginid, loginpwd, errortime, ip)"+ "VALUES('" + loginId + "', '" + user.getTxt_pwd() + "', now(), '" + request.getRemoteAddr()+ "')";
stmtSQL.executeUpdate(strSQL);
// 查询2分钟内是否有5次输错密码
ResultSet rs = stmtSQL.executeQuery("select count(1) FROM dongjie where errortime >= now() - '2m'::interval and loginid = '" + loginId + "' ");
if (rs.next()) {
count = rs.getInt(1);
if(count == 5) { //如果有5条直接把最新一条的冻结时间更新了
stmtSQL.executeUpdate("update dongjie set djtime =now() where id = (SELECT id FROM dongjie where loginid = '" + loginId + "' order by errortime desc limit 1) ");
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (stmtSQL != null)
stmtSQL.close();
if (conn != null)
conn.close();
} catch (Throwable throwable) {
}
}
}
/***
* @Description: 计算两个时间间隔的分钟数
* @return int
*/
public static int diffMinute(Date startDate, Date endDate) {
long endMillis = endDate.getTime();
long startMillis = startDate.getTime();
long s = (endMillis - startMillis) / (60 * 1000);
return (int) s; //不到1分钟返回的是0,请谨慎使用
}
JdbcTemplate的实现方法,较简洁方便(实现方法也可参考链接)::
package xxxxxxxxxx
import com.album.manager.common.utils.IPUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.servlet.http.HttpServletRequest;
import java.util.Date;
@Service
@Transactional
public class Dongjie {
@Autowired
private JdbcTemplate jdbcTemplate ;
// 查询是否被冻结 ---返回冻结时间就是还被冻结中,返回null就是没被冻结,要么就是冻结了但是已经超过15分钟了,两种情况都可以登录
public Date isDJ(String loginId) {
Date djtime;
try{
// 查询最新的一条错误记录是否有冻结时间,如果有就判断时间过了没,如果没有就不允许登录返回信息
String sql = "SELECT djtime FROM dongjie where loginid = ? order by errortime desc limit 1 ";
djtime = jdbcTemplate.queryForObject(sql, Date.class, loginId);
if (djtime != null && diffMinute(djtime, new Date()) < 15) {
return djtime;// 如果有冻结时间,且小于15分钟,就把冻结时间返回页面
}
}catch (Exception e){
return null; //使用的是查询对象,如果没有记录就会抛异常,直接返回就好了
}
return null;
}
// 保存登录密码错误并查询2分钟内是否有5条输错密码的,如果有更新最新一条数据的冻结时间
public void savaPwdInputError(String loginId, String pwd, HttpServletRequest request) {
// 新增登录密码错误
jdbcTemplate.execute("INSERT INTO dongjie (loginid, loginpwd, errortime, ip)" + "VALUES('" + loginId + "', '"
+ pwd + "', now(), '" + IPUtils.getIpAddr(request) + "')");
// 查询2分钟内是否有5次输错密码
String sql = "select count(1) FROM dongjie where errortime >= now() - '2m'::interval and loginid = ? ";
int count = jdbcTemplate.queryForObject(sql, Integer.class, loginId);
if (count == 5) { // 如果有5条直接把最新一条的冻结时间更新了
jdbcTemplate.execute("update dongjie set djtime =now() where id = (SELECT id FROM dongjie where loginid = '"
+ loginId + "' order by errortime desc limit 1) ");
}
}
/***
* @Description: 计算两个时间间隔的分钟数
* @return int
*/
public static int diffMinute(Date startDate, Date endDate) {
long endMillis = endDate.getTime();
long startMillis = startDate.getTime();
long s = (endMillis - startMillis) / (60 * 1000);
return (int) s;
}
}
登录Contrller 的判断实现:
//1.登录验证账号密码之前首先判断是否被冻结,冻结就直接返回页面
Date djTime = isDJ(userid);
if(djTime != null) { //isDJ返回true就是冻结
request.setAttribute("msg", "因2分钟内连续输错5次密码,当前用户已被冻结15分钟,冻结时间:"+ djTime);
return "login";
}
// 2.登录验证账号密码时,如果用户名或密码不正确时,调用
savaPwdInputError(info, request)
//方法 保存登录密码错误并查询2分钟内是否有5条输错密码的,如果有更新最新一条数据的冻结时间