jsp 数据库连接池 tomcat 连接出现异常
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<%!private Connection con;%>
<%
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
%>
<%
String function = request.getParameter("function");
String device_name = request.getParameter("device_name");
String device_type = request.getParameter("device_type");
try {
//请求传来的函数名
function = new String(function.getBytes("ISO-885Array-1"),
"utf-8");
//请求传来的设备名称
device_name = new String(
device_name.getBytes("ISO-885Array-1"), "utf-8");
//请求传来的设备类型
/*device_type = new String(
device_type.getBytes("ISO-885Array-1"), "utf-8");*/
} catch (Exception e) {
e.printStackTrace();
}
//获取连接
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/orcl");
try {
con = ds.getConnection();
} catch (Exception e) {
}
//System.out.println(function + device_name + device_type);
%>
<%//根据参数判断调用那个函数
if ("devprop".equals(function)) {
String response_prop = this.devprop(device_name);
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
out.clear();
out.print(response_prop);
} else if ("devpoints".equals(function)) {
String response_prop = this.devpoints(device_name);
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
out.clear();
out.print(response_prop);
} else if ("subdevice".equals(function)) {
String response_prop = this.subdevice(device_name, device_type);
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
out.clear();
out.print(response_prop);
} else if ("devsearch".equals(function)) {
String response_prop = this.devsearch(device_name, device_type);
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
out.clear();
out.print(response_prop);
}else {
out.print("{}");//函数名称不存在
}
%>
<%!public String devprop(String device_name) {//设备属性查询
String str = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
String sql = "select id,name,prop_name,did,value from(select a.id,a.name,a.prop_name,a.did,b.value from (select s.id,s.name,d.prop_name,d.id did from (select id,name,devtyp_id,parent_id from (select id,substr(sys_connect_by_path(name,'.'),2)name,devtyp_id,parent_id from device start with ID in(select id from device where parent_id=0) connect by prior id=PARENT_ID))s,devtyp_property_def d where s.devtyp_id=d.devtyp_id)a left outer join device_property_rec b on a.id=b.device_id and a.did=b.prop_id)z where name=?";
ps = con.prepareStatement(sql);
ps.setString(1, device_name);
rs = ps.executeQuery();
int did = 0;
String name = null;
String value = null;
//属性名称
String prop_name = null;
int id = 0;
String str0 = null;
StringBuffer sb = new StringBuffer();
if (!rs.next()) {
String sqlnull = "select id from(select id,name,devtyp_id,parent_id from (select id,substr(sys_connect_by_path(name,'.'),2)name,devtyp_id,parent_id from device start with ID in(select id from device where parent_id=0) connect by prior id=PARENT_ID)) where name=?";
PreparedStatement psnull = null;
ResultSet rsnull = null;
try {
psnull = con.prepareStatement(sqlnull);
psnull.setString(1, device_name);
rsnull = psnull.executeQuery();
if (rsnull.next()) {
id = rsnull.getInt("id");
str = "{" + "\"deviceid\":" + id + "," + "\"name\":"
+ "\"" + device_name + "\"" + ","
+ "\"prop\":[" + "]}";
} else {
str = "{}";//并无此设备名称！
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rsnull != null) {
try {
rsnull.close();
} catch (SQLException e) {
e.printStackTrace();
}
rsnull = null;
}
if (psnull != null) {
try {
psnull.close();
} catch (SQLException e) {
e.printStackTrace();
}
psnull = null;
}
}
} else {
do {
name = rs.getString("name");
did = rs.getInt("did");
prop_name = rs.getString("prop_name");
value = rs.getString("value");
id = rs.getInt("id");
str0 = "{" + "\"id\":" + did + "," + "\"name\":" + "\""
+ prop_name + "\"" + "," + "\"value\":" + "\""
+ value + "\"" + "},";
sb.append(str0);
} while (rs.next());
str = "{"
+ "\"deviceid\":"
+ id
+ ","
+ "\"name\":"
+ "\""
+ device_name
+ "\""
+ ","
+ "\"prop\":["
+ sb.toString()
.substring(0, sb.toString().length() - 1)
+ "]}";
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
ps = null;
}
}
return str;
}%>
（代码有省略掉两个函数）
上述jsp会在很多地方引用到 ，我现在的问题是 项目在使用过程中偶尔会出现一个异常：就像图片上那样，关键地方不小被遮到了。。。这个问题很少出现所以我现在也没法知道上面那个被遮到的到底是什么。。。主要想让大家看看我的代码哪里有问题么（主要就是数据库连接，事务，结果集这三个地方）？请大家帮帮忙，谢谢了！
--------------------编程问答-------------------- 连接已关闭。
就是说，引用到的连接，在你这次用的时候，已被关闭掉了。
一般在多线程环境下出这种问题，就是有的线程用完 conn 之后将其关闭了，其他线程又拿来用。。。 --------------------编程问答--------------------
请问这种情况改怎么解决呢？谢谢你了 --------------------编程问答-------------------- 首先，建议你的代码
if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
这个放到最后，只放一处，到处放，一不小心，还要用就关掉了 --------------------编程问答--------------------
谢谢你 我会尝试一下的 --------------------编程问答--------------------
--------------------编程问答--------------------
数据库连接没打开吧。
--------------------编程问答--------------------
除
--------------------编程问答--------------------
连接池中的连接不能关掉。con.close();错误了。
连接池是为了取连接方便，如果取出来了并且用了最后放回去，你关了算怎么回事？等你关完了还不是要重新建立连接，这就浪费时间了就和没有用连接池一样了。 --------------------编程问答-------------------- 再说你的连接池是tomcat管理的，这种方式叫做jndi（一j2ee中规范），根本不需要自己关掉。
这里有个模拟连接池的http://blog.sina.com.cn/s/blog_6383597b0100fwb8.html连接不用请放回。 --------------------编程问答-------------------- 改用spring
补充：Java , Web 开发
