社区首页 >问答首页 >如何通过以下几种方法关闭resultSet、prepareStatement、conn，以避免rs关闭和连接池被阻塞问如何通过以下几种方法关闭resultSet、prepareStatement、conn，以避免rs关闭和连接池被阻塞ENStack Overflow用户提问于 2012-01-27 22:01:55回答 3查看 2.7K关注 0票数 1整个数据操作如下所示。我想在不影响下一个连接的情况下关闭所有资源。我是否应该将构造函数更改为connection()方法，然后使用disconnect()方法，但这样做之后，我应该在哪里
代码语言：javascript复制public class DataBean{
private Connection conn = null;
private ResultSet res = null;
private InitialContext context;
private DataSource datasource;
private Statement stmt=null;
private java.sql.PreparedStatement prepar = null;
private java.sql.CallableStatement proc = null;
public static int PAGECOUNT; //²éѯºó·µ»ØµÄ×ÜÒ³Êý ÒòΪjavaµÄº¯Êý²»ÄÜ´«ÒýÓÃËùÒÔÐèÒªÓþ²Ì¬±äÁ¿À´»ñµÃ
public DataBean()
{
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/MyData", "root","loikenu");
context = new InitialContext();
datasource = (DataSource)context.lookup("jdbc/MyData");
conn = datasource.getConnection();
//stmt =conn.createStatement();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e.getMessage());
}
}
public UserBean checkUsersLogin(String userName, String userPwd) //µÇ½ÑéÖ¤
{
UserBean ub = null;
if (!checkParameter(userName + userPwd))
{
userName = "null";
userPwd = "null";
}
try
{
String sql =
"select count(*) from admin where userName=? and userPwd=?";
prepar = conn.prepareStatement(sql);
//set parameter values for preparedstatment object
prepar.setString(1, userName);
prepar.setString(2, userPwd);
//execute query using preparedstatement object
res = prepar.executeQuery();
if (res.next())
{
//get data from reults set returned by jdbc
if (res.getInt(1) > 0)
{
ub = this.getUser(userName);
}
else
{
ub = null;
}
}
}
catch (Exception e)
{
ub = null;
e.printStackTrace();
}
return ub;
}
public UserBean getUser(String userName) //ÌáÈ¡µÇ½Óû§ÐÅÏ¢
{
UserBean ub = new UserBean();
int i=1;
String sql = "select * from admin where userName=?";
try
{
prepar = conn.prepareStatement(sql);
prepar.setString(1, userName);
res = prepar.executeQuery();
while (res.next())
{
ub.setUserName(res.getString("userName"));
ub.setUserPwd(res.getString("userPwd"));
ub.setUserId(i);
}
i++;
}
catch (SQLException ex)
{
ex.printStackTrace();
}
return ub;
}
public boolean checkParameter(String para) //¹ýÂË·Ç·¨×Ö·û
{
int flag = 0;
flag += para.indexOf("'") + 1;
flag += para.indexOf(";") + 1;
flag += para.indexOf("1=1") + 1;
flag += para.indexOf("|") + 1;
flag += para.indexOf("<") + 1;
flag += para.indexOf(">") + 1;
if (flag != 0)
{
System.out.println("Ìá½»ÁË·Ç·¨×Ö·û!!!");
return false;
}
return true;
}
public ArrayList selectCDBean(String selectValue, int page, int count) //²éѯ·ÖÒ³
{
ArrayList list = new ArrayList();
if (!checkParameter(selectValue))
{
selectValue = "";
}
try
{
proc = conn.prepareCall("{call proc_page(?,?,?,?)}");
proc.setInt(1, page);
proc.setInt(2, count);
proc.setString(3, selectValue);
proc.registerOutParameter(4, Types.INTEGER); //OUTPUT²ÎÊý ·µ»Ø½á¹¹¹²¶àÉÙÒ³
res = proc.executeQuery(); //½ÓÊÕ´æ´¢¹ý³ÌµÄ½á¹û¼¯
while (res.next()) //ÌáÈ¡½á¹û¼¯µÄÿÌõ¼Ç¼
{
CDBean cb = new CDBean();
cb.setCdAlbum(res.getString("CDalbum"));
cb.setCdCompany(res.getString("CDcompany"));
cb.setCdName(res.getString("CDname"));
cb.setCdId(res.getLong("CDid"));
cb.setCdType(getCDType(res.getInt("CDtypeId")));
list.add(cb);
}
PAGECOUNT = proc.getInt(4);
}
catch (SQLException ex)
{
ex.printStackTrace();
}
return list;
}
public String getCDType(int cdtypeId)
{
ResultSet res1=null;
try
{
java.sql.PreparedStatement prepar1 = conn.prepareStatement(
"select display from CDtype where CDtypeId=?");
prepar1.setLong(1, cdtypeId);
res1 = prepar1.executeQuery();
res1.next();
return res1.getString("display");
}
catch (SQLException ex)
{
return null;
}
}
public boolean setCDBean(CDBean cb)
{
if (!checkParameter(cb.getCdName() + cb.getCdCompany() + cb.getCdAlbum() +
cb.getCdType()))
{
return false;
}
boolean flag = false;
String sql =
"update CDinfo set CDname=?,CDcompany=?,CDalbum=?,CDtypeId=? where CDid=?";
try
{
prepar = conn.prepareStatement(sql);
prepar.setString(1, cb.getCdName());
prepar.setString(2, cb.getCdCompany());
prepar.setString(3, cb.getCdAlbum());
prepar.setInt(4, Integer.parseInt(cb.getCdType()));
// prepar.setLong(5, cb.getCdId());
int result = prepar.executeUpdate();
if (result > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception ex)
{
flag = false;
ex.printStackTrace();
}
return flag;
}
public CDBean getCDBean(long id)
{
CDBean cb = new CDBean();
int i=1;
String sql = "select * from CDinfo where CDid=?";
try
{
prepar = conn.prepareStatement(sql);
prepar.setLong(1, id);
res = prepar.executeQuery();
while (res.next())
{
cb.setCdAlbum(res.getString("CDalbum"));
cb.setCdCompany(res.getString("CDcompany"));
cb.setCdName(res.getString("CDname"));
cb.setCdId(i);
cb.setCdType(getCDType(res.getInt("CDtypeId")));
}
i++;
}
catch (SQLException ex)
{
ex.printStackTrace();
}
return cb;
}
public boolean deleteCDBean(long id)
{
boolean flag = false;
String sql = "delete from CDinfo where CDid=?";
try
{
prepar = conn.prepareStatement(sql);
prepar.setLong(1, id);
int result = prepar.executeUpdate();
if (result > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception ex)
{
flag = false;
ex.printStackTrace();
}
return flag;
}
public boolean addCDBean(CDBean cb)
{
boolean flag = false;
if (!checkParameter(cb.getCdName() + cb.getCdCompany() + cb.getCdAlbum() + cb.getCdId()+
cb.getCdType()))
{
return false;
}
String sql = "insert into CDinfo values(?,?,?,default,?)";
try
{
this.prepar = conn.prepareStatement(sql);
prepar.setString(1, cb.getCdName());
prepar.setString(2, cb.getCdCompany());
prepar.setString(3, cb.getCdAlbum());
prepar.setInt(4, Integer.parseInt(cb.getCdType()));
int result = prepar.executeUpdate();
if (result > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception ex)
{
flag = false;
ex.printStackTrace();
}
return flag;
}
public boolean setUserBean(UserBean ub)
{
boolean flag = false;
String sql = "update admin set userPwd=? where userId=?";
try
{
if (!checkParameter(ub.getUserPwd()))
{
return false;
}
this.prepar = conn.prepareStatement(sql);
prepar.setString(1, ub.getUserPwd());
prepar.setLong(2, ub.getUserId());
int result = prepar.executeUpdate();
if (result > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception ex)
{
flag = false;
ex.printStackTrace();
}
return flag;
}
public boolean addUserBean(UserBean ub)
{
boolean flag = false;
String sql = "insert into admin(userName,userPwd) values(?,?)";
//int i=1;
if (!checkParameter(ub.getUserPwd() + ub.getUserName()+ub.getUserId()))
{
return false;
}
if (hasUser(ub.getUserName()))
{
return false;
}
try
{
prepar = conn.prepareStatement(sql,prepar.RETURN_GENERATED_KEYS);
prepar.setString(1, ub.getUserName());
prepar.setString(2, ub.getUserPwd());
// prepar.setLong(3,ub.getUserId());
int result = prepar.executeUpdate();
if (result > 0)
{
flag = true;
}
else
{
flag = false;
}
// i++;
}
catch (Exception ex)
{
flag = false;
ex.printStackTrace();
}
return flag;
}
public boolean hasUser(String userName)
{
boolean flag = true;
String sql = "select count(*) from admin where userName=?";
try
{
prepar = conn.prepareStatement(sql);
prepar.setString(1, userName);
res = prepar.executeQuery();
res.next();
int result = res.getInt(1);
if (result > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (SQLException ex)
{
ex.printStackTrace();
flag = true;
}
return flag;
}
}javajdbcprepared-statementresultset关注问题分享EN回答 3推荐最新Stack Overflow用户回答已采纳发布于 2012-01-27 22:45:21
代码语言：javascript复制public class DataBean{
private Connection conn = null;
private ResultSet res = null;
// ...
private Statement stmt=null;
private java.sql.PreparedStatement prepar = null;
private java.sql.CallableStatement proc = null;
// ...这是错误的。您不应该在类级别声明它们。您应该在方法级别声明它们。作为示例，我将以您的getUser()方法为例，它必须如下所示：
代码语言：javascript复制public UserBean getUser(String userName) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
// ...然后，您需要确保以与在完全相同的try块中获得的资源相反的顺序关闭finally块中的资源。下面是根据推荐的JDBC惯用法对您的getUser()方法的完整重写：
代码语言：javascript复制public UserBean getUser(String userName) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
UserBean userBean = null;
String sql = "select userId, userName, userPwd from admin where userName = ?";
try {
connection = dataSource.getConnection();
statement = connection.prepareStatement(sql);
statement.setString(1, userName);
resultSet = statement.executeQuery();
if (resultSet.next()) {
userBean = new UserBean();
userBean.setUserId(resultSet.getInt("userId");
userBean.setUserName(resultSet.getString("userName"));
userBean.setUserPwd(resultSet.getString("userPwd"));
}
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
return userBean;
}(请注意，我将用户ID固定为数据库列字段，使其成为自动增量主键，也注意到我将while固定为if -there只有1个具有此名称的用户，对吗？-，还请注意，当没有已知用户时，它将返回null，这便于以后检查)
另请参阅：
How often should Connection, Statement and ResultSet be closed in JDBC?JDBC MySql connection pooling practices to avoid exhausted connection poolAm I Using JDBC Connection Pooling?
您的checkUsersLogin()和checkParameter()方法也有问题，但这是另一个问题。
收藏分享票数 2ENStack Overflow用户发布于 2012-01-27 22:30:54
您必须使用逆序关闭每个可关闭的对象。
如果是期初订单：
连接
PreparedStatement
ResultSet
那么关闭订单将是：
ResultSet
PreparedStatement
连接
您可以将其放入finally块中。这是一般规则。
收藏分享票数 0ENStack Overflow用户发布于 2016-06-02 11:25:39
您应该使用Java7 try-with-resources。是的，当你问这个问题的时候它已经出来了！;)
大致是这样的：
代码语言：javascript复制try (Connection con = // get connection... ;
PreparedStatement ps = // create prepared statement... ;
ResultSet rs = ps.executeQuery()) {
// Process the result set...
// The AutoCloseable resources 'conn', 'ps', and 'rs' will be cleaned up at the end:
// .close() will be called on all of them whether an exception occurs or not
} catch (SQLException e) {
// TODO(mykey): Do not swallow me!!
}注意:请参阅AutoCloseable .close()的文档
收藏分享票数 0EN页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持原文链接：https://stackoverflow.com/questions/9034353
复制相关文章