Get Length of ResultSet?
I can get the length of resultset by iterating to the last row. But i want to display it early . I've a jsp page which displays the data from the database and it's in tabular format, so i want to display the total row effected(number of rows)of a particular resultset on the top of the tabular format data showing the data on the jsp page.
So, i want to display the total number of row effected from the particular resultset without iterating through the last row.
Help me !!!
in advance thanks....
# 1
Hi there,
As per your question to my knowledge caching of the resultset obtained is very much important else where other than for scrollable resultsets this getting size is not really possible.
U may go about giving it a try with RowSets
ResultSet rs = pstmt.executeQuery();
CachedRowSet crs = new CachedRowSetImpl();
crs.populate(rs);
int SIZE = crs.size();
just re-check whether this helps u or not......
REGARDS,
RaHuL
# 2
Hi,
AFAIK there is no reliable way to get the 'length' of a ResultSet. But There is a work around. Before retrieving the ResultSet do a 'SELECT count(*) ...' with the same table, joins and where conditions.
E.g.
you want to know how many records will be returned if you do a 'select * from MYTABLE where MYTABLE.ID > 123' .
DO something like that
Statement stmt = con.createStatement() ;
ResultSet rs = stmt.executeQuery("select count(MYTABLE.id) from MYTABLE where MYTABLE.ID > 123") ;
rs.next() ;
BigDecimal bdRowCount = rs.getBigDecimal(1) ;
bdRowCount no contains the number of rows that will be returned in your select statement.
# 3
Read through the result set, putting each of its rows into an object that you have designed for this data.
Once you have done that, you have a data structure that contains all the data, and now you know how many rows there are. Display this information first, then display the table by iterating through the data structure.
# 4
Thanks a lot.. It worked .....
One more thing i would like to ask u, can u tell me how to retrieve the sum of particular column. Suppose i've the SQL query as "select sum(column_name) from table_name" . I want to display my count on the beginning of the table in my jsp page!!! Also tell me the best book from which i can get cammand on JDBC and SQL 2005.
In advance, thanks for giving the suggesitons and solutions for my problem....
# 5
Everything you do on tables and databases depends on how many data you expect to be in that database.
e.g. You have a table with 10 rows in it. No problem creating a datastructure (like a vector) with all rows in it and than get the size (or length) of that structure.
Next example. You have a table with 20 columns and expect over a million rows in it. If you do it thesame way again you will most probably end up with a OutOfMemoryException . If you are unlucky garbage collection will not work after that and your machine hangs. In an applicationserver this is a worst case scenario.
Let the database do that work for. It is designed to do it. And even if there are only a thousand rows in it, the use of "select count" or "select sum" will take less time, than adding it up yourself in java.
select sum(column_name) from table_name is a vaild sql statement. Use it and don't hand this over to java !
If you do programming for a customer and his application server starts hanging because garbage collection can't handle the amount of data (unessecarily created) they will have someone take a look at the code. Believe me this is the last time you programmed for that customer.
