Select rows using List<> on sqlite android

Question

Hi I need help with getting list of rows using a list values. I was wondering if i could have a search query using list of strings. If so, how should that be implemented?

For example. I have these values inside my List<Info> info.

Jan 10, 2015 Sept 16, 2015 Sept 20, 2015 Oct 20, 2015

Now what I'm trying to do is get all the rows which contains those values on the list.

Example table

    Info table
   ---------------------------------------------
    id  name   address        dates
   ---------------------------------------------
    1   Test             Sept 16, 2015
    4   Test             Sept 20, 2015
    10  Test             Jan 2, 2015

It should get the rows

1 Test
4 Test

This is the method i used to insert those dates.

public void insertDates(List<LocalDate> dates, String name) {
        int size = dates.size();

        sqLiteDatabase = this.getWritableDatabase();
        try {
            for(int i = 0; i < size; i++) {
                ContentValues contentValues = new ContentValues();
                contentValues.put(KEY_DATE, String.valueOf(duration.get(i).toString("MMMM d, yyyy")));
                contentValues.put(KEY_IS_FILTERED, 0);
                contentValues.put(KEY_NAME, name);
                sqLiteDatabase.insert(TABLE_INFO, null, contentValues);
            }
            sqLiteDatabase.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqLiteDatabase.close();
        }

I have this query but it doesnt seem to work

  public List<Info> getInfo(String info) {
        List<Info> getInfoList= new ArrayList<>();
        sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM "+ TABLE_INFO + " WHERE " + KEY_DATES+ " LIKE '%" + info+ "%'", null);
        cursor.moveToFirst();

        while (!cursor.isAfterLast()) {
            Info info = new Info();
            info.setName(cursor.getString(1));
            getInfoList.add(info);
            cursor.moveToNext();
        }
        cursor.close();
        return getInfoList;
    }

Answer 1

Create SQLOperation java class and put blew code:

public class SQLOperations {

private final SQLiteDatabase db;

public SQLOperations(Context context) {
    DatabaseOpenHelper helper = new DatabaseOpenHelper(context);
    db = helper.getWritableDatabase();
}

public List<String> searchMethod(List<String> datesInput) {
    List<String> stringList = new ArrayList<>();
    int length = datesInput.size();
    Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
    int j = 0;
    for (int i = 0; i < length; i++) {
        while (cursor.moveToNext()){
            String dateTemp = cursor.getString(cursor.getColumnIndex("dates"));
            if (dateTemp.equals(datesInput.get(i))) {
                String name = cursor.getString(cursor.getColumnIndex("name"));
                stringList.add(j, name);
                j++;
            }
        }
    }
    return stringList;
}
}

In everywhere you want to search in your table, take this class object.

Of course don't forget the SQLiteOpenHelper class:

public class DatabaseOpenHelper extends SQLiteOpenHelper {

Context context;

public DatabaseOpenHelper(Context context) {
    super(context, SQLConstants.DATABASE_NAME, null, SQLConstants.DATABASE_VERSION);
    this.context = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(SQLConstants.CREATE_TABLE_NAME);
}

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

}

}

Good luck

Answer 2

Here is a suggestion:

You may store the date values in milliseconds (using the Date.getTime() method) in an INTEGER column (say dateMS) of the table. Then you may use the following WHERE clause in your query:

WHERE dateMS in (date1MS, date2MS, date3MS...)

where dateiMS is the value in milliseconds of the ith date you want to search for.