Selecting and storing in Android's Cursor n number of rows where one row has a specific id and the rest are random

Question

In Android Studio I'm trying to select specific amount of rows from sqlite database where one row is predetermined based on given id and the rest are picked randomly. The query should be saved in Cursor because I would display selected rows in ListView, which can take info from Cursor simply through SimpleCursorAdapter.

The initial database is created as such:

public void onCreate(SQLiteDatabase database) {
    database.execSQL("CREATE TABLE DICTIONARY(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "english TEXT, " +
            "estonian TEXT);");
    addWordPair(database, "myriad", "tohutu hulk");
    addWordPair(database, "egregious", "jõletu");
    addWordPair(database, "erroneous", "väär");
    addWordPair(database, "salient", "esile tõusev");
    addWordPair(database, "galvanize", "laengut andma");
    addWordPair(database, "tenuous", "hõre");
    addWordPair(database, "caustic", "söövitav");
}

public void addWordPair(SQLiteDatabase database, String english, String estonian) {
    ContentValues wordPair= new ContentValues();
    wordPair.put("english", english);
    wordPair.put("estonian", estonian);
    database.insert("DICTIONARY", null, wordPair);
}

The selected rows would be display in ListView using Cursor:

SimpleCursorAdapter listAdapter = new SimpleCursorAdapter(this,
                android.R.layout.simple_list_item_1,
                cursor,
                selectedLanguage,
                new int[] {android.R.id.text1},
                0);
ListView listofWordsListView = (ListView) findViewById(R.id.wordsListView);
listofWordsListView.setAdapter(listAdapter);

I get five random english words using:

Cursor cursor = database.query("DICTIONARY Order By RANDOM() LIMIT 5",
                new String[] {"english"}, null, null, null,null, null);

I get a single english word with specific id using:

Cursor cursor = database.query("DICTIONARY", 
                new String[] {"english"},
                "_id = ?", 
                new String[] {Integer.toString(id)}, 
                null, null, null);

Now I need to store both, the specific row and and random rows (specific row preferably in between random rows) in cursor in order to use it in CursorAdapter.

Answer 1

You can use a MergeCursor e.g. :-

Cursor cursor1 = database.query("DICTIONARY Order By RANDOM() LIMIT 5",
                new String[] {"english"}, null, null, null,null, null);
Cursor cursor2 = database.query("DICTIONARY", 
                new String[] {"english"},
                "_id = ?", 
                new String[] {Integer.toString(id)}, 
                null, null, null);
MergeCursor merged = new MergeCursor(new Cursor[]{cursor1,cursor2});

You then treat/handle the MergeCursor (merged in this example) like a standard cursor.

MergeCursor