?
Solved

How to retrieve No. of records in JDBC

Posted on 2003-03-26
23
Medium Priority
?
450 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
[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
  • 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
Independent Software Vendors: 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!

 
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

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

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…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

764 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