How to go through every record in sqllite db?

Question

Hi guys. I think it's kinda easy one but still I'm new to android programming so please have patience. I want to know how can I get the number of records (rows) in a specific table in my db. I need this so I can create a loop to go through every record and add each one of it to the specific Array and display it later on. This is the source:

db.openDataBase();            // open connection with db

Cursor c = db.getTitle(5);    // loop here through db, right now I'm fetching only one record

startManagingCursor(c);

//adding areas to the list here

Area o1 = new Area();

o1.setOrderName(c.getString(1) + c.getString(2));  

m_areas.add(o1);

db.close();

Does anyone can help me with this please? Thx in advance!

Answer 1

SELECT COUNT(*) FROM tablename

Answer 2

To get the number of rows in the cursor, use getCount.

To get the amount of total rows in a table, either use reinerposts solution, or do a select which select all rows in the table and get the count from the cursor. I'm guessing his solution is quicker though unless you actually need all the rows in the table.

Such a query would be:

SELECT * FROM footable;

Answer 3

You don't really need to get a count of how many first; instead, create a db.getTitles() function that returns all of the rows and returns a Cursor, then loop over the Cursor. Right now you probably have a query that looks something like SELECT ColumnA, ColumnB FROM Titles WHERE id = 5; just copy the function, remove the parameter and take off the WHERE clause so it looks like just SELECT ColumnA, ColumnB FROM Titles.

Then your code would look something like this:

db.openDataBase(); // open connection with db
Cursor c = db.getTitles();
startManagingCursor(c);

//adding areas to the list here
if (c != null && c.moveToFirst()) {
    do {
        Area o1 = new Area();
        o1.setOrderName(c.getString(1) + c.getString(2));
        m_areas.add(o1);
    } while (c.next());
}

db.close();

We check if the function returned a cursor at all, then move to the beginning of the cursor and start looping, going to the next item each time through. For more information on the Cursor interface see the API here, or to learn more about database access and related design practices better in general I suggest going through the Notepad tutorial.

Answer 4

Big thx guys for such quick response! Those solutions works just brilliant!