Android SQLite Database Tutorial

In Android, there are several ways to store persistent data. SQLite is one way of storing app data. It is very lightweight database that comes with Android OS. In Android, integrating SQLite is a tedious task as it needs writing lot of boilerplate code to store simple data. Consider SQLite when your app needs to store simple data objects. Alternatively you can consider Room Persistence Library for better APIs and easier integration.

In this article we are going to learn basics of SQLite database with a realtime example of Notes App.

1. The Notes App

We are going to create a simple Notes App with SQLite as database storage. The app will be very minimal and will have only one screen to manage the notes.

Below are the screenshots of the app.

Now let’s start by creating new project in Android Studio.

2. Creating New Project

1. Create a new project in Android Studio from File ⇒ New Project and select Basic Activity from the templates.

2. Open build.gradle under app directory and add RecyclerView dependency. The RecyclerView will be used to display the Notes in list manner.

3. Add the below resources to colors.xmldimens.xml and strings.xml

4. Quickly create few packages named databasedatabase/modelutils and view. Below is the final project structure and files we gonna need.

2.1 Writing SQLite Helper Class

We need to create a class that extends from SQLiteOpenHelper. This class perform CRUD operations (Create, Read, Update and Delete) on the database.

We also need a model class to create Note objects to manage the notes easily.

5. Under database/model package, create a class named In this class we define the SQLite table namecolumn names and create table SQL query along with getter / setter methods.

  • The notes table needs three columns i.e idnote and timestamp.
  • Column id is defined as Primary Key and Auto Increment which means each note will be uniquely identified by its id.
  • Column note stores the actual note text.
  • Column timestamp stores the date and time of the note that is created.

6. Under database package, create a class named and extend the class from SQLiteOpenHelper. This class holds the database related methods to perform the CRUD operations.

  • onCreate() will be called only once when the app is installed. In this method, we execute the create table sql statements to create necessary tables.
  • onUpgrade() called when an update is released. You need to modify the DATABASE_VERSION in order to execute this method. You have to take care of database migrations here without loosing the older data if necessary. For now, we just drop the older tables and recreate them again.

Now we’ll see the methods required to store or retrieve the notes. Add the following methods to same class.

a. Inserting Note

Inserting data requires getting writable instance (getReadableDatabase()) on database. Below, we are inserting new note in database.

  • ContentValues() is used to define the column name and its data to be stored. Here, we are just setting the note value only ignoring id and timestamp as these two will be inserted automatically.
  • Every time the database connection has to be closed once you are done with database access. Calling db.close() closes the connection.
  • Once the note is inserted, the id of newly inserted note will be returned.

b. Reading Notes

Reading data requires only read access (getReadableDatabase()) on the database.

  • getNote() takes already existed note id and fetches the note object.
  • getAllNotes() fetches all the notes in descending order by timestamp.
  • getNotesCount() returns the count of notes stored in database.

c. Updating Note

Updating data again requires writable access. Below the note is updated by its id.

d. Deleting Note

Deleting data also requires writable access. Below method deletes a note by finding its id.

After adding all the methods, the class should be like this.

7. Under utils package, create two classes named and

  • RecyclerTouchListener class adds touch event to RecyclerView row.
  • MyDividerItemDecoration class adds divider line between rows.

3. Adding Notes UI

Now we have the database helper class ready. Let’s quickly build the main interface and integrate it with the database.

First we need an adapter to display the notes in list manner. For this, we need a layout file and Adapter class.

8. Create new xml layout named note_list_row.xml. This layout holds the design of single note item in the list.

3.1 Adding Create / Edit Note Dialog

If you observe the app design, a note is created or updated using a Dialog. So, we need to create a custom layout with EditText input and inflate it in AlertDialog.

10. Create another layout named note_dialog.xml

11. Open the layout files of main activity (activity_main.xml and content_main.xml) and add RecyclerViewwidget. I am also changing the icon of FAB here.

12. Finally open and do the below changes.

  • showNoteDialog() open the alert dialog to create new note. This dialog will be shown by tapping FAB.
  • createNote() inserts new note in database and adds the newly inserted note in RecyclerView list.
  • showActionsDialog() shows a dialog with Edit and Delete options. This dialog can be invoked by long pressing the note in the list.
  • Selecting Edit, opens the update note dialog with already existed note text. You can modify the note text and update it in database by calling updateNote() method.
  • deleteNote() deletes a note from database. The deleted note is again removed from list by calling notifyItemRemoved() on adapter.
  • toggleEmptyNotes() toggles the visibility of notes and empty note view depending on the count (db.getNotesCount() > 0) of notes.

If you have followed the article carefully, you can see the app running very smoothly as shown in the video demo.

on Twitter

on Facebook

on Google+

About the author


At E-Code Geeks we help people who are trying to create their own website, with the help of our easy-to-follow tutorial videos.

We created E-Code Geeks as a place where anyone can quickly and easily learn about web design – simply, by watching a video.

And also we updated life styles articles like Food, Traveling, Health for better knowledge.

It’s much simpler and it’s fast!

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *