我如何在下面的几个方法中关闭resultSet，prepareStatement，conn以避免rs close和连接池被卡住 [英] how can i close the resultSet, prepareStatement, conn in several methods below to avoid rs close and connection pool getting jammed
本文介绍了我如何在下面的几个方法中关闭resultSet，prepareStatement，conn以避免rs close和连接池被卡住的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
整个数据操作如下。我希望关闭每个资源，而不会干扰下一个连接。我应该将构造函数更改为连接（）mthod然后有一个disconnect（）方法，但在这之后我应该在哪里
the whole data operation is below. i want to close close each and every resource without interfering with the next connection. should i change the constructor to a connection() mthod then have a disconnect() mthod, but after doing so where should i
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;
}
}
推荐答案
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（）方法，它必须如下所示：
This is wrong. You should not declare them at class level. You should declare them at method level. As an example, I'll take your getUser() method, it must look like this:
public UserBean getUser(String userName) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
// ...
然后，您需要确保关闭资源最后以相反的顺序阻止你在同一个尝试块中获得它们。这是根据推荐的JDBC习惯用法完全重写 getUser（）方法：
Then, you need to ensure that you close the resources in the finally block in the reversed order as you've acquired them in the very same try block. Here's a complete rewrite of your getUser() method according the recommended JDBC idiom:
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修改为数据库列字段，使其成为自动增量PK，还要注意我将修复为 为如果 - 只有一个用户使用此名称，对吗？ - 还要注意，如果没有已知用户可以轻松返回 null 之后检查）
(note that I fixed the user ID to be a database column field, make it an auto incremental PK, also note that I fixed the while to be an if -there is only 1 user with this name, right?-, also note that it returns null when there's no known user which allows for easy checking afterwards)
- How often should Connection, Statement and ResultSet be closed in JDBC?
- JDBC MySql connection pooling practices to avoid exhausted connection pool
- Am I Using JDBC Connection Pooling?
您的 checkUsersLogin（）和 checkParameter（）方法也很可疑，但这是另一个问题。
Your checkUsersLogin() and checkParameter() approaches are also fishy, but that's subject for a different question.
这篇关于我如何在下面的几个方法中关闭resultSet，prepareStatement，conn以避免rs close和连接池被卡住的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
