Java SQL get all rows from column

Question

I want to get the number of rows from my database How can I create a method that returns the number of rows as an int? Here's what I got but it only returns number 1

public int getAllId() {

    SQLiteDatabase db = this.getWritableDatabase();

    int x=0;
    String where = null;
    Cursor c = db.rawQuery("SELECT COUNT (*) FROM " + TABLE_PRODUCTS, null);


     while (c.isAfterLast() == false) {



        c.moveToNext();

    }

    db.close();
    return c.getCount();

By executing a `SELECT COUNT(*) FROM MYTABLE` inside your method. Show some initiative, there's plenty of information available on these things. — Kayaman, Nov 25 '15 at 20:00
Yes, I looked through many of the examples and questions here but I can't adapt it to my code because I'm still such a newbie to java — Waffles.Inc, Nov 25 '15 at 20:02
The question is to broad. We can't see your code nor what you have done so far. Consider update your question please. — Marcinek, Nov 25 '15 at 20:05
`preparedStatement.executeUpdate()` returns number of rows. Why not to get result in `ResultSet` and iterate `while` until `resultSet.next()` if `resultSet != null` does this make sense — mumair, Nov 25 '15 at 20:08

score 0 · Answer 1 · answered Nov 25 '15 at 21:38

Well if you are using simple JDBC without any libraries you should be able to get the number of rows from the ResultSet I did this a while back using two approaches:

1.

    rs.last();
    rs.getRow();

2.

      int rowcount = 0;
      while (rs.next()) { 
      // more processing here 
       rowCount++;
     }

but as a word of caution, I have used this only on oracle and mysql dbs. Also if the connection is ResultSet.TYPE_FORWARD_ONLY you will not be able to go back.

you can read more about resultSets from http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

Hope this helps :)

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

I found the way to do it thanks to

SQLite Query in Android to count rows

Here's the code I came up with:

public int getRowCount() throws Exception {
    SQLiteDatabase db = this.getWritableDatabase();


    Cursor mCount= db.rawQuery("SELECT COUNT (*) FROM " + TABLE_PRODUCTS,
    null); 

    mCount.moveToFirst();
    int rows= mCount.getInt(0);
    mCount.close();

return rows;

 }

edited May 23 '17 at 11:59

Community

1
1

answered Nov 26 '15 at 03:44

Waffles.Inc

3,310
4
13
17

Java SQL get all rows from column

2 Answers2