带有WHERE、AND、OR子句的java Android Sqlite搜索查询

Question

在这里，我需要从表'TABLE_NAME'中获取结果，其中'COLUMN_NAME_COUNTRY' = country 'AND' 'COLUMN_NAME_CATEGORY' = local 'OR' NA (Na also a value in the 'COLUMN_NAME_CATEGORY'。下面是我的代码：

public List<AppDataBean>getAllDataForSelectedCountryWithCategory(String country){

        ArrayList<AppDataBean>dataList= new ArrayList<>();
        SQLiteDatabase db = AppDbHelper.getInstance(context).getWritableDatabase();
        dataList.clear();

        try {
            Cursor c = db.rawQuery("SELECT * FROM " + AppDbConstructor.AppData.TABLE_NAME + " WHERE " + AppDbConstructor.AppData.COLUMN_NAME_COUNTRY + "='" + country + "'"
                    + " AND " + AppDbConstructor.AppData.COLUMN_NAME_CATEGORY + "='" + "Local" + "'" + " OR " + AppDbConstructor.AppData.COLUMN_NAME_CATEGORY + "='" + "NA" + "'" , null);

            if (c.getCount()>0)
            {
                if (c.moveToFirst())
                {
                    for (int i=0; i<c.getCount(); i++)
                    {
                        AppDataBean appDataBean = new AppDataBean();
                        appDataBean.setCategory(c.getString(c.getColumnIndex(AppDbConstructor.AppData.COLUMN_NAME_CATEGORY)));
                        appDataBean.setUrl(c.getString(c.getColumnIndex(AppDbConstructor.AppData.COLUMN_NAME_URL)));
                        appDataBean.setSource(c.getString(c.getColumnIndex(AppDbConstructor.AppData.COLUMN_NAME_SOURCE)));
                        appDataBean.setIconUrl(c.getString(c.getColumnIndex(AppDbConstructor.AppData.COLUMN_NAME_ICON_URL)));
                        appDataBean.setCountry(c.getString(c.getColumnIndex(AppDbConstructor.AppData.COLUMN_NAME_COUNTRY)));
                        Log.d("APP_DB_SOURCE","COLUMN_NAME_SOURCE: "+ c.getString(c.getColumnIndex(AppDbConstructor.AppData.COLUMN_NAME_URL))
                                + ", " + c.getString(c.getColumnIndex(AppDbConstructor.AppData.COLUMN_NAME_CATEGORY)));

                        dataList.add(appDataBean);
                        Log.d("APPDBSOURCE", "LIST_VALUES_ARE" + dataList.get(i).getCategory() + ", " + dataList.get(i).getCountry());
                        c.moveToNext();
                    }
                }
            }
            c.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        finally
        {
            db.close();
        }

        return dataList;
    }

最后的结果是“所有国家”和“地方”类别

Answer 1

您缺少括号

您可以这样做：

Cursor c = db.rawQuery("SELECT * FROM " + AppDbConstructor.AppData.TABLE_NAME + " WHERE " + AppDbConstructor.AppData.COLUMN_NAME_COUNTRY + "='" + country + "'"
                    + " AND (" + AppDbConstructor.AppData.COLUMN_NAME_CATEGORY + "='" + "Local" + "'" + " OR " + AppDbConstructor.AppData.COLUMN_NAME_CATEGORY + "='" + "NA" + "')" , null);

或者像这样：

Cursor c = db.rawQuery("SELECT * FROM " + AppDbConstructor.AppData.TABLE_NAME + " WHERE " + AppDbConstructor.AppData.COLUMN_NAME_COUNTRY + "='" + country + "'"
                    + " AND " + AppDbConstructor.AppData.COLUMN_NAME_CATEGORY + " IN " + "('"+ "Local"+"','"+"NA" + "')" , null);