java - 如何获取表中包含一个或多个变量的行

Question

我有一个包含 ID、名称和描述列的表格

我有字符串:

String values = "banana, apple"

现在我想获取所有行，其中 Description 列同时包含“apple”和“banana”单词。

我使用了这段代码:

 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;
    }

但我收到此错误:

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)

我该如何解决这个问题？？？

Answer 1

如果您事先不知道有多少个值，您可以在调用 split() 后循环并附加您需要的任意数量的占位符。

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);