Android SQLite Tutorial

Many times, we come across the situation where we want to store the data permanently so that it doesn't get lost while app quits. For example, I have a simple application for playing some animals' sounds. I will need information of all animals, their sound names and the corresponding sound files. I may not hard code all these values in code and read each time the application loads. This may seem easier if I have a smaller set of data. Just imagine, how long the file size would grow if I have to add such 100 animals to my app. Even if we have copy paste to help, I am sure you may want to try something smarter.... ;-)

Android provides various ways for persistent data storage. They are:

• Shared Preferences: These are key-value pairs stored in the device's internal memory within the application space. These may be shared across the applications as needed with proper permissions.
• Internal files: These are similar to various types of files which are stored into internal memory on phone within application space. These are useful for some configuration files or some secured data files. 
• SD Card Storage: These are same as the internal files except the fact that these are actually stored on the external SD card of the device and accessible to users.
• SQLite Databases: These are lightweight databases used for storing structural data.These are helpful when we need to store the data in forms of records and need to access it efficiently.

Let's now start with how to use SQLite database in our application. Consider above scenario. We need to store data for each animal like animal name, its sound, animal graphics resource and sound file location. We will have a table with the fields in it as animal_name, sound_name, graphics_id and sound_file. This is going to be our database schema.

But how am I going to add this to my application? Android provides us with SQLiteOpenHelper class for managing our SQLite databases. This class provides us with methods for creating and upgrading databases. Let's start with database creation first.

Let's have an DatabaseHelper class, inheriting SQLiteOpenHelper, for handling all database operations. When database needs to be created, onCreate() method is called by operating system. We will add our SQL statements for populating the database in this onCreate() method.

We are also going to add a method for fetching all animals records from database. Let's name it as getAllAnimals(). This will return a cursor for all database records.

public class DatabaseHelper extends SQLiteOpenHelper {

 private static final int DB_VERSION = 1;
 private static final String DB_NAME = "my_contacts.db";

 //Constants for database table names
 private static final String ANIMALS = "animals";
 
 //Constants for table fields
 private static final String ANIMAL_NAME = "animal_name";
 private static final String SOUND_NAME = "sound_name";
 private static final String GRAPHICS_ID = "graphics_id";
 private static final String SOUND_FILE = "sound_file";
 

 public DatabaseHelper(Context context) {
  super(context, DB_NAME, null, DB_VERSION);
 }

 @Override
 public void onCreate(SQLiteDatabase database) {
  //This method will be called when database needs to be created.
  //Use this to create initial setup such as creating tables.
  String sql = "create table "+ ANIMALS + "(_id integer primary key autoincrement, " +
    ANIMAL_NAME +" text," +
    SOUND_NAME+" text," +
    GRAPHICS_ID + " integer," +
    SOUND_FILE + " text)";
  database.execSQL(sql);
  
  //Our code for populating animals database goes here. 
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  //This method may not be used every time. 
  //It comes handy when you want to update database during app upgrades.
 }
 
 /**
  * This method is for fetching all animals data from database.
  * @return
  */
 Cursor getAllAnimals(){
  SQLiteDatabase db = getReadableDatabase();
  Cursor cursor = db.query(ANIMALS, null, null, null, null, null, null);
  return cursor;
 }