How to add cursor data(DbHandler.java) to listview(MainActicity.java)?

Question

My MainActivity.java code:

public class MainActivity extends Activity implements AdapterView.OnItemSelectedListener{
    Spinner spinner1;
    TextView textView;
    ListView listView;

    @Override
    public void onCreate(Bundle savedInstanceState) {                                               //ThisIsWorkingAsExpected(SoDontChange)
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        spinner1 = (Spinner) findViewById(R.id.spinner1);
        textView = (TextView) findViewById(R.id.textview);
        listView = (ListView) findViewById(R.id.listview);
        spinner1.setOnItemSelectedListener(this);
        loadSpinnerData();
   }

    public void loadSpinnerData() {                                                                //ThisIsWorkingAsExpected(SoDontChange)
        DbHandler db = new DbHandler(getApplicationContext());
        List<String> BusNoList = db.getBusNoType();
        ArrayAdapter<String> SpinnerAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_item, BusNoList); SpinnerAdapter.setDropDownViewResource(android.R.layout.select_dialog_singlechoice);
        spinner1.setAdapter(SpinnerAdapter);
    }

    @Override
    public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
        DbHandler db = new DbHandler(getApplicationContext());
        List<String> BusRouteList = db.getBusRoute();
        ArrayAdapter<String> lvAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, BusRouteList);
        listView.setAdapter(lvAdapter);
    }


    @Override
    public void onNothingSelected(AdapterView<?> parent) {

    }

}

My DbHandler.java code:

public class DbHandler extends SQLiteOpenHelper{
private static final int DATABASE_VERSION = 9;
private static final String DATABASE_NAME = "TravelMadurai";
private static final String TAG = "DbHandler";

public DbHandler(Context context)
{
    super(context, DATABASE_NAME, null , DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db)                                                         //TABLES NOT CREATINIG IF DATABASE IS ALREADY THERE
{
    try
    {
            db.execSQL("CREATE TABLE IF NOT EXISTS MDUBusRoutes(ID INTEGER PRIMARY KEY,Bus_No_Type TEXT,From_Loc TEXT,To_Loc TEXT,Route_1 TEXT,Route_2 TEXT,Route_3 TEXT,Route_4 TEXT,Route_5 TEXT,Route_6 TEXT,Route_7 TEXT,Route_8 TEXT,Route_9 TEXT,Route_10 TEXT,Route_11 TEXT,Route_12 TEXT,Route_13 TEXT,Route_14 TEXT,Route_15 TEXT,Total_Stops INTEGER)");
            db.execSQL("INSERT INTO MDUBusRoutes(Bus_No_Type,From_Loc,To_Loc,Route_1,Route_2,Route_3,Route_4,Route_5,Route_6,Route_7,Route_8,Route_9,Route_10,Route_11,Route_12,Route_13,Route_14,Route_15,Total_Stops)VALUES('C5','c5route1','c5route2','c5route3','c5route4','c5route5','c5route6','c5route7','c5route8','c5route9','c5route10','c5route11','c5route12','c5route13','c5route14','c5route15','c5route16','c5route17',15)");
            db.execSQL("INSERT INTO MDUBusRoutes(Bus_No_Type,From_Loc,To_Loc,Route_1,Route_2,Route_3,Route_4,Route_5,Route_6,Route_7,Route_8,Route_9,Route_10,Route_11,Route_12,Route_13,Route_14,Route_15,Total_Stops)VALUES('A5','a5Route1','a5Route2','a5Route3','a5Route4','a5Route5','a5Route6','a5Route7','a5Route8','a5Route9','a5Route10','a5Route11','a5Route12','a5Route13','a5Route14','a5Route15','a5Route16','a5Route17',15)");
            db.execSQL("INSERT INTO MDUBusRoutes(Bus_No_Type,From_Loc,To_Loc,Route_1,Route_2,Route_3,Route_4,Route_5,Route_6,Route_7,Route_8,Route_9,Route_10,Route_11,Route_12,Route_13,Route_14,Route_15,Total_Stops)VALUES('B7','b7Route1','b7Route2','b7Route3','b7Route4','b7Route5','b7Route6','b7Route7','b7Route8','b7Route9','b7Route10','b7Route11','b7Route12','b7Route13','b7Route14','b7Route15','b7Route16','b7Route17',15)");
    }
    catch (Exception e){
        Log.d(TAG,"ErrorCreatingTable" + e);
    }

}


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


public  List<String> getBusNoType()                                                             //ThisIsWorkingAsExpected(SoDontChange)
{
List<String> BusNoList = new ArrayList<String>();
try {
    String SelectBusNo = "SELECT * FROM MDUBusRoutes ORDER BY Bus_No_Type ASC" ;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor CursorBusNo = db.rawQuery(SelectBusNo, null);
    if (CursorBusNo.moveToFirst())
    {
        do {
            BusNoList.add(CursorBusNo.getString(1));
        }
        while (CursorBusNo.moveToNext());
    }
    CursorBusNo.close();
    db.close();
}
catch (Exception ignored){
    Log.d(TAG,"Error:..........................................................................." + ignored);


}
return BusNoList;
}

public  List<String> getBusRoute()                                                             //ThisIsWorkingAsExpected(SoDontChange)
{
    List<String> BusRouteList = new ArrayList<String>();

    try {
        String SelectBusRoute = "SELECT * FROM MDUBusRoutes" ;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor CursorBusRoute = db.rawQuery(SelectBusRoute, null);
        if (CursorBusRoute.moveToFirst()) {
            do {
                BusRouteList.add(CursorBusRoute.getString(2));
            }
            while (CursorBusRoute.moveToNext());
        }
        CursorBusRoute.close();
        db.close();
    }
    catch (Exception ignored){

    }
    return BusRouteList;
}

}

My output looks like:

Spinner items:

My Output shows the third column of every row in the table, but I want to add the columns in the table. When I select anothor item in spinner, listview should display columns where Bus_No_Type = (spinneritemselected)

For example:


If I select B7 from spinner then listview should display values from b7Route1 to b7Route17. Which is already added in table.

I am new to android. Please help me I am trying this for a long time

Thank you!

Answer 1

Change your onItemSelected as below.

@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
    String bus = (String) getItem(position);
    DbHandler db = new DbHandler(getApplicationContext());
    List<String> BusRouteList = db.getBusRoute(bus);
    ArrayAdapter<String> lvAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, BusRouteList);
    listView.setAdapter(lvAdapter);
}

and getBusRoute as below.

public  List<String> getBusRoute(String bus)                                                             //ThisIsWorkingAsExpected(SoDontChange)
{
    List<String> BusRouteList = new ArrayList<String>();

    try {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor CursorBusRoute = db.query("MDUBusRoutes", new String[] {"From_Loc","To_Loc","Route_1","Route_2","Route_3","Route_4","Route_5","Route_6","Route_7","Route_8","Route_9","Route_10","Route_11","Route_12","Route_13","Route_14","Route_15"}, "Bus_No_Type=?", new String[] { bus }, null, null, null, null);
        if (CursorBusRoute.moveToFirst()) {
            do {
                BusRouteList.add(CursorBusRoute.getString(2));
            }
            while (CursorBusRoute.moveToNext());
        }
        CursorBusRoute.close();
        db.close();
    }
    catch (Exception ignored){

    }
    return BusRouteList;
}