I'm trying to load data from my table in sqlite Database to my ExpandableListView inside my activity. I followed the answer from this question Android ExpandableListView and SQLite Database.
My parent list will show MVP_INDUSTRY_TYPE Column depends on date Selected, My child list will show MVP_BRCH_CODE_NAME and MVP_BRCH_ADDRESS.
DatabaseHelper. java
package com.example.spmapp;
import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DataBaseHelper extends SQLiteOpenHelper {
public static final String MVP_TBL = "MVP_tbl";
public static final String MVP_BRCH_CODE_NAME = "MVP_BRCH_CODE_NAME";
public static final String MVP_ID = "MVP_ID";
public static final String MVP_BRCH_ADDRESS = "MVP_BRCH_ADDRESS";
public static final String MVP_AREA = "AREA";
public static final String MVP_AREA_CODE = "AREA_CODE";
public static final String MVP_INDUSTRY_TYPE = "MVP_INDUSTRY_TYPE";
public static final String token = "TOKEN";
public static final String MVP_DATE = "SCHEDULED_DATE";
public static final String MVP_CLASS_ID = "MVP_CLASS_ID";
public DataBaseHelper(@Nullable Context context) {
super(context, "taggedList.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String createMVPVirtualTableStatement = "CREATE VIRTUAL TABLE IF NOT EXISTS " + MVP_TBL + " USING FTS3(" + MVP_BRCH_CODE_NAME + ", " + MVP_ID + " , " + MVP_BRCH_ADDRESS + ", " + MVP_AREA + ", " + MVP_AREA_CODE + ", " + MVP_INDUSTRY_TYPE + ", " + token + ", " + MVP_DATE + ", " + MVP_CLASS_ID + ")";
sqLiteDatabase.execSQL(createMVPVirtualTableStatement);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
onCreate(sqLiteDatabase);
}
public Cursor getSelectedParentMVPDate(String txtMVPDate){
SQLiteDatabase db = this.getWritableDatabase();
String query = "Select * from "+MVP_TBL+" WHERE " +MVP_DATE+ " LIKE '%"+txtMVPDate+"%' LIMIT 50";
return db.rawQuery(query, null);
}
public Cursor getSelectedChildMVPDate(String MVPIndustry){
SQLiteDatabase db = this.getWritableDatabase();
String query = "Select * from "+MVP_TBL+" WHERE " +MVP_INDUSTRY_TYPE+ " LIKE '%"+MVPIndustry+"%' LIMIT 50";
return db.rawQuery(query, null);
}
}
MasterVisitPlan.java
public class MasterVisitPlan extends AppCompatActivity implements AdapterView.OnItemSelectedListener,CalendarAdapter.OnItemListener {
private boolean isBackPressedOnce = false;
public static String email;
public static String VisitDate;
public static String ItemBrnchCodeName;
public static String mrCode;
public static String ClassID;
private static String token;
private static String bearerToken;
public static int counterVisit;
private RecyclerView calendarRecyclerView;
private LocalDate selectedDate;
private TextView monthYearText;
TextView empName, empPos, date;
GoogleSignInClient mGoogleSignInClient;
DrawerLayout drawerLayout;
ImageView gps, empPhoto;
ConstraintLayout calendar;
DataBaseHelper dataBaseHelper;
private Cursor mGroupsCursor; // cursor for list of groups (list top nodes)
private int mGroupIdColumnIndex;
private MyExpandableListAdapter mAdapter;
SimpleCursorAdapter sca;
Cursor csr;
SearchView searchView;
ListView Searchlstview;
@Override
protected void onCreate(Bundle savedIntanceState) {
super.onCreate(savedIntanceState);
setContentView(R.layout.activity_master_visit_plan);
initWidgets();
selectedDate = LocalDate.now();
setMonthView();
setOrRefreshListView();
getCurrentDate();
dataBaseHelper = new DataBaseHelper(MasterVisitPlan.this);
fillMVPdata();
}
private void fillMVPdata(){
mGroupsCursor = dataBaseHelper.getSelectedParentMVPDate(date.getText().toString());
startManagingCursor(mGroupsCursor);
mGroupsCursor.moveToFirst();
ExpandableListView Selectedlstview = (ExpandableListView) findViewById(R.id.MVPListitem);
mAdapter = new MyExpandableListAdapter(mGroupsCursor, this,
R.layout.mvp_list_parent,
R.layout.mvp_list_child,
new String[] {DataBaseHelper.MVP_INDUSTRY_TYPE},
new int[] {R.id.txtMVPParent},
new String[] {DataBaseHelper.MVP_BRCH_CODE_NAME, DataBaseHelper.MVP_BRCH_ADDRESS},
new int[] {R.id.txtviewBrnchCodeName, R.id.txtviewBrchAddr});
Selectedlstview.setAdapter(mAdapter);
}
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 cursor) {
@SuppressLint("Range") Cursor childCursor = dataBaseHelper.getSelectedChildMVPDate(cursor.getString(cursor.getColumnIndex("MVP_INDUSTRY_TYPE")));
startManagingCursor(childCursor);
childCursor.moveToFirst();
return childCursor;
}
}
}
