Presenting data from a database to a user

We have spent the last few posts looking at the class which encapsulates all the database related activity.  In the case of my Maths Test App this class is the MathsTestDbAdapter class (By the way my Maths Test App can be downloaded by searching Google Play for 'Sundev Pabla').

Today we will look at how you would use this class in the main part of your app.  You would use this class to get data from the database and put data into the database.  In my Maths Test App I use a database to store top scores.  In order to present top scores to the user I need to read top score info from the database and present it to the user.

In the Maths Test App I have a separate activity which I use for displaying scores to the user.  In the OnCreate( ) function of this activity I instantiate an instance of the MathsTestDbAdapter class and then call the open function on the instance.


        mDbHelper = new MathsTestDbAdapter(this);
        mDbHelper.open();

I can now use the mDbHelper object to call the get data functions in the MathsTestDbAdapter class.  I do this in the fillData function of  my ViewScoreActivity class.  The code for the fill data function is shown below:


    private void fillData()
    {

    Cursor topScoreCursor = mDbHelper.fetchScoresBasedOnSpecifiedGameLevelAndType(this.getResources().getStringArray(R.array.gameLevelArray)[gameLevelSpinner2.getSelectedItemPosition()],this.getResources().getStringArray(R.array.gameTypeArray)[gameTypeSpinner2.getSelectedItemPosition()]);
   
    startManagingCursor(topScoreCursor);
       
    tvInitial1 = (TextView) findViewById(R.id.initials1);
    tvScore1 = (TextView) findViewById(R.id.score1);
    tvInitial2 = (TextView) findViewById(R.id.initials2);
    tvScore2 = (TextView) findViewById(R.id.score2);
    tvInitial3 = (TextView) findViewById(R.id.initials3);
    tvScore3 = (TextView) findViewById(R.id.score3);
    tvInitial4 = (TextView) findViewById(R.id.initials4);
    tvScore4 = (TextView) findViewById(R.id.score4);
    tvInitial5 = (TextView) findViewById(R.id.initials5);
    tvScore5 = (TextView) findViewById(R.id.score5);
   
    // Will use switch case without breaks to populate table
    topScoreCursor.moveToFirst();
    switch (topScoreCursor.getCount())
    {
    case 5:
    tvInitial5.setText(""+topScoreCursor.getString(1));
    tvScore5.setText(""+topScoreCursor.getDouble(2));
    topScoreCursor.moveToNext();
    case 4:
    tvInitial4.setText(""+topScoreCursor.getString(1));
    tvScore4.setText(""+topScoreCursor.getDouble(2));
    topScoreCursor.moveToNext();
    case 3:
    tvInitial3.setText(""+topScoreCursor.getString(1));
    tvScore3.setText(""+topScoreCursor.getDouble(2));
    topScoreCursor.moveToNext();
    case 2:
    tvInitial2.setText(""+topScoreCursor.getString(1));
    tvScore2.setText(""+topScoreCursor.getDouble(2));
    topScoreCursor.moveToNext();
    case 1:
    tvInitial1.setText(""+topScoreCursor.getString(1));
    tvScore1.setText(""+topScoreCursor.getDouble(2));
    topScoreCursor.moveToNext();
    break;
    }
    }

The first thing I do in the fillData( ) function is call the fetchScoresBasedOnSpecifiedGameLevelAndType( ) function.  This returns a Cursor object which contains the data extracted from the database.

I then call startManagingCursor( ) function and pass in the cursor object.  This means that Android will look after the Cursor life cycle for me and I don't need to worry about things like closing the cursor when the activity is destroyed.  The moveToFirst( ) function on the Cursor object is used to move to the first data record returned by performing the query on the database.  In the switch statement I then use the Cursor to populate TextViews with data from the database.  For each record the first column or array element is a unique id, the second column is the players initials and the third column is the score.  To move to the next record you call moveToNext( ) on the cursor object.

Hopefully you can follow the code in the fillData( ) function, this is easy for me to say because I wrote the code and it may not be as self explanatory as I thing - so if you have got any questions then please get in touch.  I will leave it here for today, all the best until next time.

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.