整个数据操作如下。我想在不干扰下一个连接的情况下关闭每个资源。我应该把构造函数改为connection（）方法吗？然后有一个disconnect（）方法，但这样做之后我应该在哪里
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()方法，它必须看起来像这样：
public UserBean getUser(String userName) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
// ...
然后，您需要确保以与在同一个try块中获取资源相反的顺序关闭finally块中的资源。以下是根据推荐的JDBC习惯用法对getUser()方法的完整重写
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，还注意，我已将while固定为if-只有1个用户具有此名称，对吧？-还注意，当没有已知用户时，它会返回null，这便于以后检查）
另请参阅：
- JDBC中应该多久关闭一次Connection、Statement和ResultSet
- JDBC MySql连接池做法以避免耗尽连接池
- 我在使用JDBC连接池吗
你的checkUsersLogin()和checkParameter()方法也很可疑，但这是另一个问题的主题。
您必须使用反向顺序关闭每个可关闭的对象
如果打开订单：
连接
PreparedState
结果集
则关闭顺序为：
结果集
PreparedState
连接
您可以将其放入finally块中。这是一般规则。
您应该使用Java 7 try with resources。是的，当你问这个问题的时候，它已经出来了！）
大致如下：
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（）的文档