Android CRUD using SQLite

SQLite is a opensource SQL database that stores data to a text file on a device and Android has it built in SQLite database implementation.

It supports major RDBMS features.  Concept remains same as we use to do with other database

1- Create DB
2- Give Grant to DB
3- Create Table
5- Get Connection from code
6- Perform CRUD Operation
7- Close Connection

Database - Package which we used in android to do the same is android.database.sqlite.

Step 1-2 : -Database creation and Granting default permission

This can be done using openOrCreateDatabase method
SQLiteDatabase mydb = openOrCreateDatabase("OUR_DB_NAME",MODE_PRIVATE,null);

Step 3 : -Create Table/Inserting/Updating/Fetching

We can use execSQL method to create and exeute sql command like insert/update/delete etc.

mydb.execSQL("CREATE TABLE IF NOT EXISTS Siddhu_Table(Name VARCHAR PRIMARY KEY,Number VARCHAR);");


Fetching

we use Cursor class to fetch data. Cursor class method rawQuery it will return a resultset with the cursor pointing to the table. We can move the cursor forward and retrieve the data.

Cursor resultSet = mydatbase.rawQuery("Select * from Siddhu_Table",null);
resultSet.moveToFirst();
String UName = resultSet.getString(1);
String PWord = resultSet.getString(2);

Please find below code that shows CRUD operation from Android


1- content_main.xml


    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:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.example.siddhu.mydatabaseapplication.MainActivity"
    tools:showIn="@layout/activity_main">


      
            android:id="@+id/nameText1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:ems="10"
            android:inputType="textNoSuggestions"
            android:layout_centerVertical="true"
            android:layout_toRightOf="@+id/nameLabel1"
            android:layout_toEndOf="@+id/nameLabel1" 

       
            android:id="@+id/numberText2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_alignLeft="@+id/nameText1"
            android:layout_below="@+id/nameText1"
            android:ems="10" 


        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/spinner"
        android:layout_below="@+id/okButton"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"

    
            android:id="@+id/okButton"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
        android:layout_marginTop="31dp"
            android:text="OK"
        android:layout_below="@+id/numberText2"
        android:layout_alignRight="@+id/nameLabel1"
        android:layout_alignEnd="@+id/nameLabel1" 
    

            android:id="@+id/nameLabel1"
            android:layout_width="210px"
            android:layout_height="70px"
            android:text="Name:"
            android:textSize="15sp"
            android:typeface="serif"
            android:textStyle="italic"
            android:textColor="#001eff"
            android:layout_x="8px"
            android:layout_y="15px"
            android:layout_above="@+id/numberText2"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true">


            android:id="@+id/numberLabel2"
            android:layout_width="210px"
            android:layout_height="70px"
            android:text="Number:"
            android:textSize="15sp"
            android:typeface="serif"
            android:textStyle="italic"
            android:textColor="#0015ff"
            android:layout_x="10px"
            android:layout_y="82px"
            android:layout_above="@+id/okButton"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true">
  
            android:id="@+id/label3"
            android:layout_width="125px"
            android:layout_height="47px"
            android:textSize="15sp"
            android:layout_x="100px"
            android:layout_y="262px"
            android:layout_alignBaseline="@+id/spinner"
            android:layout_alignBottom="@+id/spinner"
            android:layout_toRightOf="@+id/numberLabel2"
            android:layout_alignRight="@+id/numberText2"
            android:layout_alignEnd="@+id/numberText2">


2- MainActivity.java

package com.example.siddhu.mydatabaseapplication;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.TextView;

public class MainActivity extends Activity

{
    EditText text1,text2;
    TextView label1;
    Button button2;
    Spinner spinner1;
    SQLiteDatabase mydb;
    private static final String[] array = {"ADD", "DELETE", "VIEW", "UPDATE"};
    @Override    public void onCreate(Bundle icicle)
    {
        super.onCreate(icicle);
        setContentView(R.layout.activity_main);
        text1=  (EditText) findViewById(R.id.nameText1);
        text2=  (EditText) findViewById(R.id.numberText2);
        label1 = (TextView) findViewById(R.id.label3);
        button2 = (Button) findViewById(R.id.okButton);
        spinner1 = (Spinner) findViewById  (R.id.spinner);
        ArrayAdapter   adapter = new ArrayAdapter(this,android.R.layout.simple_spinner_item, array);
        adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        spinner1.setAdapter(adapter);
        button2.setOnClickListener(new clicker());
        try        {
            mydb = this.openOrCreateDatabase("Siddhu.db", MODE_PRIVATE, null);
            //mydb = this.openDatabase("mydb", null);            mydb.execSQL("CREATE TABLE IF NOT EXISTS Siddhu_Table (Name VARCHAR PRIMARY KEY,Number VARCHAR);");
        }catch(Exception e){text2.setText("Here comes the exception :::"+e);}
    } //--------------------------------------------------    class clicker implements Button.OnClickListener
    {
        public void onClick(View v)
        {
            Cursor c;
            String s = (String) spinner1.getSelectedItem();
            Object ob1 = text1.getText();
            Object ob2 = text2.getText();
            try            {
                if(s.equals("ADD"))                 {
                    mydb.execSQL("INSERT INTO Siddhu_Table (Name,Number) values ('"+ob1+"','"+ob2+"');");
                    label1.setText("ADDED");
                }
                else                if(s.equals("DELETE"))
                {                     mydb.execSQL("DELETE FROM Siddhu_Table WHERE Name='"+ob1+"';");

                    label1.setText("DELETED");
                }
                else if(s.equals("UPDATE"))
                {
                    mydb.execSQL("UPDATE Siddhu_Table  SET Number='"+ob2+"' WHERE Name='"+ob1+"';");
                    label1.setText("UPDATED");
                }
                else                {
                    c = mydb.rawQuery("SELECT * FROM Siddhu_Table WHERE Name='"+ob1+"';",null);
                    c.moveToFirst();
                        int n = c.getColumnIndex("Number");
                        String s1 = c.getString(n);
                        text2.setText(s1);
                        label1.setText("SHOWING…");
                  }
            }catch(Exception e){text2.setText(""+e);}
        }
    } //------------------------------------------------------}