android - SQL database, how can I make my code more efficient

Question

I'm running a SQL query in a Database helper class method to return all entries from "drugs" table

list_all_drugs() {
  Cursor cursor = db.query("drugs", null , null, null, null, null, null);
  return cursor;
}

then in my activity I use the cursor data as the following :

db = new DatabaseHelper(this);
    c = db.list_all_drugs();
    c.moveToFirst();
    while (!c.isAfterLast()) {
        viewDrug(c.getString(0),c.getString(1),c.getString(2),c.getString(3),c.getString(4),c.getString(5),c.getString(6),c.getString(7),c.getString(8),c.getString(9),c.getString(10),c.getString(11));
        c.moveToNext();
}

then I pass these parameters to the viewDrug() method to display list of drugs from the database. Every thing is working fine, but I find this method not the best practice (?) specially when I want to insert or delete a database column the whole code structure would fail and I would have to do much hard coding.

So, in short, is there a better approach to dynamically interact with database columns rather than calling them one by one using their indexes ? thanks.

Answer 1

I think the best way is to declare all column name with static final variables like this example:

public static final String TABLE_DRUGS = "drugs";
public static final String COLUMN_NAME = "Name";
public static final String COLUMN_DOSAGE = "Dosage";

Then get your cursor value using Cursor.getColumnIndex(COLUMN_NAME) and Cursor.getString like this:

c.moveToFirst();
  while (!c.isAfterLast()) {
    HashMap <String,String> columnsValues = new HashMap<>();
    columnsValues.put(COLUMN_NAME, c.getString(c.getColumnIndex(COLUMN_NAME)));
    columnsValues.put(COLUMN_DOSAGE, c.getString(c.getColumnIndex(COLUMN_DOSAGE)));
    viewDrug(columnsValues);
    c.moveToNext();
}
c.close();