how to optimize huge number of data return from resultset

Question

my program is for getting result from exam taken, and convert it into excel. it contains a lot of people take the exam. my problem is i can't convert it to excel when my data is too huge, it works fine if my data below than 70. above 70, it just print out in the webpage, not convert into excel. i got error like "too many connections". how to solve this? thanks in advance. below is my code :

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

Two things come to mind when reading your code:

First, it is not organized very well.

For example, pstatement2 is executed after pstatement1 but its data is read before reading the data of rs. So the first step would be to bring some order to your code. (This also makes it easier to spot potential issues).

Second, db.getDBConnection().prepareStatement(sql); ... db.closeDBConnection(); sounds fishy. DBConnection is not a standard JDBC class, so I don't know what it does. But you call db.closeDBConnection(); multiple times in your code which sounds strange. Usually, you should only open one single connection at start and close it at the end. So maybe

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

already helps in your case.

And a final remark: Even if you fix your connection problem (which most likely comes from leaking Connection obejcts), for a big database you'll most likely end up with poor performance, because you select dependent data yourself. You should think about combining your queries using JOIN, because the database is much faster than a client when it comes to join dependent tables.