android order sqlite table

Question

I have a table called person and it contains two fields, name (TEXT) and age (INT). I want to sort this table alphabetically but I can seem to get the sql statement to work. The statement i am using is:

SELECT name FROM person ORDER BY name;

I have tried three different coded versions, none of which work.

db.rawQuery("SELECT name FROM person ORDER BY name;", null);
db.execSQL("SELECT name FROM person ORDER BY name;", null);
db.execSQL("SELECT name FROM person ORDER BY name;");

rawQuery does absolutely nothing, no messages are displayed in LogCat. the first execSQL crashes with:

java.lang.IllegalArgumentException: Empty bindArgs

The second execsql crashes with:

android.database.sqlite.SQLiteException: unknown error: Queries cannot be performed using execSQL(), use query() instead.

I also tried using query() but I couldn't make heads or tails of it. Help please.

Answer 1

Try:

db.query("person", new String[] {"name"}, null, null, null, null, "name");

As per the reference:

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) 

If for example you wanted both name and age you would use:

db.query("person", new String[] {"name", "age"}, null, null, null, null, "name");

If you wanted to select only names with "woody" in them you could use:

db.query("person", new String[] {"name"}, "name=?", new String[] { "woody" }, null, null, "name");

For processing the results you could do the following:

Cursor c = db.query("person", new String[] {"name", "age"}, null, null, null, null, "name");

startManagingCursor(c); 
int nameColumn = c.getColumnIndex("name");
int ageColumn = c.getColumnIndex("age");
c.moveToFirst()) 
do 
{
    // Perform Logic
    Log.i('TEST INFO', 'Name: '.c.getString(nameColumn).' Age: '.c.getInt(ageColumn));
}
while (c.moveToNext());

Amendment as per the comment: I am not quite sure why you would want to reorder the data in a database, but the following function should do what you want, you will probably need to modify to suite, it will get all entries (ordered), create a temp table, add the entries, drop the real table and rename the temp table to become the main table again:

Doing this is very resource hungry - using a properly laid out SQL SELECT statement to order result as they are collected from the DB will use much less.

private void orderDB()
{
    Cursor c = db.query("person", new String[] {"name", "age"}, null, null, null, null, "name");
    startManagingCursor(c); 

    //Create a temp table:
    db.execSQL("CREATE TABLE IF NOT EXISTS tmp_person (_id integer primary key autoincrement, "
        + "name text not null, " 
        + "age integer);");

    // Get column ID's
    int nameColumn = c.getColumnIndex("name");
    int ageColumn = c.getColumnIndex("age");

    // Iterate through all entries
    c.moveToFirst(); 
    do 
    {
        if (c.getCount() >0)
        {
            ContentValues orderedValues = new ContentValues();
            orderedValues.put("name", c.getString(nameColumn));
            orderedValues.put("age", c.getInt(ageColumn));
            try
            {
                // Insert the entry into a temp table
                db.insert("tmp_person", null, orderedValues);
            }
            catch (SQLException e)
            {
                Log.e("TEST INFO", e.toString());
            }
        }
    }
    while (c.moveToNext());

    // Drop the current person table:
    db.execSQL("DROP TABLE IF EXISTS person");

    // Rename the temp table to person:
    db.execSQL("ALTER TABLE tmp_person RENAME TO person");  
}