Java – SQLite database cursor returned the wrong resource ID

I’m trying to use references to drawable and string resources in the SQLite database to show the appropriate resources in the fragment. I have a database helper file to populate the database, a database utility file to create cursors (or just get cursor data in an array), and a fragment file.

    DatabaseHelper.java
    DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

private void updateMyDatabase(SQLiteDatabase db, int oldVersion, int newVersion) {
            if (oldVersion == 1) {
                db.execSQL("CREATE TABLE FOOD (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                        + "NAME TEXT, "
                        + "IMAGE_RESOURCE_ID INTEGER, "
                        + "QUOTE INTEGER);" );

insertFood(db,"Alcohol", R.drawable.alcohol, R.string.symptom9);
}

private static void insertFood(SQLiteDatabase db, String name, int toxicity, int resourceId, int quote){
        ContentValues foodValues = new ContentValues();
        foodValues.put("NAME", name);
        foodValues.put("TOXICITY", toxicity);
        foodValues.put("IMAGE_RESOURCE_ID", resourceId);
        foodValues.put("QUOTE", quote);
        db.insert("FOOD", null, foodValues);
    }
}

Here, R. drawable.alcohol = 2131099733

I’m using Android Studio and when I hover over the values I want to add to the database and display the drawable with one of those values, it’s the correct drawable, but when I request the cursor (or cursor-based), the cursor contains a completely different value than the value stored in the database and produces a resource not found error.

I tried to return an array of data from the helper method,

but this also gives incorrect integer references to the drawable and string, so I returned the cursor from the helper method instead:

DatabaseUtilities.java
get all of the database data for a particular food given that food's name
    public static Cursor getFoodById(Context context, long itemId){

try {
            SQLiteOpenHelper DatabaseHelper = new DatabaseHelper(context);
            SQLiteDatabase db = DatabaseHelper.getReadableDatabase();
            return db.query(DatabaseHelper.FOOD,
                    new String[]{
                            DatabaseHelper.NAME, DatabaseHelper.IMAGE_RESOURCE_ID,
                            DatabaseHelper.QUOTE},
                    "_id = ?", new String[] {Long.toString(itemId)}, null, null, null
            );

} catch (SQLiteException e) {
            TODO Add toast - food not available
            return null;
        }
    }

Finally, I tried to display the value here:

DetailFragment.java
 @Override
    public void onViewCreated(@NonNull View view, @Nullable Bundle savedInstanceState) {
        super.onViewCreated(view, savedInstanceState);

Cursor items = DatabaseUtilities.getFoodById(getActivity(), itemId);
        if(items.moveToFirst()) {    
            set appropriate img
            int img = items.getInt(1);
            ImageView photo = (ImageView) view.getRootView().findViewById(R.id.detail_photo);
            photo.setImageResource(img);

int quote = items.getInt(2);
            TextView desc = (TextView) view.getRootView().findViewById(R.id.detail_text);
            String descText = getString(quote);
            desc.setText(descText);
        }
    }

This is the log output for all cursor columns in the previous file:

DetailFragment - cursor: Alcohol, 2131165269, 2131558463

The value after Alcohol should be R.drawable.alcohol, 2131099733, but the cursor returns 2131165269. The same is true for string references after drawable references. Also, cursors return different values for each inserted row, they are just wrong values, I don’t know where they come from or if there is a way to convert them to the correct value.

Except that the database does not return the correct resource reference, everything in the program works fine.

Do not store integer IDs of resources in tables.
Each time you change resources and recompile a project, there is no guarantee that their values will be the same.
Instead, store the resource ID as a string literal.
Therefore, change the definition of the table to:

CREATE TABLE FOOD (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + "NAME TEXT, "
                    + "IMAGE_RESOURCE_ID TEXT, "
                    + "QUOTE TEXT);

Now store the string ID in the IMAGE_RESOURCE_ID and QUOTE columns.
When you use a cursor to retrieve such a string id from a table, you can get its integer id:

int drawableId = getResources().getIdentifier(drawableString, "drawable", getPackageName());
int stringId = getResources().getIdentifier(stringString, "string", getPackageName()); 

You may need to provide valid Context to getResources() and getPackageName(), for example:

context.getResources().getIdentifier(drawableString, "drawable", context.getPackageName());

Replace drawableString and stringString with the values of the image and string that you retrieved from the table.
If you make the above suggested changes in the definition of a table, you must uninstall the application from the device/emulator and rerun it so that the database is dropped and recreated with the new table definition.