Shares

Ultimate Guide to Android SQLite Database

Shares

 

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.

Java class definition for Product class.

 

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

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.

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.

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.

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

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.

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:

 

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:

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

 

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.

 

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.

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

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

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

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.

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.

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.

 

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:

 

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.

To complete the checkout, we need to add two new methods to save the Transaction and the LineItem. Here is the method that saves the Transactions:

The method that saves LineItem and Product has been added to the source code accompanying this Android SQLite Database tutorial.

Read Records from SQLite Database

Databases will have limited use case if they only allow a one-way method of inserting rows without a corresponding method of retrieving them. The ability to efficiently read data from the database is an essential skill to have, and in this section, we will dive into how to read data from SQLite in Android. There is no SQLite database method called read()  so you will have to write manual SQL Queries for each read method that you want to create. To see the full list of available methods that Android SQLiteDatabase exposes click here.

Read Get Specific Customer Record from Database

To get a specific Customer from the database implies that the Customer already exists in the database and that we have a piece of information that uniquely identifies the customer. In our case, that unique information (or Unique constraint) is the Id. A unique constraint according to the official SQLite documentation is similar to a PRIMARY KEY constraint, except that a single table may have any number of UNIQUE constraints.
Using the Id we want to search a particular table and return the row where the id matches the Id we are given. This single row is  returned in a Cursor, here is content of a method that retrieves a single record of a Customer from the database.

Here is what we did in the above code, we created and executed a query that searches the Customer table for a row whose id corresponds to the id that we are passed in. This query returns a Cursor. We need to take another extra step since we want to return a Customer object out of this method and not a Cursor, we need to create another method that takes a Cursor and returns a Customer object for us. This method is called getCustomerFromCursor and it is already created for you in the Customer class. Here is what this method looks like:

 

Let us step through what the above code is doing

  1. The Cursor class is not just a wrapper around rows of the dataset; it has useful methods to retrieve the contents of those rows of data.
  2. Remember that any SQLite column can contain any type of data and the Cursor provides methods to convert them to specific Java data types.
  3. For example, if you want to get a String from a column then you can use cursor.getString() to unbox that column content into a String, and so on.
  4. These methods getString(), getLong(), etc. accepts an integer which is the index of the column that you want to unbox.
  5. However, instead of memorizing the indexes of your column, the Cursor object also have a method called getColumnIndex() that accepts a name that you can use to return the index of your columns.
  6. Here again, we took advantage of the fact that we defined our column names in Constants to supply a friendly version of our columns.
  7. Then we can use the retrieved information to re-create the Customer object that was saved in that row which we then return.
  8. We made the getCustomerFromCursor() method static so we can have access to it without creating a new instance of Customer.

We follow the same approach to retrieve all of our queries to their respective Java classes. In the source code, you will find a method that converts a Cursor to a Java object for the Transactions, Product, Category and LineItem classes. Now we want to take a look on how we can return multiple objects from the database.

Get List of Customers from Database

To display the list of Products and Customers we need to fetch a List of the respective objects from the database. Here is the method to get a list of Customers. Update the getCustomers() method in CustomerSQLiteRepository() method to look like this:

Let us walk through what the above code is doing.

  1. First, we initialized an empty ArrayList<Customers>.
  2. Then we created a String named selectQuery whose value is raw SQL query statement to retrieve all the rows in the Customer table.
  3. This query could be any SQL-compliant query statement; your query statement will get more complex as your SQL language skills grow.
  4. Then we get an instance of the actual database and then optionally lock the database for read.
  5. Then we run the query statement that we created, by calling the rawQuery() method of SQLiteDatabase and this method accepts the parameter of Surprise! Surprise! – Raw query. Therefore, if there is a syntax error in your query statement then either you get an error message or nothing will be returned or the wrong data set will be returned.
  6. The result of the query is returned as a Cursor object which we store in a variable called cursor.
  7. At this point SQLiteDatabase is done, what we do with the Cursor object is up to us. We have indicated that we want to return an ArrayList<Customers> but what is contained in the Cursor is a list of rows of Customer table data,
  8. We need to use the getCustomerFromCursor() method to retrieve all those rows and convert each of them into a Customer.java object that we can add to our ArrayList and return.

This completes the section on CRUD operation with Android SQLite database. The rest of the CRUD methods are included with accompanying source code.

Upgrade Android SQLite Database

At some point, you will need to modify your database schema to support new features of your app. The simplest way to accomplish this when your app is under development is to drop the database and then re-create it with the new schema applied. To do this, you just increment the version of the database from say 1 to 2. And next time you run the app the upgrade method of your SQLiteOpenHelper class will be called. Here is how you can override your upgrade method to drop and re-create the database.

 

 

 

 


Also published on Medium.

About the Author valokafor

I am a Software Engineer with expertise in Android Development. I am available for Android development projects.

follow me on:

Leave a Comment:

8 comments
Add Your Reply