Solved

Question about SQL ResultSet

Posted on 1998-11-04
4
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
jsp login check 12 65
Better way to make a string with template variables in java 3 52
glassfish admin console 1 47
using executorService 3 19
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses

734 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