Get the size of a ResultSet in Java for SQLite

Question

I'm attempting to grab the size of a ResultSet. Now because I'm using SQLite, and SQLite only supports TYPE_FORWARD_ONLY cursors I'm trying to figure out how to grab it's size without throwing an error. This wont work:

int size = 0;    
rs.last();
size = rs.getRow();

Nor can I use a prepared statement to change the cursor to scroll-able. Are Is there anyway to get the size?

I've also tried creating a while loop method to handle this and it still doesn't seem to to work

 public static int getResultSetSize(ResultSet rs) throws SQLException{
     int size = 0;

     while(rs.next()){
         size++;
     }

     return size;
 }

Any help would be appreciated.

EDIT:

I've tried the following with no success

ResultSet rs = DBHelpers.getResultSet("SELECT COUNT(*) FROM USERS;");
    int count = 0;

    try {
        count =rs.getInt(0) ;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

The helper method for that is as follow:

 public static ResultSet getResultSet(String sql){
     ResultSet rs = null;;
        try (Statement stmt  = conn.createStatement()){
              rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }
        return rs;
    }

Conn is static connection variable initialized elsewhere in the program to point to the proper database and it has not caused any problems.

score 1 · Accepted Answer · answered Oct 19 '16 at 14:02

1

You should map your result to an entity. Create a list of those entities, and then simply get the size of the list.

If you want the size and the size only, then simply make a query to return a count of the result of your original query.

answered Oct 19 '16 at 14:02

facundop

482
6
28

I've tried the count but it says the result set is closed when I go to assign it to an int. – Jrawr Oct 19 '16 at 14:05
I believe your issue is in your try with resources. The rs is getting closed at the end of it. Remove the try with resources and make sure you are using a createStatement always instead of reusing the same rs. – facundop Oct 19 '16 at 14:09
Actually removing the try catch block helped and it works now. Thank you. – Jrawr Oct 19 '16 at 15:08

Jesus Peralta · Answer 2 · 2016-10-19T14:32:57.597

1

The column begins with index 1. Try this...

Removes the semicolon from your query

ResultSet rs = DBHelpers.getResultSet("SELECT COUNT(*) FROM USERS");

And then

while (rs.next()) {
            System.out.println(rs.getInt(1));
        }

Instead of

count =rs.getInt(0);

edited Oct 19 '16 at 14:32

answered Oct 19 '16 at 14:07

Jesus Peralta

661
1
8
18

score 0 · Answer 3 · answered Oct 19 '16 at 13:57

0

Do a count on the database for the same query...SELECT Count(*) your query....

Run that before you do your query and you should have the size of your result set.

answered Oct 19 '16 at 13:57

brso05

13,142
2
21
40

What does that return exactly? An int? – Jrawr Oct 19 '16 at 13:58
@Jrawr it will return a result set with a field that contains your count. You can do `resultSet.getInt(0)`... – brso05 Oct 19 '16 at 13:59
I'm getting this exception when I do that: – Jrawr Oct 19 '16 at 14:02
java.sql.SQLException: ResultSet closed – Jrawr Oct 19 '16 at 14:02

Get the size of a ResultSet in Java for SQLite

3 Answers3