CODEWITHSUNDEEP
javasqldatabasejdbciteration
Tianxin Liu
Tianxin Liu

Reputation: 13

JDBC nested while loop

there. I am a beginner using java. I want to do a nested while loop for my SQL query results.

My original result was like following,

eno ename   title   date_visit  ssn         pname       charge    
103 Jekyl   Doctor  20170717    946883650   Gershwin    125
106 Ratchet Nurse   20170817    946883650   Gershwin    125
103 Jekyl   Doctor  20170917    946883650   Gershwin    182
104 Caligari    Doctor  20170707    831287780   Schubert    182
106 Ratchet Nurse   20170930    799023031   Haydn   190
102 Welby   Doctor  20170818    416806352   Bernstein   210
102 Welby   Doctor  20170808    874136439   Brahms  245
104 Caligari    Doctor  20170808    796235486   Wagner  245
102 Welby   Doctor  20170929    445139565   Chopin  405
102 Welby   Doctor  20170910    874136439   Brahms  512
103 Jekyl   Doctor  20170910    524246868   Verdi   512
103 Jekyl   Doctor  20170909    129141378   Vivaldi 667
103 Jekyl   Doctor  20170909    524246868   Verdi   667

I wrote my java statement like following,

   //Now we execute our query and store the results in the myresults object:       
   ResultSet myresults1 = stmt1.executeQuery("SELECT DISTINCT eno, ename, title FROM Staff_Activity");
   ResultSet myresults2 = stmt2.executeQuery("SELECT eno, ename, title, date_visit, ssn, pname, SUM(charge) AS total_charge FROM Staff_Activity GROUP BY eno, ename, title, date_visit, ssn, pname");

   System.out.println("Employee_ID\tEmployee_Name\tTitle");
   System.out.println("-----------\t-------------\t------"); //Print a header

   while (myresults1.next()) { //pass to the next row and loop until the last         
      System.out.println(myresults1.getInt("eno") + "\t\t" +  myresults1.getString("ename") + "\t\t" +  myresults1.getString("title"));

      while (myresults2.next()) {
          if (myresults1.getInt("eno")==(myresults2.getInt("eno"))) {
              System.out.println(myresults2.getInt("date_visit") + "\t\t" +  myresults2.getInt("ssn") + "\t\t" +  myresults2.getString("pname") + "\t\t" +  myresults2.getInt("total_charge"));
          }//Print the current row
        }
        System.out.println();
    }

I only got results for only one staff in my table,

Employee_ID     Employee_Name   Title
-----------     -------------   ------
103             Jekyl           Doctor
20170909                524246868               Verdi           667
20170909                129141378               Vivaldi         667
20170910                524246868               Verdi           512
20170717                946883650               Gershwin                125
20170917                946883650               Gershwin                182

106             Ratchet         Nurse

102             Welby           Doctor

104             Caligari                Doctor

Really want to know what was wrong with my code.

Thanks in advance.

Upvotes: 1

Views: 720

Answers (1)

Loris Securo
Loris Securo

Reputation: 7638

The first time the second loop is executed, it is going to reach the end of myresults2; once that happens myresults2.next() will always return false, so it is going to work only the first time.

If the ResultSet is forward only you can't reset the cursor position to the start, so you'll have to re-execute the second query every time you entirely consume its ResultSet.

To make the ResultSet scrollable (not forward only) and therefore be able to reset its cursor position without re-executing the query, see this example: https://stackoverflow.com/a/8033197/6245535.

Another optimization would be to execute the second query filtered using a WHERE clause with the current "eno" from the first loop (preferably using a PreparedStatement) instead of always checking if the "eno" code matches with the if statement.

Upvotes: 2

Related Questions