Passing Sqlite data randomly in notification

Question

I'm using prepopulated Sqlite database in my Android app which has three tables (id, word , definition). What i want to do is show a notification to the user randomly from the database. I am doing something like the code below but it is not working.

DatabaseHelper.java

public Cursor getNotification(int id) {
    SQLiteDatabase db = this.getWritableDatabase();
    String Query = "Select * from " + TABLE_DICTIONARY + " where id = " + id  ;
    Cursor cursor = db.rawQuery(Query, null);
    if(cursor.getCount() <= 0){
        cursor.close();
    }
    cursor.close();
    return cursor;
}

And Here is my MainActivity.java

private void showNotification() {
   int position =  dictionaryAdapter.cursor.getPosition();
  int cursorCount = dictionaryAdapter.cursor.getCount();

    final int random = new Random().nextInt((cursorCount) - 1);

   Cursor i =  mDBHelper.getNotification(random);
    NotificationCompat.Builder mBuilder = new NotificationCompat.Builder(this,CHANNEL_ID)
            .setSmallIcon(R.mipmap.ic_launcher)
            .setContentTitle(String.valueOf(i))
            .setPriority(NotificationCompat.PRIORITY_DEFAULT);

    NotificationManagerCompat managerCompat = NotificationManagerCompat.from(this);
    managerCompat.notify(1,mBuilder.build());

}

Answer 1

Fix part 1

In brief you could utilise the following :-

public Cursor getNotification() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.query(TABLE_DICTIONARY,null,null,null,null,null,"random()","1");
    return cursor;
}

The above equates to producing the SQL :-

SELECT * FROM your_table ORDER BY random() LIMIT 1; 

• Note that your_table is only represetative of your table name, it would very likely have to be changed to suit.

You may wish to have a look at query

Of course you could use the SQL above in the rawQuery. However, it is generally recommended to use the convenience methods, such as query, rather than the rawQuery and execSQL methods when the convenience methods can be used.

The above works by ordering the rows using SQLite's random() function and then LIMITing the number of resultant rows to just 1.

Additional Issues

1. Additionally, and importantly you cannot use a closed cursor as per :-

   • Closes the Cursor, releasing all of its resources and making it completely invalid. Unlike deactivate() a call to requery() will not make the Cursor valid again.

2. Furthermore to access the stored data from a Cursor you have to move to a row, as initially the Cursor is positioned at before the first row (e.g. i.moveToFirst() is used below).

3. Also as the single random row is returned there is no need to attempt to randomly retrieve one of the rows. However, you should check to see if a row has been returned.

Fix Part 2

As such the code in the Activity could be :-

private void showNotification() {


    Cursor i =  mDBHelper.getNotification();
    if (i.moveToFirst()) {
        NotificationCompat.Builder mBuilder = new NotificationCompat.Builder(this,CHANNEL_ID)
            .setSmallIcon(R.mipmap.ic_launcher)
            .setContentTitle(String.valueOf(i)) //???????????
            .setPriority(NotificationCompat.PRIORITY_DEFAULT);

        NotificationManagerCompat managerCompat = NotificationManagerCompat.from(this);
        managerCompat.notify(1,mBuilder.build());
    } else {
        //code here to handle nothing being obtained and thus 
    }
    i.close(); // You should always close a Cursor when done with it.
}

Another Issue

Using String.valueOf(i) will not be any value from the database rather it will be the what ever the Cursor's object's toString method produces. It would be something like android.database.sqlite.SQLiteCursor@a40a2ab

Fix part 3

You would want something along the lines of :-

    .setContentTitle(i.getString(i.getColumnIndex("word") + " is defined as " + i.getString(i.getColumnIndex("definition"))) // builds the title from columns of the selected row

This sets the title to the_word is defined as the_definition_of_the_word where the_word is the value stored in the word column and the_definition_of_the_word is the value stored in the definition column.

Additional

• NOTE the Cursor getColumnIndex method has a bug in that it is case dependant. It is thus advisable to use a constant value for column names and thus that the case is always correct as opposed to hard coding the column names when they are used. (e.g. like you use the constant TABLE_DICTIONARY).