Solved

Question about SQL ResultSet

Posted on 1998-11-04
4
178 Views
Last Modified: 2010-03-30
I need count the total number of these people who want to run with somebody whose request is "late". The "Request" includes: "early", "late", or "with #somebodyBibNo"(such as with #100).
The following function doCount() can not get the next record from while(rs1.next()). But doCountTest() can. Could you tell me what the problem is? How can I do a query inside another query?Thanks!!

public ResultSet doQuery() throws SQLException  {    
 ResultSet rs;
 String  query = "SELECT * FROM Competitors WHERE Request LIKE 'with%'";
 rs = db.getStatement().executeQuery(query);
 return rs;
 }
 
 public ResultSet doQuery1(int BibNo) throws SQLException  {
  ResultSet rs;
 String  query = "SELECT * FROM Competitors WHERE BibNo = " + BibNo;
  rs = db.getStatement().executeQuery(query);
  return rs;
 }


public void doCount()  throws SQLException{
      String new_request;
      int somebodyBibNo;
      ResultSet rs1, rs2;

      rs1 = doQuery();
      while(rs1.next()){
        new_request = rs1.getString("Request");
       somebodyBibNo = Integer.parseInt(new_request.substring(6));
       rs2 = doQuery1(somebodyBibNo);         
       if(rs2.next()){
            new_request = rs2.getString("Request");
            if (new_request.equals("late"))
              countlate = countlate + 1;
        }
      }
  }


public void doCountTest()  throws SQLException{
      String new_request;
      int somebodyBibNo;
      ResultSet rs1, rs2;

      rs1 = doQuery();
      while(rs1.next()){
          new_id = Integer.parseInt(rs1.getString("BibNo"));
         System.out.println(new_id);
         new_request = rs1.getString("Request");
         somebodyBibNo = Integer.parseInt(new_request.substring(6));
         rs2 = doQuery1(somebodyBibNo);         
     /*    if(rs2.next()){
            new_request = rs2.getString("Request");
            if (new_request.equals("late"))
              countlate = countlate + 1;
             } */
      }
  }


When I stored data such as:   
BibNo         Request
100               late
200              with #100
300              with #100
400              with #200

When I added a code after  while(rs1.next()) inside the doCountTest() to print out the "BibNo" for checking the results from the ResultSet, there were 200 and 100. They should be 200, 300 and 400. It seems that the 100 come from the rs2, not the rs1 when second time to do the code:
  new_id = Integer.parseInt(rs1.getString("BibNo"));

 Do you have a good idea to solve this problem?
0
Comment
Question by:Jun080298
  • 3
4 Comments
 
LVL 16

Accepted Solution

by:
heyhey_ earned 70 total points
Comment Utility
the first ResultSet returns this data

200              with #100
300              with #100
400              with #200

you start a second request for each of these records and you get only one hit (record) for each internal RecordSet (rs2)

200              with #100
   100               late   //the hit BibNo = 100

--
300              with #100
   100               late   //the hit BibNo = 100

--
400              with #200
   200              with #100    //the hit BibNo = 200

and of course you got 'late' only two times. (countlate = 2)

I am not sure what do you want to implement. If you want to traverse the tree style structure, you will need another approach.
<Code>
late - 100
        |
        |-like- 200
        |
        |-like- 300
                 |
                 |-like- 400
</CODE>

The basic (but not best :) approach is to for each element (BibNo) to find his father (like#100) the look at his father's father (grandfather) and so on ... until you reach early or late.

(for 400 you check if it is early or late, then check his father 300 if it is early or late, then check 100 and ok - it's '<B>late</B>'

of course
1. this may result in too many database calls.
2. you can have problems, if this isn't exactly tree, but cyclic graph

hope this helps
  heyhey
0
 

Author Comment

by:Jun080298
Comment Utility
Thank you for your interesting my question. But sorry, the countlate is 1, not 2, and also the BibNo are 200 and 100 in order.

0
 
LVL 16

Expert Comment

by:heyhey_
Comment Utility
???
>>But sorry, the countlate is 1, not 2, and also the BibNo are 200 and 100 in order.
???
it seems that i don't understand you ...

so the first resultSet should return all 'people' whose requst is 'late' and second resultSet shoul return the number of these people who want to run with somebody (whose request is "late").

so there is only one 'person' which is late (100)
and there are two persons that 'want to run with somebody whose request is "late") - that's 200 and 300 ...

if you need a tree like hierachy, you'll need another approach ...
0
 
LVL 16

Expert Comment

by:heyhey_
Comment Utility
ok i looked closer and it seems that your SQLs are messed
(you extract the 'with#..." persons and then count the ones from the "..." that are late ...

why don't do it this way

  String sql 1 = "SELECT * FROM Competitors WHERE Request = late"

to find all that are late
for every one of them

  String sql 1 = "SELECT * FROM Competitors WHERE Request = with#" + curLate;

will find out all that "want to run with somebody whose request is "late""

you can count them and print them ...

(if you need to find all that "want to run with somebody that wants to run with somebody whose request is "late""
you have to make another (third) result set and so on ...


0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
copyEndy  challenge 15 54
sumHeights2  challenge 7 75
array11 challenge 16 50
XML Paring  Error - Premature end of file. 7 55
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now