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
– Kayaman, Commented Nov 25, 2015 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
– Waffles.Inc, Commented Nov 25, 2015 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
– Marcinek, Commented Nov 25, 2015 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
– mumair, Commented Nov 25, 2015 at 20:08

Messele · Accepted Answer · 2015-11-25 21:38:43Z

0

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 :)

answered Nov 25, 2015 at 21:38

Messele

1

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:59:23Z

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, 2017 at 11:59

CommunityBot

11 silver badge

answered Nov 26, 2015 at 3:44

Waffles.Inc

3,5804 gold badges16 silver badges18 bronze badges

Collectives™ on Stack Overflow

Java SQL get all rows from column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related