android database sqlite listview simplecursoradapter

Structure for SQLite database info to ListView

I've seen a few answers here, however a lot of the answers are either going over my head or I can't relate them to my app. I was wondering if anyone could break it down a little more for me as I'm kind of new to programming .

I'm trying to fill an ExpandableListView with data from an SQLite Database in Android. I have a class called DatabaseHelper which creates the database/tables and adds and removes data, I can even recall the information through a Cursor to display the data in an AlertDialog. However I'm trying to have this information dynamically loaded into the ExpandableListView.

I have this method in Database helper:

public Cursor getAllData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("SELECT * FROM "+ TABLE_NAME, null);
    return res;

which returns a Cursor object with the table inside. However to then fill a the ListView my understanding is that I need to use a SimpleCursorAdapter as well and apply it to the ListView. Do I need to create a seperate Class for the SimpleCursorAdapter? and if so how then do I apply that SimpleCursorAdaptor to my expandableListView?

All my Code as below.

MainActivity.java

public class MainActivity extends AppCompatActivity {

DatabaseHelper myDb;
EditText editName, editSurname, editAge;
Button btnAddData, btnViewAll;
ListView listView;
private SimpleCursorAdapter dataAdapter;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    myDb = new DatabaseHelper(this);
    Cursor c = myDb.getReadableDatabase().rawQuery("SELECT * FROM " + DatabaseHelper.TABLE_NAME, null);



    editName = (EditText) findViewById(R.id.editText_Name);
    editSurname = (EditText) findViewById(R.id.editText_Surname);
    editAge = (EditText) findViewById(R.id.editText_Age);
    btnAddData = (Button) findViewById(R.id.button_add);
    btnViewAll = (Button) findViewById(R.id.button_viewAll);
    listView = (ListView) findViewById(R.id.expandableListView);






    displayListView();
    AddData();
    viewAll();
}

private void displayListView() {

    Cursor cursor = myDb.getAllData();

    String[] columns = new String[] {
        DatabaseHelper.COL1,
        DatabaseHelper.COL2,
        DatabaseHelper.COL3,
        DatabaseHelper.COL4
    };

    int[] to = new int[] {
      R.id.textView2,
      R.id.textView4,
      R.id.textView6,
    };

    dataAdapter = new SimpleCursorAdapter(
            this, R.layout.listlayout,
            cursor,
            columns,
            to,
            0);


    listView.setAdapter(dataAdapter);


}

public void viewAll() {
    btnViewAll.setOnClickListener(
            new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    Cursor res = myDb.getAllData();
                    if (res.getCount() == 0) {
                        // show message (no data in table)
                        showMessage("Error", "No Data Found");
                        return;
                    }

                    StringBuffer buffer = new StringBuffer();
                    while (res.moveToNext()) {
                        buffer.append("ID: "+ res.getString(0) + "\n");
                        buffer.append("Name: "+ res.getString(1) + "\n");
                        buffer.append("Surname: "+ res.getString(2) + "\n");
                        buffer.append("Age: "+ res.getString(3) + "\n");
                    }

                    showMessage("Data", buffer.toString());

                }
            }
    );
}




public void showMessage(String title, String message){
    AlertDialog.Builder builder = new AlertDialog.Builder(this);
    builder.setCancelable(true);
    builder.setTitle(title);
    builder.setMessage(message);
    builder.show();

}

public void AddData() {
    btnAddData.setOnClickListener(
            new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    Boolean isInserted = myDb.insertData(editName.getText().toString(),
                            editSurname.getText().toString(),
                            editAge.getText().toString());

                    if(isInserted = true) {
                        Toast.makeText(MainActivity.this, "Data Inserted", Toast.LENGTH_SHORT).show();
                    } else {
                        Toast.makeText(MainActivity.this, "Data is not Inserted", Toast.LENGTH_SHORT).show();
                    }
                }
            }
    );
}

}

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper{

public static final String DATABASE_NAME = "People.db";
public static final String TABLE_NAME = "people_table";
public static final String COL1 = "ID";
public static final String COL2 = "NAME";
public static final String COL3 = "SURNAME";
public static final String COL4 = "AGE";



public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);

}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,SURNAME TEXT,AGE INTEGER)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

public boolean insertData(String name, String surname, String age) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL2, name);
    contentValues.put(COL3, surname);
    contentValues.put(COL4, age);
    long result = db.insert(TABLE_NAME, null, contentValues);
    if (result == -1)
        return false;
    else
        return true;

}

public Cursor getAllData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("SELECT * FROM "+ TABLE_NAME, null);
    return res;
}
}

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context="com.welcometech.database.MainActivity">

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textAppearance="?android:attr/textAppearanceLarge"
    android:text="Name"
    android:id="@+id/textView"
    android:layout_alignParentTop="true"
    android:layout_toLeftOf="@+id/editText_Name"
    android:layout_toStartOf="@+id/editText_Name" />

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textAppearance="?android:attr/textAppearanceLarge"
    android:text="Surname"
    android:id="@+id/textView2"
    android:layout_below="@+id/textView"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_marginTop="44dp" />

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textAppearance="?android:attr/textAppearanceLarge"
    android:text="Age"
    android:id="@+id/textView3"
    android:layout_marginTop="37dp"
    android:layout_below="@+id/textView2"
    android:layout_toLeftOf="@+id/editText_Age"
    android:layout_toStartOf="@+id/editText_Age" />

<EditText
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/editText_Name"
    android:layout_alignTop="@+id/textView"
    android:layout_alignParentRight="true"
    android:layout_alignParentEnd="true"
    android:layout_toRightOf="@+id/textView2"
    android:layout_toEndOf="@+id/textView2" />

<EditText
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/editText_Surname"
    android:layout_above="@+id/textView3"
    android:layout_alignRight="@+id/editText_Name"
    android:layout_alignEnd="@+id/editText_Name"
    android:layout_toRightOf="@+id/textView2"
    android:layout_toEndOf="@+id/textView2" />

<EditText
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/editText_Age"
    android:layout_alignBottom="@+id/textView3"
    android:layout_alignParentRight="true"
    android:layout_alignParentEnd="true"
    android:layout_toRightOf="@+id/textView2"
    android:layout_toEndOf="@+id/textView2" />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Add Data"
    android:id="@+id/button_add"
    android:layout_centerVertical="true"
    android:layout_below="@+id/textView3"
    android:layout_marginTop="20dp"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true" />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="View All"
    android:id="@+id/button_viewAll"
    android:layout_alignTop="@+id/button_add"
    android:layout_toRightOf="@+id/button_add"
    android:layout_toEndOf="@+id/button_add" />

<ExpandableListView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/expandableListView"
    android:layout_alignParentBottom="true"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_below="@+id/button_add" />
</RelativeLayout>

listlayout.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context="com.welcometech.database.MainActivity">

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textAppearance="?android:attr/textAppearanceLarge"
    android:text="Name"
    android:id="@+id/textView"
    android:layout_alignParentTop="true"
    android:layout_toLeftOf="@+id/editText_Name"
    android:layout_toStartOf="@+id/editText_Name" />

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textAppearance="?android:attr/textAppearanceLarge"
    android:text="Surname"
    android:id="@+id/textView2"
    android:layout_below="@+id/textView"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_marginTop="44dp" />

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:textAppearance="?android:attr/textAppearanceLarge"
    android:text="Age"
    android:id="@+id/textView3"
    android:layout_marginTop="37dp"
    android:layout_below="@+id/textView2"
    android:layout_toLeftOf="@+id/editText_Age"
    android:layout_toStartOf="@+id/editText_Age" />

<EditText
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/editText_Name"
    android:layout_alignTop="@+id/textView"
    android:layout_alignParentRight="true"
    android:layout_alignParentEnd="true"
    android:layout_toRightOf="@+id/textView2"
    android:layout_toEndOf="@+id/textView2" />

<EditText
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/editText_Surname"
    android:layout_above="@+id/textView3"
    android:layout_alignRight="@+id/editText_Name"
    android:layout_alignEnd="@+id/editText_Name"
    android:layout_toRightOf="@+id/textView2"
    android:layout_toEndOf="@+id/textView2" />

<EditText
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/editText_Age"
    android:layout_alignBottom="@+id/textView3"
    android:layout_alignParentRight="true"
    android:layout_alignParentEnd="true"
    android:layout_toRightOf="@+id/textView2"
    android:layout_toEndOf="@+id/textView2" />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Add Data"
    android:id="@+id/button_add"
    android:layout_centerVertical="true"
    android:layout_below="@+id/textView3"
    android:layout_marginTop="20dp"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true" />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="View All"
    android:id="@+id/button_viewAll"
    android:layout_alignTop="@+id/button_add"
    android:layout_toRightOf="@+id/button_add"
    android:layout_toEndOf="@+id/button_add" />

<ExpandableListView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/expandableListView"
    android:layout_alignParentBottom="true"
    android:layout_alignParentLeft="true"
    android:layout_alignParentStart="true"
    android:layout_below="@+id/button_add" />
</RelativeLayout>

Solution

You need to create one Adapter. In which you need to pass

ArrayList <HashMap<String,String>>

Your getAllData() method should be like this :

public ArrayList<HashMap<String,String>> getAllData(){
        SQLiteDatabase db = this.getWritableDatabase();

        ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
        HashMap<String,String> hashmap;

        String query_select = "SELECT column1, column2 FROM tablename ;

        Cursor cursor = db.rawQuery(query_select,null);
        if (cursor.moveToFirst()) {
            do {
                hashMap = new HashMap<String,String>();
                hashMap.put("column1", cursor.getString(0));
                hashMap.put("column2",cursor.getString(1));
                list.add(hashmap);
            } while (cursor.moveToNext());
        }
        cursor.close();

        return list;
    }

This method will return ArrayList and you need to set this to your adapter and then set your adapter to your ListView.