SQLite Database Tutorial In Android

SQLite Database in Android used to store persistent data. If you want to store some data into local storage then SQLite Database is the most common storage option. It is lightweight database that comes with Android OS.

In Android There are others data storage options also available Like

  • Shared Preference to store small amount of data in form of Key / Value pair.
  • SQLite Database
  • File System

Here we are going to learn how to use SQLite Database in Android application. SQLite is an open source SQL database which store the data into text file on android device. SQLite database in android also support relational database features. In order to access SQLite Database you don’t need to establish any kind of connections like JDBC, ODBC.

1. Overview

In this tutorial we are going to create Student App to learn all the operation related the SQLite Database. The app is very simple and which allow us to insert, update, delete and view student data. We will use 2 screen to perform all this operations. In first screen we fetch full list of student from the student table while using second screen update or add new student record.

Following is the Student app screen shot.

SQLite Database in AndroidNow let’s start by creating new project in Android Studio.

2. Create / Setup 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 CardView library. CardView is used to display Student List inside the Card.

3. Add following code into  colors.xml and strings.xml files.



4. Create following packages named adapter, db, model and ui. The following is the final project structure and files are required.

SQLite Database Android Project Structure

3. Create SQLite Helper Classes

5. In this tutorial we have taken student example to perform all operation. So first we need to create one model class named under model package. contain student information like id, name, grade and result. Further we will used this class to store and retrieve student information from the SQLite database.

6. Now we need to create a class that extends from SQLiteOpenHelper. So create under db package. perform all the database operations like Create, Update, Delete and Read.

Before the proceed let’s understand use of all the methods and table structure of the StudentInfo.

  • onCreate() method will be called only once when application launch first time in android phone. So from this method we will execute sql statement to create Student table.
  • onUpgrade() method will be called when update the application. You need to change the DB_VERSION in order to execute this methods.
  • The StudentInfo table needs four column : _id, name, result and grade.
  • Column _id is declared as Primary Key and Auto Increment which means it is unique key to identify the Students.
  • name contains student name, result is ether pass or fail, grade contains student grade.

So let add the following code into DBHelper Class.

7. Now we need to implement four methods to perform CRUD [Create, Read, Update, Delete] operations with student data. Let’s have look of each methods.

7.1 Insert Student Data

  • Inserting data requires writable instance of the SQLiteDatabase So Create instance using getWritableDatabase().
  • ContentValues() is used to add database values in respective column. So setup all the value with respective column in ContentValues(), Skip _id because it’s auto inserted.
  • Close the database connection after inserting is done.

7.2 Update Student Data.

Updating data also requiring writable access of the SQLiteDatabase. Student data will be updated using _id column.

7.3 Delete Student Data.

To delete student data we also required to declare SQLiteDatabase instance with writable access. The following methods will be Delete specified student record from the database using _id

7.4 Fetch Student Data

  • To read data from the database table requires SQLiteDatabase instance as a read access. For that we can define SQLiteDatabase instance using getReadableDatabase().
  • Following method will fetch all students data from the database table.

So add all above methods into class, The final code of the class looks like this.

4. Create List Adapter

8. Create new layout files under layout folder named student_list_row.xml. This layout file holds single items in list.

9. Now create the new class named under adapter package.

  • This is the Student List adapter and it will show you all the student records from the database.
  • From the list you can Delete or Update particular student data by tapping on Delete or Update button.

5. Create Add / Update Activity

10. Create new layout files under layout folder named activity_add_update.xml. This the layout of Add / Update student data activity.

11. Now create the file under ui package. Using this class we can perform Add / Update operations.

6. Setup Main Activity

12. Finally open and add following code into this. MainActivity will show you all the student records in form of list.

  • Using Floating Action Button you can add new student data in table.
  • Edit Button allows to edit perticular student data from the list.
  • By tapping Delete Button from the list you can delete particular student data.

I hope you like this article. If you have followed this tutorial carefully then application run without any issue. Write us if there is any issue to implement SQLite Database In Android.

Happy Coding 🙂