Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Question about SQL ResultSet

Posted on 1998-11-04
4
Medium Priority
?
191 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 140 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

963 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