App data and files in Android

Save data using SQLite

Saving data to a database is ideal for repeating or structured data, such as contact information.

• Define a schema and contact
• Schema is a formal declaration of how the database is organized. 
• You should create a companion class, known as a contract class, which explicitly specifies the layout of your schema in a systematic and self-documenting way.
• A contract class is a container for constants that define names for URIs, tables and columns. The contract class allows you to use the same constants across all the other classes in the same package.
• A good way to organize a contract class is to put definitions that are global to your whole database in the root level of the class. Then create an inner class for each table. Each inner class enumerates the corresponding table's columns.
• For complete implementation please click the header or check the e.g.

 public class DeviceContract {
    private DeviceContract() {
    }

    public static class DeviceEntry implements BaseColumns {
        public static final String TABLE_NAME = "devices";
        public static final String COLUMN_NAME_ID = "device_ID";
        public static final String COLUMN_NAME_DEVICE_NAME = "device_name";
        public static final String COLUMN_NAME_DEVICE_MODEL = "device_model";
        public static final String COLUMN_NAME_DEVICE_APP_VERSION = "device_app_version";
        public static final String COLUMN_NAME_DEVICE_VERSION = "device_version";
        public static final String QUERY_CREATE_DEVICE_TABLE =
                "CREATE TABLE " + TABLE_NAME +
                        " (" + COLUMN_NAME_ID +
                        " INTEGER PRIMARY KEY AUTOINCREMENT," +
                        COLUMN_NAME_DEVICE_NAME + " TEXT," +
                        COLUMN_NAME_DEVICE_MODEL + " TEXT," +
                        COLUMN_NAME_DEVICE_APP_VERSION + " TEXT," +
                        COLUMN_NAME_DEVICE_APP_VERSION + " TEXT)";
        public static final String QUERY_DROP_DEVICE_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
        public static final String QUERY_SHOW_DEVICES = "SELECT * FROM " + TABLE_NAME;
        public static final String QUERY_SHOW_DEVICE =
                "SELECT * FROM " + TABLE_NAME +
                        " WHERE " +
                        COLUMN_NAME_ID + " = ?";
    }
 }

• Create a database using an SQL helper
• For complete implementation please click the header.
• Android stores your database in your application's private folder.
• SQLiteOpenHelper class contains a useful set of APIs for managing your database.
• Call getWritableDatabase() and getReadableDatabase() in a background thread.
• To access your database, instantiate your subclass of SQLiteOpenHelper
• Put information into a database
• Insert data into the database by passing a ContentValues object to the insert() method.
• For complete implementation please click the header.
• The first argument of insert() is simply the table name.
• The second argument tells the framework what to do in the event that the ContentValues is empty. If you specify the name of a column, the framework inserts a row and sets the value of the column to null. If you specify null, the framework does not insert a row when there are no values.
• The insert() methods return the ID for the newly created row, or it will return -1 if there was an error inserting the data.
• Read information from a database
• To read from a database, use the query() method, passing it your selection criteria and desired columns. 
• The results of the query are returned to you in a Cursor object.
• The selection and selectionArgs are combined to create a WHERE clause. Because the arguments are provided separately from the selection query, they are escaped before being combined.
• To look at a row in the cursor, use one of the Cursor move methods, which you must always call before you begin reading values.
• Note: The cursor starts at position -1
• When finished iterating through results, call close() on the cursor to release its resources.
• For complete implementation please click the header.
• Delete information from a database
• To delete rows from a table, you need to provide selection criteria that identify the rows to the delete() method.
• For complete implementation please click the header.
• The return value for the delete() method indicates the number of rows that were deleted from the database.
• Update a database
• When you need to modify a subset of your database values, use the update() method.
• For complete implementation please click the header.
• Persisting database connection
• It is optimal to close the database in the onDestroy() method of the calling Activity.

 @Override
 protected void onDestroy() {
    deviceViewModel.closeDatabase(); // deviceViewModel is the ViewModel of this Activity
    super.onDestroy();
 }

• Debug your database
• The Android SDK includes a sqlite3 shell tool that allows you to browse table contents, run SQL commands and perform other useful functions on SQLite databases.
• Disadvantages of SQL
• There is no compile-time verification of raw SQL queries. As your data graph changes, you need to update the affected SQL queries manually. This process can be time consuming and error prone.
• You need to use lots of boilerplate code to convert between SQL queries and data objects.
• For these reasons, it is highly recommended to use the Room Persistence Library.

Do's and Don'ts

• The cursor starts at position -1

Reference

1. https://developer.android.com/training/data-storage/sqlite