Android CursorTreeAdapter does not update group cursor / collapses group after Cursor update

Question

I would like ExpandableListView to update (rebind?) both the group items and the child items when a SQLite database change is made. I have implemented my own subclass of CursorTreeAdapter. When the database is updated, the backing cursors (both children and group) need to be requeried and the views refreshed, preserving expanded/collapsed state of the groups. However, I'm unable to achieve this behavior.

Desired behavior: Both the group cursor and the child cursor are updated. A child item will be added, and all groups currently expanded will remain expanded.

Scenario #1 - Updating using CursorTreeAdapter.notifyDataSetChanged()

When a database change occurs, and I invoke this method on the Adapter, the child item is added, the groups remain expanded as they were but the group item text is not updated.

In this scenario, the group view displayed data is not changed to reflect the child data. I can see that the group cursor has not been updated by invoking this method, and contains stale data.

Scenario #2 - Updating using CursorTreeAdapter.getCursor().requery()

When a database change occurs, and I invoke this method on the Adapter, the child item is added, the group which has the new child collapses automatically, and the group item text is updated.

In this scenario, I can see that the group cursor has been updated and the data in the group view is correct, but the group should not collapse after a child is added. I do not understand why the expanded state is not preserved, but it should be.

Applicable code follows...

If it is of any consequence, here is the build.gradle pertinent settings with packaging options and dependencies omitted for brevity:

apply plugin: 'com.android.application'

android {
    compileSdkVersion 29
    buildToolsVersion '30.0.2'

    defaultConfig {
        applicationId "com.conceptualsystems.kitting"
        minSdkVersion 24
        targetSdkVersion 29
        versionCode 92
        versionName "2.29"
        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
        multiDexEnabled true
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
        }
    }
}

Handler:

/**
 * a Handler which will be invoked when a database change has occurred.
 * I have tried several possibilities to achieve the desired behavior to no avail.
 */
public android.os.Handler mRefreshHandler = new Handler() {
    @Override
    public void handleMessage(android.os.Message msg) {
        new SummaryTask().execute();
        //mShipKitExpandableListAdapter.notifyDataSetChanged();
        //mShipKitExpandableListAdapter.setGroupCursor(
        //      DbSingleton.getInstance().getDatabase().rawQuery(
        //              getGroupQuery(), null
        //      ));
        mShipKitExpandableListAdapter.getCursor().requery();
        //mShipKitExpandableListView.invalidateViews();
    }
};

ShipKitExpandableAdapter:

public class ShipKitExpandableAdapter extends CursorTreeAdapter {
    public ShipKitExpandableAdapter(Cursor cursor, Context context) {
        super(cursor, context, true);
    }

    @Override
    protected Cursor getChildrenCursor(Cursor groupCursor) {
        String productName = groupCursor.getString(
                groupCursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_FIN_ID));

        return DbSingleton.getInstance().getDatabase().rawQuery(
                getChildQuery(productName), null
        );
    }

    @Override
    public View getGroupView(int groupPosition, boolean isExpanded, View convertView, ViewGroup parent) {
        View groupView = super.getGroupView(
                groupPosition,
                isExpanded,
                convertView,
                parent);
        if(groupView != null) {
            ColorLayout.setTheme(groupView);
        }

        return groupView;
    }

    @Override
    protected View newGroupView(Context context, Cursor cursor, boolean isExpanded, ViewGroup parent) {
        return mInflater.inflate(R.layout.ship_list_group, parent, false);
    }

    @Override
    protected void bindGroupView(View view, Context context, Cursor cursor, boolean isExpanded) {
        try {
            String count = cursor.getString(cursor.getColumnIndex("count"));
            ((TextView)view.findViewById(R.id.group_pieces)).setText(count);
            String netUnits = cursor.getString(cursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_NET));
            ((TextView)view.findViewById(R.id.group_net_units)).setText(netUnits);
            ((TextView)view.findViewById(R.id.group_net_uom)).setText(" lbs");
            String productName = cursor.getString(cursor.getColumnIndex(DbSchemaKitting.KitProductSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitProductSchema.COLUMN_NAME));
            ((TextView)view.findViewById(R.id.group_product_name)).setText(productName);
            String grossUnits = cursor.getString(cursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_GROSS));
            ((TextView)view.findViewById(R.id.group_gross_units)).setText(grossUnits);
            ((TextView)view.findViewById(R.id.group_gross_uom)).setText(" lbs");
            ColorLayout.setTheme(view);
        } catch(Exception e) {
            mLogger.info("error getting a field: ", e);
            e.printStackTrace();
        }
    }

    @Override
    public View getChildView(final int groupPosition,
                             final int childPosition, boolean isLastChild, View convertView,
                             ViewGroup parent) {
        final View childView = super.getChildView(
                groupPosition,
                childPosition,
                isLastChild,
                convertView,
                parent);
        if(childView != null) {
            ColorLayout.setTheme(childView);

            childView.setOnLongClickListener(new View.OnLongClickListener() {
                @Override
                public boolean onLongClick(View v) {
                    //long press deletes a shipment record.
                    //mShipKitAdapter.getCursor().moveToPosition(position);
                    //String kit_id = kitCursor.getString(kitCursor.getColumnIndex(DbSchema.KitSchema.TABLE_NAME+DbSchema.KitSchema.COLUMN_ID));
                    String kit_id = ((TextView)childView.findViewById(R.id.kit_id)).getText().toString();
                    removeDialog(DIALOG_CONFIRM_DELETE);

                    Bundle b = new Bundle();
                    if(kit_id!=null) {
                        b.putInt("kit_id", Integer.valueOf(kit_id));
                    }

                    showDialog(DIALOG_CONFIRM_DELETE, b);

                    return true;
                }
            });
        }

        return childView;
    }

    @Override
    protected View newChildView(Context context, Cursor cursor, boolean isLastChild, ViewGroup parent) {
        return mInflater.inflate(R.layout.ship_list_item, parent, false);
    }

    @Override
    protected void bindChildView(View view, Context context, Cursor cursor, boolean isLastChild) {
        try {
            ((TextView)view.findViewById(R.id.kit_id)).setText(cursor.getString(cursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_ID)));
            ((TextView)view.findViewById(R.id.net_units)).setText(cursor.getString(cursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_NET)));
            ((TextView)view.findViewById(R.id.net_uom)).setText(" lbs");
            ((TextView)view.findViewById(R.id.product_name)).setText(cursor.getString(cursor.getColumnIndex(DbSchemaKitting.KitProductSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitProductSchema.COLUMN_NAME)));
            ((TextView)view.findViewById(R.id.gross_units)).setText(cursor.getString(cursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_GROSS)));
            ((TextView)view.findViewById(R.id.gross_uom)).setText(" lbs");
            ColorLayout.setTheme(view);
        } catch(Exception e) {
            mLogger.info("error getting a field: ", e);
            e.printStackTrace();
        }
    }
}

Setting Adapter:

mShipKitExpandableListAdapter = new ShipKitExpandableAdapter(
    DbSingleton.getInstance().getDatabase().rawQuery(
            getGroupQuery(), null
    ),
    ShipActivity.this
);
mShipKitExpandableListView.setAdapter(mShipKitExpandableListAdapter);
mShipKitExpandableListView.setGroupIndicator(null);

Database queries:

public String getGroupQuery() {
    return "SELECT " +
            "COUNT(*) AS count, " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema._ID +
            " AS _id, " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_ID +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_ID + ", " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_FIN_ID +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_FIN_ID + ", " +
            "SUM(" +
                DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_GROSS +
            ")" +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_GROSS + ", " +
            "SUM(" +
                DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_NET +
            ")" +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_NET + ", " +
            DbSchemaKitting.KitProductSchema.TABLE_NAME + "." + DbSchemaKitting.KitProductSchema.COLUMN_NAME +
            " AS " + DbSchemaKitting.KitProductSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitProductSchema.COLUMN_NAME +
            " FROM " + DbSchemaKitting.KitSchema.TABLE_NAME +
            " LEFT OUTER JOIN " + DbSchemaKitting.KitProductSchema.TABLE_NAME +
            " ON " + DbSchemaKitting.KitSchema.TABLE_NAME + "." +  DbSchemaKitting.KitSchema.COLUMN_FIN_ID + "=" + DbSchemaKitting.KitProductSchema.TABLE_NAME + "." +  DbSchemaKitting.KitProductSchema.COLUMN_ID +
            " WHERE " + DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_SHIP_ID + "=" + mShipmentID +
            " GROUP BY " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_FIN_ID;
}

public String getChildQuery(String productName) {
    return "SELECT " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema._ID +
            " AS _id, " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_ID +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_ID + ", " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_FIN_ID +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_FIN_ID + ", " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_GROSS +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_GROSS + ", " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_NET +
            " AS " + DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_NET + ", " +
            DbSchemaKitting.KitProductSchema.TABLE_NAME + "." + DbSchemaKitting.KitProductSchema.COLUMN_NAME +
            " AS " + DbSchemaKitting.KitProductSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitProductSchema.COLUMN_NAME +
            " FROM " + DbSchemaKitting.KitSchema.TABLE_NAME +
            " LEFT OUTER JOIN " + DbSchemaKitting.KitProductSchema.TABLE_NAME +
            " ON " + DbSchemaKitting.KitSchema.TABLE_NAME + "." +  DbSchemaKitting.KitSchema.COLUMN_FIN_ID + "=" + DbSchemaKitting.KitProductSchema.TABLE_NAME + "." +  DbSchemaKitting.KitProductSchema.COLUMN_ID +
            " WHERE " + DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_SHIP_ID + "=" + mShipmentID + " AND " +
            DbSchemaKitting.KitSchema.TABLE_NAME + "." + DbSchemaKitting.KitSchema.COLUMN_FIN_ID + "='" + productName + "'";
}

Answer 1

I ended up solving this by manually keeping track of all expanded states within the adapter and creating a requery() call through in my Adapter implementation which recreates the expansion states when the Cursor is requeried. I felt like this was the most elegant solution for solving this rather strange quirk with CursorTreeAdapter updates.

I added the following code to the CursorTreeAdapter implementation:

public void requery() {
    getCursor().requery();
    for(int i = 0; i < getCursor().getCount(); i++) {
        Cursor groupCursor = getGroup(i);
        String productID = groupCursor.getString(groupCursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_FIN_ID));
        if(expansionStates.containsKey(productID)) {
            if(expansionStates.get(productID))
                mShipKitExpandableListView.expandGroup(i);
        }
    }
}

@Override
public void onGroupCollapsed(int groupPosition) {
    Cursor groupCursor = getGroup(groupPosition);
    String productID = groupCursor.getString(groupCursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_FIN_ID));
    expansionStates.put(productID, false);
}

@Override
public void onGroupExpanded(int groupPosition) {
    Cursor groupCursor = getGroup(groupPosition);
    String productID = groupCursor.getString(groupCursor.getColumnIndex(DbSchemaKitting.KitSchema.TABLE_NAME + "dot" + DbSchemaKitting.KitSchema.COLUMN_FIN_ID));
    expansionStates.put(productID, true);
}

From here, it's just a simple matter of calling the ShipKitExpandableAdapter.requery() method rather than the underlying Cursor's requery method, and, lo and behold, on every requery the expansion states are preserved. Note that you can, of course, preserve the expanded state using any identifier in your Cursor result by changing the Map.Entry type arguments (if necessary) and filling in your own column name in getColumnIndex().

Handler:

/**
 * a Handler which will be invoked when a database change has occurred.
 * I have tried several possibilities to achieve the desired behavior to no avail.
 */
public android.os.Handler mRefreshHandler = new Handler() {
    @Override
    public void handleMessage(android.os.Message msg) {
        new SummaryTask().execute();
        mShipKitExpandableListAdapter.requery();
    }
};