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 queries.

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

It 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.

Syntax to Insert Record into database table.

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

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

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

}

Updating Record in Android SQLite database table

The following snippet shows how to update a single record.

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

Delete Record from Table:-

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=".SqliteActivity11">
    <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="RNO"
                android:id="@+id/strno" >
            </EditText>
            <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.testapp;

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 SqliteActivity11 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_sqlite11);
        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 view) {
                obj.open();
                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();
            }
        });
        btnupdate.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                obj.open();
                obj.updateStudent(Integer.parseInt(txtrno.getText().toString()),txtname.getText().toString(),txtbranch.getText().toString(),Integer.parseInt(txtfees.getText().toString()));
                obj.closeConn();
                displaySelect();
            }
        });
        btndelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                obj.open();
               obj.deleteStudent(Integer.parseInt(txtrno.getText().toString()));
               obj.closeConn();
                displaySelect();
            }
        });
        btnselect.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                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.testapp;

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";
    static final int DB_VERSION = 1;
    // Database Information
    static final String DB_NAME = "test.db";
    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 i, int i1) {
        db.execSQL("Drop table if exists "+TABLE_NAME);
    }
}


DBMANAGER.JAVA

package com.example.testapp;

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

import android.database.SQLException;

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();
    }
}