• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8995
  • Last Modified:

Getting the number of rows returned by resultset.getFetchSize()

Hi Experts,

Just having a small problem with getting the number of rows returned from a db using the getFetchSize() ftn. Have a look @ the code below:

//variable rooms is a JTextField
//variable room_type is a JCheckBoxGroup

 public String searchs(){  
        String theText = room_type.getSelectedCheckbox().getLabel();
        try
        {     String filename = "H:/db2.mdb";
                 String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
                 database+= filename.trim() + ";DriverID=22;READONLY=False}";
                 // get the connection from the DriverManager*/
                  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  Connection con = DriverManager.getConnection( database ,"","");
                  Statement stmt = con.createStatement();                  
                 
                  System.out.println("theText: "+theText);
                  String theQuery = new String ("SELECT * from RoomType WHERE Room_Type  '"             +   theText + "'" );
                  System.out.println("theQuery: "+theQuery);
               
                    ResultSet rs = stmt.executeQuery(theQuery);
                 
                    System.out.println("rs size: "+rs.getFetchSize());
                    int numResults = rs.getFetchSize();
                    System.out.println(numResults);
                    if(numResults !=0){
                        rooms.setText(String.valueOf(numResults));
                    }
                    else
                        rooms.setText("None");
                                 
       }
        catch (Exception err)
                    {
                        System.out.println("ERROR: " + err);
                    }      
           return theText;        
    }


THE PROBLEM: I always seem to be getting the value 1 for numResults (= rs.getFetchSize) and yet there are obviously more values of rows that fit the description within the database.
Below is an example of what is printed out to System:

 
theText: Single
theQuery: SELECT * from RoomType WHERE Room_Type ='Single'
rs size: 1
1

Bonus marks for a helpful response within 2 hours as I need it asap.
Thanks

TheEastern


0
TheEastern
Asked:
TheEastern
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
neonlinesProject managerCommented:
getFetchSize retrieves the number of result set rows that is the default fetch size for result sets generated from this Statement object. If this Statement object has not set a fetch size by calling the method setFetchSize, the return value is implementation-specific.

It returns the default fetch size for result sets generated from this Statement object.

to get the size of ur resultset u try with the following code

       int count = 0;
        rs.last();
        count = rs.getRow();
        rs.beforeFirst();

I think this will solve ur problem


0
 
neonlinesProject managerCommented:
Code explaination as follows

// Initialize a variable
int count = 0;

//Move the resultset to Last row
rs.last();

//Get the current row number (The first row is number 1, the second number 2, and so on)
count = rs.getRow(); // 0 if there is no current row

//Again move the resultset pointer to first row for further operation with ur resultset
rs.beforeFirst();

0
 
MogalManicCommented:
The getFetchSize() method only returns the # of rows fetched by the cursor.  For a forward only cursor(the default) there is no way to get the row count until you have retrieved ALL of the rows in the cursor.  The only way to get a row count of a result set is to go to the LAST row and get the row number.  This can be done using a TYPE_SCROLL_INSENSITIVE  or TYPE_SCROLL_SENSITIVE  cursor like this:

                  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);                  
                 
                  System.out.println("theText: "+theText);
                  String theQuery = new String ("SELECT * from RoomType WHERE Room_Type  '"             +   theText + "'" );
                  System.out.println("theQuery: "+theQuery);
               
                    ResultSet rs = stmt.executeQuery(theQuery);
                 
                    rs.last();
                    int numResults = rs.getRow();
                    System.out.println(numResults);
                    if(numResults !=0){
                        rooms.setText(String.valueOf(numResults));
       
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
TheEasternAuthor Commented:
Guys,
Thanks for the quick responses.
neonlines :
This is the output I'm getting from the code:

this is a single room
theText: Single
theQuery: SELECT * from RoomType WHERE Room_Type ='Single'

ERROR: java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY


MogalManic:
Gonna try your code now. will get back in 5

TheEastern
0
 
TheEasternAuthor Commented:
You guys are stars!

MagalManic:
Your code is working like a charm

neonlines:
Gonna give you some points coz you pointed me in the right direction, plus some of the code you provided is contained in the accepted answer.

Increased the point value as promised.

Cheers,

TheEasten
0
 
CEHJCommented:
Your query will still use cursor-like functionality and will not perform well probably. If all you want is the row count and don't wish to retrieve rows then why not do:

String theQuery = "SELECT Count(Room_Type) from RoomType WHERE Room_Type = '" +   theText + "'" ;

?
0
 
CEHJCommented:
?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now