how can i close the resultSet, prepareStatement, conn in several methods below to avoid rs close and connection pool getting jammed

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; } }

Upvotes: 1

Answers (3)

Marsellus Wallace

Reputation: 18601

You should use Java 7 try-with-resources. Yes it was already out when you asked the question! ;)

Something along these lines:

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!!
}

NOTE: See documentation for AutoCloseable .close()

Upvotes: 0

BalusC

Reputation: 1108692

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;
    // ...

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;
    // ...

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;
}

(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 can i close the resultSet, prepareStatement, conn in several methods below to avoid rs close and connection pool getting jammed

Answers (3)

See also:

Related Questions