Adding an SQL lite Database to Android Apps

Adding an SQL lite database to your applications is surprisingly easy.  I was able to reuse code from the standard notepad tutorial for Android developers.  The notepad tutorial can be found at the following url:

http://developer.android.com/training/notepad/index.html

In this tutorial all the database related tasks are encapsulated in a class called NotesDbAdapter, this class was fairly generic and it was easy to modify it to suit my own purposes.  I named my own class MathsTestDbAdapter.java (To make it easier to follow this blog, download my Android App - Maths Test, locate it by searching Google Play for 'Sundev Pabla').

The MathsTestDbAdapter class encapsulates all the database related activity such as defining the database schema (Schema refers to the definition of tables in the database and the columns making up the tables), creating the database and handling the database activities known as CRUD (Create, Read, Update and Delete).

We will look at each of the CRUD activities in subsequent posts.  Today we will have a quick look at defining the database schema and creating the database.  The database is created by executing an SQL command.  I store the SQL command in a String called DATABASE_CREATE.  The code for setting up my SQL command for creating the database is shown below:

// String variables to describe the schema of the database
private static final String DATABASE_NAME = "Maths_Test_Db";
private static final String DATABASE_TABLE = "Performace_Stats";
private static final int DATABASE_VERSION = 1;

private static final String TAG = "MathsTestDbAdapter";
public static final String KEY_ROWID = "_id";
public static final String KEY_USER_INITIALS = "initials";
public static final String KEY_SCORE = "score";
public static final String KEY_LEVEL = "level";
public static final String KEY_GAME_TYPE = "type";

// Store an SQL command for creating the database in a string variable called
// DATABASE_CREATE
    private static final String DATABASE_CREATE ="create table " + DATABASE_TABLE + " ("
    + KEY_ROWID + " integer primary key autoincrement, "
    + KEY_USER_INITIALS + " text not null, "
    + KEY_SCORE + " real not null, "
    + KEY_LEVEL + " text not null, "
    + KEY_GAME_TYPE + " text not null);";

From the code you should be able to determine the schema of my Database.  My Database has 1 table called Performance_Stats.  The table contains 5 columns.  The columns are a unique row id column, a column to hold the initials of a high scorer, a column for the high score, a column to store the game level which the high score relates to and finally a column to store the game type being played when the high score was obtained.

I think this is enough for today.  If I was you I would now download the NotePad tutorial from the link above and have a look at the schema for the database in the NotePad tutorial.  You could also have a look at the functions for performing the CRUD operations - this will make it easier to follow subsequent posts.  If you do download the NotePad tutorial then remember the source file you want to be looking at is called NotesDbAdapter.java.

All the best until next time, enjoy your android journey.