时间戳

首页 > 解决方案 > 在 SQLite 中通过 2 个参数过滤字符串选择

java - 在 SQLite 中通过 2 个参数过滤字符串选择

问题描述

无法弄清楚在我的联接表中如何正确过滤。当我使用：

String selection = " quiz_list_id " + " = ? " + " AND " + " LevelID " + " = ? ";

当我点击从数据库中正确分配名称的按钮时，什么也没有发生。我应该在哪里添加缺少的参数？

 public ArrayList<Question> getNLevelQuestions( int TestLevelID, int quizListID) {
    ArrayList<Question> questionList = new ArrayList<>();
    db = getReadableDatabase();


    String table = QuestionsTable.TABLE_NAME + " JOIN " + QuizListTable.TABLE_NAME +
            " ON " + QuizListTable._ID + " = " + QuestionsTable.COLUMN_QUIZ_LIST_ID;


    String selection = "quiz_list_id" + " = ? " + " AND " + "LevelID" + " = ?";

    String[] selectionArgs = new String[]{String.valueOf(quizListID), String.valueOf(TestLevelID)};

    Cursor c = db.query(table,
            null,
            selection,
            selectionArgs,
            null,
            null,
            null
    );
if (c.moveToFirst()) {
        do {
            Question question = new Question();
            question.setId(c.getInt(c.getColumnIndex("question_id")));
            question.setQuestion(c.getString(c.getColumnIndex("question")));
            question.setOption1(c.getString(c.getColumnIndex("option1")));
            question.setOption2(c.getString(c.getColumnIndex("option2")));
            question.setOption3(c.getString(c.getColumnIndex("option3")));
            question.setOption4(c.getString(c.getColumnIndex("option4")));
            question.setAnswerNB(c.getInt(c.getColumnIndex("answer_nb")));
            question.setQuizListID(c.getInt(c.getColumnIndex("quiz_list_id")));
            questionList.add(question);
        } while (c.moveToNext());
    }

    c.close();
    return questionList;
}

这里我的 StartingScreenActivity 将信息传递给主要活动

PracticeList = findViewById(R.id.Practice_n_group);
    for(int i=0; i<categorySize;i++){

        practiceButton = new Button(this);
        practiceButton.setText("" + categoryName.get(i));
        practiceButton.setId(i+1);
        final int index = i;
        practiceButton.setLayoutParams(new LinearLayout.LayoutParams(LinearLayout.LayoutParams.MATCH_PARENT, LinearLayout.LayoutParams.WRAP_CONTENT));
        practiceButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                startQuiz(v.getId(), categoryName.get(index).toString());
            }
        });
        // adding button to layout
        PracticeList.addView(practiceButton);
    }
}

private void startQuiz(int practiceListID, String quizListName) {
    Intent intent = new Intent(StartingScreenActivity.this, MainActivity.class);
    intent.putExtra(EXTRA_PRACTICE_ID, practiceListID);
    intent.putExtra(EXTRA_PRACTICE_NAME, quizListName);
    startActivityForResult(intent, REQUEST_CODE_QUIZ);
}

标签： javaandroidandroid-sqlite

解决方案

您不能将方法query()用于 sql 语句，而只能用于查询表。
您必须使用rawQuery()方法。
所以设置完整的 sql 语句，如：

String sql ="SELECT .... WHERE LevelID  = ? and quiz_list_id = ?";

接着：

String[] selectionArgs = new String[]{ String.valueOf(JLPTID), String.valueOf(quizListID)};
Cursor c = db.rawQuery(sql, selectionArgs);

注意：在您的 sql 语句中，为列使用适当的限定符，因此没有不明确的列名。

推荐阅读