What’s a SQLite database?

It’s a popular, open source database engine found on many small devices.

It stores data in a file on your device. The data is secure and only accessible by the app that created it.

You can store different data types like strings, long, float, etc. without any problems.

You can also share the data with other apps if you choose to by using a Content Provider.

Working with the database

I’ll explain how to use a SQLite database with the help of an example.

I want to save the names, email addresses and phone numbers of my friends. Let’s see how it’s done.

Do it in the background

Creating, opening and using the database takes time.

This could cause the app to hang if you use the database on the main thread.

Asynchronously

The best practice is to do all database transactions off the main thread. There are two commonly used ways of doing this:

• Using an AsyncTask
• Using an IntentService

I won’t be discussing these in this tutorial but note that you should do all database activity off the main thread.

Database design

Before you start to create your database, you should:

• Decide on the data that you want to save and how you want it organised
• Decide on your database name, table name and names of all the column titles
• You should also include a column for the unique row index. Although it’s only required if you’re going to use the database with a Content Provider

Keep it constant

Make them all constants and put them in a separate class.

If you need to make changes later, it’s easy to do that here.

Quick tip

Don’t store files in the database. Rather store the file’s address.

The Contract class

The contract class contains the database constants, such as the:

• database name – myContacts_database
• table name – MyContacts_table
• version number – 1. You need to change the version number whenever you make changes to the database structure, like adding columns for example
• column titles – name, email, phone

Our database constants: The KEY_ID is the unique index that is needed if you’re going to use the database with a Content Provider

The SQLiteOpenHelper class

We use this class to efficiently create, open and upgrade a database.

It contains two methods:

• onCreate() – used to create a new database. This is done once only
• onUpgrade() – used to upgrade the database if the required version number is later than the existing version

This SQL string used to create the database for the first time

Using this onUpgrade() method deletes the existing database and creates a new one. All data is lost so you may want to first backup the data

The SQLiteDatabase class

We use this class’s methods to query and transact with the database

Opening a database

When we try and open a database, the Open Helper first checks to see if the database exists. If it does, then the version number is checked. If it’s the latest version then it opens the database.

The SQLite Open Helper caches the opened database. This makes any future use of the database pretty fast as an instance of it exists in memory.

It is best practice to open the database just before you are about to use it.

Creating a database

What if the database does not exist or our version is later than the existing version?

When you need to access the database, construct a subclass of SQLiteOpenHelper, MyDbOpenHelper. Then create a helper object, dbHelper and use this to create or open the database

When you call getWriteableDatabase(), it creates the database if it doesn’t exist and if the existing database’s version number is outdated, then the database is deleted and a new one is created.

Which do we use, getWriteableDatabase() or getReadableDatabase()

If you’re going to write to the database, use getWriteableDatabase() to open the database. Sometimes this can fail so it’s wise to fall back on trying to open the database using getReadableDatabase(). In most cases, it will provide the cached writeable database or failing this, a readable database.

Closing a database

Once opened, an instance of the database exists in memory so it makes sense not to close it until you are sure that you will no longer be using it.

You can close it in the Activity’s onDestroy() method, for example.

Transactions and queries

You’ll need an instance of the database to do any transactions or queries.

You can add data, update data, delete data and search for data using these SQLiteDatabase methods:

• Insert() – to put data into the database
• delete() – to delete data
• query() – to search for data
• update() – to make changes to existing data

Here’s the code for getting a database object:

First get the SQLiteOpenHelper object and then open the database. It is best practice to do this for each query or transaction

Querying the database

Doing a search? Use the query() method. It returns a Cursor object.

The Cursor is an interface to the set of data returned by the query. It gives you access to that data.

The query returns a Cursor

The query() method’s parameters:

• the database table name
• projection – a String array of the column titles that you would like returned
• selection – this is the SQL WHERE clause that filters the rows to return
• selectionArguments – a String array of arguments for the WHERE clause
• groupBy – a filter for grouping the rows
• having – a filter for which row groups to include
• orderBy – how the rows should be ordered

The query parameters

Note the following:

• I want to get the values from all the columns (KEY_ID,NAME,EMAIL,PHONE). I could have passed NULL here as this would return all columns by default
• I’m looking for all records with a NAME value of Reginald. Passing NULL for the WHERE clause and argument would return all rows by default

Extract values from cursor

To get the values out of the cursor:

• position the cursor at the correct row
• use the get methods to get the stored value

Here’s an example:

Using a While loop to iterate through the cursor to get the values

moveToFirst() positions the cursor at the first record.

The While loop will continue until the cursor passes the last record (when isAfterLast() is true).

The getString() method is passed the column index. These indexes are determined by the position of the columns in our projection parameter.

Very important

You must close the cursor when you have finished using it to avoid memory leaks.

Adding a row of data

Adding a row is easy. Simply put all your data into a ContentValues object. Then pass the ContentValues object to the insert() method as a parameter.

ContentValues objects stores the data in key/value pairs

Insert the data into the database using the insert() method.

You need three parameters:

• the database table name
• the nullColumnHack
• the data in the form of a ContentValues object

Pass the ContentValues object to the insert() method. The data is inserted into the database and the row ID returned

NullColumnHack

When inserting a new row. Always specify at least one column and corresponding value.

If you have to add an empty row then you must pass the name of the column to set to null as the second parameter.

An exception will be thrown if the nullColumnHack is set to null and you add an empty row.

Set this parameter to NULL as long as you’re adding data to any of the columns.

Updating rows

It’s easy to update rows in the database.

As with all queries or transactions, you must first get a database object.

Then simply put the new data into a ContentValues object and use the WHERE clause to identify the rows that you want to update.

In this example, I want to change the name from Reginald to Roland.

I place the new name into the ContentValues object. Then I set up the WHERE clause:

This will change all the names matching Reginald to Roland

And put it all together in the update() method:

Once updated, it returns the number of rows updated

Important

If you have an open cursor, then you need to run a new query so that it reflects the changes you made to the database by updating it.

Deleting rows

Use the delete() method to delete rows.

Get a database object.

Use the WHERE clause to identify which row or rows you want to delete then call delete():

Pass the table name, WHERE clause and its arguments to delete a row. It returns the number of records deleted

Here’s an example of setting up the WHERE clause and its arguments to delete a specific row:

Here we target a specific row by passing the row ID as the WHERE argument

Important

If you have an open cursor, then you need to run a new query so that it reflects the changes you made to the database by updating it.

Where can I find the database file?

You’ll find the database file in the /data/data//databases folder of your device but you won’t see it with a file browser unless the device is rooted.

You will be able to access it if you’re using the emulator and Eclipse for example.

You can of course copy the file over to another folder that you have access to.

The Tutorial app

Here’s a quick overview of the tutorial app which you can download:

Pressing the Add Data button loads 5 records from a String array into the database. The row ID’s are displayed in the LogCat. Pressing Read Data displays all the rows in a list

Pressing the MainActivity Read Data button starts a list activity and displays the data read from the database in a list

These buttons are displayed in the activity started by pressing the More button in the MainActivity

Pressing each of these buttons first loads a row of data in the database. This is purely for demonstration purposes to ensure that there is at least one row of data in the database.

• Delete a record – deletes a single record identified by its row id. The number of rows deleted is displayed in the LogCat
• Find a record – queries the database to find all rows where the NAME column value matches a given name. It returns a cursor which we loop through, displaying the rows in the LogCat
• Update a record – updates all rows where the NAME column value matches a given name. The number of rows updated is displayed in the LogCat

I hope that you have found this tutorial helpful.

Have a look at the series of tutorials on Content Providers. Here's the first in the series, Android Content Providers.

You may also be interested in our Firebase tutorial, Using a Firebase Realtime Database in your app

Also, check out Realm databases for Android beginners where we show you how to use Realm to save your data

This tutorial was created using . You can download the project files here  

Are you using Eclipse or another IDE? Here's how you can use this project's Android Studio files.