问题描述
我有一个jsp Web应用程序.我正在使用jdbc连接池来使用位于服务器端的mysql数据库.我的Web应用程序有时会挂起,当我检查调试时发现它挂在ds.getConnection();上.它不返回任何异常/错误.浏览器将仅处于加载状态.我已经包含了我的DBConnection类,该类用于创建dadasource和从数据源获取连接.从池中获取连接的方法.我叫closeConnection();在所有jsp方法中,最后阻塞关闭连接.因此,我假设一旦我关闭连接,连接就会返回到池中
I have a jsp web application. I'm using jdbc connection pooling to use mysql database located at the server side. My web application hangs sometimes, when i checked debugging i found that it hangs at ds.getConnection(); It does not return any exception/error. Browser will be simply in loading state. I have included my DBConnection class which i use to create dadasource and getconnection from datasource.I'm calling getConnection(); method to get connection from pool. I have called closeConnection(); method in all jsp finally block to close connection. So i assume that the connection will be returned to pool once i close the connection
public class DBConnection {
public static Connection con = null;
public static String user_id = null;
public static DataSource ds = null;
public static Connection getConnection() {
try {
con = ds.getConnection();
con.setAutoCommit(false);
} catch (SQLException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
}
return con;
}
static {
con = establishConnection();
}
public static void closeConnection() {
if (con != null) {
try {
if (!con.isClosed()) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
}
}
} catch (SQLException ex) {
Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
Context.xml
Context.xml
<Context path="/KnestAdmin">
<Resource name="dbconn" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
username="root" password="" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mobitute_lms_data?useEncoding=true&characterEncoding=UTF-8"/>
</Context>
web.xml
<resource-env-ref>
<description>DB Connection</description>
<resource-env-ref-name>dbconn</resource-env-ref-name>
<resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
<res-auth>Container</res-auth>
</resource-env-ref>
示例servlet来说明我如何使用连接
Example servlet to show how i use the connection
public class Category extends HttpServlet {
Connection connection = null;
Statement statement = null;
IST ist;
PrintWriter out;
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
out = response.getWriter();
}
public void connectToServer() throws SQLException, NamingException {
connection = DBConnection.getConnection();
statement = connection.createStatement();
ist = new IST();
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
try {
connectToServer();
switch (request.getParameter("mode")) {
case "checkAvailability": {
String category_name = request.getParameter("category_name");
String sql = "Select category_name from course_category where category_name='" + category_name + "'";
ResultSet resultset_val = statement.executeQuery(sql);
if (resultset_val.next()) {
out.print("exist");
}
break;
}
case "checkAvailability1": {
String category_id = request.getParameter("category_id");
String category_name = request.getParameter("category_name");
String sql = "Select category_name from course_category where category_name='" + category_name + "' and category_id!='" + category_id + "'";
ResultSet resultset_val = statement.executeQuery(sql);
if (resultset_val.next()) {
out.print("exist");
}
break;
}
case "checkwarning": {
String category_id = request.getParameter("category_id");
String warning = "";
String sql_query = "select * from course where category_id='" + category_id + "' and status='Active'";
ResultSet resultset_val = statement.executeQuery(sql_query);
if (resultset_val.next()) {
warning = "yes";
}
String sql_query1 = "select * from assessment where category_id='" + category_id + "' and status='Active'";
ResultSet resultset_val1 = statement.executeQuery(sql_query1);
if (resultset_val1.next()) {
warning = "yes";
}
if (warning.equals("yes")) {
throw new Exception();
}
break;
}
case "active_inactive": {
String category_id = request.getParameter("category_id");
String status = request.getParameter("status");
String sql = "Update course_category set status='" + status + "',last_updated_user='" + request.getSession(false).getAttribute("log_user_id") + "',last_updated_ts='" + ist.getLastUpdatedts() + "' where category_id='" + category_id + "'";
int i = statement.executeUpdate(sql);
if (i > 0) {
}
connection.commit();
break;
}
default:
break;
}
} catch (Exception ex) {
Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
try {
response.setStatus(400);
if (!connection.isClosed()) {
connection.rollback();
}
} catch (SQLException ex1) {
Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex1);
}
} finally {
try {
out.flush();
out.close();
if (!statement.isClosed()) {
statement.close();
}
DBConnection.closeConnection();
connection=null;
} catch (SQLException ex) {
Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
try {
connectToServer();
switch (request.getParameter("mode")) {
case "add": {
String category_name = request.getParameter("category_name");
String category_desc = request.getParameter("category_desc");
String status = request.getParameter("status");
String sql = "INSERT INTO course_category(category_name,category_desc,status,created_user,created_ts,last_updated_user,last_updated_ts) VALUES ('" + category_name + "', '" + category_desc + "','" + status + "','" + request.getSession(false).getAttribute("log_user_id") + "','" + ist.getLastUpdatedts() + "','" + request.getSession(false).getAttribute("log_user_id") + "','" + ist.getLastUpdatedts() + "')";
statement.executeUpdate(sql);
connection.commit();
break;
}
case "edit": {
String category_id = request.getParameter("category_id");
String category_name = request.getParameter("category_name");
String category_desc = request.getParameter("category_desc");
String sql = "update course_category set category_name='" + category_name + "',category_desc='" + category_desc + "',last_updated_user='" + request.getSession(false).getAttribute("log_user_id") + "',last_updated_ts='" + ist.getLastUpdatedts() + "' where category_id='" + category_id + "'";
statement.executeUpdate(sql);
connection.commit();
break;
}
default:
break;
}
} catch (Exception ex) {
try {
Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
if (ex instanceof SQLIntegrityConstraintViolationException) {
response.setStatus(1);
}else{
response.setStatus(2);
}
if (!connection.isClosed()) {
connection.rollback();
}
} catch (SQLException ex1) {
Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex1);
}
} finally {
try {
out.flush();
out.close();
if (!statement.isClosed()) {
statement.close();
}
DBConnection.closeConnection();
connection=null;
} catch (SQLException ex) {
Logger.getLogger(Category.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
@Override
public String getServletInfo() {
return "Short description";
}
}
推荐答案
您的代码似乎是一大竞争条件.只有一个servlet实例用于多个请求.结果,在并发请求时,您当前的代码可能并且将泄漏连接.
Your code seems to be one big race condition. Only one servlet instance is used for multiple requests. As a result, on concurrent requests, your current code can and will leak connections.
当执行并发请求时,它们中的每一个都将创建一个连接并将其分配给相同的实例变量,因此一个或多个连接将丢失并保持打开状态. DBConnection.getConnection
/DBConnection.closeConnection
的使用表明您也可能在那里泄漏连接.
When concurrent requests are executed, each of them will create a connection and assign it to the same instance variable, so one or more connections will be lost and remain open. The use of that DBConnection.getConnection
/DBConnection.closeConnection
suggests that you are potentially leaking connections there as well.
请停止使用字段来保留您的connection
和statement
,并改用这些局部变量.还可以考虑使用try-with-resources来正确关闭连接,并考虑直接使用DataSource
而不是使用(可能不必要的)DBConnection
抽象.
Please stop using fields to keep your connection
and statement
, and make these local variables instead. Also consider using try-with-resources to properly close connections, and consider using a DataSource
directly instead of using that (probably unnecessary) abstraction of DBConnection
.
这篇关于jdbc连接池中的数据源未返回连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!