Generating random output from SQLite Database

Question

Hi I currently have a database that has 5 questions and answers stored in it. I want to randomly output three of the questions each time the app runs. I have the below code but my app crashes when it loads

public List<Question> getAllQuestions() {
        List<Question> quesList = new ArrayList<Question>();
        int nQuestions = 3; //select COUNT(*) from questions

        Random random = new Random();
        int id = random.nextInt(nQuestions);

        String selectQuery = "SELECT  id FROM " + TABLE_QUEST;
        dbase=this.getReadableDatabase();
        Cursor cursor = dbase.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Question quest = new Question();
                quest.setID(cursor.getInt(0));
                quest.setQUESTION(cursor.getString(1));
                quest.setANSWER(cursor.getString(2));
                quest.setOPTA(cursor.getString(3));
                quest.setOPTB(cursor.getString(4));
                quest.setOPTC(cursor.getString(5));
                quesList.add(quest);
            } while (cursor.moveToNext());{
                 id = random.nextInt(nQuestions);
            }
        }
        // return quest list
        return quesList;
    }

Answer 1

There are a couple of mistakes in your code.

First, your SQL query is wrong:

String selectQuery = "SELECT  id FROM " + TABLE_QUEST;

gives you all the values of the "id" column in the table TABLE_QUEST, not the column with the id you determined with your call to Random.nextInt.

String selectQuery = "SELECT * FROM " + TABLE_QUEST + " WHERE id=" + id;

The "*" stands for "all columns in this table" and the "WHERE" enables you to filter your rows according to the conditions that follow (i.e. id=3).

Note while it's not a problem in this case, passing unescaped values (i.e., the WHERE id=" + id part is very bad form because it might make you vulnerable to SQL injections as soon as you use user input for this).

Second: the rest of the code doesn't make much sense for various reasons. I'll try to list a few:

• if your code would work (i.e. with * instead of id in the SELECT, but without the WHERE clause), you'd just add all of the questions in your database to the list, because there's no condition.
• if the condition would work, you'd get just one row with a certain id. your do-while loop would run only once.
• if you had a loop around the select, then it might work, but it'd probably return the same rows TWICE or even more often.
• you'd still get occasional crashes for various reasons, among them
  • primary keys - the id attribute which is probably auto-increment - is not necessarily in the range 0..n-1. it might be in your case, but that's not exactly typical. auto-incrementing primary keys usually start at 1.
  • if your question-answers records are CRUD (create, read, update, delete), the ids might have holes, i.e. "1, 2, 6, 8, 12".

i'd rewrite the whole method; there are a couple of ways of doing it. just a few hints:

1. if there are very few records in the table (e.g. less than a couple hundreds) you can just load all the questions into list A.
   1. create list A
   2. add all records from the database to list A
   3. create a list B
   4. as long as there are fewer elements in list B than you want (i.e. listB.size() < 3) remove a random element from List A and add it to List B.
   5. return listB. // this seems wasteful, but would probably be ok in your case.
2. let the database do the randomizing:
   1. SELECT * FROM table ORDER BY RANDOM() LIMIT 3;
   2. read all the records into a list
   3. return the list
   4. done! (note: this works with sqlite, but is, as far as i know, not universally accepted by different database systems).

there are countless other ways of achieving your goal. but i'd just use the ORDER BY RANDOM() LIMIT x trick, it's probably the easiest way.

Answer 2

The issue is, you are only selected the id from the table, for all entries.

Your random will also select only questions 0, 1 or 2. Your random should be random.nextInt(5) since you have 5 questions.

Finally, this should be in a loop and then add multiple wheres to your query to get multiple questions.

String query = "SELECT * FROM " + TABLE_QUEST + " WHERE ";
    for (int x = 0; x < nQuestions; x++) {
        if (x > 0) {
            query += " OR ";
        }
        query += "id=" + random.nextInt(5);
    }

This will make your query look like this:

SELECT * FROM TABLE_QUEST WHERE id=4 OR id=2 OR id=3

Finally, change the way your cursor.moveToNext() is. Your code should look something like this:

public List<Question> getAllQuestions() {
    List<Question> quesList = new ArrayList<Question>();
    int nQuestions = 3; //select COUNT(*) from questions

    Random random = new Random();

    String query = "SELECT * FROM " + TABLE_QUEST + " WHERE ";
    for (int x = 0; x < nQuestions; x++) {
        if (x > 0) {
            query += " OR ";
        }
        query += "id=" + random.nextInt(5);
    }

    dbase=this.getReadableDatabase();
    Cursor cursor = dbase.rawQuery(query, null);
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Question quest = new Question();
            quest.setID(cursor.getInt(0));
            quest.setQUESTION(cursor.getString(1));
            quest.setANSWER(cursor.getString(2));
            quest.setOPTA(cursor.getString(3));
            quest.setOPTB(cursor.getString(4));
            quest.setOPTC(cursor.getString(5));
            quesList.add(quest);
        } while (cursor.moveToNext());
    }
    // return quest list
    return quesList;
}

Answer 3

Sorry that I write this as an answer, but I don't have permissions to write comments to a question, so I want to tell you that first of all you will always become one of the first 3 added questions as a random question, because for the random id you use as limitations 3 id = random.nextInt(nQuestions); instead of nQuestions (which is 3 for you, you should use questList.size() and do it 3 times for the numbers from 0 to questList.size()