java - 如何在下面的几个方法中关闭resultSet、prepareStatement、conn以避免rs关闭和连接池堵塞

Question

整个数据操作如下。我想关闭每个资源而不干扰下一个连接。我应该将构造函数更改为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;
    }


}

Answer 1

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

这是错误的。您不应该在类(class)级别声明它们。您应该在方法级别声明它们。作为示例，我将采用您的 getUser() 方法，它必须如下所示:

public UserBean getUser(String userName) {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    // ...

然后，您需要确保以相反的顺序关闭 finally block 中的资源，因为您是在同一个 try block 中获取资源的。下面是根据推荐的 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，以便事后轻松检查)

另请参阅:

• 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() 方法也很可疑，但这是另一个问题的主题。