以下是我的报错详细信息:
org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction;
nested exception is org.hibernate.service.UnknownServiceException: Unknown service requested [org.hibernate.cache.spi.RegionFactory]
org.springframework.orm.hibernate5.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:564)
org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374)
org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:474)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:289)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
com.sun.proxy.$Proxy77.findByLoginName(Unknown Source)
xxxxxxxxxx.UserServiceImpl.findByLoginName(UserServiceImpl.java:43)
xxxxxxxxxx.Login.login(Login.java:76)
因公司规定需要在所有系统上添加2分钟内输错密码5次就冻结15分钟,因此在很多旧系统中我就直接使用jdbc来添加该功能,然而在一个使用hibernate的系统上已添加了冻结功能相关逻辑,并测试没问题后上线正式服务器,然而每天都有用户反馈无法登录,查看日志之后发现以上错误,
问题出现的原因可能有以下几个:
1.hibernate不需要手动关闭连接,我写的jdbc手动关闭了连接导致的问题
2. jdbc手动关闭也没关闭成功,因此出现数据库连接池满了(因页面能打开就是登录时一直转圈圈,无法连接数据库校验并登录)
解决办法:
把原生JDBC的写法替换成使用JdbcTemplate来实现,两种实现方式如下:
1. 一种是直接new个类继承JdbcDaoSupport即可直接调用getJdbcTemplate()执行sql
package com.xxxxxxxxxx;
import java.util.Date;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class LoginDJ extends JdbcDaoSupport {
@Autowired
public LoginDJ(DataSource dataSource) {
setDataSource(dataSource);
}
// 查询是否被冻结 ---返回冻结时间就是还被冻结中,返回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 = getJdbcTemplate().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) {
// 新增登录密码错误
getJdbcTemplate().execute("INSERT INTO dongjie (loginid, loginpwd, errortime, ip)" + "VALUES('" + loginId + "', '"
+ pwd + "', now(), '" + getIpAddr(request) + "')");
// 查询2分钟内是否有5次输错密码
String sql = "select count(1) FROM dongjie where errortime >= now() - '2m'::interval and loginid = ? ";
int count = getJdbcTemplate().queryForObject(sql, Integer.class, loginId);
if (count == 5) { // 如果有5条直接把最新一条的冻结时间更新了
getJdbcTemplate().execute("update dongjie set djtime =now() where id = (SELECT id FROM dongjie where loginid = '"
+ loginId + "' order by errortime desc limit 1) ");
}
}
/**
* 获取用户真实IP地址,不使用request.getRemoteAddr()的原因是有可能用户使用了代理软件方式避免真实IP地址,
* 可是,如果通过了多级反向代理的话,X-Forwarded-For的值并不止一个,而是一串IP值
*
* @return ip
*/
public static String getIpAddr(HttpServletRequest request) {
String ip = request.getHeader("x-forwarded-for");
if (ip != null && ip.length() != 0 && !"unknown".equalsIgnoreCase(ip)) {
// 多次反向代理后会有多个ip值,第一个ip才是真实ip
if (ip.indexOf(",") != -1) {
ip = ip.split(",")[0];
}
}
if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
ip = request.getHeader("Proxy-Client-IP");
}
if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
ip = request.getHeader("WL-Proxy-Client-IP");
}
if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
ip = request.getHeader("HTTP_CLIENT_IP");
}
if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
ip = request.getHeader("HTTP_X_FORWARDED_FOR");
}
if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
ip = request.getHeader("X-Real-IP");
}
if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
ip = request.getRemoteAddr();
}
return ip;
}
/***
* @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;
}
}
2. 直接引入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;
}
}