[FIXED] How to filter out data from a table using a DB cursor in SQLite?

Issue

Suppose I have table with four attributes: id, name, address and phone number. Assuming a seperate database cursor class is created to fetch all records of students in the table, how can I use this cursor to get a record with only a specific id or sort the records by id? Thanks in advance!

Solution

The typical way is to use the query, that drives the building of the Cursor to invoke SELECT SQL that selects the required rows via a WHERE clause and in the case of sorting uses an ORDER BY clause.

A Cursor is simply a flexible class designed to hold any output from such a query.

As an example you could use the convenience query method provided by the SQLiteDatabase class:-

    Long id=0L; /* set according to the id required */
    Cursor csr = db.query(
            "the_table", /* the name of the table */
            null, /* null equates to * which means all unhidden columns */
            "id=?", /* the WHERE clause without the WHERE keyword */
            new String[]{String.valueOf(id)}, /* A String array of the arguments 1 arg for each ? place holder */
            null, /* the GROUP BY clause without the GROUP BY keyword */
            null, /* the HAVING clause without the HAVING keyword */
            "id ASC" /* the ORDER BY clause without the ORDER BY keyword */
    );
    /* This would build and execute a query that equates to"-
        SELECT * FROM the_table WHERE id='n' ORDER BY id ASC
        where n would be the value as per the variable id (0)
     */
    /* The cursor will contain the respective rows in the respective order */
    while (csr.moveToNext()) {
        /* do something with the data held in the Cursor */
    }
    csr.close(); /* should always close the cursor when done with it */

Answered By - MikeT
Answer Checked By - Timothy Miller (JavaFixing Admin)