Working With SQLite Databases and Data Backup in Android


In the previous articles we discussed saving the data locally, meaning in the local storage of the phone. We also learnd about data saving in the SD card or externally.

Saving data when developing an application requires connecting it to the database. In this case the database is highly optimized for the handheld devices. Saving the data to a database is good for structured data such as contact information. All the classes related and used for development is given in the package “android.database.sqlite”.

Defining a schema and contract

First of all we need to configure the schema and must implement the schema, such that it is one of the principles of SQL databases. It is very helpful for declaring the contract classes that explicitly specify the layout of the schema in a systematic and self-documenting manner.

Let us have a look at how to create the table and rows in a SQLite database.

  1. public final class FeedReaderContract {   
  2. // To prevent someone from accidentally instantiating the contract class,   
  3. // give it an empty constructor.   
  4. public FeedReaderContract() {}   
  6. /* Inner class that defines the table contents */   
  7.    public static abstract class FeedEntry implements BaseColumns {   
  8.    public static final String TABLE_NAME = "entry";   
  9.    public static final String COLUMN_NAME_ENTRY_ID = "entryid";   
  10.    public static final String COLUMN_NAME_TITLE = "title";   
  11.    public static final String COLUMN_NAME_SUBTITLE = "subtitle";   
  12.    ...   
  13.    }   
  14. }   
The preceding written code is just a basic illustration. Now let us see the code with the SQL helper. Once you have defined how your database looks, you should implement methods that create and maintain the database and tables. Here are some typical statements that create and delete a table:
  1. private static final String TEXT_TYPE = " TEXT";   
  2. private static final String COMMA_SEP = ",";   
  3. private static final String SQL_CREATE_ENTRIES =   
  4.    "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +   
  5.    FeedEntry._ID + " INTEGER PRIMARY KEY," +   
  8.    ... // Any other options for the CREATE command   
  9.    " )";   
  10. private static final String SQL_DELETE_ENTRIES =   
  11.    "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;   
Just like files and other data any user save on the device locally, it means the device's internal storage. Each and every data entered or saved in the disk is completley secure because the other application can't access those resources. To use the sqliteOpenHelper create a subclass that overrides the onCreate(), onUpgrade() and onOpen() callback methods. For example the implementation of sqliteOpenHelper that uses some of the commands shown below. 
  1. public class FeedReaderDbHelper extends SQLiteOpenHelper {   
  2.     // If you change the database schema, you must increment the database version.   
  3.      public static final int DATABASE_VERSION = 1;   
  4.      public static final String DATABASE_NAME = "FeedReader.db";   
  6.      public FeedReaderDbHelper(Context context) {   
  7.         super(context, DATABASE_NAME, null, DATABASE_VERSION);   
  8.      }   
  9.      public void onCreate(SQLiteDatabase db) {   
  10.         db.execSQL(SQL_CREATE_ENTRIES);   
  11.      }   
  12.      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {   
  13.      // This database is only a cache for online data, so its upgrade policy is   
  14.      // to simply to discard the data and start over   
  15.         db.execSQL(SQL_DELETE_ENTRIES);   
  16.         onCreate(db);   
  17.      }   
  18.      public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {   
  19.         onUpgrade(db, oldVersion, newVersion);   
  20.      }   
  21.     }   
To access your database, instantiate your subclass of SQLiteOpenHelper:
  1. FeedReaderDbHelper mDbHelper = new FeedReaderDbHelper(getContext());   
Now we have seen various commands in that previously written code. It is now time to put the information into the database.

Put Information into a Database

The following shows Inserting the data into the database by ing a contentValue object to the insert() method:
  1. // Gets the data repository in write mode   
  2. SQLiteDatabase db = mDbHelper.getWritableDatabase();   
  4. // Create a new map of valueswhere column names are the keys   
  5. ContentValues values = new ContentValues();   
  6.    values.put(FeedEntry.COLUMN_NAME_ENTRY_ID, id);   
  7.    values.put(FeedEntry.COLUMN_NAME_TITLE, title);   
  8.    values.put(FeedEntry.COLUMN_NAME_CONTENT, content);   
  10. // Insert the new row, returning the primary key value of the new row   
  11.    long newRowId;   
  12.    newRowId = db.insert(   
  13.    FeedEntry.TABLE_NAME,   
  14.    FeedEntry.COLUMN_NAME_NULLABLE,   
  15.    values);   
The first argument for insert() is simply the table name. The second one provides the name of a column the methods combines the elements of insert().

Reading the information from the database

To read from a database, use the query() method and it onto your selection criteria and desired columns. This method combines elements of insert() and update(), except the column list defines the data you want to fetch from it, rather than the data to insert. The results of the query are returned to you in a Cursor object. Have a look below.
  1. SQLiteDatabase db = mDbHelper.getReadableDatabase();   
  3. // Define a projection that specifies which columns from the database   
  4. // you will actually use after this query.   
  5. String[] projection = {   
  6.    FeedEntry._ID,   
  7.    FeedEntry.COLUMN_NAME_TITLE,   
  8.    FeedEntry.COLUMN_NAME_UPDATED,   
  9.    ...   
  10. };   
  12. // How you want the results sorted in the resulting Cursor   
  13. String sortOrder =   
  14. FeedEntry.COLUMN_NAME_UPDATED + " DESC";   
  16. Cursor c = db.query(   
  17.    FeedEntry.TABLE_NAME, // The table to query   
  18.    projection, // The columns to return   
  19.    selection, // The columns for the WHERE clause   
  20.    selectionArgs, // The values for the WHERE clause   
  21.    null, // don't group the rows   
  22.    null, // don't filter by row groups   
  23.    sortOrder // The sort order   
  24. );   
For each row, you can read a column's value by calling one of the Cursor get methods, such as getString() or getLong(). For each of the get methods, you must the index position of the column you desire that you can get by calling getColumnIndex() or getColumnIndexOrThrow(). Let us take an example: 
  1. cursor.moveToFirst();   
  2. long itemId = cursor.getLong(   
  3.    cursor.getColumnIndexOrThrow(FeedEntry._ID)   
  4. );   
Delete Information From the Database

Since the preceding information and any data stored must eventually be deleted, to delete the data we must provide commands for deleting the data. To delete rows from the table we must first do a select that identifies the rows. The APIs of this database provides the mechanism for creating the selection criteria that protects against SQL injection.
  1. // Define 'where' part of query.   
  2. String selection = FeedEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";   
  3. // Specify arguments in placeholder order.   
  4. String[] selectionArgs = { String.valueOf(rowId) };   
  5. // Issue SQL statement.   
  6. db.delete(table_name, selection, selectionArgs);   
Update a Database

When you need to modify a subset of your database values, use the update() method.

Updating the table combines the content value syntax of insert() with the where syntax of delete().
  1. SQLiteDatabase db = mDbHelper.getReadableDatabase();   
  3. // New value for one column   
  4. ContentValues values = new ContentValues();   
  5. values.put(FeedEntry.COLUMN_NAME_TITLE, title);   
  7. // Which row to update, based on the ID   
  8. String selection = FeedEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";   
  9. String[] selectionArgs = { String.valueOf(rowId) };   
  11. int count = db.update(   
  12.    FeedReaderDbHelper.FeedEntry.TABLE_NAME,   
  13.    values,   
  14.    selection,   
  15.    selectionArgs);   
Backup the Data on cloud

Google provides the backup service to Android users in such a way that when the factory reset is done by the user all the third-party applications are uninstalled. The user might wonder whether all the data has been lost and cannot be restored due to an inadequate understanding in this field.

When a user installs again those applications the user sees that it automatically restores the data and user does not need to set the custom settings as he has done with the prior state. This service is provided by Google in which all this data has been saved and the backup to the Google cloud is automatic.

Some vendors, like Xiaomi, Sony and Samsung gives the cloud storage to the user free of cost up to a limit so that during an emergency and the state of data is corruption the user can backed up their data as well. During a backup operation that your application can request, Android's Backup Manager (BackupManager) queries your application for backup data, then hands it to a backup transport that then delivers the data to the cloud storage.

Package included in the Backup process

As we see in the preceding, all backup and restore operations are controlled by the BackupManager. Each application that would like to enable backup and preserve its data on remote storage must implement a backup agent. A backup agent can be built by extending either BackupAgent or BackupAgentHelper.

The BackupAgentHelper class provides a wrapper around BackupAgent that simplifies the procedures to implement a backup agent by employing backup helpers such as SharedPreferencesBackupHelper and FileBackupHelper.

The backup APIs let applications:
  • Perform backup of arbitrary data to remote storage.
  • Easily perform backup of SharedPreferences and files.
  • Restore the data saved to remote storage.

This article illustrates the basics of data saving in the SQLite database of Android systems. We have already acquired some knowledge of the data saving that is local storage and external storage. As we can see we have specified various commands used to delete user data and insert it into a SQLite database.