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 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 🙂

57 thoughts on “Android SQLite Database Example – CRUD Operation in SQLite”

  1. Your way of guiding is amazing. I really learnt making db so easily. I tried many sites but did not understand so quickly. One question, how to save values for checkboxes and radio buttons in db. If you have other such tutorial pls advice. Otherwise request you to guide.

    Reply
  2. Just awesome tutorial . I have no words to appreciate your work. I am getting an error in my log cat can you please please sort out this

    Reply
  3. sir can u tell where to paste ” private void createEmployeeTable() ” function in code …or pls send full code on my mail

    Reply
  4. Hi,

    After updating the control goes to reloadDataFromDatabase(), from this the control should go to getView() if I am correct. But there is no call to that method. Does the control defaultly goes to getView()? Please explain the flow and what is the use of notifyDataSetChanged()?

    Thank you.

    Reply
  5. I am getting this error: SQLiteException: no such table
    the line of code –
    mDatabase.execSQL(insertSQL, new String[]{name, dept, joiningDate, salary});
    I hope their is error in creating table.
    Please suggest to fix this error.

    Reply
  6. I get the following error:

    Error:(57, 19) error: constructor EmployeeAdapter in class EmployeeAdapter cannot be applied to given types;
    required: Context,int,List,SQLiteDatabase
    found: EmployeeActivity,int,List
    reason: actual and formal argument lists differ in length

    Reply
    • add mDatabase to the parameters like below:

      adapter = new EmployeeAdapter(this, R.layout.list_layout_employee, employeeList,mDatabase);

      Reply
  7. Hi, I would like to create Depertment table. Two tables (employee & depertment). How to use one to many relationship (PK,FK) between two tables? One Depart have Many Employees. And then, if users delete depart, also delete relative employee. How to do that? 🙁

    Reply
  8. coming error on error: SQLiteException: no such table
    the line of code –
    mDatabase.execSQL(insertSQL, new String[]{name, dept, joiningDate, salary});
    this line please send me link of source code… where to i get solve this
    …..

    Reply
  9. Hello sir,your way of guiding is good.But sir i have some problem in my code,i taken fragment instead of EmployeeActivity that was in ur code .i got error in this line,

    adapter=new CustomPatientListView(this,R.layout.listview_patient,patientList));
    In the word of “this” i got error ,so what i have to write in the place “this”.
    please give me solution sir…

    Reply
  10. Hello,
    thanks for your tutorials. They are quite easy to understand and it is a huge job.
    I am new in Android and Java and I spent months to learn about this.
    Sorry, but I am a little bit upset reading that you only showing basic queries to access SQLite. I understand this is a first approach but I would be happier if you had mentioned other way to to query Android database (as ContentProvider for instance).
    Just to say, when I began on Android I spent a lot of time learning Android SQLite basics until I discovered Content providers that changed my life :-)). You do not need to build CRUD for each table, just have to add it to the content provider. Content providers are widely used in android.
    Hope this tutorial will get a bother with ContentProvider!
    Thanks.

    Reply
  11. Thanks for the this post sir… I have a problem to display the content when i click on view employee it stopped working …how to solve it

    Reply
  12. Good work. Thank you. Why not display the query result as a simple string? and show that in a simple EditView? Will it work? I will try your code and let you know. Thanks again.

    Reply
  13. hope someday i will master these concepts. Thank you so much for helping people like me who are looking for help. It was a real help. Thank you again.

    Reply
  14. Hello Sir,
    Thank you for teaching me a great lesson freely, without a penny.

    I have grasped the whole thing very well, I able to create the DB and start the app and the GUIs have come well. However, when I tried to insert data the app crushes. I tried a lot of means. But could not resolve the issue. Do you have some advice for me, sir?

    Thank You

    Reply
  15. package com.sliit.model2018;

    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.view.View;

    public class DBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = “userInfo”;
    private static final String createTable = “CREATE TABLE ” + UserProfile.Users.TABLE_NAME + ” (” + UserProfile.Users._ID + ” INTEGER PRIMARY KEY,” + UserProfile.Users.COLUMN_NAME_USERNAME + ” TEXT NOT NULL ,” + UserProfile.Users.COLUMN_NAME_DOB + ” TEXT ,” + UserProfile.Users.COLUMN_NAME_PWD + ” TEXT ,” + UserProfile.Users.COLUMN_NAME_GENDER + ” TEXT )”;
    private static final String deleteTable = “DROP TABLE IF EXISTS ” + UserProfile.Users.TABLE_NAME;

    public DBHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    SQLiteDatabase db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    db.execSQL(createTable);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL(deleteTable);
    onCreate(db);

    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    onUpgrade(db, oldVersion, newVersion);
    }

    public Boolean addInfo(String uName, String dob, String pwd, String gender) {
    long newRowId = 0;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(UserProfile.Users.COLUMN_NAME_USERNAME, uName);
    values.put(UserProfile.Users.COLUMN_NAME_DOB, dob);
    values.put(UserProfile.Users.COLUMN_NAME_PWD, pwd);
    values.put(UserProfile.Users.COLUMN_NAME_GENDER, gender);

    newRowId = db.insert(UserProfile.Users.TABLE_NAME, null, values);

    if (newRowId == -1) {
    return false;
    } else {
    return true;
    }

    }

    public Boolean updateInfor( String uName, String dob, String pwd, String gender) {

    ContentValues values = new ContentValues();
    SQLiteDatabase db = this.getWritableDatabase();
    values.put(UserProfile.Users.COLUMN_NAME_USERNAME, uName);
    values.put(UserProfile.Users.COLUMN_NAME_DOB, dob);
    values.put(UserProfile.Users.COLUMN_NAME_PWD, pwd);
    values.put(UserProfile.Users.COLUMN_NAME_GENDER, gender);

    String condition = UserProfile.Users.COLUMN_NAME_USERNAME + ” LIKE ?”;
    String[] condition_value = {uName};

    int result = db.update(UserProfile.Users.TABLE_NAME, values, condition, condition_value);

    if (result == -1) {
    return false;
    } else {
    return true;
    }

    }

    public Cursor readAllInfor() {

    SQLiteDatabase db = getWritableDatabase();
    Cursor data = db.rawQuery(“SELECT * FROM ” + UserProfile.Users.TABLE_NAME, null);
    return data;

    }

    public Cursor readAllInfor(String uName) {

    SQLiteDatabase db = getWritableDatabase();
    Cursor data = db.rawQuery(“SELECT * FROM ” + UserProfile.Users.TABLE_NAME + ” WHERE ” + UserProfile.Users.COLUMN_NAME_USERNAME+ ” = ‘” + uName+”‘”, null);
    return data;

    }

    public Integer deleteInfo(String uName) {

    SQLiteDatabase db = getWritableDatabase();
    int result = db.delete(UserProfile.Users.TABLE_NAME, UserProfile.Users.COLUMN_NAME_USERNAME + ” LIKE ?”, new String[]{uName});
    return result;

    }

    }

    ………………..profile management………
    package com.sliit.model2018;

    import android.content.Context;
    import android.content.Intent;
    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.view.View;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.RadioButton;
    import android.widget.RadioGroup;
    import android.widget.Toast;

    public class ProfileManagement extends AppCompatActivity {

    private Button registerBtn;
    private EditText getUName;
    private EditText getDob;
    private EditText getPwd;
    private int compareId;
    private RadioGroup getGender;
    private RadioButton radioBtnGender;
    private DBHandler dbHandler;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_profile_management);
    registerBtn = (Button) findViewById(R.id.register_btn_two);
    dbHandler = new DBHandler(this);
    registerBtn.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {
    getUName = (EditText) findViewById(R.id.user_Name_txt);
    getDob = (EditText) findViewById(R.id.dob_txt);
    getPwd = (EditText) findViewById(R.id.pwd_txt);
    getGender = (RadioGroup) findViewById(R.id.gender_radio_group);
    compareId =getGender.getCheckedRadioButtonId();
    radioBtnGender = (RadioButton) findViewById(compareId);

    Boolean id = dbHandler.addInfo(getUName.getText().toString(),getDob.getText().toString(),getPwd.getText().toString(),radioBtnGender.getText().toString());
    // Boolean id = dbHandler.addInfo(“Tharindu”,”94/09/30″,”Secret”,”male”);

    if(id==false)
    {

    Toast.makeText(ProfileManagement.this, “The Database is empty :(.”, Toast.LENGTH_LONG).show();

    } else
    {
    Toast.makeText(ProfileManagement.this, “Successfully added..!”, Toast.LENGTH_LONG).show();
    }

    }
    });

    }
    }

    ……………………………Edit profile…………………………..

    package com.sliit.model2018;

    import android.database.Cursor;
    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.view.View;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.RadioButton;
    import android.widget.RadioGroup;
    import android.widget.TextView;
    import android.widget.Toast;

    public class EditProfile extends AppCompatActivity {

    Button search;
    Button edit;
    Button delete;
    EditText uName;
    EditText dob;
    EditText pwd;
    RadioButton male;
    RadioButton female;
    DBHandler db ;
    Cursor data;
    String x;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_edit_profile);

    uName = (EditText) findViewById(R.id.search_u_name);
    search = (Button) findViewById(R.id.search_btn);
    dob = (EditText) findViewById(R.id.get_dob);
    pwd = (EditText) findViewById(R.id.get_pwd);
    male = (RadioButton) findViewById(R.id.edit_male_radio_btn);
    female = (RadioButton) findViewById(R.id.edit_female_radio_btn);
    edit = (Button) findViewById(R.id.edit_btn);
    delete = (Button) findViewById(R.id.delete_btn);

    db = new DBHandler(this);

    search.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {

    data = db.readAllInfor(uName.getText().toString());

    while (data.moveToNext()) {
    dob.setText(data.getString(2));
    pwd.setText(data.getString(3));
    //x = data.getString(4);
    if(“male”.equals(data.getString(4)) || “Male”.equals(data.getString(4))) {

    male.setChecked(true);

    }else {

    female.setChecked(true);

    }
    }

    }
    });

    edit.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {

    Boolean result = db.updateInfor(uName.getText().toString(),dob.getText().toString(),pwd.getText().toString(),x);

    if(result==false)
    {

    Toast.makeText(EditProfile.this, “Update was failed..! :(.”, Toast.LENGTH_LONG).show();

    } else
    {
    Toast.makeText(EditProfile.this, “Successfully updated..!”, Toast.LENGTH_LONG).show();
    }

    }
    });

    delete.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {

    int val = db.deleteInfo(uName.getText().toString());

    if(val != 1)
    {

    Toast.makeText(EditProfile.this, “Not updated..! :(“, Toast.LENGTH_LONG).show();

    } else
    {
    Toast.makeText(EditProfile.this, “Successfully updated..!”, Toast.LENGTH_LONG).show();
    }

    }
    });

    }
    }

    …………………..home……………………..

    package com.sliit.model2018;

    import android.content.Intent;
    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.view.View;
    import android.widget.Button;

    public class Home extends AppCompatActivity {

    private Button updateProfileBtn;
    private Button registerBtn;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_home);

    registerBtn = (Button) findViewById(R.id.register_btn);

    registerBtn.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {
    Intent intent = new Intent(Home.this,ProfileManagement.class);
    startActivity(intent);
    }
    });

    updateProfileBtn = (Button) findViewById(R.id.update_profile_btn);

    updateProfileBtn.setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {

    Intent intent = new Intent(Home.this,EditProfile.class);
    startActivity(intent);

    }

    });

    }
    }

    Reply
  16. Bro i am not able to find below method anywhere in the code. Can you/anyone tell me where it is.

    private void createEmployeeTable() {
    mDatabase.execSQL(
    “CREATE TABLE IF NOT EXISTS employees (\n” +
    ” id int NOT NULL CONSTRAINT employees_pk PRIMARY KEY,\n” +
    ” name varchar(200) NOT NULL,\n” +
    ” department varchar(200) NOT NULL,\n” +
    ” joiningdate datetime NOT NULL,\n” +
    ” salary double NOT NULL\n” +
    “);”
    );
    }

    Reply
    • got it!!!! thanks anyways. downloaded the source code.

      Hey Billal i would like to give you a suggestion. But somewhere i think this will be applicable for only small tutorials not for big or i can say long line coding projects. Already sorry if i am wrong.

      1. Don’t write unnecessary code while explaining the code ex. imported packages, variable initialisation, findviewbyid and so on

      2. Write the “Complete Last and best final code” after finishing the all the explanations. This would be in sequence as first the .java file then all the .xml files associated with it. You can post code before the download link.

      Hope it will be useful and/or helpful to you and if not please ignore my suggestion and forgive me if am wrong. The thought of giving suggestion came in my mind because of the issue i faced.

      Reply
  17. Hello sir
    In my case whenever I am call from sqlite method in work good in emulator.
    But whenever I am build in my new phone method get suppresed or never used showing.
    Please help me out 😭

    Reply
  18. Thanks for this great tutorial.
    I have a question, though, why the database seems to not have any created table?
    I mean, I copy the file and when I open it in the sqlitemanager, it will not show any data.
    Thanks in advance for your time.

    Reply
    • Click the download link,
      You will see the download page where link is locked, now follow these steps
      #1 Click on Subscribe Button, it will open a new tab, in this tab subscribe if you are not already subscribed. If you are already subscribed ignore this tab and go back to the download page. Make sure you don’t open the download page again and go back to the already opened page.
      #2 Wait for few seconds and the download link will unlock.

      Reply
  19. Can you please do this with kotlin or is there any method to do same this program without sql. please reply.
    Thanks in advance

    Reply

Leave a Comment