Performing Multiple values from Multiple column in sqlite using query?

Question

I have refered this link and able to make Database query for filtering. Currently I am using below code for filtering 7 BusTypes from Database and the below code is working fine, But I have to now apply filter for boardingpoint and droppingpoints also. How can I do that ?

My Database contains list of multiple boarding_point as one string, as every city has multiple boarding,like., [city pointone, city pointtwo, city point_three] as string in in bording_point. Now I want to query with one city point like., if I searched with city pointone it should give me all buses which have this point..

public final class DatabaseConstants {

// To prevent someone from accidentally instantiating the contract class,
// make the constructor private.

private DatabaseConstants(){
}

public static class FeedEntry implements BaseColumns {
    public static final String TABLE_NAME = "temp_table";
    public static final String ID = "id";
    public static final String FROM_CITY_NAME = "fromcityname";
    public static final String SRC_TIME = "srctime";
    public static final String TO_CITY_NAME = "tocityname";
    public static final String DES_TIME = "destime";
    public static final String DISTANCE = "distance";
    public static final String BUS_ID = "busid";
    public static final String OPERATOR_NAME = "operatorname";
    public static final String BUS_TYPE = "bustype";
    public static final String BUS_SEAT = "busseat";
    public static final String DURATION = "total_duration";
    public static final String BOARDING_POINT = "boarding_points";
    public static final String DROPPING_POINT = "dropping_points";

    public static final String CREATE_TABLE = ("CREATE TABLE " + TABLE_NAME + " ( "
            + ID + " TEXT PRIMARY KEY , "
            + FROM_CITY_NAME + " TEXT, "
            + SRC_TIME + " TEXT, "
            + TO_CITY_NAME + " TEXT, "
            + DES_TIME + " TEXT, "
            + DISTANCE + " TEXT, "
            + BUS_ID + " TEXT, "
            + OPERATOR_NAME + " TEXT, "
            + BUS_TYPE + " TEXT, "
            + BUS_SEAT + " TEXT, "
            + DURATION + " TEXT, "
            + BOARDING_POINT + " TEXT, "
            + DROPPING_POINT + " TEXT )");

    public static final String DROP_TABLE = ("DROP TABLE IF EXISTS " + TABLE_NAME);
}

}

//Model class

    public class BusDataModel {
    private String id;
    private String fromcityname;
    private String src_time;
    private String tocityname;
    private String des_time;
    private String distance;
    private String busid;
    private String operatorname;
    private String bustype;
    private String busseat;
    private String busduration;
    private String boarding_point;
    private String droppint_point;

    public BusDataModel() {
    }

    public BusDataModel(String id, String fromcityname, String src_time, String tocityname, String des_time,
                        String distance, String busid, String operatorname, String bustype, String busseat,String busduration,
                        String boarding_point,String droppint_point) {
        this.id = id;
        this.src_time = src_time;
        this.des_time = des_time;
        this.fromcityname = fromcityname;
        this.tocityname = tocityname;
        this.distance = distance;
        this.busid = busid;
        this.operatorname = operatorname;
        this.bustype = bustype;
        this.busseat = busseat;
        this.busduration=busduration;
        this.boarding_point=boarding_point;
        this.droppint_point=droppint_point;
    }

    public String getBusduration() {
        return busduration;
    }

    public void setBusduration(String busduration) {
        this.busduration = busduration;
    }

    public String getid() {
        return id;
    }

    public String getSrc_time() {
        return src_time;
    }

    public String getDes_time() {
        return des_time;
    }

    public String getFromcityname() {
        return fromcityname;
    }

    public String getTocityname() {
        return tocityname;
    }

    public String getDistance() {
        return distance;
    }

    public String getBusid() {
        return busid;
    }

    public String getOperatorname() {
        return operatorname;
    }

    public String getBustype() {
        return bustype;
    }

    public String getBusseat() {
        return busseat;
    }

    public void setid(String id) {
        this.id = id;
    }

    public void setSrc_time(String src_time) {
        this.src_time = src_time;
    }

    public void setDes_time(String des_time) {
        this.des_time = des_time;
    }

    public void setFromcityname(String fromcityname) {
        this.fromcityname = fromcityname;
    }

    public void setTocityname(String tocityname) {
        this.tocityname = tocityname;
    }

    public void setDistance(String distance) {
        this.distance = distance;
    }

    public void setBusid(String busid) {
        this.busid = busid;
    }

    public void setOperatorname(String operatorname) {
        this.operatorname = operatorname;
    }

    public void setBustype(String bustype) {
        this.bustype = bustype;
    }

    public void setBusseat(String busseat) {
        this.busseat = busseat;
    }

    public String getBoarding_point() {
        return boarding_point;
    }

    public void setBoarding_point(String boarding_point) {
        this.boarding_point = boarding_point;
    }

    public String getDroppint_point() {
        return droppint_point;
    }

    public void setDroppint_point(String droppint_point) {
        this.droppint_point = droppint_point;
    }
}

//sqlite class

public class FilterData extends SQLiteOpenHelper {
    private Context context;
    List<BusDataModel> filterdData = new LinkedList<>();
    public static final String DATABASE_NAME = "temp_user";

    public FilterData(Context context) {
        super(context, DATABASE_NAME, null, 3);
        this.context = context;
        onCreate(this.getWritableDatabase());
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME + ";");
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }


    /**
     * Method to save all Data into Database
     *
     * @param busDataModel to get all Data Generically
     */
    public boolean addBusDetails(BusDataModel busDataModel) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(ID, busDataModel.getid());
        values.put(FROM_CITY_NAME, busDataModel.getFromcityname());
        values.put(SRC_TIME, busDataModel.getSrc_time());
        values.put(TO_CITY_NAME, busDataModel.getTocityname());
        values.put(DES_TIME, busDataModel.getDes_time());
        values.put(DISTANCE, busDataModel.getDistance());
        values.put(BUS_ID, busDataModel.getBusid());
        values.put(OPERATOR_NAME, busDataModel.getOperatorname());
        values.put(BUS_TYPE, busDataModel.getBustype());
        values.put(BUS_SEAT, busDataModel.getBusseat());
        values.put(DURATION, busDataModel.getBusduration());
        values.put(BOARDING_POINT, busDataModel.getBoarding_point());
        values.put(DROPPING_POINT, busDataModel.getDroppint_point());

        long isInserted = db.insert(TABLE_NAME, null, values);
        db.close();
        if (isInserted == -1)
            return false;
        else
            return true;
    }

public List<BusDataModel> setFilterByBusType(List<String> bus_types, List<String> boarding_types, List<String> dropping_types) {

        List<BusDataModel> busList = new LinkedList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String[] data = {ID, FROM_CITY_NAME, SRC_TIME, TO_CITY_NAME, DES_TIME,
                DISTANCE, BUS_ID, OPERATOR_NAME, BUS_TYPE, BUS_SEAT, DURATION, BOARDING_POINT, DROPPING_POINT};

        String selection = BUS_TYPE + " IN (?,?,?,?,?,?,?)";
        String[] selectionArgs = (String[]) bus_types.toArray(new String[bus_types.size()]);

        Cursor cursor = db.query(TABLE_NAME, data, selection, selectionArgs,
                null, null, null);

        BusDataModel busDataModel;
        if (cursor.moveToFirst()) {
            do {
                busDataModel = new BusDataModel();
                busDataModel.setid(cursor.getString(cursor.getColumnIndex(ID)));
                busDataModel.setFromcityname(cursor.getString(cursor.getColumnIndex(FROM_CITY_NAME)));
                busDataModel.setSrc_time(cursor.getString(cursor.getColumnIndex(SRC_TIME)));
                busDataModel.setTocityname(cursor.getString(cursor.getColumnIndex(TO_CITY_NAME)));
                busDataModel.setDes_time(cursor.getString(cursor.getColumnIndex(DES_TIME)));
                busDataModel.setDistance(cursor.getString(cursor.getColumnIndex(DISTANCE)));
                busDataModel.setBusid(cursor.getString(cursor.getColumnIndex(BUS_ID)));
                busDataModel.setOperatorname(cursor.getString(cursor.getColumnIndex(OPERATOR_NAME)));
                busDataModel.setBustype(cursor.getString(cursor.getColumnIndex(BUS_TYPE)));
                busDataModel.setBusseat(cursor.getString(cursor.getColumnIndex(BUS_SEAT)));
                busDataModel.setBusduration(cursor.getString(cursor.getColumnIndex(DURATION)));
                busDataModel.setBoarding_point(cursor.getString(cursor.getColumnIndex(BOARDING_POINT)));
                busDataModel.setDroppint_point(cursor.getString(cursor.getColumnIndex(DROPPING_POINT)));

                busList.add(busDataModel);
            } while (cursor.moveToNext());
        }
        db.close();
        cursor.close();

        return busList;

    }

    }

Please show a complete code example. You should show a class with appropriate fields and methods. Also show how the table is created. — Code-Apprentice
– Code-Apprentice, Commented Mar 11, 2017 at 11:20
I suggest using the Cursor directly rather than loading data into a list. This is especially important when you have a large data set that you do not or cannot load into memory all at once. — Code-Apprentice
– Code-Apprentice, Commented Mar 11, 2017 at 12:06
setFilterByBusType() should only take one parameter for the possible bus types — Code-Apprentice
– Code-Apprentice, Commented Mar 11, 2017 at 12:10

Code-Apprentice · Accepted Answer · 2017-03-11 12:08:15Z

0

My Database contains list of multiple boarding_point as one string, as every city has multiple boarding,like., [city pointone, city pointtwo, city point_three] as string in in bording_point.

First you need to parse the string so that you have each individual city. Then you can build a query using those cities.

answered Mar 11, 2017 at 12:08

Code-Apprentice

84k26 gold badges163 silver badges290 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Performing Multiple values from Multiple column in sqlite using query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related