Android SQLite Database Example – CRUD Operation in SQLite

Hey, friends here is an Android SQLite Database Example. You may already know that we have SQLite database in android that we can use as a local SQL Database for our android application. This Android …

android sqlite database example

Hey, friends here is an Android SQLite Database Example. You may already know that we have SQLite database in android that we can use as a local SQL Database for our android application.

This Android SQLite Database Example will cover Creating Database, Creating Tables, Creating Records, Reading Records, Updating Records and Deleting Records in Android SQLite Database. After going through this post you will be having a complete idea about using SQLite database for your Android Application. So let’s start.

What is SQLite?

SQLite is an SQL Database. I am assuming here that you are familiar with SQL databases. So in SQL database, we store data in tables. The tables are the structure of storing data consisting of rows and columns. We are not going in depth of what is an SQL database and how to work in SQL database. If you are going through this post, then you must know the Basics of SQL.

What is CRUD?

As the heading tells you here, we are going to learn the CRUD operation in SQLite Database.
But what is CRUD? CRUD is nothing but an abbreviation for the basic operations that we perform in any database. And the operations are

  • Create
  • Read
  • Update
  • Delete 

Android SQLite Database Example App Apk

  • Before moving ahead on this tutorial if you want to know what we will be building, you can get the final apk of this tutorial from the link given below.

Android SQLite Database Example App Apk Download

Android SQLite Database Example

Creating a new Android Studio Project

  • As always we will create a new Android Studio Project. For this example, I have a new project named SQLiteCRUDExample.
  • Once your project is loaded, we can start working on it.

The Database Structure

  • The first thing needed is the database structure. We create database structure according to the system. But here we are not building an application, and it is only an example demonstrating the use of SQLite Database. So for this, I will use the following table structure.
Android SQLite Database Example
Database Structure
  • Now we have only a single table, but in real-world scenarios, you will have multiple tables with some complex relationships. Also, remember one thing whenever you create a table create a column named id with int as PRIMARY KEY and AUTOINCREMENT. (If you are confused don’t worry we will see now how do we create tables in database using SQL).

SQL Queries

  • Now let’s see how we can create the above table in our SQLite database.
Creating the Table

Creating a new Record

Reading All Existing Records

Reading Specific Record

Note: * means selecting all the columns, if you want a specific column or multiple columns but not all you can write names of the columns like SELECT name, department.

Updating a Record

Deleting a Record

These are just some simple basics operations, but we can perform many tasks in our database. For this, you need to learn SQL in detail.

Note: SQL Queries are not case sensitive. 

User Interface Design

  • To implement all the above-given queries in our application, we need an Interface from where we can accomplish these tasks. Now, lets think about the screens that we need to make an app that will perform all the above-given queries with user interaction.

Adding a new Employee

  • The first thing is adding a new record to our database, and for this, we can use the following screen.
Creating Record
Creating Record
  • As you can see we have EditText, Button, Spinner and some TextViews. To create the above interface, you can use the following XML code.  You need to paste the following code inside activity_main.xml which is generated by default in any project because this will be the first screen for our application.

  • For the spinner that we used in the above screen, we need to define an Array as the entries for the spinner. So go inside values->strings.xml and modify it as below.

Fetching All the Employees

  • Now after storing employee to the database, we also need to see all the stored employee from the database. For this, we can use a ListView.
  • So, to create a new EmptyActivity in your project named EmployeeActivity. It will create a java file named EmployeeActivity.java and a layout file called activity_employee.xml.
creating an empty activity
Creating an Empty Activity
  • For this screen we can use the following design.

view employee screen

  • This screen contains a only a ListView. The xml for the above screen is below.

  • But here we will not use a simple ListView as we need to display multiple items in the List we will be using a Custom ListView. You can learn about creating custom ListView from this tutorial -> Creating a CustomListview.
  • And for the ListView item we need one more layout as well, so create one more layout file named list_layout_employee.xml and write the following xml code.

  • The above xml code will generate the following layout.

employee list

Updating the Employee

  • Now we will create one more layout file (not a new activity only a layout file because we will edit the employee in an alert dialog) to update the employee data. So create a new layout file named dialog_update_employee.xml.

  • This code will generate the following design.

update employee

  • We don’t need a separate design for deleting the employee as we will be doing it from the button that we created on the List.
  • So we are done with the interface design. Now lets start coding.

Coding the Application

Adding an Employee

  • We will perform this operation inside MainActivity.java so inside this file write the following code.

  • In the above code you see we used a method openOrCreateDatabase().
  • This method takes 3 parameters.
    First parameter is the database name as String, that we need to open. Lets assume we passed some name “xyz” as the first parameter then if there exist a database named “xyz” it will open it, if no database found with the specified name it will create a new database named “xyz” and will open it.
    Second Parameter is the open mode as int. We have some predefined values for it, right now we are using MODE_PRIVATE and it simply means that only this application can access this database.
  • Now we will create a method to create the Employee table. So create a method named createEmployeeTable().

  • Now just call this method just after calling the openOrCreateDatabase() method. In the above method we are just calling the method execSQL() to create our database table. The execSQL() method takes String as a parameter and the String is actually the SQL query that we need to execute.
  • Remember we use execSQL() method only for creating table, inserting or updating records. We cannot use it to retrieve values.
  • Till now we have the database, the table now we need to insert the employee in the table and we need to do this inside addEmployee() method.

  • Now you can test your application for the Create Operation.
android sqlite database example - create operation
Android SQLite Database Example – Create Operation
  • Now lets move towards fetching the stored employees which we call the Read Operation.

Retrieving All Employees

  • We will do this operation inside EmployeeActivity.java. But before fetching the employees, we need two more java classes to display employees in the list. The first class is a regular java class to store the employee as an object and a CustomAdapter class for the ListView.
Employee Model
  • Create a simple java class named Employee.java and write the following code.

  • Now we will create a custom adapter class for our ListView.
Custom ListView Adapter
  • Create a java class named EmployeeAdapter and write the following code.

  • Now we can fetch the employees to display them in the ListView.
Retrieving and Displaying in ListView
  • Come inside EmployeeActivity.java and write the following code.

  • Now try running the application to see the Read Operation.
android sqlite database example - read operation
Android SQLite Database Example – Read Operation

Updating an Employee

  • We have done with Create and Read, now lets do the Update Operation. We will perform it inside the EmployeeAdapter class.
  • For the Update operation we need two new methods inside the EmployeeAdapter.java. One is to update the employee and the second one is to reload the employee from database to show updated information.
  • So inside the class you need to make below given two methods named updateEmployee() and reloadEmployeesFromDatabase() method.

  • The update operation is same as the insert operation, we changed the query only to update from insert. If you are having any confusion understanding the codes please comment and I will help you.
  • Now we need to modify the getView() method of EmployeeAdapter as below.

  • Now you can test the Update Operation.
android sqlite database example - update operation
Android SQLite Database Example – Update Operation

Deleting an Employee

  • We have done with Create, Read and Update. Now the last operation is the Delete Operation. So lets do this to complete the CRUD for this tutorial.
  • We will do the deletion also in the EmployeeAdapter class. The operation is same we just need to change the SQL String to perform delete operation the rest part is exactly the same.
  • So to make the delete operation again modify the getView() of EmployeeAdapter as below.

  • So the operation is complete and now you can test all the operation in your application and you should see something like this.
Android SQLite Database Example
Android SQLite Database Example

Android SQLite Database Example Source Code

  • If you are facing any troubles building, the application then don’t worry here is my source code for you.

Android SQLite Database Example Source Code Download

So that’s all for this Android SQLite Database Example friends. I hope you liked it. For any confusions or questions just leave your comments below. Please write your real name in the comment box as I don’t approve comments with spam names.

You can share this post to help us. Thank You 🙂

Hi, my name is Belal Khan and I am a Google Developers Expert (GDE) for Android. The passion of teaching made me create this blog. If you are an Android Developer, or you are learning about Android Development, then I can help you a lot with Simplified Coding.

Expand Your Knowledge: Next Tutorial Picks

0 0 votes
Article Rating
Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x