CRUD Operations on SQLite database in android

Hey Technoz! Many times, We must have seen many apps which saves data in device memory. Similarly, it uses the SQLite database for CRUD operations and to store the data. So, we will study how to use SQLite database to store the data in the form of tables – rows and columns in this CRUD Operations on SQLite database in android tutorial.

What is SQLite?

The SQLite database is an in-built, open source and lightweight database in android. Certainly, it stores data in a form of text file on a device. When there is requirement of storing large amount of data, SQLite is more preferable way than any other data structure like Shared Preferences.

What is CRUD?

The CRUD is nothing but an acronym of:

  • C: Create
  • R: Read
  • U: Update
  • D: Delete

Obviously, There is no need of any special software or connectivity for performing CRUD operations in SQLite database. However, This tutorial assumes you have some basic knowledge of SQL with create, insert, update and delete queries. So lets move towards CRUD Operations on SQLite database in android.

CRUD Operations on SQLite database in android

First of all, Lets start by creating a table ‘Employee’ with 4 fields as id (AUTO_INCREMENT), name, email and cname (company name). As a result, We will add, view, update and delete records from the table.

Defining Constants

Besides, create a class Constants as follows to define all constants like table name and column names.

Adding Helping Classes

Above all, the SQLiteOpenHelper is a class which helps to handle database operations. Now, we will create a new class and extend it from SQLiteOpenHelper class. Of course, it needs to implement two methods as onCreate and onUpgrade and one constructor. The constructor has database name and version name defined statically. Certainly, Each time the database is modified, the version name is updated. The onUpgrade method will drop the table if it already exists. Lets code the file as follows.

Defining Required Functions

Now, as we have to perform CRUD operations in SQLite database, we will make a collection of respective methods in the following class file

Furthermore, the above class contains a constructor in which the object of class DBHelper is created. Probably, we have created an insertdata method for insert operation. Because it is a paramaterized method, it takes arguments from user and insert() is a predefined method in SQLiteDatabase class. Before performing any operation, we need to open database connection. The getWritableDatabase() method does this task. A ContentValues class put the values in Global constants. As a result, status of operation is held in retid variable and returned to calling function.

Similarly, viewdata() method is used to retrieve data from database. The Cursor class is used to fetch values as shown above. The StringBuffer() method is used to send the fetched data to the calling function.

Even more, an update task is completed by edit() function. update() method is predefined in class SQLiteDatabase.

Finally, the delete task is achieved by delete method. Consequently, we again open database connection and use predefined delete() method to accomplish a task.

Creating Activity

So far we have made java classes, now we create an activity to perform all those operations. Similarly, create new activity as follows. Furthermore, for CRUD operations in SQLite database, it takes the input from user, passing parameters to functions and showing data.

Designing the Layouts

So, paste the following code in file content_main.xml

Code following file as activity_main.xml

See the Output Screens

As a result, we completed the coding part for performing CRUD operations in SQLite database. Hence, you can test the code on emulator or on a real device. Even more, see the view below.


Data Insert Operation

Data Update Operation

Record Delete Operation

Finding the SQLite Database File

Thus, we have successfully completed the CRUD Operations on SQLite database in android. If you want to see the records in table form, follow the following steps: (Assuming app is running on virtual device)

  1. Open Device File Explorer on right side in android studio.
  2. Go to path: data => data => [app-package-name] => databases => [database-name]
  3. Take the file as save with .db extension
  4. You will need ‘DB Browser for SQLite’ (Download_link) to open this file.
  5. Drag and drop the file to the window to see the database in table form.

As a result, see the screenshot below for clarification.

Device File Explorer

DB Browser showing database details

Download Source Code

Rather, you can always download source code files of CRUD operations from the below link.

Even more, if you have any question implementing the above tutorial, feel free to comment below. Please subscribe to our newsletter to catch such knowledgeable tutorials. 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.