My problem is centered around having code that is easily maintained and efficient. More specifically it revolves around getting data from an SQLite Cursor.
When I first started using cursors I would hard code something along the lines of mystrvar = cursor.getString(?) where ? would be the offset to the respective row.
I then started using constants that were defined along with the table column names. e.g. I'd have something like :-
// Table Aisles
public static final String AISLES_TABLE_NAME = "aisles";
public static final String AISLES_COLUMN_ID = PRIMARY_KEY_NAME;
public static final String AISLES_COLUMN_ID_FULL = AISLES_TABLE_NAME + AISLES_COLUMN_ID;
public static final int AISLES_COLUMN_ID_INDEX = 0; ........
and, as an example would code something along the lines of :-
mystrvar = cursor.getString(DBHelper.AISLES_COLUMN_ID_INDEX);
This was an improvement, but had the flaw of not being that good at coping with joined tables.
I then became aware of cursor.getColumnIndex(), BUT suspected that solely using this. Would have overheads that could be circumvented.
What I have done is to include code that has sparse use of getColumnIndex(). It sets offset variables via getColumnIndex() just once in an activity/custom cursor adapter and subsequently uses the respective offset variable which is the cursor offset for the respective column.
The following is an example (split into 3 chunks, the variable definitions, second a method that sets the variables and then third, the actual data extraction from the cursor :-
1) variable definitions :-
public class Database_Inspector_AislesDB_Adapter extends CursorAdapter {
// Variables to store aisles table offsets as obtained via the defined column names by
// call to setAislesOffsets (aisles_aisleid_offset set -1 to act as notdone flag )
public static int aisles_aisleid_offset = -1;
public static int aisles_aislename_offset;
public static int aisles_aisleorder_offset;
public static int aisles_aisleshopref_offset;
public Database_Inspector_AislesDB_Adapter(Context context, Cursor cursor, int flags) {
super(context, cursor, 0);
setAislesOffsets(cursor); //** Calls method to set offsets
........
}
2) Method that sets the offsets just once (returns virtually immediately if they have already been set)
// Set Aisles Table query offsets into returned cursor, if not already set
public void setAislesOffsets(Cursor cursor) {
if(aisles_aisleid_offset != -1) {
return;
}
aisles_aisleid_offset = cursor.getColumnIndex(ShopperDBHelper.AISLES_COLUMN_ID);
aisles_aislename_offset = cursor.getColumnIndex(ShopperDBHelper.AISLES_COLUMN_NAME);
aisles_aisleorder_offset = cursor.getColumnIndex(ShopperDBHelper.AISLES_COLUMN_ORDER);
aisles_aisleshopref_offset = cursor.getColumnIndex(ShopperDBHelper.AISLES_COLUMN_SHOP);
}
3) example use of offsets
textviewaisleid.setText(cursor.getString(aisles_aisleid_offset));
textviewaislesaislename.setText(cursor.getString(aisles_aislename_offset));
textviewaislesorder.setText(cursor.getString(aisles_aisleorder_offset));
textviewaisleshopref.setText(cursor.getString(aisles_aisleshopref_offset));
However, the above coding has to be used for each activity/adapter that uses the table table. There are 7 tables with 56 columns. Joined tables need combinations. Is there a way that an equivalent of global variables could be used (I'm assuming using shared preferences would be more of an overhead). That is I could set the offsets just once from anywhere and then access them from anywhere (by anywhere I mean from within any activity or adpater)? To re-iterate, mainly to reduce maintenance overheads/issues and with consideration of run efficiency.
It appears that using the run once per activity to set column offset values, cannot be improved upon without introducing overheads.