SQLITE Database In Android Studio Example

SQLITE in android is an open source database storage system that stores data locally in android mobile phones. In this article we will learn how to create a database using SQLITE in android devices. Then creating tables, inserting the data, fetching the data, deleting and updating the data in the database.

Sqlite Database in Android
Sqlite Database in Android

What is SQLITE in Android Studio?

SQLITE is an open source local storage database in android mobiles. We can save the data locally without the internet and can do all CRUD operations like INSERT, DELETE, UPDATE and READ.. SQLITE stores data offline in devices in the form of text.

When Should I Use SQLITE Database In Android?

As you can understand SQLITE is used when we want to store data locally in mobile devices. The data which is required without the internet can be stored in SQLITE database. SQLITE data available only for that mobile device user. So you SQLITE when you need the data without the internet.

SQLITE Database Example in Android Studio Step By Step

To create SQLITE database in android follow the following steps.

1. Create New Project

Create a new android project in android studio and select the JAVA programming language.

2. Add Storage Permission

Add the following storage permission in Manifiest.java file in your android studio project. This is necessary to use the local storage from any mobile device.

<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

3. Creating SQLITE Database Class

Create a class and extend it to the SQLiteOpenHelper class. I named my class as SqliteHelper, you can provide a name as you want and I extended it to the SQLiteOpenHelper.

When you extends it to the SQLiteOpenHelper, You will get a red sign on the left side, Click on that and get the constructor and override the two methods.

  1. onCreate() 
  2. onUpgrade()
package com.example.androidtestingproject.Sqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.example.androidtestingproject.Model.SqliteDataModel;

import java.util.ArrayList;
import java.util.List;

public class SqliteHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "rakesh.db";
    public static final int DB_VERSION = 1;
    public static final String TABLE_NAME = "details";

    public static final String ID = "id";
    public static final String TITLE = "ptitle";
    public static final String QUANTITY = "quantity";
    public static final String PRICE = "price";
    public static final String NUMBER = "number";

    public SqliteHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query = "CREATE TABLE " + TABLE_NAME + "("+ ID +  " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + TITLE + " TEXT NOT NULL, " + QUANTITY + " TEXT NOT NULL, " + PRICE + " TEXT NOT NULL, " + NUMBER + " TEXT NOT NULL)";
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS details");
        onCreate(db);
    }

    //insert data
    public void addToCart(String title, String productQuantity, String productPrice, String productNumber){
        Log.e("ismethodCalled", "yes");
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(TITLE, title);
        values.put(QUANTITY, productQuantity);
        values.put(PRICE, productPrice);
        values.put(NUMBER, productNumber);
        db.insert(TABLE_NAME, null, values);
        db.close();
    }

    //get all data
    public Cursor getData(){
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * from details", null);
        return cursor;
    }

    //reading all data from the table in the list form
    public List<SqliteDataModel> readData() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM details", null);
        List<SqliteDataModel> sqliteDataModels = new ArrayList<>();

        //getting all the data from the database and inserting into the model list class
        if (cursor.moveToFirst()) {
            do {
                SqliteDataModel contact = new SqliteDataModel();
                contact.setTitle(cursor.getString(1));
                contact.setQuantity(cursor.getString(2));
                contact.setPrice(cursor.getString(3));
                contact.setNumber(cursor.getString(4));
                // adding the objects to the model list
                sqliteDataModels.add(contact);
            } while (cursor.moveToNext());
        }

        // returning the model list
        return sqliteDataModels;
    }

    //deleting the data from the table
    public void deleteData(String name){
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME,  "ptitle=?", new String[]{name});
        db.close();
    }

    //updating the values in table
    public void updateData(String title, String quantity, String price, String number){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
//        values.put(TITLE, title);
        values.put(QUANTITY, quantity);
        values.put(PRICE, price);
        values.put(NUMBER, number);
        db.update(TABLE_NAME, values, "ptitle=?", new String[]{title});
        db.close();
    }
}

Explanation

Let visiting all these methods and other components in a glance.

  • Create a database name and provide the extension .db
  • Create a table name and database version. Increase the number of database version when you do anything changes or updates in database, table and its column names.
  • Finally create the name of column fields.

Creating Table / onCreate() Method

The onCreate() method is used to create a table in the database. Use SQL query to create table in the database. Please note that always put space before and after the string while specifying the type of any column as below.

COLUMN_NAME + " TEXT NOT NULL, "

onUpgrade() Method

If the table which is create inside the onCreate() method is already exists, The onUpgrade() method will drop the table.

SqliteDatase() Class

This class is used when we want to read and write the database. So every time when we will perform any operation the object of this class will be used.

Readable Database

Used while feting the data only.

SQLiteDatabase db = this.getReadableDatabase();

Writeable Database

Used when inserting, updating and deleting the fields.

SQLiteDatabase db = this.getWritableDatabase();

Inserting Data into Android SQLITE DB Table

To insert the data into any table, you can create the method in following way which is already created in the above class. ContentValues() class is used to store the multiple data into single variable.

Call below method and pass the required parameters. This will insert the data into the table. You can call this method by creating the class variable.


    public void addToCart(String title, String productQuantity, String productPrice, String productNumber){
        
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(TITLE, title);
        values.put(QUANTITY, productQuantity);
        values.put(PRICE, productPrice);
        values.put(NUMBER, productNumber);
        db.insert(TABLE_NAME, null, values);
        db.close();
    }

We can call this method in following way-

Creating Class Variable

 SqliteHelper sqliteHelper = new SqliteHelper(this);

//call and pass the parameters
sqliteHelper.addToCart("Namkeen", "200gm", "Rs.200", "5");

Reading Android SQLITE Data From Database

To read the data from any table, create a model class which will hold all the data. Here I created a model class and used this model while fetching the data from SQLITE database in android.

Model Class

package com.example.androidtestingproject.Model;

import com.google.gson.annotations.SerializedName;

public class SqliteDataModel {
    @SerializedName("title") private String title;
    @SerializedName("quantity") private String quantity;
    @SerializedName("price") private String price;
    @SerializedName("number") private String number;

    public SqliteDataModel(){}
    public SqliteDataModel(String title, String quantity, String price, String number) {
        this.title = title;
        this.quantity = quantity;
        this.price = price;
        this.number = number;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getQuantity() {
        return quantity;
    }

    public void setQuantity(String quantity) {
        this.quantity = quantity;
    }

    public String getPrice() {
        return price;
    }

    public void setPrice(String price) {
        this.price = price;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }
}

Method To Fetch The Data From SQLITE Database in ANDROID

We are fetching the data in the form of list so we created model.

 //reading all data from the table in the list form
    public List<SqliteDataModel> readData() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM details", null);
        List<SqliteDataModel> sqliteDataModels = new ArrayList<>();

        //getting all the data from the database and inserting into the model list class
        if (cursor.moveToFirst()) {
            do {
                SqliteDataModel contact = new SqliteDataModel();
                contact.setTitle(cursor.getString(1));
                contact.setQuantity(cursor.getString(2));
                contact.setPrice(cursor.getString(3));
                contact.setNumber(cursor.getString(4));
                // adding the objects to the model list
                sqliteDataModels.add(contact);
            } while (cursor.moveToNext());
        }

        // returning the model list
        return sqliteDataModels;
    }

Deleting Data From SQLITE Database Table In Android

To delete any row from the table, You can use the below method in your android SQLITE projects. Just pass the id for which you want to delete the data from the table.

//deleting the data from the table
    public void deleteData(String name){
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME,  "ptitle=?", new String[]{name});
        db.close();
    }

Updating the SQLITE Data in Android

To update the data for any user in the table just use the update query of SQL in following way. Is is quite similar to the inserting method.

  //updating the values in table
    public void updateData(String title, String quantity, String price, String number){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
//        values.put(TITLE, title);
        values.put(QUANTITY, quantity);
        values.put(PRICE, price);
        values.put(NUMBER, number);
        db.update(TABLE_NAME, values, "ptitle=?", new String[]{title});
        db.close();
    }

Note: Please note that I used Databinding in this below example. You can quick learn Databinding from here.

Example Class To Performing All SQLITE Operations in Android

Below is the demo class in which I called all the SQLITE methods from the SqliteHelper() class.

package com.example.androidtestingproject.Activity;

import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;
import androidx.databinding.DataBindingUtil;
import androidx.fragment.app.Fragment;
import androidx.fragment.app.FragmentManager;
import androidx.fragment.app.FragmentPagerAdapter;
import androidx.viewpager.widget.ViewPager;

import android.Manifest;
import android.content.Context;
import android.content.Intent;
import android.content.pm.PackageManager;
import android.database.Cursor;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.net.Uri;
import android.os.Build;
import android.os.Bundle;
import android.os.Environment;
import android.os.FileUtils;
import android.util.Log;
import android.view.View;
import android.webkit.WebView;
import android.webkit.WebViewClient;
import android.widget.Toast;

import com.example.androidtestingproject.Fragment.CallFragment;
import com.example.androidtestingproject.Fragment.ChatFragment;
import com.example.androidtestingproject.Model.SqliteDataModel;
import com.example.androidtestingproject.R;
import com.example.androidtestingproject.Sqlite.SqliteHelper;
import com.example.androidtestingproject.databinding.ActivityDemoBinding;
import com.google.android.material.tabs.TabLayout;
import com.theartofdev.edmodo.cropper.CropImage;
import com.theartofdev.edmodo.cropper.CropImageView;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class DemoActivity extends AppCompatActivity {
    private ActivityDemoBinding binding;
    private SqliteHelper sqliteHelper;
    private Cursor cursor;
    private List<SqliteDataModel> sqliteDataModels;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        binding = DataBindingUtil.setContentView(this, R.layout.activity_demo);

        initView();
    }

    private void initView() {

        sqliteHelper = new SqliteHelper(this);
        cursor = sqliteHelper.getData();
        cursor.moveToFirst();

        sqliteDataModels = new ArrayList<>();
        //insert or update button
        binding.button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
//                insertData();
                updateData();
            }
        });

        fetchData();

        //deleting the data
        binding.buttonDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                deleteData();
            }
        });
    }

    //inserting the data
    public void insertData() {
        String title = binding.textName.getText().toString().trim();
        String quantity = binding.textQuantity.getText().toString().trim();
        String price = binding.textPrice.getText().toString().trim();
        String number = binding.textNumber.getText().toString().trim();

        sqliteHelper.addToCart(title, quantity, price, number);
        Toast.makeText(getApplicationContext(), "Data Inserted", Toast.LENGTH_SHORT).show();

    }

    //fetching the data
    public void fetchData() {
        sqliteDataModels = sqliteHelper.readData();
        for (int i = 1; i < sqliteDataModels.size(); i++) {
            String logString = "Title: " + sqliteDataModels.get(i).getTitle() + " ,Quantity: " + sqliteDataModels.get(i).getQuantity() + " ,price: " +
                    sqliteDataModels.get(i).getPrice();
            Log.d("Name: ", logString);
        }
    }

    //deleting the data
    public void deleteData() {
        sqliteHelper.deleteData(binding.inputName.getText().toString().trim());
        Toast.makeText(getApplicationContext(), "Data Deleted", Toast.LENGTH_SHORT).show();
    }

    //updating data in according to the ptitle
    public void updateData() {
        String title = binding.textName.getText().toString().trim();
        String quantity = binding.textQuantity.getText().toString().trim();
        String price = binding.textPrice.getText().toString().trim();
        String number = binding.textNumber.getText().toString().trim();
        sqliteHelper.updateData(title, quantity, price, number);
        Toast.makeText(getApplicationContext(), "Data Updated", Toast.LENGTH_SHORT).show();

    }
    

}

Layout Class

Below is the layout class for above java class.

<?xml version="1.0" encoding="utf-8"?>
<layout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:background="@color/purple_700"
        android:orientation="vertical">
        

        <TextView
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Sqlite Operations"
            android:textSize="20dp"
            android:gravity="center"
            android:textColor="@color/white"
            android:layout_margin="15dp"/>

        <com.google.android.material.textfield.TextInputLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="10dp"
            app:boxCornerRadiusBottomEnd="5dp"
            app:boxCornerRadiusBottomStart="5dp"
            app:boxCornerRadiusTopEnd="5dp"
            app:boxCornerRadiusTopStart="5dp">

            <com.google.android.material.textfield.TextInputEditText
                android:id="@+id/textName"
                android:layout_width="match_parent"
                android:layout_height="match_parent"
                android:hint="Name"
                android:textSize="18dp" />
        </com.google.android.material.textfield.TextInputLayout>

        <com.google.android.material.textfield.TextInputLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="10dp"
            app:boxCornerRadiusBottomEnd="5dp"
            app:boxCornerRadiusBottomStart="5dp"
            app:boxCornerRadiusTopEnd="5dp"
            app:boxCornerRadiusTopStart="5dp">

            <com.google.android.material.textfield.TextInputEditText
                android:id="@+id/textQuantity"
                android:layout_width="match_parent"
                android:layout_height="match_parent"
                android:hint="Quantity"
                android:textSize="18dp" />
        </com.google.android.material.textfield.TextInputLayout>

        <com.google.android.material.textfield.TextInputLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="10dp"
            app:boxCornerRadiusBottomEnd="5dp"
            app:boxCornerRadiusBottomStart="5dp"
            app:boxCornerRadiusTopEnd="5dp"
            app:boxCornerRadiusTopStart="5dp">

            <com.google.android.material.textfield.TextInputEditText
                android:id="@+id/textPrice"
                android:layout_width="match_parent"
                android:layout_height="match_parent"
                android:hint="Price"
                android:textSize="18dp" />
        </com.google.android.material.textfield.TextInputLayout>

        <com.google.android.material.textfield.TextInputLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="10dp"
            app:boxCornerRadiusBottomEnd="5dp"
            app:boxCornerRadiusBottomStart="5dp"
            app:boxCornerRadiusTopEnd="5dp"
            app:boxCornerRadiusTopStart="5dp">

            <com.google.android.material.textfield.TextInputEditText
                android:id="@+id/textNumber"
                android:layout_width="match_parent"
                android:layout_height="match_parent"
                android:hint="Number"
                android:textSize="18dp" />
        </com.google.android.material.textfield.TextInputLayout>

        <Button
            android:id="@+id/button"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:backgroundTint="@color/black"
            android:text="Add"
            android:textSize="18dp"
            android:padding="10dp"
            android:layout_margin="10dp"/>

        <Button
            android:id="@+id/buttonShow"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:backgroundTint="@color/teal_200"
            android:text="Show"
            android:textSize="18dp"
            android:padding="10dp"
            android:layout_margin="10dp"/>

        <EditText
            android:id="@+id/inputName"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Enter name to delete the data"
            android:background="@color/white"
            android:padding="10dp"
            android:layout_margin="10dp"/>
        <Button
            android:id="@+id/buttonDelete"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:backgroundTint="@color/red"
            android:text="Delete"
            android:textSize="18dp"
            android:padding="10dp"
            android:layout_margin="10dp"/>

    </LinearLayout>
</layout>
sqlite operation
sqlite operation

Fetching Data

I log the fetched data in to logcat.

sqlite data logcat
sqlite data logcat

SQLITE DB Browser

The SQLITE DB browser is used to show the created databases and tables. This is local database browser for android SQLITE, You can download it from here.

Using SQLITE DB

First export the database from the android studio and upload into the SQLITE DB as below.

How To Export SQLITE Database From Android Studio

To export database from android studio follow the following path. Search the “Device file explorer” from the search icon shown in top right corner in android studio then ->

Search -> Device File Explorer -> Data – > Data – > Your Project’s name – > Database – > right click on database and save it.

sqlite database export

Uploading Database File into SQLITE DB

To upload the SQLITE db file into SQLITE DB follow the below steps.

Sqlite DB in Android

Conclusion

We successfully performed all the SQLITE operation in android local storage. Thank you for reading this blog, Hope you learn SQLITE from here. Please comment for any issue in the comment section below.

close

Don’t miss new tips!

We don’t spam! Read our [link]privacy policy[/link] for more info.

Leave a Comment