Reading and Deleting data from an Android SQL database

To date we have looked at setting up the SQL database and creating the schema and also how you go about adding data to the database.  Today we will look at reading data from the database.

Reading data from the database

In my Maths Test App (Download this by searching Google Play for 'Sundev Pabla'), I provide two functions in the MathsTestDbAdapter class which allow data to be read from the database.  The functions return a Cursor object which contains the data returned by performing an SQL query on the database.  The code for the two functions is shown below.  The first function returns all the top scores in the database, whereas the second function uses arguments passed in to the function to filter the data and return a subset of the top scores.

    public Cursor fetchAllScores()
    {
    // This function returns data for all scores in the database in ascending order
    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID,KEY_USER_INITIALS,KEY_SCORE}, null, null, null, KEY_SCORE + " ASC", null);
    }


    public Cursor fetchScoresBasedOnSpecifiedGameLevelAndType(String level, String type)
    {
      return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID,KEY_USER_INITIALS,KEY_SCORE}, KEY_LEVEL + " = " + "'" + level + "'" + " and " + KEY_GAME_TYPE + " = " + "'" + type + "'" , null, null,null,KEY_SCORE + " ASC", null);
    }

We will look at the Cursor object in a subsequent post, possibly when we look at presenting data read from a database to the user (This will probably be the next post).  Today we will focus on the mDB.query function used to read data from the database in the above two functions.

The first argument to the mDb.query function tells the function which table you want to query, the second parameter is a list of columns you would like returned, the third parameter is a SQL WHERE clause (without the WHERE) specifying which data records you would like to get from the database - passing in null returns all the records.  The next three arguments I don't use and simply pass in null.  I will leave it as an exercise for yourselves to find out how setting these parameters would affect the data returned by the database.  The seventh argument allows you to specify how you would like the data ordered.  I am ordering the data in Ascending order of Score -  so the first record will be the lowest score and the last will be the highest.  The last argument which I do not use, would allow you to specify a limit on the amount of data returned.

Deleting data from the database

Deleting data from the database is relatively simple.  In my Maths Test App I provide a function for deleting data from the database.  This is needed because I only store the top 5 scores, so when a score falls out of the top 5 it is deleted.  The function takes a rowId for the row of data to be deleted.  So in the main part of my application code I would have to first read data from the database, determine which row I want to delete and then call the delete function below using the rowID as an argument to the function call.  In the delete function below, the first argument is the table from which I want to delete data. The second argument is an SQL WHERE clause without the WHERE, it is used to specify the data I wish to delete.  The third argument I do not use - again I will leave it as an exercise for you to find out what it does and how it might be used.


    public boolean deleteScore(long rowId)
    {
    return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    

I think thats probably enough for today - so until next time good bye.