Ultimate Guide to Android SQLite Database

If you want to become a professional Android developer then you must know how to save and retrieve data. SQLite is by far the most commonly used mobile database technology. In this Android SQLite Database tutorial, I will teach you everything you need to know to start working with Android SQLite in Android. There are other database technologies that you can use to save data in Android such as Realm database and Firebase Realtime database and I will cover those technologies in separate blog posts.

In this post, you will learn about SQLiteOpenHelper class, how to create a database, how to create tables, how to write queries, how to perform Create, Read, Update and Delete (CRUD) operations using SQLite.You will also learn how to backup and restore SQLite database and other best practices with working with SQLite. These skills will help you to better understand the other mobile database technologies such as Realm database and Firebase. My book Pronto SQLite — Master Android Data Persistence covers all these technologies in detail, this post is an excerpt from the book.

I assume that you have at a minimum a rudimentary understanding of Android development. This Android SQLite Database tutorial will focus on data persistence in Android using SQLite and will not provide a thorough introduction to Android development concepts. So if you have not covered the fundamentals of Android development, you may struggle with the concepts discussed in this book. If you are a complete beginner in Android development, you may want to take advantage of the many resources available online including the Android Beginner Developer Guide.

Introduction to SQLite

Let us get the official definition of SQLite Database

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

http://sqlite.com/about.html

Let us review that official definition of SQLite above

1. SQLite is a SQL compliant database (very important)
2. It does not need a separate server to run
3. It does not require separate installation, if the platform you are working with needs SQLite then it is already included just like in Android
4. It is free

SQLite in Android

SQLite predates Android, it is used in other major applications other than Android. While it is the case that SQLite is widely used in Android development today, however not a small number of developers has been amused and confused by SQLite; and for this reason, many attempts has been made to unseat or abandon SQLite. Many of these efforts are band-aids to SQLite such as the many flavors of ORMs, some compound the confusion surrounding SQLite such as ContentProvider and some are new approaches such as Realm database.

The acronym “SQL” in SQLite means Structured Query Language which means that SQLite is a SQL compliant database engine which means that SQLite is used to store structured data, unlike say SharedPreference which is used to store key-value pairs of data. Working with SQLite in Android means that you are mentally and programmatically translating between two different languages. The data you want to save and the logic that generates the data are written in an object-oriented language such as Java. Then you have SQLite which understand the language of SQL. Here is an example of this difference.

SQL statement to create Product table.

CREATE TABLE product(
_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
manufacturer TEXT,
promo_message TEXT,
price NUMERIC,
purchase_price NUMERIC,
image_path TEXT,
category_id INTEGER,
category_name TEXT,
create_date BIGINT,
last_update_date BIGINT,
FOREIGN KEY(category_id) REFERENCES category(_id))

Java class definition for Product class.

public class Product {
private long id;
private String productName;
private String description;
private String promoMessage;
private double salePrice;
private double purchasePrice;
private String imagePath;
private long categoryId;
private String categoryName;
private long dateAdded;
private long dateOfLastTransaction;
}

In comparison to other SQL-compliant DBMS SQLite is easy to use! At least easy to setup. The challenge here is that “easy” is relative, what is relatively easy to you may be challenging for me. This point is not lost on the designers of Android, so they went some step further and created helper classes that make it easier to get work with SQLite in Android and below are three components of SQLite that you need to understand because they are central to working with SQLite in Android.

1. SQLiteOpenHelper — this is the most important class that you will work with in Android SQLite. You will use SQLiteOpenHelper to create and upgrade your SQLite Database. In other words, SQLiteOpenHelper removes the effort required to install and configure database in other systems.
2. SQLiteDatabase — this is the actual database where your data is stored. When you created your database with SQLiteOpenHelper class, it sets everything in motion to create your database but holds off until you are ready to use that database. And the way SQLiteOpenHelper knows that you are ready to use your database is when you access that database either with getReadableDatabase() or getWritableDatabase() for read and write operations respectively.
3. Cursor — The reason you store your data in a database is so you can access them later. That access is called a query and a successful query will return a list of the items you queried for. If that list is so long, your Android device may choke if you want to access all of the items in the returned result. This is where the Cursor comes in, the list of the items that you queries for are wrapped in a Cursor and the Cursor hands them over to you in batches of any number.

Demo Application

To get the most out of this Android SQLite Database tutorial we will add the data persistence layer of a demo Android Shopping Cart application. While this tutorial will focus on the data persistence needs of this app and not on the user interface, the accompanying source code comes with a fully developed user interface. The name of this demo app is Pronto Shop and here is how we can describe this demo app:

Pronto Shop is a fictitious Shopping Cart app that allows users to add and display a list of Products for sale. The Products will be grouped by Categories. The app will also enable them to add and display the list of their Customers. To create a Transaction, a user should add Products to the Cart by clicking on the Products. To complete the Transaction, the user should click on the checkout button. The app should keep a history of Transactions and daily, weekly and monthly sales Report.

From the above description, we can identify the following persistable objects, which has also been highlighted above.

1. Product
2. Customer
3. Transaction
4. Categories
5. Report

These objects will become tables in our database. They are also the business model classes in the app. Here is a screenshot of what the app looks like.

Pronto SQLite Demo App Screenshot

Source Code

The accompanying source code for this tutorial is divided into two parts: beginning and end.

1. Pronto_Shop_SQLite_Beginning — in this source code, the user interface of the demo app have been fully setup so that you can add and display Products, Customers, and Transactions to the demo Pronto Shop app. This project does not include the database yet.
2. Pronto_Shop_SQLite_End — this source code contains the project with SQLite database added. This is what your code should look like if you followed along with the tutorial.

Download Demo APK Download Source Code

Create Android SQLite Database

To create an SQLite database in Android, you create a standard Java class file. To make this class file a database class file you extend it from the SQLiteOpenHelper class. As soon as you extend this class, you will be required to perform three actions which are:

1. Constructor: Since our DatabaseHelper.java class file extended from SQLiteOpenHelper, then it must have a constructor which matches the super class. This constructor accepts a Context object, the name of the database, a Cursor factory and the version of the database.
2. onCreate() — this method is called when the database is created for the first time and this method is where the database tables are created. The parameter for this method is the actual database that you are creating and this database has a method called execSQL() that you use to execute the query that creates the database tables.
3. onUpgrade() — this method is called when the database needs an upgrade. Your database will be flagged for an upgrade when you increment the version number of the database. For example, if you go from database version 1 to version 2, this method will be called. Inside this method is where you write the methods that will implement the changes that you want to make to your database schema. Database upgrade is tricky because by the time you are upgrading your database it could be that people are already using the app and you do not want to wipe out their data. You will see an example of onUpgrade() later.

Here is an example of a blank database class that inherits from SQLiteOpenHelper class.

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "pronto_shop.db";
    private static final int DATABASE_VERSION = 1;



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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("some sql statement to create table");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("some sql statement to do something");
    }
}

Since we now know the parameters that the super class expects, we can generate these parameters from within our class. Because of this, we can now update theDatabaseHelper class constructor to only accept a Context object. Below is an updated onCreate() method that includes a SQL statement to create a Category table. If we run this app now and call this class passing it a Context, it will create a database called pronto_shop.db containing just one table.

public class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context) {

super(context, "pronto_shop.db", null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(
"create table category " +
"(id integer primary key, name text)"
);

}

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

}
}

The challenge with this approach is that SQL is an error-prone language. To reduce the typo errors that results from writing SQL queries we will define our queries using Constants.

Create Database Tables

I have added all the Constants that is needed for this app to the accompanying source code for this project so we can now proceed to add the tables. In database terminology, a table is a collection of related records, you might have a table for employees, a table for customers, another for sales and so on. All these tables would be combined into one database for ease of use.

Schema definition

Before we can go ahead and create the database tables, what kind of database tables are we going to create? how many tables should we create? And what kind of data should be stored in those tables?. The schema will answer these question for us. The schema tells us how to structure our database logically. We need to understand the business logic of the app to create an effective database schema. The model classes can help us understand the business logic of the app.

Model Classes

Each table in our database will represent an entity in our app. Tables usually map to model classes. An example of a model class can be Person.java, and this can represent a real life person in our app. Since a person usually has a name, then our Person.java class will have a property of FirstName to represent the first name of a real life person. Here are the model classes for our demo app.

1. Product.java
2. LineItem.java
3. Customer.java
4. Transactions.java
5. Category.java

Each table will have a column that maps to a field in the model class that it represents. A Column in database terminology is a vertical arrangement of information or data. You give every column a name so that it describes the data stored. Examples of column names could include FirstName, LastName, Price, etc.

Create Customer Table

Here is the class definition of the Customer model class.

public class Customer {
    private long id;
    private String customerName;
    private String emailAddress;
    private String phoneNumber;
    private String profileImagePath;
    private String streetAddress;
    private String streetAddress2;
    private String city;
    private String state;
    private String postalCode;
    private String note;
    private long dateAdded;
    private long dateOfLastTransaction;
}

Now, here is how we can create a Customer table using the Constants we added for ease of readability.

//String to create a customer table
    private static final String CREATE_CUSTOMER_TABLE =
            "CREATE TABLE " + Constants.CUSTOMER_TABLE + "("
                    + Constants.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"

                    + Constants.COLUMN_EMAIL + " TEXT, "
                    + Constants.COLUMN_WEBSITE + " TEXT, "
                    + Constants.COLUMN_IMAGE_PATH + " TEXT, "
                    + Constants.COLUMN_PHONE + " TEXT, "
                    + Constants.COLUMN_STREET1 + " TEXT, "
                    + Constants.COLUMN_STREET2 + " TEXT, "
                    + Constants.COLUMN_CITY + " TEXT, "
                    + Constants.COLUMN_STATE + " TEXT, "
                    + Constants.COLUMN_ZIP + " TEXT, "
                    + Constants.COLUMN_NOTE + " TEXT, "
                    + Constants.COLUMN_DATE_CREATED + " BIGINT, "
                    + Constants.COLUMN_LAST_UPDATED + " BIGINT " + ")";

The above SQL statement will create a database table whose columns have almost a one to one mapping to the Java class it represents. We have to wrap the SQL statements in double quotes, and since we have already defined the literal Strings in Constants, we just referenced them to avoid typos. We use String concatenation “+” to join the String across multiple lines for ease of readability.Here is the actual SQL query statement that will be executed to create the Customer table.

CREATE TABLE customer(_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL, email TEXT,  image_path TEXT, phone TEXT, 
street1 TEXT, street2 TEXT, city TEXT, state TEXT, zip TEXT, note TEXT, 
create_date BIGINT, last_update_date BIGINT )

Notice the difference between SQLite data types and Java primitive data type. A String value in Java code will be saved as SQLite Text. Most of your time building data-driven Android applications will be spent managing the translation between how SQLite represents data on the disk and the object representation in Java code. This is the challenges that both ORMs and Realm database set out to solve.

After adding all the SQL statements to create the database tables, you can now update the onCreate() method of the DatabaseHelper class so that those statements are executed like this:

@Override
public void onCreate(SQLiteDatabase db) {
    try {
        db.execSQL(CREATE_CATEGORY_TABLE);
        db.execSQL(CREATE_CUSTOMER_TABLE);
        db.execSQL(CREATE_TRANSACTION_TABLE);
        db.execSQL(CREATE_PRODUCT_TABLE);
        db.execSQL(CREATE_LINEITEM_TABLE);            
    } catch (SQLException e) {
        Log.d(LOG_TAG, " Error create database " + e.getMessage());
    }
}

The SQL statements to create the Product, LineItem, Category and Transaction tables has been added to the source code accompanying this Android SQLite Database tutorial.

Access the Database

We need to create an instance of the DatabaseHelper class before we can access the database. Open MainActivity.java and two instance variables near the top of the file like this:

private DatabaseHelper mDBHelper;
private SQLiteDatabase mDatabase;

Now in the onCreate method of the same Activity, go ahead and instantiate these two variables like this:

mDBHelper = new DatabaseHelper(this);
mDatabase  = mDBHelper.getWritableDatabase();
mDatabase.close()

Now run the app again, and if you did not get any error message, then the database has been created.

Singleton Pattern

When you open an Android SQLite database for either a read or write access, it is important that you close it when you are done to prevent a memory leak. You should design your app such that each database’s close method has a matching database’s open method. For a data-driven application, this will result in managing database open/close state in multiple places in your app. Using a singleton pattern can help with this. With singleton design pattern, you will only need to open and close your database once.

Currently, our DatabaseHelper class has a public constructor which means that a new instance of this class is created each time we access this class. To implement a singleton pattern, we need to refactor this class so that we have a factory method that returns a single instance of this class. This process will be familiar to you if you have created new Fragments using the newInstance method.Here is what the updated constructor of our DatabaseHelper class will now look like.

private static DatabaseHelper mDatabaseInstance = null;
 private Context mContext;

public static DatabaseHelper newInstance(Context context){    
    if (mDatabaseInstance == null){
        mDatabaseInstance = new DatabaseHelper(context.getApplicationContext());
    }   
    return mDatabaseInstance;
}

private DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    mContext = context;
}

First, the above method checks if the mDatabaseInstance member is null, and if it is null then it will create a new one. Either way, an instance of the DatabaseHelper class is always returned each time this method is called. This pattern ensures that only one instance of the DatabaseHelper class exists. Now in the MainActivity we can now create the database like this.

mDBHelper = DatabaseHelper.newInstance(this);
mDatabase  = mDBHelper.getWritableDatabase();
mDatabase.close();

CRUD Operations with Android SQLite Database

The acronym CRUD stands for Create, Read, Update, and Delete. These represent the most common operations you perform against a database. In Android, CRUD operations are commonly defined as Java methods, and inside these methods, you have the actual SQL statement that is run against the SQLite database because you cannot run Java programming logic directly against an SQLite database.

Here is a classic example of SQL Statement

SELECT * FROM customer;

The query above is a select statement to select all the customers in the customer table. In Android, we will wrap that SQL statement using Java code like this

Cursor cursor = database.rawQuery("SELECT * FROM customer", null);

When the above statement runs, the data set received from the SQL query will be wrapped in an Android Cursor object.

Insert Records to SQLite Database

In this section, we will write the SQL statements that will create all the persistable objects starting from the Customer. The insert() method of Android SQLite database is used to create a row in a table. The term “insert” and “create” are often used interchangeably in this tutorial. The signature of the insert() method is

public long insert (String table, String nullColumnHack, ContentValues values).

The returned value from this method is the id of the row that was just inserted into the table or -1 if the insert failed.

Insert Customer Record

Open the AddCustomerDialogFragment.java in the source code for this Android SQLite Database tutorial and you will find a method near the bottom of the class called saveCustomerToDatabase

private void saveCustomerToDatabase(Customer customer) {
    //Todo - save customer to database
}

We now need to update this method with the code that saves the passed in Customer object to the database. For production apps, you should try to separate data access operations from user interface code. My book Pronto SQLite contain an example of how to achieve this using MVP design pattern and Repository pattern. Here is an updated saveCustomerToDatabase method containing the SQL statement that saves a Customer object to the database.

private void saveCustomerToDatabase(Customer customer) {
        if (mDatabase != null){
            //prepare the transaction information that will be saved to the database
            ContentValues values = new ContentValues();
            values.put(Constants.COLUMN_NAME, customer.getCustomerName());
            values.put(Constants.COLUMN_EMAIL, customer.getEmailAddress());
            values.put(Constants.COLUMN_PHONE, customer.getPhoneNumber());
            values.put(Constants.COLUMN_IMAGE_PATH, customer.getProfileImagePath());
            values.put(Constants.COLUMN_STREET1, customer.getStreetAddress());
            values.put(Constants.COLUMN_STREET2, customer.getStreetAddress2());
            values.put(Constants.COLUMN_CITY, customer.getCity());
            values.put(Constants.COLUMN_STATE, customer.getCity());
            values.put(Constants.COLUMN_ZIP, customer.getPostalCode());
            values.put(Constants.COLUMN_NOTE, customer.getNote());
            values.put(Constants.COLUMN_DATE_CREATED, System.currentTimeMillis());
            values.put(Constants.COLUMN_LAST_UPDATED, System.currentTimeMillis());

            mDatabase.insert(Constants.CUSTOMER_TABLE, null, values);            
        }
    }

Let us go through what the above code block is doing.

1. First, we wrapped the code block in an if statement in case the SQLitedatabase object we are referencing is null.
2. Then we created a bundle to hold the information that will be inserted into the database using ContentValue which is similar to the Bundle object we use with Intents.
3. After we build up all our values then we called the insert method of the database passing it the name of the database table that we want to insert to and the bundle containing the values that we want to save to that table.
4. SQLite will take it from there and if the insert is successful it will return the primary key of the row that is inserted back to you as the result.

Notice that we do not have to set the Id of the row that we are inserting into the database because the id is an auto incremented property meaning that SQLite will automatically increment that property each time we insert a row in that table.

Dealing with Insert Error

What happens if there is a problem with the insert operation? If you want to know if the insert operation failed or what caused the failure then you have to use another database method called insertOrThrow instead of insert .This will cause the insert operation to throw a SQLException if something goes wrong during the insert operation. To watch for insert error, here is what the method should look like instead.

private void saveCustomerToDatabase(Customer customer) {
        if (mDatabase != null){
            //prepare the transaction information that will be saved to the database
            ContentValues values = new ContentValues();
            values.put(Constants.COLUMN_NAME, customer.getCustomerName());
            values.put(Constants.COLUMN_EMAIL, customer.getEmailAddress());
            values.put(Constants.COLUMN_PHONE, customer.getPhoneNumber());
            values.put(Constants.COLUMN_IMAGE_PATH, customer.getProfileImagePath());
            values.put(Constants.COLUMN_STREET1, customer.getStreetAddress());
            values.put(Constants.COLUMN_STREET2, customer.getStreetAddress2());
            values.put(Constants.COLUMN_CITY, customer.getCity());
            values.put(Constants.COLUMN_STATE, customer.getCity());
            values.put(Constants.COLUMN_ZIP, customer.getPostalCode());
            values.put(Constants.COLUMN_NOTE, customer.getNote());
            values.put(Constants.COLUMN_DATE_CREATED, System.currentTimeMillis());
            values.put(Constants.COLUMN_LAST_UPDATED, System.currentTimeMillis());
            try {
                mDatabase.insertOrThrow(Constants.CUSTOMER_TABLE, null, values);
                Log.d(LOG_TAG, "Customer Added");

            } catch (SQLException e) {
                Log.d(LOG_TAG, "Error " + e.getCause() + " " + e.getMessage());
            }
        }
    }

Managing SQLite Relationships

Foreign Key constraint is the primary mechanism used to implement relationships in SQLite. According to the SQLite documentation “SQL foreign key constraints are used to enforce “exists” relationships between tables.” Foreign Key support needs to be enabled for it to work. To enable Foreign Key, override the onConfigureMethod() of our DatabaseHelper.java class like this.

@Override
public void onConfigure(SQLiteDatabase db) {
db.setForeignKeyConstraintsEnabled(true);
}

With this we can now enforce some integrity constraints to our tables, for example, we can delete all LineItems if the Transactions that they belong to get deleted like this:

private static final String CREATE_LINEITEM_TABLE 
"CREATE TABLE " + Constants.LINEITEM_TABLE + "("
+ Constants.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Constants.COLUMN_QUANTITY + " INT NOT NULL, "
+ Constants.COLUMN_PRODUCT_ID + " INTEGER, "
+ Constants.COLUMN_TRANSACTION_ID + " INTEGER, "
+ Constants.COLUMN_DATE_CREATED + " BIGINT, "
+ Constants.COLUMN_LAST_UPDATED + " BIGINT, " 
+ "FOREIGN KEY(product_id) REFERENCES product(_id),"
+ "FOREIGN KEY(transaction_id) REFERENCES transactions(_id) ON DELETE CASCADE" + ")";

Since the LineItem has a dependency on Transaction, the referenced Transaction should exist before an insert into the LineItem table is allowed. Since the Foreign Key relationship is defined in the LineItem this means that the relationship between a Transaction and a LineItem is one-to-many relationship. Also notice the relationship between a LineItem and a Product, that also is a one-to-many relationship, with Product being the one end and LineItem being the many end.

Insert Transaction Records into Database

In the CheckoutFragment.java class file, you will see an empty method called checkout() , we need to implement this method to save Transaction records to the database. This is a demo shopping cart, we do not process payment for the Transaction, we are merely creating a Transaction record with the information on the shopping cart.

Since Transaction has a one-to-many relationship LineItems, we need to implement the checkout() method in two steps. First, we create and save the Transaction record. Then using the Primary Key of that record that is returned as an id, we will iterate through the list of the LineItems for this Transaction and save each with the id of the Transaction. Here is an updated checkout() method.

private void checkout() {
        SalesTransaction transaction = new SalesTransaction();
        transaction.setCustomerId(mCart.getSelectedCustomer().getId());
        transaction.setLineItems(mCart.getShoppingCart());
        transaction.setTaxAmount(tax);
        transaction.setSubTotalAmount(subtotal);
        transaction.setTotalAmount(total);
        transaction.setPaymentType(selectedPaymentType);
        transaction.setPaid(paid);

        List<LineItem> lineItems = mCart.getShoppingCart();
   
        long id = saveTransactionToDatabase(transaction);

        if (id != -1) {
            for (LineItem lineItem: lineItems){
                saveLineItemToDatabase(id, lineItem);
            }
        }

    }

Originally published at Val Okafor.