Solved

Question about SQL ResultSet

Posted on 1998-11-04
4
184 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
ID: 1226976
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
ID: 1226977
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_
ID: 1226978
???
>>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_
ID: 1226979
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tomcat administrtor 12 68
hibernate example issues from command prompt 10 63
How to determine if a string is a valid SHA value 7 43
Java array 10 53
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

679 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