SQLITE in Android

 What is SQLite?

It is a light-weight RDBMS package for android and iOS, it takes very little memory space and best performance as compared to normal database software.

SQLite provides separate memory space under Android Runtime, It supports all SQL query.

This means we can create tables, insert rows, update rows, and delete rows using SQL Query.

Android provides an SQLiteOpenHelper class to create and upgrade the database.

We will create a class with three mandatory components.

1)  Constructor():-

public DatabaseHelper(Context context) {

        super(context, DB_NAME, null, DB_VERSION);

    }

2) onCreate()

 It is used to create a database. when we install any app and SQLite is present then by default onCreate() will be called.

3) onUpgrade()

It is used to change or destroy the database objects. When we uninstall the app then upgrade() will be called.

Insert Record into database table.

Android provides ContentValue class to store object data using key=>value pair, where key contains column name and value contains column data.

public void insert(String name, String desc) { ContentValues contentValue = new ContentValues(); contentValue.put(columnname, columndata); .,...

database.insert(DatabaseHelper.TABLE_NAME, null, contentValue); }

Update record into the database?

Updating Record in Android SQLite database table

The following snippet shows how to update a single record.

public int update(long _id, String name, String desc) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DatabaseHelper.SUBJECT, name);
        contentValues.put(DatabaseHelper.DESC, desc);
        int i = database.update(DatabaseHelper.TABLE_NAME, contentValues, DatabaseHelper._ID + " = " + _id, null);
        return i;
    }

Delete Record from 

We just need to pass the id of the record to be deleted as shown below.

public void delete(long _id) {
        database.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper._ID + "=" + _id, null);
    }

CODE  for SQLite:-

DESIGN FILE:-

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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:orientation="vertical"
    tools:context=".sqliteexample">

    <LinearLayout
        android:layout_width="wrap_content"
        android:orientation="vertical"
        android:layout_height="wrap_content">
        <LinearLayout
        android:layout_width="wrap_content"
            android:layout_height="wrap_content">

    <EditText
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:hint="NAME"
        android:id="@+id/stname">

    </EditText>
    <EditText
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:hint="BRANCH"
        android:id="@+id/stbranch">

    </EditText>
    <EditText
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:hint="FEES"
        android:id="@+id/stfees">

    </EditText>
        </LinearLayout>
        <LinearLayout
            android:layout_width="wrap_content"
            android:layout_height="wrap_content">

        <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/btnsqlite"
        android:text="INSERT"
        ></Button>
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/btnsselect"
            android:text="SELECT"
            ></Button>
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/btnupate"
            android:text="UPDATE"
            ></Button>
        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/btndelete"
            android:text="DELETE"
            ></Button>

        </LinearLayout>
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content">
        <ListView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/lstshowdata">

        </ListView>

    </LinearLayout>
</LinearLayout>

SQLITEEXAMPLE.JAVA:-

package com.example.customdrawer;

import androidx.appcompat.app.AppCompatActivity;

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

public class sqliteexample extends AppCompatActivity {

    Button btn,btnselect,btnupdate,btndelete;
    DBManager obj;
    EditText txtrno,txtname,txtbranch,txtfees;
    ListView lst;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqliteexample);
        obj = new DBManager(this);
        obj.open();
        btn = findViewById(R.id.btnsqlite);
        txtrno = findViewById(R.id.strno);
        txtname = findViewById(R.id.stname);
        txtbranch = findViewById(R.id.stbranch);
        txtfees = findViewById(R.id.stfees);
        lst = findViewById(R.id.lstshowdata);
        btnselect = findViewById(R.id.btnsselect);
        btnupdate = findViewById(R.id.btnupate);
        btndelete = findViewById(R.id.btndelete);
        btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
              obj.insert(txtname.getText().toString(),txtbranch.getText().toString(),Integer.parseInt(txtfees.getText().toString()));
              Toast.makeText(getApplicationContext(),"Insert success" ,Toast.LENGTH_LONG).show();
              obj.closeConn();
              displaySelect();


            }
        });


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

                  displaySelect();
            }
        });
       btnupdate.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View v) {
              obj.open();
               obj.updateStudent(Integer.parseInt(txtrno.getText().toString()),txtname.getText().toString(),txtbranch.getText().toString(),Integer.parseInt(txtfees.getText().toString()));
               Toast.makeText(getApplicationContext(),"Insert success" ,Toast.LENGTH_LONG).show();
               obj.closeConn();
               displaySelect();
           }
       });

       btndelete.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View v) {
             obj.open();
             obj.deleteStudent(Integer.parseInt(txtrno.getText().toString()));
             obj.closeConn();
             displaySelect();
           }
       });

    }

    void displaySelect()
    {
        obj.open();

        Cursor c=obj.fetch();
        c.moveToFirst();
        String s[] = new String[c.getCount()];
        Toast.makeText(getApplicationContext(),""+c.getCount() ,Toast.LENGTH_LONG).show();
        for(int i=0;i<c.getCount();i++)
        {
            s[i] = c.getInt(0) + " "+c.getString(1) + " "+ c.getString(2)+ " "+ c.getInt(3);
            c.moveToNext();
        }
        ArrayAdapter aa = new ArrayAdapter(getApplicationContext(),android.R.layout.simple_list_item_1,s);
        lst.setAdapter(aa);
        obj.closeConn();
    }
}
MYDB.JAVA

package com.example.customdrawer;

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

public class MyDB extends SQLiteOpenHelper {
    public static final String TABLE_NAME = "Student";

    // Table columns
    public static final String RNO = "rno";
    public static final String SNAME = "sname";
    public static final String BRANCH = "branch";
    public static final String FEES = "fees";

    // Database Information
    static final String DB_NAME = "test.db";

    // database version
    static final int DB_VERSION = 1;
    private static final String CREATE_TABLE = "create table " + TABLE_NAME + "(" + RNO
            + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SNAME + " TEXT NOT NULL, " + BRANCH + " TEXT ,"+ FEES + " INTEGER)";
    public  MyDB(Context ctx)
    {
      super(ctx,DB_NAME,null,DB_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
         db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("Drop table if exists "+TABLE_NAME);

    }
}

DBMANAGER.JAVA

package com.example.customdrawer;

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

public class DBManager {
    private MyDB dbHelper;

    private Context context;

    private SQLiteDatabase database;

    public DBManager(Context c) {
        context = c;
    }

    public DBManager open() throws SQLException {
        dbHelper = new MyDB(context);
        database = dbHelper.getWritableDatabase();
        return this;
    }

    public void insert(String name, String branch, int fees) {
        ContentValues contentValue = new ContentValues();
        contentValue.put(MyDB.SNAME, name);
        contentValue.put(MyDB.BRANCH, branch);
        contentValue.put(MyDB.FEES, fees);
        database.insert(MyDB.TABLE_NAME, null, contentValue);

    }
    public Cursor fetch() {

        Cursor cursor = database.rawQuery("Select * from "+ MyDB.TABLE_NAME,null);
        return cursor;
    }

    public int updateStudent(int rno, String name, String branch, int fees) {


        ContentValues contentValue = new ContentValues();
        contentValue.put(MyDB.SNAME, name);
        contentValue.put(MyDB.BRANCH, branch);
        contentValue.put(MyDB.FEES, fees);

        // updating row
        return database.update(MyDB.TABLE_NAME, contentValue, MyDB.RNO + " = ?",
                new String[] { String.valueOf(rno) });
    }

    public void deleteStudent(int rno) {

        database.delete(MyDB.TABLE_NAME, MyDB.RNO + " = ?",
                new String[] { String.valueOf(rno) });

    }

    public void closeConn()
    {
        database.close();
    }
}