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.
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.
- onCreate()
- 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>
Fetching Data
I log the fetched data in to 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.
Uploading Database File into SQLITE DB
To upload the SQLITE db file into SQLITE DB follow the below steps.
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.