Search
  • Sushree Barsa Pattnayak

Android SQLite Database



SQLite is a relational database management system contained in a C library.

In contrast to many other database management systems, SQLite is not a client–server database engine. Rather, it is embedded into the end program. SQLite is developed by D.Richard Hipp ,written in C.It is licensed in public domain,the file extension is .db, .sqlite, .sqlite3 .

We can say that sqlite is a open source SQL database that stores data to a text file on a device. Android comes in with built in SQLite database implementation .

Now the question is ,sqlite is preferable or not ??

So, whenever an application need to store large amount of data then using sqlite is more preferable than other repository system like sharedpreferences or saving data in files.


There are some advantages associated with using a mobile database:

*full offline modes for apps that depend on stored data

*Frugal on bandwidth for apps that depend on stored data

*Stable and predictable performance independent from network availability

*Personal data can be stored with the user, where some say they belong

To create database and tables we can use SQLiteOpenHelper class .To use SQLiteOpenHelper ,we need to create a subclass that overrides the Oncreate() and OnUpgrade () call-back methods.We can insert data into the SQLite database by passing content values to insert() method .We can read the database using the Query() method in android applications. Similarly to update and delete the data we need to use update() method & delete() method respectively.


Fig-1: SQliteDB helper working principle

There are some advantages and disadvantages of SQLite database as follows...


There are plenty of SQLite alternatives. If you simply find it unpleasant to write a lot of SQL and boilerplate code, you can use a object abstraction on top of SQLite.

This abstraction is usually an ORM (object/relational mapping). But if you want to replace SQLite completely, there are also quite a few alternative databases: Couchbase Lite, Interbase, LevelDB, Oracle Berkeley DB (formerly Oracle's mobile database was "Oracle Database Lite"), Realm, SnappyDB, Sparksee Mobile (graph database, brand-new at the time of this article), SQL Anywhere, SQL Server Compact (discontinued), and UnQLite.

To give you an overview, i have compiled a small comparison table:

Why mobile database


Why mobile database

To implement sqlite database in android here i am giving a sample Mainactivity.xml code as below....

<?.. xml version="1.0" encoding="utf-8"?>
<RelativeLayout 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"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:padding="10dp"
    tools:context=".MainActivity">

    <TextView
        android:id="@+id/texttitle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Please enter the details below"
        android:textSize="24dp"
        android:layout_marginTop="20dp"

         />
    <EditText
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Name"
        android:textSize="24dp"
        android:layout_below="@+id/texttitle"
        android:inputType="textPersonName"
        />
    <EditText
        android:id="@+id/contact"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Contact"
        android:textSize="24dp"
        android:layout_below="@+id/name"
        android:inputType="number"
        />
    <EditText
        android:id="@+id/dob"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Date of Birth"
        android:textSize="24dp"
        android:layout_below="@+id/contact"
        android:inputType="number"
        />
    <Button
        android:id="@+id/btninsert"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="24dp"
        android:text="Insert New Data"
        android:layout_marginTop="30dp"
        android:layout_below="@+id/dob"
        />
    <Button
        android:id="@+id/btnUpdate"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="24dp"
        android:text="Update Data"
        android:layout_marginTop="30dp"
        android:layout_below="@+id/btninsert"
        />
    <Button
        android:id="@+id/btnDelete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="24dp"
        android:text="Delete Existing Data"
        android:layout_marginTop="30dp"
        android:layout_below="@+id/btnUpdate"
        />
    <Button
        android:id="@+id/btnView"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:textSize="24dp"
        android:text="View Data"
        android:layout_marginTop="30dp"
        android:layout_below="@+id/btnDelete"
        />

</RelativeLayout>

To implement sqlite database in android here i am giving a sample Mainactivity.java code as below....

package com.example.sqliteapplication;

import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    EditText name, contact, dob;
    Button insert, update ,delete , view;
    DBHelper DB;

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

        name = findViewById(R.id.name);
        contact =findViewById(R.id.contact);
        dob = findViewById(R.id.dob);
        insert = findViewById(R.id.btninsert);
        update = findViewById(R.id.btnUpdate);
        delete = findViewById(R.id.btnDelete);
        view = findViewById(R.id.btnView);
        DB = new DBHelper(this);

        insert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String nameTXT = name.getText().toString();
                String contactTXT = contact.getText().toString();
                String dobTXT = dob.getText().toString();
                Boolean checkinsertdata = DB.insertuserdata(nameTXT, contactTXT, dobTXT);
                if (checkinsertdata==true)
                    Toast.makeText(MainActivity.this, "New Entry Inserted", Toast.LENGTH_SHORT).show();
                else
                    Toast.makeText(MainActivity.this, "New Entry Not Inserted", Toast.LENGTH_SHORT).show();
            }
        });
        update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String nameTXT = name.getText().toString();
                String contactTXT = contact.getText().toString();
                String dobTXT = dob.getText().toString();
                Boolean checkupdatedata = DB.updateuserdata(nameTXT, contactTXT, dobTXT);
                if (checkupdatedata==true)
                    Toast.makeText(MainActivity.this, "Entry Updated", Toast.LENGTH_SHORT).show();
                else
                    Toast.makeText(MainActivity.this, "New Entry Not Updated", Toast.LENGTH_SHORT).show();
            }
        });
        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String nameTXT = name.getText().toString();
                Boolean checkdeletedata = DB.deletedata(nameTXT);
                if (checkdeletedata==true)
                    Toast.makeText(MainActivity.this, "Entry Deleted", Toast.LENGTH_SHORT).show();
                else
                    Toast.makeText(MainActivity.this, "New Entry Not Deleted", Toast.LENGTH_SHORT).show();
            }
        });
        view.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Cursor res = DB.getdata();
                if (res.getCount()==0){
                    Toast.makeText(MainActivity.this, "No Entry Exists", Toast.LENGTH_SHORT).show();
                    return;
                }
                StringBuffer buffer = new StringBuffer();
                while (res.moveToNext()){

                    buffer.append("Name :"+res.getString(0)+"\n");
                    buffer.append("Contact :"+res.getString(1)+"\n");
                    buffer.append("Date of Birth :"+res.getString(2)+"\n\n");

                }
                AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
                builder.setCancelable(true);
                builder.setTitle("User Entries");
                builder.setMessage(buffer.toString());
                builder.show();

            }
        });
    }
}

By creating a new java class user has to name it as DBHelper ..then will have to write this follwing code for DBHelper.java.........

package com.example.sqliteapplication;

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

import androidx.annotation.Nullable;

public class DBHelper extends SQLiteOpenHelper {
    public DBHelper(Context context) {
        super(context, "Userdata.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase DB) {
        DB.execSQL("create Table Userdetails(name TEXT primary key, contact TEXT,dob TEXT)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase DB, int i, int i1) {
       DB.execSQL("drop Table if exists Userdetails");

    }
    public Boolean insertuserdata(String name, String contact, String dob)
    {
        SQLiteDatabase DB = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("contact", contact);
        contentValues.put("dob", dob);
        long result=DB.insert("Userdetails", null,contentValues);
        if (result==-1) {
            return false;

        }else {
            return true;
        }
    }
    public Boolean updateuserdata(String name, String contact, String dob)
    {
        SQLiteDatabase DB = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("contact", contact);
        contentValues.put("dob", dob);
        Cursor cursor = DB.rawQuery("Select * from userdetails where name = ?",new String[] {name});
        if (cursor.getCount()>0)
        {
            long result = DB.update("Userdetails", contentValues, "name=?", new String[] {name});

            if (result == -1) {
                return false;

            } else {
                return true;
            }
            }else
                {
                    return false;

        }}
        public Boolean deletedata(String name)
        {
            SQLiteDatabase DB = this.getWritableDatabase();
            Cursor cursor = DB.rawQuery("Select * from userdetails where name = ?",new String[] {name});
            if (cursor.getCount()>0)
            {
                long result = DB.delete("Userdetails", "name=?", new String[] {name});

                if (result == -1) {
                    return false;

                } else {
                    return true;
                }
            }else
            {
                return false;

            }

    }
    public Cursor getdata ()
    {
        SQLiteDatabase DB = this.getWritableDatabase();
        Cursor cursor = DB.rawQuery("Select * from userdetails", null);
        return cursor;
    }
}

This following code will be for build.gradle

apply plugin: 'com.android.application'

android {
    compileSdkVersion 30
    buildToolsVersion "30.0.0"

    defaultConfig {
        applicationId "com.example.sqliteapplication"
        minSdkVersion 16
        targetSdkVersion 30
        versionCode 1
        versionName "1.0"

        testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }

    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }
}

dependencies {
    implementation fileTree(dir: "libs", include: ["*.jar"])
    implementation 'androidx.appcompat:appcompat:1.2.0'
    implementation 'androidx.constraintlayout:constraintlayout:2.0.1'
    testImplementation 'junit:junit:4.12'
    androidTestImplementation 'androidx.test.ext:junit:1.1.2'
    androidTestImplementation 'androidx.test.espresso:espresso-core:3.3.0'

}

Now the question is where the data will be stored ??

Open the file explorer .Go to data directory inside data directory .Search for your application package name .Inside your application package go to databases where you will found your database ...save your database anywhere you like in text format...

EXAMPLE of SQLite database in Android-

All the android contacts information is saved in SQLite databse.You can use android device monitor to browser database file.



The app will look as following video.......

Brought to you By-

CoE-AI(CET-BBSR)-An initiative by CET-BBSR,Tech Mahindra and BPUT to provide to solutions to REal world Problems through ML and IoT

www.coeaibbsr.in

https://www.facebook.com/CoEAIBBSR

3 views