How to get the rows where table contains one or more variables

Question

I have table with columns Id, Name and Description

I have String:

String values = "banana, apple"

Now I want to get all the rows, where column Desctiption contains both "apple" and "banana" word.

I used this code:

 public List<Flavours> getNeedFlavours(String text) {
        List<Flavours> flavoursList = new ArrayList<Flavours>();
        // Select All Query
//        String selectQuery = "SELECT " + KEY_DATE + " FROM " + TABLE_TV_PROGRAMM;

        String table = TABLE_FLAVOURS;
        String where = KEY_DESCRIPTION + " = ?" ;
        String[] args = text.split(",");


        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(table, null, where, args, null, null, null);


        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Flavours flavours= new Flavours();
                flavours.setID(Integer.parseInt(cursor.getString(0)));
                flavours.setName(cursor.getString(1));
                flavours.setDescription(cursor.getString(2));


                // Adding contact to list
                flavoursList.add(flavours);
            } while (cursor.moveToNext());
        }

        // return channels list
        return flavoursList;
    }

But I'm getting this error:

android.database.sqlite.SQLiteBindOrColumnIndexOutOfRangeException: bind or column index out of range: handle 0x21443d8
            at android.database.sqlite.SQLiteProgram.native_bind_string(Native Method)
            at android.database.sqlite.SQLiteProgram.bind(SQLiteProgram.java:247)
            at android.database.sqlite.SQLiteProgram.bindString(SQLiteProgram.java:299)
            at android.database.sqlite.SQLiteProgram.bindAllArgsAsStrings(SQLiteProgram.java:401)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:55)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:58)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1653)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1538)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1494)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1574)
            at uz.ugadaykin.hookah.db.DatabaseHandler.getNeedFlavours(DatabaseHandler.java:175)
            at uz.ugadaykin.hookah.fragment.FlavoursFragment.makeListView(FlavoursFragment.java:87)
            at uz.ugadaykin.hookah.fragment.FlavoursFragment$1.onClick(FlavoursFragment.java:69)
            at android.view.View.performClick(View.java:3549)
            at android.view.View$PerformClick.run(View.java:14400)
            at android.os.Handler.handleCallback(Handler.java:605)
            at android.os.Handler.dispatchMessage(Handler.java:92)
            at android.os.Looper.loop(Looper.java:154)
            at android.app.ActivityThread.main(ActivityThread.java:4944)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
            at dalvik.system.NativeStart.main(Native Method)

How I can solve this problem???

You're giving two values to `whereArgs` with only one `?` placeholder. If you have two elements in the `args` array, do this: `String where = KEY_DESCRIPTION + " = ? OR " + KEY_DESCRIPTION + " = ?" ;` — Daniel Nugent, Apr 26 '15 at 21:03
The problem is that I don't know how many values there will be. There also can be only "apple" or three or four values ("apple, banana, cherry") — KMDev, Apr 26 '15 at 21:06

Daniel Nugent · Accepted Answer · 2015-04-26T22:01:08.977

0

If you don't know ahead of time how many values you have, you could just loop through after calling split() and just append however many placeholders you need.

String table = TABLE_FLAVOURS;
StringBuilder where = new StringBuilder() ;
String[] args = text.split(",");

//construct args with proper formatting
String[] likeArgs = new String[args.length];

for (int i = 0; i < args.length; i++){
    //construct LIKE args
    likeArgs[i] = "%" + args[i] + "%";

    //construct where clause
    if (i == 0){
       where.append(KEY_DESCRIPTION + " LIKE ?");
    }
    else{
        where.append(" OR " + KEY_DESCRIPTION + " LIKE ?");
    }
}

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.query(table, null, where.toString(), likeArgs, null, null, null);

edited Apr 26 '15 at 22:01

answered Apr 26 '15 at 21:28

Daniel Nugent

43,104
15
109
137

I think this is what I need, but KEY_DESCRIPTION must contain(not equal) these words. How I can handle this? – KMDev Apr 26 '15 at 21:43
You could use `LIKE`, but you would have to add '%' around each item in your `args` array. See this answer: http://stackoverflow.com/questions/9076561/android-sqlitedatabase-query-with-like/9076679#9076679 – Daniel Nugent Apr 26 '15 at 21:51

score -1 · Answer 2 · answered Apr 26 '15 at 21:05

-1

You are passing more than one value in args (I guess 2, according to your example) but have only one placeholder =? for selectionArgs.

You perhaps mean KEY_DESCRIPTION + " IN ( ? )" passing text as selectionArgs.

answered Apr 26 '15 at 21:05

Diego Torres Milano

65,697
9
111
134

Yes, exactly.I've changed it, but still doesn't work. However, there is no error anymore – KMDev Apr 26 '15 at 21:19
quote the strings: "\"banana\", \"apple\"", otherwise they are consider columns. – Diego Torres Milano Apr 26 '15 at 21:24

How to get the rows where table contains one or more variables

2 Answers2