How do I get the size of a java sql ResultSet

Question

Shouldn t this be a pretty straightforward operation  However  I see there s neither a size   nor length   method

User · Answer

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

User · Answer

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  gt   1            System out println  Product ID already exists           else           System out println  New Product ID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Do a SELECT COUNT    FROM     query instead   OR  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

User · Answer

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

User · Answer

int i   0  while rs next          i

User · Answer

int i   0  while rs next          i

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Do a SELECT COUNT    FROM     query instead   OR  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt   1            System out println  Product ID already exists           else           System out println  New Product ID

User · Answer

Do a SELECT COUNT    FROM     query instead   OR  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Do a SELECT COUNT    FROM     query instead   OR  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

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to sql

Examples related to jdbc

Examples related to resultset

Examples related to record-count