How do you search or sort in SQLite Database?

Question

I have an application that stores information in a database based on the information from 5 text fields. I'm having trouble being able to sort through fields or just even sorting from one field. Also, what method does the ORDER BY clause go in? Snippets of code for the database is below:

 public static final String MYDATABASE_NAME = "MY_DATABASE";
 public static final String MYDATABASE_TABLE = "MY_TABLE";
 public static final int MYDATABASE_VERSION = 1;
 public static final String KEY_CONTENT = "Content";

 public static final String lastName = "lastName";
 public static final String firstName = "firstName";
 public static final String school = "school";
 public static final String email = "email";
 public static final String intrest = "intrest";




      Cursor d = sqLiteDatabase.rawQuery("SELECT * from " + MYDATABASE_TABLE + "ORDER BY " + lastName + " ASC" , null); 
             while (d.moveToNext()) {
                 int sort = d.getColumnIndex(lastName);
                 int sort2 = d.getColumnIndex(intrest);
                 String lastName = d.getString(sort);
                 String intrest = d.getString(sort2);
                 System.out.println("GOT STUDENT " +  lastName + " LIKES " + intrest);
             }

Answer 1

Try to use (cursor.getCount() > 0) method and (cursor != null) condition in your code to check whether you gets data or not. That I have shown below. and try to use Log for your query for the perfection and put that query in your sqlite database to check whether it is working properly or not.

Cursor d = sqLiteDatabase.rawQuery("SELECT * from " + MYDATABASE_TABLE + " ORDER BY " + lastName + " ASC" , null); // here I have edited space before ORDER BY word starts. Please note this
Log.d("query",">>>>"+SELECT * from " + MYDATABASE_TABLE + " ORDER BY " + lastName + " ASC"); // check this query is going to right way or not using local database.
if(d != null){
     if(d.getCount() > 0){  // to check you get one or more data 
       d.moveToFirst();    
         do{
                 int sort = d.getColumnIndex(lastName);
                 int sort2 = d.getColumnIndex(intrest);
                 String lastName = d.getString(sort);
                 String intrest = d.getString(sort2);
                 System.out.println("GOT STUDENT " +  lastName + " LIKES " + intrest);
           } while (d.moveToNext());
      }
}

Try this. Hope it will help you.

Answer 2

I like query method better, because it's more clear:

public static final String MYDATABASE_NAME = "MY_DATABASE";
public static final String MYDATABASE_TABLE = "MY_TABLE";
public static final int MYDATABASE_VERSION = 1;
public static final String KEY_CONTENT = "Content";

public static final String lastName = "lastName";
public static final String firstName = "firstName";
public static final String school = "school";
public static final String email = "email";
public static final String interest = "interest";
public final String[] columns = new String[] {
    lastName,
    firstName,
    school,
    email,
    interest
};

public void printInOrder(String column) {
    Cursor d = sqLiteDatabase.query(MYDATABASE_TABLE, columns, null, null, null, null, column + " ASC");
    while (d.moveToNext()) {
        int sort = d.getColumnIndex(lastName);
        int sort2 = d.getColumnIndex(interest);
        String lastName = d.getString(sort);
        String interest = d.getString(sort2);
        System.out.println("GOT STUDENT " +  lastName + " LIKES " + interest);
    }
}