Android CRUD SQLite Database Example
Android SQLite Database Tutorial – CRUD Operations with validation
1. UI or Design XML code
<?xml version="1.0" encoding="utf-8"?>
<ScrollView
android:layout_marginTop="20sp"
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"
tools:context=".MainActivity">
<RelativeLayout
android:layout_width="match_parent"
android:layout_height="match_parent">
<EditText
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:hint="Name"
android:id="@+id/name_edit"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<EditText
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:hint="Phone No"
android:inputType="number"
android:layout_below="@id/name_edit"
android:id="@+id/phone_edit"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<EditText
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:hint="Phone No"
android:inputType="number"
android:layout_below="@id/phone_edit"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<Button
android:textSize="18sp"
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:id="@+id/btn_save"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_below="@id/phone_edit"
android:background="@drawable/btn_bg"
android:text="save"
android:textColor="#fff" />
<Button
android:textSize="18sp"
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:layout_marginTop="10dp"
android:textColor="#fff"
android:background="@drawable/btn_bg"
android:text="Display"
android:id="@+id/btn_display"
android:layout_below="@id/btn_save"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<EditText
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:id="@+id/edit_delete"
android:inputType="number"
android:hint="Enter Id"
android:layout_below="@id/btn_display"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<Button
android:textSize="18sp"
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:layout_marginTop="10dp"
android:textColor="#fff"
android:background="@drawable/btn_bg"
android:id="@+id/delete_btn"
android:layout_below="@id/edit_delete"
android:text="Delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<EditText
android:inputType="number"
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:id="@+id/update_id"
android:hint="Enter ID"
android:layout_below="@id/delete_btn"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<EditText
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:id="@+id/update_name_edit"
android:layout_below="@id/update_id"
android:hint="Enter Updated Name"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
<Button
android:textSize="18sp"
android:layout_marginLeft="20sp"
android:layout_marginRight="20sp"
android:layout_marginTop="10dp"
android:textColor="#fff"
android:background="@drawable/btn_bg"
android:id="@+id/update_btn"
android:text="Update"
android:layout_below="@id/update_name_edit"
android:layout_width="match_parent"
android:layout_height="wrap_content"/>
</RelativeLayout>
</ScrollView>
2. DatabaseHelperClass.java
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;
class DatabaseHelper extends SQLiteOpenHelper
{
public static final String db_name="emp.db";
public static final String table_name="emp_table";
public String col1="ID";
public String col2="NAME";
public String col3="PHNO";
public DatabaseHelper(@Nullable Context context)
{
super(context, db_name, null, 1);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL("create table "+table_name+"(ID INTEGER PRIMARY KEY ,NAME TEXT,PHNO INTEGER)");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
public boolean insertData(String name,String phone)
{
SQLiteDatabase db=this.getWritableDatabase();
ContentValues contentValues=new ContentValues();
contentValues.put(col2,name);
contentValues.put(col3,phone);
long result=db.insert(table_name,null,contentValues);
if(result==-1)
{
return false;
}
else
{
return true;
}
}
public Cursor getAllData()
{
SQLiteDatabase database=this.getWritableDatabase();
Cursor res=database.rawQuery("select * from "+table_name,null);
return res;
}
public Integer deleteData(String id)
{
SQLiteDatabase database=this.getWritableDatabase();
return database.delete(table_name,"ID=?",new String[] {id});
}
public boolean updateData(String id,String name)
{
SQLiteDatabase sqLiteDatabase=this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(col1,id);
contentValues.put(col2,name);
// contentValues.put(col3,phone);
sqLiteDatabase.update(table_name,contentValues,"ID=?",new String[] {id});
return true;
}
}
3. MainActivity.java
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity
{
Button button,button2,button_delete,button_update;
EditText editname,editphone,edit_id,edit_update_id,edit_update_name;
DatabaseHelper myDB;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
myDB=new DatabaseHelper(this);
editname=(EditText)findViewById(R.id.name_edit);
editphone=(EditText)findViewById(R.id.phone_edit);
edit_id=(EditText)findViewById(R.id.edit_delete);
edit_update_id=(EditText)findViewById(R.id.update_id);
edit_update_name=(EditText)findViewById(R.id.update_name_edit);
button=(Button)findViewById(R.id.btn_save);
button2=(Button)findViewById(R.id.btn_display);
button_delete=(Button)findViewById(R.id.delete_btn);
button_update=(Button)findViewById(R.id.update_btn);
viewAll();
Adddata();
DeleteData();
UpdateData();
}
private void UpdateData() {
button_update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String id=edit_update_id.getText().toString();
boolean idupdate=myDB.updateData(id,edit_update_name.getText().toString());
if(edit_update_id.getText().toString().isEmpty())
{
edit_update_id.setError("Please Enter id");
edit_update_id.requestFocus();
}
else
{
if(idupdate)
{
Toast.makeText(MainActivity.this, "Updated", Toast.LENGTH_SHORT).show();
edit_update_id.setText("");
edit_update_name.setText("");
}
else
{
Toast.makeText(MainActivity.this, "Not Updated", Toast.LENGTH_SHORT).show();
}
}
}
});
}
private void DeleteData() {
button_delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String id=edit_id.getText().toString();
Integer delrow=myDB.deleteData(id);
if(delrow>0)
{
Toast.makeText(MainActivity.this, "Deleted", Toast.LENGTH_SHORT).show();
edit_id.setText("");
}
else
{
if(edit_id.getText().toString().isEmpty())
{
edit_id.setError("Please Enter Id");
edit_id.requestFocus();
}
}
}
});
}
private void viewAll() {
button2.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Cursor res=myDB.getAllData();
if(res.getCount()==0)
{
showMessage("Alert","Nothing Found");
}
StringBuffer buffer=new StringBuffer();
while (res.moveToNext())
{
buffer.append("Id:"+res.getString(0)+"\n");
buffer.append("Name:"+res.getString(1)+"\n");
buffer.append("Phone_no:"+res.getString(2)+"\n\n");
}
showMessage("Data",buffer.toString());
}
});
}
private void Adddata() {
button.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String name=editname.getText().toString();
String phone=editphone.getText().toString();
if(validation())
{
boolean isinserted=myDB.insertData(name,phone);
if(isinserted)
{
Toast.makeText(getApplicationContext(),"Inserted",Toast.LENGTH_SHORT).show();
editname.setText("");
editphone.setText("");
}
else
{
Toast.makeText(getApplicationContext(),"Data not Inserted",Toast.LENGTH_SHORT).show();
}
}
}
});
}
public void showMessage(String title, String message)
{
AlertDialog.Builder builder=new AlertDialog.Builder(this);
builder.setCancelable(true);
builder.setTitle(title);
builder.setMessage(message);
builder.show();
}
public boolean validation()
{
if(editname.getText().toString().isEmpty())
{
editname.setError("Please Enter Name");
editname.requestFocus();
return false;
}
if(editphone.getText().toString().isEmpty())
{
editphone.setError("Please Enter Phone no");
editphone.requestFocus();
return false;
}
else
{
return true;
}
}
}
