Android 过滤 ListView 用户输入

Question

我正在尝试在我的应用程序中的 ListView 上实现过滤器。目前，我有一个由接收查询结果的游标填充的 ListView (这工作正常)。我有一个 float 按钮，单击时会弹出一个对话框，其中包含可能的过滤器选项供用户输入。

选项采用三个旋转器的形式。当用户从每个旋转器中选择一个项目时，结果将发送到三个不同查询之一。这些查询是我用来执行过滤器的查询，并且每个查询中都包含不同的 where 子句。但是，如果未选择任何内容，则变量值将设置为 0 或 ""

我遇到两个问题，第一是 for 循环检查是否已选择某些过滤器(如果已选择它们，这将确定要使用的过滤器查询)，它似乎只检查第一个 for 即使我没有更改某些微调器中的值，第二个是在下面的 SQL 显示中。

我也知道我应该使用比原始查询更好的东西，但我有一种复杂的查询，我知道它可以与原始查询一起使用，所以还不急于更改它。

任何帮助都会非常感谢

旋转器

                final Spinner heat = (Spinner) dialog.findViewById(R.id.heatSpinner);
            heat.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
                    String heat2 = heat.getSelectedItem().toString();
                    possibleRecipes.finalHeat = Integer.parseInt(heat2);

                }

                @Override
                public void onNothingSelected(AdapterView<?> parentView) {
                    possibleRecipes.finalHeat = 0;
                }
            });

            final Spinner dietary = (Spinner) dialog.findViewById(R.id.dietarySpinner);
            dietary.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
                     possibleRecipes.finalDietary = dietary.getSelectedItem().toString();

                }
                @Override
                public void onNothingSelected(AdapterView<?> parentView) {
                    possibleRecipes.finalDietary = "";
                }
            });

            final Spinner time = (Spinner) dialog.findViewById(R.id.timeSpinner);
            time.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parentView, View selectedItemView, int position, long id) {
                    String time2 = time.getSelectedItem().toString();
                    possibleRecipes.finalTime = Integer.parseInt(time2);

                }
                @Override
                public void onNothingSelected(AdapterView<?> parentView) {
                    possibleRecipes.finalTime = 0;
                }
            });

For 循环检查微调器信息并运行适当的查询

Button dialogButtonOk = (Button) dialog.findViewById(R.id.dialogButtonOK);
            // if button is clicked, close the custom ingredient_dialog
            dialogButtonOk.setOnClickListener(new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    if(possibleRecipes.finalHeat != 0 && possibleRecipes.finalTime != 0 && possibleRecipes.finalDietary !="")
                    {
                        //full filter search
                        possibleRecipes.filterCursor = adapter.fullFilter(finalHeat, finalDietary, finalTime);

                        String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
                        int[] to = new int[] {R.id.recipeName};

                        SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.filterCursor, columns, to, 0);
                        ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
                        recipeList.setAdapter(myCursorAdapter);
                    }
                    if(possibleRecipes.finalHeat == 0 && possibleRecipes.finalTime != 0 && possibleRecipes.finalDietary !="")
                    {
                        //filter for final time and dietary
                        possibleRecipes.timeAnddietaryFilterCursor = adapter.timeAnddietaryFilter(finalTime, finalDietary);
                        String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
                        int[] to = new int[] {R.id.recipeName};

                        SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.timeAnddietaryFilterCursor, columns, to, 0);
                        ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
                        recipeList.setAdapter(myCursorAdapter);
                    }
                    if(possibleRecipes.finalHeat != 0 && possibleRecipes.finalTime == 0 && possibleRecipes.finalDietary !="")
                    {
                        //filter for heat and dietary
                        possibleRecipes.heatAnddietaryFilterCursor = adapter.heatAnddietaryFilter(finalHeat, finalDietary);
                        String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
                        int[] to = new int[] {R.id.recipeName};

                        SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.heatAnddietaryFilterCursor, columns, to, 0);
                        ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
                        recipeList.setAdapter(myCursorAdapter);
                    }
                    if(possibleRecipes.finalHeat != 0 && possibleRecipes.finalTime != 0 && possibleRecipes.finalDietary.length()>1)
                    {
                        //filter for heat and time
                        possibleRecipes.heatAndtimeFilterCursor = adapter.heatAndtimeFilter(finalHeat, finalTime);
                        String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
                        int[] to = new int[] {R.id.recipeName};

                        SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.heatAndtimeFilterCursor, columns, to, 0);
                        ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
                        recipeList.setAdapter(myCursorAdapter);
                    }
                    else
                    {
                        String[] columns = new String[] {adapter.KEY_RECIPE_NAME, adapter.KEY_ID};
                        int[] to = new int[] {R.id.recipeName};

                        SimpleCursorAdapter myCursorAdapter = new SimpleCursorAdapter(getApplicationContext(),R.layout.row1, possibleRecipes.cursor, columns, to, 0);
                        ListView recipeList = (ListView) findViewById(R.id.possibleRecipeList);
                        recipeList.setAdapter(myCursorAdapter);
                    }

                        dialog.dismiss();
                }
            });
            dialog.show();

过滤器查询

    public Cursor fullFilter(int finalHeat, String finalDietary, int finalTime)
{
    return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
            "(select ingredients.recipe_code from ingredients inner join kitchen on " +
            "kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = "+finalHeat+" and recipes.dietary='"+finalDietary+"' and recipes.time = "+ finalTime, null);

}

public Cursor timeAnddietaryFilter(int finalTime, String finalDietary)
{
    return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
            "(select ingredients.recipe_code from ingredients inner join kitchen on " +
            "kitchen.ingredient_name = ingredients.ingredient_name) where recipes.dietary='"+finalDietary+"' and recipes.time = "+ finalTime, null);

}

public Cursor heatAnddietaryFilter(int finalHeat, String finalDietary)
{
    return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
            "(select ingredients.recipe_code from ingredients inner join kitchen on " +
            "kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = "+finalHeat+" and recipes.dietary='"+finalDietary, null);

}

public Cursor heatAndtimeFilter(int finalHeat, int finalTime)
{
    return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
            "(select ingredients.recipe_code from ingredients inner join kitchen on " +
            "kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = "+finalHeat+" and recipes.time = "+ finalTime, null);

}

错误

02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: Process: com.example.rory.prototypev2, PID: 26534
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime: android.database.sqlite.SQLiteException: near "where": syntax error (code 1): , while compiling: select recipe_name, _id from recipes where recipes.recipe_code in (select ingredients.recipe_code from ingredients inner join kitchen on kitchen.ingredient_name = ingredients.ingredient_name) where recipes.heat = 1 and recipes.dietary='Normal' and recipes.time = 15
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at com.example.rory.prototypev2.DBMain.fullFilter(DBMain.java:224)
02-01 19:48:15.305 26534-26534/com.example.rory.prototypev2 E/AndroidRuntime:     at com.example.rory.prototypev2.possibleRecipes$2$4.onClick(possibleRecipes.java:139)

Answer 1

我对旋转器不太确定，但你的 SQL 是错误的，每个语句中有两个 where 子句，删除最后一个 where 并将其替换为和。我更改了下面的查询，但您可以将其应用于其他查询。希望这有帮助

更换全过滤器

public Cursor fullFilter(int finalHeat, String finalDietary, int finalTime)
{
return db.rawQuery("select recipe_name, _id from recipes where recipes.recipe_code in " +
        "(select ingredients.recipe_code from ingredients inner join kitchen on " +
        "kitchen.ingredient_name = ingredients.ingredient_name) and recipes.heat = "+finalHeat+" and recipes.dietary='"+finalDietary+"' and recipes.time = "+ finalTime, null);

}