java - 如何优化结果集返回的大量数据

Question

我的程序是从考试中获取结果，并将其转换为Excel。它包含很多人参加考试。我的问题是当我的数据太大时我无法将其转换为Excel，如果我的数据低于70，它可以正常工作。高于70，它只是在网页中打印出来，而不是转换为Excel。我收到“连接太多”之类的错误。怎么解决这个问题？提前致谢。下面是我的代码:

String sql = "SELECT * FROM surveydata WHERE survey_wfverid=? LIMIT 100";// AND ID='c6500937-3a9e-42f2-b01a-c9174f9e2304'";

    try{

        pstatement = db.getDBConnection().prepareStatement(sql);
        pstatement.setString(1, project_id);
        rs = pstatement.executeQuery();

        int proj_id = Integer.parseInt(project_id);
        int wfstnodeid = 0;

        if (proj_id==10869){
                System.out.println("project_id 2 : " +proj_id);
                strRecord = "\"ID\",\"Start\",\"End\",\"Department\",\"Topic\",\"Division\",\"Name\",\"StaffCode\",\"Designation\",\"TotalScore\",\"Answer\",,,,,,,,,,,,,,,,,,,,\n";
            }
        else if (proj_id==10293){
                System.out.println("project_id 3 : " +proj_id);
                strRecord = "\"ID\",\"Start\",\"End\",\"Department\",\"Topic\",\"Name\",\"StaffCode\",\"Designation\",\"TrainerName\",\"TotalScore\",\"Answer\",,,,,,,,,,,,,,,,,,,,\n";
                }

            pstatement2 = db.getDBConnection().prepareStatement("SELECT wfstnodeid FROM wfstation WHERE wfstdesc=? AND wfverid=?");
            pstatement2.setString(1, "Start");
            pstatement2.setString(2, project_id);
            rs2 = pstatement2.executeQuery();
            wfstnodeid = 0;
            while(rs2.next()){
                wfstnodeid = rs2.getInt("wfstnodeid");
                System.out.println("wfstnodeid "+wfstnodeid);
            }
            rs2.close();
            pstatement2.close();


            while(rs.next()){

            String department = null;
            String topic = null;
            String division = null;

            int wflinktoid = 0;
            int wfformid = 0;
            int score = 0;


             ByteArrayInputStream localByteArrayInputStream = new ByteArrayInputStream(rs.getBytes("DATA"));
             ObjectInputStream localObjectInputStream = new ObjectInputStream(localByteArrayInputStream);
             Map<String, String[]> map = (Map<String, String[]>)localObjectInputStream.readObject();

            strRecord += "\"" + rs.getString("ID") + "\",";
            strRecord += "\"" + rs.getTimestamp("START").toString() + "\",";
            strRecord += "\"" + rs.getTimestamp("END").toString() + "\",";

            department = map.get("department")[0];
            topic = map.get("topic")[0];

            strRecord += "\"" + department + "\",";
            strRecord += "\"" + topic + "\",";

            if(proj_id==10869){

                if (map.get("division")==null){
                    division = "old samples";
                }

                else{
                    division = map.get("division")[0];
                }

                strRecord += "\"" + division + "\",";   
                strRecord += "\"" + map.get("11163")[0] + "\","; //name
                strRecord += "\"" + map.get("11163")[1] + "\","; //staff code 
                strRecord += "\"" + map.get("11163")[2] + "\","; //designation
                division = null;
            }

            else if(proj_id==10293){
                strRecord += "\"" + map.get("10322")[0] + "\","; //name
                strRecord += "\"" + map.get("10322")[1] + "\",";
                strRecord += "\"" + map.get("10322")[2] + "\",";
                strRecord += "\"" + map.get("10322")[3] + "\",";
            }

            //get routing topic -- start from here get topic id from wflinktoid
            pstatement2 = db.getDBConnection().prepareStatement("SELECT wflinktoid FROM WFLINK WHERE WFLINKFROMID=? AND wfrule=?");
            pstatement2.setInt(1, wfstnodeid);//station id
            pstatement2.setString(2, "department == '" + department + "' && topic == '" + topic + "'");
            rs2 = pstatement2.executeQuery();
            wflinktoid = 0;
            while(rs2.next()){
                wflinktoid = rs2.getInt("wflinktoid");

            }
            rs2.close();
            pstatement2.close();
            db.closeDBConnection();
            department = null;
            topic = null;

            pstatement2 = db.getDBConnection().prepareStatement("SELECT wfformid FROM wfsubsform where wfstationid=?");
            pstatement2.setInt(1, wflinktoid);
            rs2 = pstatement2.executeQuery();
            while(rs2.next()){
                wfformid = rs2.getInt("wfformid");
            }
            rs2.close();
            pstatement2.close();
            db.closeDBConnection();

            pstatement2 = db.getDBConnection().prepareStatement("select q.* from survey_questions q, nodelist n where q.fieldid = n.nlnodeid and  n.nlidatch=? and (q.exttype='checkbox' or q.exttype='radio') and q.answer is not null order by q.seq");
            pstatement2.setInt(1, wfformid);
            rs2 = pstatement2.executeQuery();
            score = 0;
            while(rs2.next()){

                if(map.get(rs2.getString("fieldid"))==null){
                    strRecord += "\"" + map.get(rs2.getString("fieldid")) + "\",";
                    if(rs2.getString("ANSWER").trim().equals(map.get(rs2.getString("fieldid")))){
                        score += Integer.parseInt(rs2.getString("SCORE"));//get score   
                    }   
                }

                else{

                    String map_answer = "";
                    int y = 0;

                    while (y < map.get(rs2.getString("fieldid")).length){
                        map_answer += map.get(rs2.getString("fieldid"))[y] + ",";
                        y++;    
                    }

                    map_answer = map_answer.substring(0, map_answer.length() - 1);

                //compare answer
                if(rs2.getString("ANSWER").trim().equals(map_answer)){
                    score += Integer.parseInt(rs2.getString("SCORE"));//get score   
                }
                }
                }
            rs2.close();
            pstatement2.close();
            db.closeDBConnection();

            strRecord += "\"" + score + "\",";

            pstatement2 = db.getDBConnection().prepareStatement("select q.* from survey_questions q, nodelist n where q.fieldid = n.nlnodeid and  n.nlidatch=? and (q.exttype='checkbox' or q.exttype='radio') and q.answer is not null order by q.seq");
            pstatement2.setInt(1, wfformid);
            rs2 = pstatement2.executeQuery();
            wfformid = 0;
            score = 0;
            while(rs2.next()){

                if(map.get(rs2.getString("fieldid"))==null){

                    strRecord += "\"" + map.get(rs2.getString("fieldid")) + "\",";
                    if(rs2.getString("ANSWER").trim().equals(map.get(rs2.getString("fieldid")))){
                        score += Integer.parseInt(rs2.getString("SCORE"));//get score   
                    }   
                }

                else{

                    String map_answer = "";
                    int y = 0;

                    while (y < map.get(rs2.getString("fieldid")).length){
                        map_answer += map.get(rs2.getString("fieldid"))[y] + ",";
                        y++;    
                    }

                    map_answer = map_answer.substring(0, map_answer.length() - 1);

                    strRecord += "\"" + map_answer + "\","; //print out user answer

                }       
                }
            strRecord = strRecord.substring(0, strRecord.length() - 1);
            strRecord += "\n";

            rs2.close();
            pstatement2.close();
            db.closeDBConnection();

        }//rs

        output = strRecord;

        rs.close();
        pstatement.close();
        db.closeDBConnection();

Answer 1

阅读代码时会想到两件事:

首先，它的组织得不是很好。

例如，pstatement2在pstatement1之后执行，但其数据在读取rs的数据之前读取。所以第一步是给你的代码带来一些顺序。 (这也使得更容易发现潜在问题)。

第二，db.getDBConnection().prepareStatement(sql); ... db.closeDBConnection(); 听起来很可疑。 DBConnection 不是标准的 JDBC 类，所以我不知道它是做什么的。但你打电话db.closeDBConnection(); 在代码中多次出现，这听起来很奇怪。通常，您应该在开始时只打开一个连接并在结束时关闭它。所以也许

Connection connection = db.getDBConnection();
pstatement = connection.prepareStatement(sql);
// ...
pstatement2 = connection.prepareStatement("SELECT ...");
// ...
// and at the end (in the finally block):
db.closeDBConnection();

已经对您的情况有所帮助。

最后一点:即使您修复了连接问题(很可能来自连接对象泄漏)，对于大型数据库，您很可能最终会获得较差的性能，因为您自己选择了依赖数据。您应该考虑使用 JOIN 组合查询，因为在连接依赖表时数据库比客户端快得多。