Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to retrieve No. of records in JDBC

Posted on 2003-03-26
23
Medium Priority
?
452 Views
Last Modified: 2011-04-14
Hello Ppl,

I am firing a simple query like

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM MyTable");

I want to find out the number of rows returned by this query. How do i do this?

Like in ASP there is a method RecordCount .... isin't there something similar in JDBC.
0
Comment
Question by:bhayzone
  • 8
  • 7
  • 4
  • +3
23 Comments
 
LVL 9

Expert Comment

by:yongsing
ID: 8208914
You can count the number of records as you iterate through the result set:

int recordCount = 0;
while (rs.next()) {
  ++recordCount;
}
0
 
LVL 9

Accepted Solution

by:
Ovi earned 150 total points
ID: 8208947
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT count(id) FROM MyTable");
rs.next();
int count = rs.getInt();
0
 
LVL 9

Expert Comment

by:Ovi
ID: 8208963
This method will delegate to the server the job of counting rows and not to your application. Calculating the count on the client side especially when you are using 'select * from ...' can be a very time-consuming method. Is known that reading from a ResultSet is expensive.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 9

Expert Comment

by:yongsing
ID: 8208973
How about this:

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT count(*) FROM MyTable");
rs.next();
int recordCount = rs.getInt();
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8208997
I don't think that's what he wants.... he actually wants to know the number of rows in a ResultSet, not by executing another query "SELECT COUNT (*)....".

What if there is a complex query like:

"SELECT F1, F2,.... FROM TABLE1, TABLE2,.... WHERE .... "

and he wishes to count the number of elements in this query's ResultSet, not the number of rows in a table.

Mayank.
0
 

Author Comment

by:bhayzone
ID: 8209001
guys guys .... i wanted to know if there were any predefined methods in the JDBC API to do this. Till date, this is what i have been doing ... either iterating over the resultset or using count .... i was just wondering if there is some better way of doing this.

For eg. I want to retrive all the Names (a field in MyTable) and store it in an array. Now to create the array, I need to know the size in advance (dont wanna make initial guesses). As of now, I use a Vector or an ArrayList to hold the elements and then use the toArray() method on the collection. All this i have to do only because i dont know how big my array is gonna be in advance. Isint there a better way out to find out the number of rows
0
 

Author Comment

by:bhayzone
ID: 8209014
yeah Mayank you're absolutely right .... i think i cudnt frame my question properly .... thanks for the correction ... and how bout the answer ;)
0
 
LVL 9

Expert Comment

by:yongsing
ID: 8209030
You should just stick to Vector and ArrayList. Why do you need to convert to an array after that?
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8209032
Such a method is not there in ResultSet or in ResultSetMetaData. Maybe that you there could be a method to convert a ReslutSet to an Object array or something of that sort.... then we can use the length property.... will have to search for more.

Mayank.


0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8209035
Such a method is not there in ResultSet or in ResultSetMetaData. Maybe that there could be a method to convert a ReslutSet to an Object array or something of that sort.... then we can use the length property.... will have to search for more.

Mayank.
0
 

Author Comment

by:bhayzone
ID: 8209038
There is a method in the ResultSet interface called as getFetchSize() .... could that be a solution. What does this method do. The API just says that it fetches the size for this ResultSet object. I have tried it earlier, without success ... so i guess, its not what i thought it to be.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8209050
Yeah.... it does something else.... there is also a setFetchSize ( int ), so obviously, it can't be the number of elements in the ResultSet :-(

Mayank.
0
 

Author Comment

by:bhayzone
ID: 8209053
Yongsing .... Vectors and ArrayLists and the other Collection objects are heavyweight objects as compared to an array. If there is some processing to be done on the elements .... using an array would result in far better perfomance. Another thing, if the elements were of some primitive datatype, then it makes absolutely no sense to use the collection objects to store and process them ... i mean you are paying a performance penalty. I always prefer using arrays (if i can). I am not sure if the others agree with my argument.
0
 

Author Comment

by:bhayzone
ID: 8209062
the only argument i have for JDBC having a predefined method is that you have a method for this purpose in ODBC called as RecordCount ... i just wanted to know if there is something like this in JDBC.
0
 
LVL 9

Expert Comment

by:yongsing
ID: 8209073
The purpose of using a collection object is to make your life easier. Using an array may have better performance, but I don't think that you'll be able to notice it.
0
 

Author Comment

by:bhayzone
ID: 8209074
Anyways, there is one more question .. which is not related to any technology.

I am pretty new here ... how do i accept answers from more than one of the people ... can i in the first place?
0
 
LVL 9

Expert Comment

by:yongsing
ID: 8209093
The answer to your question is that there is no method to determine the number of rows in the result set.
0
 
LVL 1

Expert Comment

by:umangjoshi
ID: 8209094
Statement stmt = con.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE,               ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
rs.last();
int reccount = rs.getRow();
0
 
LVL 9

Expert Comment

by:yongsing
ID: 8209104
You can split the points. Just post a question in community support asking them to do so, and include the link to this question.
0
 

Author Comment

by:bhayzone
ID: 8209106
but yon what how much do you loose by coding for performance ... if someone has no idea about things like these .. then its a seperate issue .... but if one is aware of these things ... dont u think its a better idea to implement them even though there mite not be any percieveable difference. Also sometime later, when ur application is being used heavily ... it just mite matter. Another example i can give u is to use StringBuffers instead of concatinating strings repeatedly. You will argue again that the perfomance is not much. But its better to plan for the future and code for it too. I think its an individual prefrence.
0
 

Author Comment

by:bhayzone
ID: 8209124
Hey umangioshi .... can u explain the above code that you have written.

rs.last will i guess, take u to the last row in the resultset.

what is rs.getRow()????
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 8209130
Don't get hung up about performance. You'll never notice the differences among the container objects you mentioned.
Issue the query and store the result in a java.util.LinkedList. Forget the row count.
0
 
LVL 9

Expert Comment

by:yongsing
ID: 8209154
No doubt that using arrays are faster. But in using the collection classes, you speed up your coding, and you won't have ugly codes everywhere trying to manipulate the arrays (increasing sizes, etc). Also, it's easier for a maintenance person to read.
0

Featured Post

Industry Leaders: 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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
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…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses
Course of the Month11 days, 6 hours left to enroll

571 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