Android ExpandableListView and SQLite Database

Question

I have two tables in my database - rooms and devices. Each room can have many devices. I want to make an expandable listview with room name as group and devices name and state as children (on or off).

Can any one give me a simple idea because I am kind of new with Android and all tutorials are hard to make

This is my database:

public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table if not exists rooms (
                                    id_room integer primary key autoincrement, "
                + "name_room text not null" 
                + ");");    
        db.execSQL("create table if not exists devices (
                                    id_device integer primary key autoincrement, "
                + "name_device text not null," 
                + "state_device text not null,"
                + "id_room references rooms"
                + ");");
    }

Barak · Accepted Answer · 2012-05-18T15:09:18.290

Following is a very simple, no frills expandable list implementation.

You need two methods in your db helper class to gather the cursors you will need.

public Cursor fetchGroup() {
    String query = "SELECT * FROM rooms"
    return mDb.rawQuery(query, null);
}

public Cursor fetchChildren(String room) {
    String query = "SELECT * FROM devices WHERE id_room = '" + room + "'";
    return mDb.rawQuery(query, null);
}

Then you need to set up your adapter (in your activity):

public class MyExpandableListAdapter extends SimpleCursorTreeAdapter {
    public MyExpandableListAdapter(Cursor cursor, Context context,int groupLayout, 
        int childLayout, String[] groupFrom, int[] groupTo, String[] childrenFrom, 
        int[] childrenTo) {
            super(context, cursor, groupLayout, groupFrom, groupTo,
                  childLayout, childrenFrom, childrenTo);
        }
    }

    @Override
    protected Cursor getChildrenCursor(Cursor groupCursor) {
        Cursor childCursor = mDbHelper.fetchChildren(groupCursor.getString(groupCursor.getColumnIndex("id_room"));            
        getActivity().startManagingCursor(childCursor);
        childCursor.moveToFirst();
        return childCursor;
    }
}

And finally call the adapter and set it to your list (in your activity):

private void fillData() {
    mGroupsCursor = mDbHelper.fetchGroup();
    getActivity().startManagingCursor(mGroupsCursor);
    mGroupsCursor.moveToFirst();

    ExpandableListView elv = (ExpandableListView) getActivity().findViewById(android.R.id.list);

    mAdapter = new MyExpandableListAdapter(mGroupsCursor, getActivity(),
        R.layout.rowlayout_expgroup,                     // Your row layout for a group
        R.layout.rowlayout_itemlist_exp,                 // Your row layout for a child
        new String[] { "id_room" },                      // Field(s) to use from group cursor
        new int[] { android.R.id.room },                 // Widget ids to put group data into
        new String[] { "name_device", "state_device" },  // Field(s) to use from child cursors
        new int[] { R.id.device, R.id.state });          // Widget ids to put child data into

        lv.setAdapter(mAdapter);                         // set the list adapter.
    }
}

Hope this helps!

EDIT

It should all come together like so:

public class List_Exp extends Activity {
    @Override
    public void onActivityCreated(Bundle savedInstanceState) {
        super.onActivityCreated(savedInstanceState);
        mDbHelper = new YourDB(getActivity());
        mDbHelper.open();
        fillData();
    }

    private void fillData() { 
        // set list adapter here
    }

    public class MyExpandableListAdapter extends SimpleCursorTreeAdapter {
        // Your adapter
    }
}

EDIT 2

To catch clicks, set listeners in you activity:

lv.setOnChildClickListener(new ExpandableListView.OnChildClickListener() {
    @Override
    public boolean onChildClick(ExpandableListView parent, View v,
        int groupPosition, int childPosition, long id) {
        // Your child click code here
        return true;
    }
});

lv.setOnGroupClickListener(new ExpandableListView.OnGroupClickListener() {
    @Override
    public boolean onGroupClick(ExpandableListView parent, View v,
        int groupPosition, int groupPosition, long id) {
        // Your group click code here
        return true;
    }
});

I have to remove this getActivity().startManagingCursor(childCursor) on the getChildrenCursor() because i bo not have any solytion to remove error The method getActivity() is undefined for the type MyExpandableListAdapter !! — hamzarh, May 18 '12 at 11:21
Ah, that code was in a fragment, so it would work there. You might try using `this` instead, or `youractivity.this`. — Barak, May 18 '12 at 14:18
I tried to use this but as u can see MyExpandableListAdapter() is not an activity ??!! — hamzarh, May 18 '12 at 14:27
Ah, I think I see what your issue is... MyExpandableListAdapter needs to be contained in an activity, it's not it's own class. The adapter should be in the same activity as the list calling it. I'll modify my answer to explain that. — Barak, May 18 '12 at 14:32
Then one (or both) of the columns `name_device` or `state_device` do not exist in your cursor. Since we pulled all columns by using `SELECT *`, they should be there. I took the column names you showed in your original post. You'll need to find out what the correct names are and use them. Or you could just use the index reference(0 and 1). — Barak, May 18 '12 at 14:54
One more thing, how i contorl click and longclick on both rooms and devices (roup and children) ?! — hamzarh, May 18 '12 at 15:02
sir can you please answer my question http://stackoverflow.com/questions/29457424/how-to-populate-expandablelistview-from-android-sqlite-database — silverFoxA, Apr 17 '15 at 18:35
@Barak how to handle with more than one childs and childs are not fixed could u please — kiran kumar, Jul 23 '15 at 04:51

Android ExpandableListView and SQLite Database

1 Answers1

Linked