Servlet自学第30讲(最后一讲)：SQLHelper工具类的改进

原SQLHelper工具类的不足

1. 我们的连接数据库的变量都是static，这样有一定的危险。如果访问量过大，可能造成一些用户的超时。我们可以这样做，把static变量变成非static，在调用SQLHelper时，创建SQLHelper对象，然后调用其方法。
   2.我们的SQLHelper查询数据时，没有在本类中关闭，不是好习惯。
   解决方案如下：

public ArrayList executeQuery(String sql, String[] parameters) {
        connection();//连接数据库
        ArrayList list = null;
        try {
            pst = ct.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (parameters != null) {
                //    System.out.println(sql);
                for (int i = 0; i < parameters.length; i++) {
                    //  System.out.println(i+"    "+parameters[i]);
                    pst.setString((i + 1), parameters[i]);
                }
            }
            rs = pst.executeQuery();
            list = new ArrayList();

            ResultSetMetaData rmd = rs.getMetaData();
            int column = rmd.getColumnCount();
            while (rs.next()) {
                Object obj[] = new Object[column];
                for (int i = 0; i < column; i++) {
                    obj[i] = rs.getObject(i+1);
                }
                list.add(obj);
            }

        } catch (Exception ex) {
            Logger.getLogger(DBUtil.class.getName()).log(Level.SEVERE, null, ex);
            throw new RuntimeException(ex.getMessage());
        } finally {
            closeConn(); //关闭资源
        }
        return list;
    }

    public ArrayList<HashMap> executeQuery2(String sql, String[] parameters) {
        connection();
        ArrayList list = null;
        try {
            pst = ct.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (parameters != null) {
                //    System.out.println(sql);
                for (int i = 0; i < parameters.length; i++) {
                    //  System.out.println(i+"    "+parameters[i]);
                    pst.setString((i + 1), parameters[i]);
                }
            }
            rs = pst.executeQuery();
            list = new ArrayList();
            int column = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                HashMap obj = new LinkedHashMap();
                for (int i = 0; i < column; i++) {
                    String key = rs.getMetaData().getColumnName(i+1);
                    Object val = rs.getObject(i+1);                   
                    obj.put(key, val);
                }
                list.add(obj);
            }

        } catch (Exception ex) {
            Logger.getLogger(DBUtil.class.getName()).log(Level.SEVERE, null, ex);
            throw new RuntimeException(ex.getMessage());
        } finally {
            closeConn();
        }
        return list;
    }
//executeQuery2使用方法
    public ArrayList<Users> getAll() {
        ArrayList list = new ArrayList();
        String sql = "select * from users";
        String[] parameters = {};
        DBUtil db = new DBUtil();
        ArrayList<HashMap> al = db.executeQuery2(sql, parameters);
        for(HashMap map:al){            
            Users u = new Users();
            u.setUserid(Integer.parseInt(map.get("userid").toString()));
            u.setUname(map.get("uname").toString());
            u.setEmail(map.get("email").toString());
            u.setPwd(map.get("pwd").toString());
            u.setGrade(Integer.parseInt(map.get("grade").toString()));
            list.add(u);            
        }
        return list;
    }