Saving to a SQLite database in your Android application

This is the fourth post in my series about saving data in Android applications. Here are the other posts :

Introduction : How to save data in your Android application
Saving data to a file in your Android application
Saving preferences in your Android application

The previous posts described how to save files to the file system and to the preferences files. This can be enough if for a simple application, but if you data has a complex structure or if you have a lot of data to save, using a database is a better option. Managing a database requires more knowledge and setup, but it comes with many validations and performance optimization. The Android SDK includes the open source SQLite database engine and the classes needed to access it.

SQLite is a self-contained relational database that requires no server to work. The database itself is saved to a file in the internal storage of your application, so each application has its own private database that is not accessible to other applications. You can learn more about the SQLite project itself and its implementation of the SQL query language at

New to databases? A relational database saves data to tables. Each table is made of columns, and for each column you must choose a name and the type of data that can be saved in it. Each table should also have a column or many column that are set as the key of the table so each row of data can be uniquely identified. Relationships can also be defined between tables.
The basics of databases and the SQL query language used by most databases could take may articles to explain. If you don’t know how to use a database, this is a subject worth learning more aboutsince databases are used in almost all applications to store data.

To demonstrate how to create a database and interact with it, I created a small sample application, which is available at The application is a row counter for knitting projects: the user can create a knitting project containing one or many counters used to track the current number of rows done and to show the total amount of rows to reach. The structure of the database is as follow, with a project table in relation with a row_counter table :
First, to be able to create the database, we need a contract class for each table that describes the name of the elements of the table. This class should be used each time the name of elements in the database is required. To describe the name of each column, the contract class also contains a subclass with an implementation of the android.provider.BaseColumn, which automatically adds the name of an_ID and of a _COUNT column. I also like to put the CREATE TABLE SQL query in the contract class so all the strings used in SQL queries are at the same place. Here is the contract class for the row_counter table in the example :

* This class represents a contract for a row_counter table containing row
* counters for projects. The project must exist before creating row counters
* since the counter have a foreign key to the project.
public final class RowCounterContract {

* Contains the name of the table to create that contains the row counters.
public static final String TABLE_NAME = "row_counter";

* Contains the SQL query to use to create the table containing the row counters.
public static final String SQL_CREATE_TABLE = "CREATE TABLE "
+ RowCounterContract.TABLE_NAME + " ("
+ RowCounterContract.RowCounterEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ RowCounterContract.RowCounterEntry.COLUMN_NAME_PROJECT_ID + " INTEGER,"
+ RowCounterContract.RowCounterEntry.COLUMN_NAME_CURRENT_AMOUNT + " INTEGER DEFAULT 0,"
+ RowCounterContract.RowCounterEntry.COLUMN_NAME_FINAL_AMOUNT + " INTEGER,"
+ "FOREIGN KEY (" + RowCounterContract.RowCounterEntry.COLUMN_NAME_PROJECT_ID + ") "
+ "REFERENCES projects(" + ProjectContract.ProjectEntry._ID + "));";

* This class represents the rows for an entry in the row_counter table. The
* primary key is the _id column from the BaseColumn class.
public static abstract class RowCounterEntry implements BaseColumns {

   // Identifier of the project to which the row counter belongs
   public static final String COLUMN_NAME_PROJECT_ID = "project_id";

   // Final amount of rows to reach
  public static final String COLUMN_NAME_FINAL_AMOUNT = "final_amount";

   // Current amount of rows done
   public static final String COLUMN_NAME_CURRENT_AMOUNT = "current_amount";

To create the tables that stores the data described by the contracts, you must implement the android.database.sqllite.SQLLiteOpenHelper class that manages the access to the database. The following methods should be implemented as needed:

  • onCreate: this method is called the first time the database is opened by your application. You should setup the database for use in that method by creating the tables and initializing any data you need.
  • onUpdate: this method is called when your application is upgraded and the version number has changed. You don’t need to do anything for your first version, but in the following versions you must provide queries to modify the database from the old version to the new structure as needed so your user don’t loose their data during the upgrade.
  • onDowngrade (optional) : you may implement this method if you want to handle the case where your application is downgraded to a version requiring an older version. The default implementation will throw a SQLiteException and will not modify the database.
  • onOpen (optional) : this method is called after the database has been created, upgraded to a newer version or downgraded to an older version.

Here is a basic implementation of the android.database.sqllite.SQLLiteOpenHelper for the example that executes an SQL CREATE TABLE query for each table of the database in the onCreate method. There is no method available in the android.database.sqlite.SQLiteDatabase class to create a table, so you must use the execSQL method to execute the query.

* This class helps open, create, and upgrade the database file containing the
* projects and their row counters.
public class ProjectsDatabaseHelper extends SQLiteOpenHelper {
   // If you change the database schema, you must increment the database version.
   public static final int DATABASE_VERSION = 1;
   // The name of the database file on the file system
   public static final String DATABASE_NAME = "Projects.db";

   public ProjectsDatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);

    * Creates the underlying database with the SQL_CREATE_TABLE queries from
    * the contract classes to create the tables and initialize the data.
    * The onCreate is triggered the first time someone tries to access
    * the database with the getReadableDatabase or
    * getWritableDatabase methods.
    * @param db the database being accessed and that should be created.
   public void onCreate(SQLiteDatabase db) {
      // Create the database to contain the data for the projects

    * This method must be implemented if your application is upgraded and must
    * include the SQL query to upgrade the database from your old to your new
    * schema.
    * @param db the database being upgraded.
    * @param oldVersion the current version of the database before the upgrade.
    * @param newVersion the version of the database after the upgrade.
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // Logs that the database is being upgraded
            "Upgrading database from version " + oldVersion + " to " + newVersion);

Once the android.database.sqllite.SQLLiteOpenHelper is implemented, you can get an instance of the database object android.database.sqlite.SQLiteDatabase using the getReadableDatabase method of the helper if
you only need to read data or the getWritableDatabase method if you need to read and write data. There are four kinds of basic operations that can be done with the data, and modifications can not be undone like in all databases.

  • Inserting a new row:the insert method of the android.database.sqlite.SQLiteDatabase object inserts a new row of data in a table. Data can be inserted with a SQL INSERT query using the execSQL method, but using insert is recommended to avoid SQL injection: only one database row can be created by the insert method and nothing else, regardless of the input. In the following example, a few test projects are initialized in the database of the application by the onCreate method of the database helper after the creation of the table:
     * Initialize example data to show when the application is first installed. 
     * @param db the database being initialized.
    private void initializeExampleData(SQLiteDatabase db) {
       // A lot of code is repeated here that could be factorized in methods, 
       // but this is clearer for the example
       // Insert the database row for an example project in the project table in the
       // database
       long projectId;
       ContentValues firstProjectValues = new ContentValues();
                              "Flashy Scarf");
       projectId = db.insert(ProjectContract.TABLE_NAME, null, firstProjectValues);
       // Insert the database rows for a row counter linked to the project row 
       // just created in the database (the insert method returns the 
       // identifier of the row)
       ContentValues firstProjectCounterValues = new ContentValues();
                                       .RowCounterEntry.COLUMN_NAME_PROJECT_ID, projectId);
                                       .RowCounterEntry.COLUMN_NAME_FINAL_AMOUNT, 120);
       db.insert(RowCounterContract.TABLE_NAME, null, firstProjectCounterValues);
       // Insert the database row for a second example project in the project 
       // table in the database.
       ContentValues secondProjectValues = new ContentValues();
                               "Simple Socks");
       projectId = db.insert(ProjectContract.TABLE_NAME, null, secondProjectValues);
       // Insert the database rows for two identical row counters for the 
       // project in the database
       ContentValues secondProjectCounterValues = new ContentValues();
                                        .RowCounterEntry.COLUMN_NAME_PROJECT_ID, projectId);
                                        .RowCounterEntry.COLUMN_NAME_FINAL_AMOUNT, 80);
       db.insert(RowCounterContract.TABLE_NAME, null, secondProjectCounterValues);
       db.insert(RowCounterContract.TABLE_NAME, null, secondProjectCounterValues);	
  • Reading existing rows: the query method from the android.database.sqlite.SQLiteDatabase class retrieves the data that was previously inserted in the database. This method will return a cursor that points to the collection of rows returned by your request, if any. You can then convert the data fetched from the database table to an object can be used in your application: in the example, the rows from the project table are converted to Project objects.
    * Gets the list of projects from the database.
    * @return the current projects from the database.
    public ArrayList getProjects() {
       ArrayList projects = new ArrayList();
       // Gets the database in the current database helper in read-only mode
       SQLiteDatabase db = getReadableDatabase();
       // After the query, the cursor points to the first database row
       // returned by the request.
       Cursor projCursor = db.query(ProjectContract.TABLE_NAME, null, null, 
                                    null, null, null, null);
       while (projCursor.moveToNext()) {
          // Get the value for each column for the database row pointed by
          // the cursor using the getColumnIndex method of the cursor and
          // use it to initialize a Project object by database row
          Project project = new Project();
          int idColIndex = projCursor.getColumnIndex(ProjectContract.ProjectEntry._ID);
          long projectId = projCursor.getLong(idColIndex);
          int nameColIndex = projCursor.getColumnIndex(ProjectContract
          // Get all the row counters for the current project from the
          // database and add them all to the Project object
       return (projects);
  • Updating existing rows: the update method of an instance of the android.database.sqlite.SQLiteDatabase class updates the data in a row or in multiple rows of a database table. Like with the insert method, you could use the execSQL query to run a SQL UPDATE query, but using the update method is safer. In the following example, the current row counter value for the row counter in the row_counter table is updated with the new value. According to the condition specified only the row counter with the identifier passed as a parameter is updated but with another condition you could update many rows, so you should always make sure that the condition only selects the rows you need.
     * Updates the current amount of the row counter in the database to the value 
     * in the object passed as a parameter.
     * @param rowCounter the object containing the current amount to set.
    public void updateRowCounterCurrentAmount(RowCounter rowCounter) {
       SQLiteDatabase db = getWritableDatabase();
       ContentValues currentAmountValue = new ContentValues();
    	     RowCounterContract.RowCounterEntry._ID +"=?",
    	     new String[] { String.valueOf(rowCounter.getId()) });
  • Deleting existing rows:the delete method of an instance of the android.database.sqlite.SQLiteDatabase class deletes a row or in multiple rows of a database table. Like with the insert method, you could use the execSQL query to run a SQL UPDATE query, but using the delete method is safer. In the following example, a row counter in the row_counter table is deleted. According to the condition specified only the row counter with the identifier passed as a parameter is deleted but with another condition you could delete many rows, so you should always make sure that the condition only selects the rows you need so you don’t delete too much data.
     * Deletes the specified row counter from the database.
     * @param rowCounter the row counter to remove.
    public void deleteRowCounter(RowCounter rowCounter) {
       SQLiteDatabase db = getWritableDatabase();
                 RowCounterContract.RowCounterEntry._ID +"=?",
                 new String[] { String.valueOf(rowCounter.getId()) });

Finally, if you want to encapsulate access to the data in your database to avoid calling the database helper directly in your activity, you can also implement the android.content.ContentProvider class from the Android SDK. This is only required if your application must share data with other applications: you do not need one to get started, but you should consider using it as your data gets more complex.

Saving preferences in your Android application

This is the third post in my series about saving data in Android. The other posts can be found here :

A preference is a type of data that needs to be saved by most applications. Preferences allow users to change how the application works by giving choices about things like the layout, the features to enable and the measurement units to use to display data. Default preferences should be good enough for most users, but you may need to offer a choice for more advanced users.

If you must save preferences in your application, the Android SDK includes the Preference APIs made specifically to store this kind of data. The preferences are saved by the android.content.SharedPreferences class to an XML file that contains pairs of key-value; the values can be booleans, floats, ints, longs or strings. If you application is uninstalled, all the preferences are also removed since the file is saved to the internal storage of the application. The values for the preferences are saved in clear text, so you should encrypt your data if you want to store sensitive information like credentials.

You can save values directly to the preferences file without user action using the android.content.SharedPreferences class, but in general users will set their preferences from a Settings window that can be accessed from the action bar of your application.  To create the Settings window, the Preference APIs includes a android.preference.PreferenceFragment to add to your own activity. This fragment shows a list of preferences and saves the values selected by the user automatically using the android.content.SharedPreferences class.

Before using the android.preference.PreferenceFragment, you need to define which preferences will be available from the fragment with a configuration file that is saved to the /res/xml/ folder. Basic preferences types are available from the Android SDK, but you can also create your own custom preference type by overriding the android.preference.Preference class or one of its sub classes. Here is an example of a preferences.xml file containing a android.preference.CheckboxPreference, a android.preference.ListPreference and an android.preference.EditTextPreference :

<PreferenceScreen xmlns:android="" >
   android:defaultValue="@string/pref_default_welcome_text" />
   android:entryValues="@array/colorValuesArray" />
   android:summary="@string/pref_summary_show_welcome_text" />

After that, you must use the android.preference.PreferenceFragment to create your own fragment and specify the configuration to use. Your own fragment can be extended later on if you need to add custom behaviours by implementing the android.content.SharedPreferences.OnSharedPreferenceChangeListener that is triggered when a preference is modified.

public class SettingsFragment extends PreferenceFragment {
   public void onCreate(Bundle savedInstanceState) {

   // Load the preferences as configured in the /res/xml/preferences.xml file
   // and displays them.
   // The preferences will be automatically saved.

Finally, to be able to manage your preferences, you must create the activity that hosts the fragment you just defined, which can then be started from another activity by the user.

public class SettingsActivity extends Activity {
   protected void onCreate(Bundle savedInstanceState) {

   // Display the preferences fragment as the content of the activity
                       .replace(, new SettingsFragment()).commit();

This is enough to save the preferences to a file automatically, but to do something interesting with the preferences saved you must retrieve them in your other activities. To get preferences values, you can use the android.content.SharedPreferences class that is returned by the android.preference.PreferenceManager for the current context. To complete the previous example, the following code gets the SharedPreferences object during the onResume event of the activity and modifies the UI according to the current preferences. The onResume event is called when the activity starts and when the user comes back from another activity, in that case the SettingsActivity we created to manage the preferences.

public class MainActivity extends Activity {
   public void onResume() {
      SharedPreferences preferences = PreferenceManager.getDefaultSharedPreferences(this);

      TextView welcomeTextView = (TextView) findViewById(;

      String defaultWelcomeText = getResources().getString(R.string.pref_default_welcome_text);
      String welcomeText = preferences.getString("welcome_text", defaultWelcomeText);

      String defaultWelcomeTextColor = getResources()
      String welcomeTextColor = preferences.getString("welcome_text_color",

      boolean showWelcomeText = preferences.getBoolean("show_welcome_text", 
                                                       true /*showWelcomeText*/);
      if (showWelcomeText)

For the complete example that can be executed, see the following GitHub project :

Saving data to a file in your Android application

This is the second post in my series about storage in Android applications. The other post is available here :

This post is about saving to a file from an Android application, which is the easiest way to store data. There are many situations where you may need to save a file : you may want to use an existing file format to create files that can be opened by the user in another application or the data is simple enough that it can be represented by a text file or a format like XML or YAML. For complex data a database may be a better option, since accessing and parsing a large file can be slow and there are no integrity checks unless you code them by hand. On the other hand, there is less overhead and it easier to work with files than debugging with the data in a database. Depending on how the user will interact (or not) with your files, you will need to decide first which kind of storage to use.

Internal storage

Each application has its own private internal storage to save files. This is the kind of storage to use if the user shouldn’t be able to modify the file from outside your application, and if other application shouldn’t be able to access those files. Since the internal storage is private to your application, the files will be deleted if your application is uninstalled. The internal storage is also where your application is installed by default, so your files will always be available. On some older or cheaper devices the internal storage is quite limited, so you need to be careful about the size of the data you save if you need to support those devices.

You should never hardcode the path to the storage directories, since the directory may changes depending on the version of the Android OS used.  Also, Android 4.4 introduces the concept of multiple users : in that case, the internal and external storage depend on the user logged in and the files of the other users will be invisible. Here are some of the methods used to get the paths to the internal storage:

  • android.content.Context.getFilesDir(): returns a object representing the root directory of the internal storage for your application from the current context.
  • android.content.Context.getDir(String name, Context.MODE_PRIVATE): returns a object representing the directory name in the internal storage, creating the directory if it does not exists. The second parameter can also be used to set the directory to MODE_WORLD_READABLE or MODE_WORLD_WRITABLE so it is visible by all the other applications, but this is is risky security-wise and was deprecated in API level 17 (Android 4.2).
  • android.content.Context.getCacheDir(): returns a object representing the internal cache directory for the application. This is mean for small files (the documentation suggests no more that 1MB total) that can be deleted at any time when the system needs more storage. There is no guarantee that the cache will be cleared, so you must also clear those files manually when they are not needed anymore.

As you can see, the files are represented by the File object from the namepace: there is no file object specific to the Android SDK and the standard Java APIs for reading and writing files are used. Also, there is no specific application permission to set in the Android manifest to use the internal storage since it is already private to the application.

External storage

In addition of the internal storage, there is an external storage space shared by all the applications that is kept when your application is uninstalled. This is the storage that is shown when using a file explorer application and when the device is plugged in your computer. It may be implemented as a SD card that can be removed or as a partition of the built-in storage in the device, so your application should be able to work even if the card is removed or changed. To check the current state of the external storage, you can call the getExternalStorageState() method.

On device with many users (starting with Android 4.4), the external storage is specific to the current user and files for other users can’t be accessed. Also, there may be more than one external storage if the device has a built-in external storage which is a partition on the internal memory and a SD card: in that case, the built-in storage is the primary external storage. Reading files from the external storage requires the READ_EXTERNAL_STORAGE permission and writing or reading files requires the WRITE_EXTERNAL_STORAGE permission.

Here are the methods you should use to call to get the directories of the primary external storage:

  • android.os.Environment.getExternalStorageDirectory(): returns a object representing the root directory of the primary external storage of the device that is shared by all applications.
  • android.os.Environment.getExternalStoragePublicDirectory(): returns a object representing a public directory for files of a particular type on the primary external storage of the device.  For example, you can get the path to the public music directory by calling Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_MUSIC) or the public pictures directory by calling Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_PICTURES).
  • android.content.Context.getExternalFilesDir(): returns a representing the root directory of the primary external storage specific to your application, which is under the directory returned by getExternalStorageDirectory(). Unlike the other directories of the external storage,  the files you store in that folder will be deleted when your application is uninstalled. So, if you need to store files that are only needed by your application you should use this folder. Also, there is no specific permission needed for the application to read or write to its own external storage starting with Android 4.4, but with older versions your application needs the READ_EXTERNAL_STORAGE or WRITE_EXTERNAL_STORAGE permission.
  • android.content.Context.getExternalFilesDirs(): returns an array of representing the root directories of all the external storage directories that can be used by your application with the primary external storage as the first directory in the array. All those directories works the same as the primary storage returned by the getExternalFilesDir() method. If the device has a built-in storage as the primary external storage and a SD card as a secondary external storage, this is the only way to get the path to the SD card. This method was introduced in Android 4.4, before that it was impossible to get the path to the  secondary storage.
  • android.content.Context.getExternalCacheDir(): returns a object representing the cache of the application on the primary external storage. This cache is not visible to the user and is deleted when the application is uninstalled. There is no mechanism in the Android SDK to delete files in the cache directory, so you need to manage your cache to keep it to a reasonable maximum size. Starting with Android 4.4, the application does not need permission to access its own cache, but with older versions your application needs the READ_EXTERNAL_STORAGE or WRITE_EXTERNAL_STORAGE permission.

Example code to save to a file

To save a file, you need to get the path to the storage you want to use which is used the same way regardless of the type of storage used since all the methods returns a object representing the directory to use. Here is an example of using the external storage to save a text file from an Activity :

                            // Creates a trace file in the primary external storage space of the 
                            // current application.
                            // If the file does not exists, it is created.
   File traceFile = new File(((Context)this).getExternalFilesDir(null), "TraceFile.txt");
   if (!traceFile.exists())
                            // Adds a line to the trace file
   BufferedWriter writer = new BufferedWriter(new FileWriter(traceFile, true /*append*/));
   writer.write("This is a test trace file.");
                           // Refresh the data so it can seen when the device is plugged in a
                           // computer. You may have to unplug and replug the device to see the 
                           // latest changes. This is not necessary if the user should not modify
                           // the files.
                                     new String[] { traceFile.toString() },
catch (IOException e)
    Log.e("com.cindypotvin.FileTest", "Unable to write to the TraceFile.txt file.");