SQLite Storage Framework in Android

Storage is an important component of any software program. A user friendly android application should have the ability to store important user data and present them to the user whenever needed.

There are many storage methods in android some of which include SharedPreferences, Internal Storage, External Storage, SQLite etc. This tutorial however focuses on the SQLite Database method of storing data in android.

What Will I Learn?

• About SQLite Classes in Android
• How to read from an SQLite Database in Android
• How to write to an SQLite Database in Android

Requirements

• Android Studio 2.3 and above
• Basic Knowledge of native Android development using Java
• Basic knowledge of Relational Database Management Systems (RDBMS)

Difficulty

• Intermediate

Tutorial Contents

Image Source

The SQLite class in android is a Java layer class that sits ontop the SQLite database management system. This class provides a means of communication between the developer and the underlying SQLite storage of the android architecture. It ensures this interaction by using another class known as the SQLiteOpenHelper. This class enables the creation and update of the database, think of it like a middle man between you and your database.

The SQLiteOpenHelper is used by creating a class which extends it and overriding the required methods. In android studio, create a new java class called MyDatabase and input the following code

public class MyDatabase extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "MyFirstDatabase";

    public MyDatabase (Context context){
        super(context,DATABASE_NAME, null, DATABASE_VERSION);
    }
@Override
public void onCreate(SQLiteDatabase db){

}

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

}

The above code shows the creation of a new database class known as MyDatabase. The class contains two static constants of String and Integer types with names of DATABASE_NAME and DATABASE_VERSION respectively. These constants are passed to the superclass constructor at instantiation of the database and help the android framework to identify the name of the database created and its version number if eventually an upgrade is to be made.

The onUpgrade() method is used to perform an upgrade on the database and it takes in 3 parameters. the oldVersion integer must be less than the newVersion to be able to perform the upgrade. The onCreate() method is the method in which the database is created with its tables having columns. To create a database table, the database object passed into the onCreate() method is used to execute a string which specifies the table name and number of columns to create. See code below,

public static final String DATABASE_TABLE = "MyFirstTable";
private static final String _ID = "_id";
public static final String FIRST_COLUMN = "Column1";
public static final String SECOND_COLUMN = "Column2";
public static final String THIRD_COLUMN = "Column3";
@Override
public void onCreate(SQLiteDatabase db){
String databaseDefined = "CREATE TABLE "+ DATABASE_TABLE+ " ("+
                _ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"+
                FIRST_COLUMN + " TEXT,"+
                SECOND_COLUMN + " TEXT," +
                THIRD_COLUMN +" INTEGER);";
        db.execSQL(databaseDefined);
}

In the code snippet shown, five new constant variables are defined which represent the table name, table ID and the 3 columns in the database table.
A string called databaseDefined is declared and this string houses standard SQL commands for defining a database table.

Create Table creates a database table with the next concatenated string which is the table name and gives it an _ID column that automatically increases as the number of rows in the table increases. The other columns are created with the data type they hold, TEXT and INTEGER indicating that string and numbers respectively can be held in those columns.

Finally, the defined String is then passed into a database method known as execSQL() and the database is created.

Reading from a Database

To read from the created database anywhere in the android application, the created SQLite helper class is instantiated and its object is used to get a database object that can be read from. Code below explains this.

MyDatabase myDatabaseObject = new MyDatabase(this);
SQLiteDatabase database = myDatabaseObject .getReadableDatabase();
Cursor cursor = database.query(MyDatabase.DATABASE_TABLE, MyDatabase.FIRST_COLUMN, null, null, null);
//code to recover data from cursor
cursor.close();

In the code above, the MyDatabase class which extends the SQLiteOpenHelper class is used to create an object, this object is then used to get a readable database from the android device through the method getReadableDatabase().

The returned database object can be read by calling the query() method on it and specifying the table name to query and column as parameters. The other parameters are sorting parameters that help to sort the data in the cursor.

To get the raw data from the cursor, a method is called on the cursor depending on the type of data stored in that row. To recover a string cursor.getString() is called and the column index is passed in as a parameter.

The cursor is closed after reading data from it so as to free up system resources.

Writing To a Database

To write to a database, the SQLiteOpenHelper class is used to create an object that is used to get a writable database object. A class called ContentValues which allows key/value pairs to be declared is used to hold the data to be written or stored in the database. See code snippet below,

MyDatabase myDatabaseObject = new MyDatabase(this);
SQLiteDatabase database = myDatabaseObject .getWritableDatabase();
ContentValues values = new ContentValues();
values.put(MyDatabase.FIRST_COLUMN, "This is a boy");
values.put(MyDatabase.THIRD_COLUMN, 0);
database.insert(MyDatabase.DATABASE_TABLE, null, values);

The ContentValues object holds the column names in the database as keys and the data to be stored in them are inputted as values. The database then calls the insert() method passing in the name of the table where the data should be written to, a nullable string and the values object which holds all the details to be stored.

CONCLUSION

In conclusion, the SQLite Storage framework is very easy to use in android and it is very important for any well developed app as a means of storage. It is normally best practice to define constants in a separate class so they can be easily called from anywhere without having to worry about mistakes in their values.

Thanks for reading, I hope you learned something.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---