Insert, Update, Delete, and Select Data in SQLITE Database using Android.

03/06/2017   12:30:29 PM

Hello in this android article i will explain how to insert  data in to sqlite database and perform update,delete and select that data using android application.Data will store on your android mobile locally so without internet you can easily store and access your data.so Lets start this example

Firstly need to know about SQLITE database so lets look short description about sqlite database.

What is SQLITE?

1. SQLITE database is an open source sql database.

2. It stores the data in the text form on device.

3. Sqlite is built in with android OS.

 

1.Create a new project in android studio/add the code to/res/layout/activity_main.xml file 

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"
    android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">
    <EditText
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/edtemployeename"
        android:text="Search"
        android:layout_alignRight="@+id/btnupdate"
        android:layout_alignEnd="@+id/btnupdate"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />
      <EditText
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/editText"
        android:text="Employee Name"
        android:layout_below="@+id/edtemployeename"
        android:layout_alignParentRight="true"
        android:layout_alignParentEnd="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />
     <EditText
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/editText2"
        android:layout_below="@+id/editText"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_alignRight="@+id/editText"
        android:layout_alignEnd="@+id/editText"
        android:text="Employee MailId" />
     <EditText
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/editText3"
        android:layout_below="@+id/editText2"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_alignRight="@+id/editText2"
        android:layout_alignEnd="@+id/editText2"
        android:text="Employee Salary" />
     <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Save"
        android:id="@+id/btnsave"
        android:layout_centerVertical="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Update"
        android:id="@+id/btnupdate"
        android:layout_alignTop="@+id/btnsave"
        android:layout_alignParentRight="true"
        android:layout_alignParentEnd="true" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete"
        android:id="@+id/btndel"
        android:layout_centerVertical="true"
        android:layout_centerHorizontal="true" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Search All Record"
        android:id="@+id/btnselect"
        android:layout_alignTop="@+id/btnselectperticular"
        android:layout_alignLeft="@+id/btndel"
        android:layout_alignStart="@+id/btndel" />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Search"
        android:id="@+id/btnselectperticular"
        android:layout_below="@+id/btnsave"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_marginTop="53dp" />
</RelativeLayout>

 

2.Add the following code in to MainActivity.java for Insert, Update, Delete, and Select Data in SQLITE

In the following code we will first create sqlite database name as EmployeeDB and then  create a table Employee.in this table create  four fields are

EmpId ( AUTOINCREMENTED) ,EmpName ,EmpMail ,EmpSalary  
        
package com.simplylearnprogramming.sqliteinsertupdtedelsel;
 
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
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 implements android.view.View.OnClickListener {
    SQLiteDatabase db;
    EditText editsearchname,editempname,editempmail,editempsalary;
    Button Add, Delete, Modify, View,search ;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //Create database,EmployeeDB database name
         db=openOrCreateDatabase("EmployeeDB", Context.MODE_PRIVATE, null);
        //create table Employee
        db.execSQL("CREATE TABLE IF NOT EXISTS Employee(EmpId INTEGER PRIMARY KEY                                                                                
         AUTOINCREMENT,EmpName VARCHAR,EmpMail VARCHAR,EmpSalary VARCHAR);");
        editsearchname = (EditText) findViewById(R.id.edtemployeename);
        editempname = (EditText) findViewById(R.id.editText);
        editempmail = (EditText) findViewById(R.id.editText2);
        editempsalary = (EditText) findViewById(R.id.editText3);
        Add = (Button) findViewById(R.id.btnsave);
        Delete= (Button) findViewById(R.id.btndel);
        Modify= (Button) findViewById(R.id.btnupdate);
        View= (Button) findViewById(R.id. btnselect);
        search=(Button) findViewById(R.id. btnselectperticular);
        Add.setOnClickListener(this);
        Delete.setOnClickListener(this);
        Modify.setOnClickListener(this);
        View.setOnClickListener(this);
        search.setOnClickListener(this);
}
 
    public void msg(Context context,String str)
    {
        Toast.makeText(this,str,Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onClick(View v) {
        if(v.getId()==R.id.btnsave)
        {
         // code for save data
            if(editempname.getText().toString().trim().length()==0||
                    editempmail.getText().toString().trim().length()==0||
                    editempsalary.getText().toString().trim().length()==0)
            {
                msg(this, "Please enter all values");
                return;
            }
            db.execSQL("INSERT INTO Employee(EmpName,EmpMail,EmpSalary)VALUES('"+ editempname.getText()+"','"+ editempmail.getText()+ "','"+    editempsalary.getText()+"');");
            msg(this, "Record added");
        }
 
  else if(v.getId()==R.id.btnupdate)
        {
            //code for update data
            if(editsearchname.getText().toString().trim().length()==0)
            {
                msg(this, "Enter Employee  Name");
                return;
            }
            Cursor c=db.rawQuery("SELECT * FROM Employee WHERE EmpName='"+ editsearchname.getText()+"'", null);
            if(c.moveToFirst()) {
                db.execSQL("UPDATE Employee  SET EmpName ='"+ editempname.getText()+"', EmpMail='"+ editempmail.getText()+"',EmpSalary='"+      editempsalary.getText()+"' WHERE EmpName ='"+editsearchname.getText()+"'");
                msg(this, "Record Modified");
            }
            else
            {
                msg(this, "Invalid Employee Name");
            }
        }
        else if(v.getId()==R.id.btndel)
        {
            //code for delete data
            if(editsearchname.getText().toString().trim().length()==0)
            {
                msg(this, " Please enter Employee  Name ");
                return;
            }
            Cursor c=db.rawQuery("SELECT * FROM Employee WHERE EmpName ='"+ editsearchname.getText()+"'", null);
            if(c.moveToFirst())
            {
                db.execSQL("DELETE FROM Employee WHERE EmpName ='"+ editsearchname.getText()+"'");
                msg(this, "Record Deleted");
            }
            else
            {
                msg(this, "Invalid Employee Name ");
            }
        }
        else if (v.getId() == R.id.btnselect)
        {
           //code for select all data
            Cursor c=db.rawQuery("SELECT * FROM Employee", null);
            if(c.getCount()==0)
            {
                msg(this, "No records found");
                return;
            }
            StringBuffer buffer=new StringBuffer();
           while(c.moveToNext())
            {
                buffer.append("Employee Name: "+c.getString(1)+"\n");
                buffer.append("Employee Mail: "+c.getString(2)+"\n\n");
                buffer.append("Employee Salary: "+c.getString(3)+"\n\n");
            }
            msg(this, buffer.toString());
        }
        else if(v.getId()==R.id.btnselectperticular)
        {
        //code for select particular data
            if(editsearchname.getText().toString().trim().length()==0)
            {
                msg(this, "Enter Employee Name");
                return;
            }
            Cursor c=db.rawQuery("SELECT * FROM Employee WHERE EmpName='"+editsearchname.getText()+"'", null);
            if(c.moveToFirst())
            {
                editempname.setText(c.getString(1));
                editempmail.setText(c.getString(2));
                editempsalary.setText(c.getString(3));
}
            else
            {
                msg(this, "Invalid Employee Name");
            }
        }
    }
}
 

 

OUTPUT

1.Insert Data Into Sqlite

Insert Data Into Sqlite android 

2.Select Data From Sqlite

Select Data From Sqlite android

3.Update Data From Sqlite

Update Data From Sqlite android

4.Select Updated Data From Sqlite

Select Updated Data From Sqlite android

5.Delete Data From Sqlite

Delete Data From Sqlite android


Output

                   

Support us by Sharing