Get random row from SQLite Database Android SDK

Question

I want to get a random row from a database I created but I can't figure out how to do it.

I found several questions and answers here on StackOverflow which say I should use this:

Cursor cursor = this.db.query("mainTable Order BY RANDOM() LIMIT 1",
            new String[] { "*" }, null, null, null, null, null);

However, I think I have some kind of other database because it returns an error not knowing the method 'query'.

This is the database code I use:

package com.example.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter{

    private static final String TAG = "DBAdapter";

    public static final String KEY_ROWID = "_id";
    public static final int COL_ROWID = 0;

    public static final String KEY_PIC = "picture";
    public static final String KEY_ANS = "answer";

    public static final int COL_PIC = 1;
    public static final int COL_ANS = 2;


    public static final String[] ALL_KEYS = new String[] {KEY_ROWID, KEY_PIC, KEY_ANS};

    public static final String DATABASE_NAME = "MyDb";
    public static final String DATABASE_TABLE = "mainTable";
    public static final int DATABASE_VERSION = 2;   

    private static final String DATABASE_CREATE_SQL = 
            "create table " + DATABASE_TABLE 
            + " (" + KEY_ROWID + " integer primary key autoincrement, "
            + KEY_PIC + " integer not null, "
            + KEY_ANS + " text not null"
            + ");";

    private final Context context;

    private DatabaseHelper myDBHelper;
    private SQLiteDatabase db;

    public GevaarherkenningDBHelper(Context ctx) {
        this.context = ctx;
        myDBHelper = new DatabaseHelper(context);
    }

    public GevaarherkenningDBHelper open() {
        db = myDBHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        myDBHelper.close();
    }

    public long insertRow(int pic, String answer) {
        ContentValues initialValues = new ContentValues();

        initialValues.put(KEY_PIC, pic);
        initialValues.put(KEY_ANS, answer);

        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    public boolean deleteRow(long rowId) {
        String where = KEY_ROWID + "=" + rowId;
        return db.delete(DATABASE_TABLE, where, null) != 0;
    }

    public void deleteAll() {
        Cursor c = getAllRows();
        long rowId = c.getColumnIndexOrThrow(KEY_ROWID);
        if (c.moveToFirst()) {
            do {
                deleteRow(c.getLong((int) rowId));              
            } while (c.moveToNext());
        }
        c.close();
    }

    public Cursor getAllRows() {
        String where = null;
        Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS, 
                            where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    public Cursor getRow(long rowId) {
        String where = KEY_ROWID + "=" + rowId;
        Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS, 
                        where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    public Cursor getRowRandom(String rowId) {
        Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS, 
                        null, null, null, null, "RANDOM()", "1");
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    public boolean updateRow(long rowId, int pic, String answer) {
        String where = KEY_ROWID + "=" + rowId;

        ContentValues newValues = new ContentValues();
        newValues.put(KEY_PIC, pic);

        newValues.put(KEY_ANS, answer);

        return db.update(DATABASE_TABLE, newValues, where, null) != 0;
    }

    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase _db) {
            _db.execSQL(DATABASE_CREATE_SQL);           
        }

        @Override
        public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading application's database from version " + oldVersion
                    + " to " + newVersion + ", which will destroy all old data!");

            _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

            onCreate(_db);
        }
    }
}

Is there a way I can implement the 'query'-method is this database class and if so, how? Or does anyone suggest using another database code?

EDIT:

I probably have not asked the right question here... My problem is that using any of the methods given in the answers at this point, such as

db.rawQuery("SELECT * FROM mainTable ORDER BY RANDOM() LIMIT 1", null);

returns an error that it is undefined.

So what do I need to change in the database adapter code to properly use this code?

Answer 1

Try to remove this and make sure your db is SQLiteDatabase. Also you can get random row in the following way:

db.rawQuery("SELECT * FROM mainTable ORDER BY RANDOM() LIMIT 1", null);

BTW, you already have getRowRandom method in your code with correct query.

Answer 2

query() is a method on SQLiteDatabase. You are already using query() in your DBAdapter class, such as in your getAllRows() method.