Reputation: 15217

How do I get the size of a java.sql.ResultSet?

Shouldn't this be a pretty straightforward operation? However, I see there's neither a size() nor length() method.

Upvotes: 321

Answers (16)

Jericho P. Dupo

Reputation: 1

The comments I can see here are somewhat too manual and I so happen to come across a simpler answer. Hope this helps.

resultSet.getFetchSize();

It returns an int of all the data you got after executing the query.

Upvotes: -2

Peter.Chu

Reputation: 365

String sql = "select count(*) from message";
ps =  cn.prepareStatement(sql);

rs = ps.executeQuery();
int rowCount = 0;
while(rs.next()) {
    rowCount = Integer.parseInt(rs.getString("count(*)"));
    System.out.println(Integer.parseInt(rs.getString("count(*)")));
}
System.out.println("Count : " + rowCount);

Upvotes: 2

user7120462

Reputation: 26

Easiest approach, Run Count(*) query, do resultSet.next() to point to the first row and then just do resultSet.getString(1) to get the count. Code :

ResultSet rs = statement.executeQuery("Select Count(*) from your_db");
if(rs.next()) {
   int count = rs.getString(1).toInt()
}

Upvotes: 0

finnw

Reputation: 48619

Do a SELECT COUNT(*) FROM ... query instead.

int size =0;
if (rs != null) 
{
  rs.last();    // moves cursor to the last row
  size = rs.getRow(); // get row id 
}

In either of the case, you won't have to loop over the entire data.

Upvotes: 305

ReMaX

Reputation: 1

Give column a name..

String query = "SELECT COUNT(*) as count FROM

Reference that column from the ResultSet object into an int and do your logic from there..

PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, item.getProductId());
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
    int count = resultSet.getInt("count");
    if (count >= 1) {
        System.out.println("Product ID already exists.");
    } else {
        System.out.println("New Product ID.");
    }
}

Upvotes: -1

parksangdonews

Reputation: 11

Today, I used this logic why I don't know getting the count of RS.

int chkSize = 0;
if (rs.next()) {
    do {  ..... blah blah
        enter code here for each rs.
        chkSize++;
    } while (rs.next());
} else {
    enter code here for rs size = 0 
}
// good luck to u.

Upvotes: 1

Dan

Reputation: 970

I got an exception when using rs.last()

if(rs.last()){
    rowCount = rs.getRow(); 
    rs.beforeFirst();
}

java.sql.SQLException: Invalid operation for forward only resultset

it's due to by default it is ResultSet.TYPE_FORWARD_ONLY, which means you can only use rs.next()

the solution is:

stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY);

Upvotes: 14

Vit Bernatik

Reputation: 3792

[Speed consideration]

Lot of ppl here suggests ResultSet.last() but for that you would need to open connection as a ResultSet.TYPE_SCROLL_INSENSITIVE which for Derby embedded database is up to 10 times SLOWER than ResultSet.TYPE_FORWARD_ONLY.

According to my micro-tests for embedded Derby and H2 databases it is significantly faster to call SELECT COUNT(*) before your SELECT.

Here is in more detail my code and my benchmarks

Upvotes: 5

Israel Hernández

Reputation: 1

I was having the same problem. Using ResultSet.first() in this way just after the execution solved it:

if(rs.first()){
    // Do your job
} else {
    // No rows take some actions
}

Documentation (link):

boolean first()
    throws SQLException
Moves the cursor to the first row in this ResultSet object.

Returns:

true if the cursor is on a valid row; false if there are no rows in the result set

Throws:

SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY

SQLFeatureNotSupportedException - if the JDBC driver does not support this method

Since:

1.2

Upvotes: 0

Anptk

Reputation: 1123

The way of getting size of ResultSet, No need of using ArrayList etc

int size =0;  
if (rs != null)   
{  
rs.beforeFirst();  
 rs.last();  
size = rs.getRow();
}

Now You will get size, And if you want print the ResultSet, before printing use following line of code too,

rs.beforeFirst();

Upvotes: 4

bhaskar

Reputation: 163

int i = 0;
while(rs.next()) {
    i++;
}

Upvotes: 13

clausavram

Reputation: 576

I checked the runtime value of the ResultSet interface and found out it was pretty much a ResultSetImpl all the time. ResultSetImpl has a method called getUpdateCount() which returns the value you are looking for.

This code sample should suffice:
ResultSet resultSet = executeQuery(sqlQuery);
double rowCount = ((ResultSetImpl)resultSet).getUpdateCount()

I realize that downcasting is generally an unsafe procedure but this method hasn't yet failed me.

Upvotes: 1

Unai Vivi

Reputation: 3101

Well, if you have a ResultSet of type ResultSet.TYPE_FORWARD_ONLY you want to keep it that way (and not to switch to a ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE in order to be able to use .last()).

I suggest a very nice and efficient hack, where you add a first bogus/phony row at the top containing the number of rows.

Example

Let's say your query is the following

select MYBOOL,MYINT,MYCHAR,MYSMALLINT,MYVARCHAR
from MYTABLE
where ...blahblah...

and your output looks like

true    65537 "Hey" -32768 "The quick brown fox"
false  123456 "Sup"    300 "The lazy dog"
false -123123 "Yo"       0 "Go ahead and jump"
false       3 "EVH"    456 "Might as well jump"
...
[1000 total rows]

Simply refactor your code to something like this:

Statement s=myConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                         ResultSet.CONCUR_READ_ONLY);
String from_where="FROM myTable WHERE ...blahblah... ";
//h4x
ResultSet rs=s.executeQuery("select count(*)as RECORDCOUNT,"
                           +       "cast(null as boolean)as MYBOOL,"
                           +       "cast(null as int)as MYINT,"
                           +       "cast(null as char(1))as MYCHAR,"
                           +       "cast(null as smallint)as MYSMALLINT,"
                           +       "cast(null as varchar(1))as MYVARCHAR "
                           +from_where
                           +"UNION ALL "//the "ALL" part prevents internal re-sorting to prevent duplicates (and we do not want that)
                           +"select cast(null as int)as RECORDCOUNT,"
                           +       "MYBOOL,MYINT,MYCHAR,MYSMALLINT,MYVARCHAR "
                           +from_where);

Your query output will now be something like

1000 null     null null    null null
null true    65537 "Hey" -32768 "The quick brown fox"
null false  123456 "Sup"    300 "The lazy dog"
null false -123123 "Yo"       0 "Go ahead and jump"
null false       3 "EVH"    456 "Might as well jump"
...
[1001 total rows]

So you just have to

if(rs.next())
    System.out.println("Recordcount: "+rs.getInt("RECORDCOUNT"));//hack: first record contains the record count
while(rs.next())
    //do your stuff

Upvotes: 21

Ben

Reputation: 19

theStatement=theConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet theResult=theStatement.executeQuery(query); 

//Get the size of the data returned
theResult.last();     
int size = theResult.getRow() * theResult.getMetaData().getColumnCount();       
theResult.beforeFirst();

Upvotes: 1

JeeBee

Reputation: 17546

ResultSet rs = ps.executeQuery();
int rowcount = 0;
if (rs.last()) {
  rowcount = rs.getRow();
  rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
}
while (rs.next()) {
  // do your standard per row stuff
}

Upvotes: 105

CounterSpell

Reputation: 123

It is a simple way to do rows-count.

ResultSet rs = job.getSearchedResult(stmt);
int rsCount = 0;

//but notice that you'll only get correct ResultSet size after end of the while loop
while(rs.next())
{
    //do your other per row stuff 
    rsCount = rsCount + 1;
}//end while

Upvotes: 3

How do I get the size of a java.sql.ResultSet?

Answers (16)

Related Questions