Android SQLite Database Tutorial (ADD, DELETE, FETCH)

SQLite is an open-source and relational database management system. Compared to SQL servers and Oracle, it is very light in weight. It is serverless, i.e., not a client-server-based management system.

It doesn’t require a specific type of installation or configuration. It uses dynamic types for tables. It allows a single database connection to access multiple database files simultaneously.

TABLE STRUCTURE

Let us have a DATABASE named MY_DATABASE. In this Database, we have a TABLE named MY_TABLE.

FIELD	DATATYPE
EMP_ID	STRING
EMP_NAME	STRING
EMP_EMAIL	STRING
EMP_PASSWORD	STRING

1. Create a new project in the android studio from File ⇒ New Project and select the Basic Activity.

2. Open the ACTIVITY_MAIN.XML file and copy the below

<?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"
tools:context=".MainActivity"
android:orientation="vertical">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text= "SQLite Tutorial"
android:textColor="#000000"
android:textSize="40dp"/>
<EditText
android:id="@+id/i1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Employee_id"
android:layout_marginTop="10dp"/>
<EditText
android:id="@+id/i2"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Employee_name"
android:layout_marginTop="10dp"/>
<EditText
android:id="@+id/i3"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Employee_emailid"
android:layout_marginTop="10dp"/>
<EditText
android:id="@+id/i4"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Employee_password"
android:layout_marginTop="10dp"
android:inputType="textPassword"/>
<Button
android:id="@+id/b1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="SUBMIT"
android:layout_marginTop="20dp"/>
<Button
android:id="@+id/b2"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="DELETE"
android:onClick="delete"
android:layout_marginTop="10dp"/>
<Button
android:id="@+id/b3"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="VIEW"
android:onClick="loademp"
android:layout_marginTop="10dp"/>
<Button
android:id="@+id/t1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="20dp"
android:textColor="#000000"/>
</LinearLayout>

3. Make a Java File Name MYDBHANDLER.JAVA

package com.example.sqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
import android.database.Cursor;
public class MyDBHandler extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "MY_DATABASE";
// User table name
private static final String TABLE_NAME = "MY_TABLE";
// User Table Columns names
private static final String EMP_ID = "user_id";
private static final String EMP_NAME = "user_name";
private static final String EMP_EMAIL = "user_email";
private static final String EMP_PASSWORD = "user_pass";
private final Context context;
private String CREATE_TABLE= "CREATE TABLE " + TABLE_NAME + "(" + EMP_ID + " TEXT," + EMP_NAME + " TEXT," + EMP_EMAIL + " TEXT," + EMP_PASSWORD + " TEXT " + ")";
private String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
public MyDBHandler(Context context)
{
super(context,DATABASE_NAME,null,DATABASE_VERSION);
this.context=context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_TABLE);
onCreate(db);
}
public void addemp(String empid, String name, String email, String pass)
{
SQLiteDatabase db= this.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(MyDBHandler.EMP_ID,empid);
values.put(MyDBHandler.EMP_NAME,name);
values.put(MyDBHandler.EMP_EMAIL,email);
values.put(MyDBHandler.EMP_PASSWORD,pass);
long status=db.insert(TABLE_NAME,null,values);
if(status<=0){
Toast.makeText(context, "Insertion Unsuccessful", Toast.LENGTH_SHORT).show();
}
else
{
Toast.makeText(context, "Insertion Successful", Toast.LENGTH_SHORT).show();
}
db.close();
}

public void deleteUser(String id) {
SQLiteDatabase db = this.getWritableDatabase();
// delete user record by id
long s=db.delete(TABLE_NAME, EMP_ID + " = ?",
new String[]{String.valueOf(id)});
if(s<=0){
Toast.makeText(context, "Deletion Unsuccessful", Toast.LENGTH_SHORT).show();
}
else
{
Toast.makeText(context, "Deletion Successful", Toast.LENGTH_SHORT).show();
}
db.close();
}
public String load()
{
String result = "";
String query = "Select*FROM " + TABLE_NAME;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
while (cursor.moveToNext()) {
int result_0 = cursor.getInt(0);
String result_1 = cursor.getString(1);
String result_2 = cursor.getString(2);
String result_3 = cursor.getString(3);
result += String.valueOf(result_0) + " " + result_1 + " " + result_2 + " " + result_3 + "\n";
System.getProperty("line.separator");
}
cursor.close();
db.close();
return result;
}
}

4. On the MAINACTIVITY.JAVA add the following code.

package com.example.sqlite;
import android.content.ContentValues;
import android.content.Context;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity {
EditText e1,e2,e3,e4;
String d1,d2,d3,d4, del;
Button b;
TextView t;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
e1=(EditText)findViewById(R.id.i1);
e2=(EditText)findViewById(R.id.i2);
e4=(EditText)findViewById(R.id.i4);
e3=(EditText)findViewById(R.id.i3);
t=(TextView)findViewById(R.id.t1);
b=(Button)findViewById(R.id.b1);
b.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
d1=e1.getText().toString();
d2=e2.getText().toString();
d3=e3.getText().toString();
d4=e4.getText().toString();
MyDBHandler handler=new MyDBHandler(MainActivity.this);
handler.addemp(d1,d2,d3,d4);
e1.setText(" ");
e2.setText(" ");
e3.setText(" ");
e4.setText(" ");
}
});
}
public  void delete(View view)
{
del=e1.getText().toString();
MyDBHandler handler=new MyDBHandler(this);
handler.deleteUser(del);}public void loademp(View view) {MyDBHandler dbHandler = new MyDBHandler(this);t.setText(dbHandler.load());}}

Let’s study each module one by one:-

SQLite Database

It is an open-source SQL Database which acts as an interface between application code and the underlying database. It has many methods like insert(), delete(), query(), and execSQL(), which execute a single SQL statement that does not return result data.

SQLiteOpenHelper Class

It is used to manage database creation and version management. It has many methods like:-

onCreate() – This method is only called to create a database when there is no existing database.

onUpdate() – This method is only called when the database file exists but the stored version number is lower than requested in the constructor.

onOpen() – This method is called when work needs to be done when the database is opened.

getWritableDatabase() – It allows to create a database for reading and writing.

getReadableDatabase() – It allows to create a database only for reading.

close() – It is used to close the database.

TO ADD RECORD

This code will add a new record to our database.

We have used ContentValues class which allows us to put data inside an object in the form of Key-Value pairs for columns and their value. We can pass this object to the insert() function of the SQLite Database class.

Insert() function of the SQLite Database class allows us to add a new row/ record to the existing table of our database.

Also, we have used a status variable to check whether our record insertion into the database table is successful.

public void addemp(String empid, String name, String email, String pass)
{
SQLiteDatabase db= this.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(MyDBHandler.EMP_ID,empid);
values.put(MyDBHandler.EMP_NAME,name);
values.put(MyDBHandler.EMP_EMAIL,email);
values.put(MyDBHandler.EMP_PASSWORD,pass);
long status=db.insert(TABLE_NAME,null,values);
if(status<=0){
Toast.makeText(context, "Insertion Unsuccessful", Toast.LENGTH_SHORT).show();
}
else
{
Toast.makeText(context, "Insertion Successful", Toast.LENGTH_SHORT).show();
}
db.close();
}

TO DELETE RECORD

This code will delete a record from our database. That record will be deleted whose id user will insert.

delete() function of the SQLite Database class allows us to delete a record from our Database.

public void deleteUser(String id) {
SQLiteDatabase db = this.getWritableDatabase();
// delete user record by id
long s=db.delete(TABLE_NAME, EMP_ID + " = ?",
new String[]{String.valueOf(id)});
if(s<=0){
Toast.makeText(context, "Deletion Unsuccessful", Toast.LENGTH_SHORT).show();
}
else
{
Toast.makeText(context, "Deletion Successful", Toast.LENGTH_SHORT).show();
}
db.close();
}

TO FETCH RECORD

This code allows us to fetch all records of a particular Table stored in our Database.

We have used a query variable which uses SQL query to fetch all rows from the table.

We have a Cursor class which is like a pointer which iterates through the cursor and sets the pointer to the desired position. getString(0) to getString(3) fetches all records from all four columns 0 to 3.

public String load()
{
String result = "";
String query = "Select*FROM " + TABLE_NAME;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
while (cursor.moveToNext()) {
int result_0 = cursor.getString(0);
String result_1 = cursor.getString(1);
String result_2 = cursor.getString(2);
String result_3 = cursor.getString(3);
result += String.valueOf(result_0) + " " + result_1 + " " + result_2 + " " + result_3 + "\n";
System.getProperty("line.separator");
}
cursor.close();
db.close();
return result;
}

This was a simple Android SQLite Database tutorial, in which we learn how to add a Record to Database, Delete a Record and fetch a Record from Database.

DOWNLOAD FROM GITHUB

Hope you like this article. For any query, comment down below