Android SQLite Database Tutorial (ADD, DELETE, FETCH)

SQLite is an open-source and relational database management system. Compared to SQL server and Oracle, it is very light in weight. It is a 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 we have a DATABASE named MY_DATABASE. In this Database, we have a TABLE named MY_TABLE.

FIELDDATATYPE
EMP_IDSTRING
EMP_NAMESTRING
EMP_EMAILSTRING
EMP_PASSWORDSTRING

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

Read More: Android AutoCompleteTextView Example

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(), execSQL() which executes 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 in 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 SQLite Database class.

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

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

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, 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

Recent Articles

[Updated] Temporarily Free Games and Apps on Google Play Store

Every week there are free games and apps on the Google Play store. Here is the list of free games and apps on the Play Store you...

OnePlus 7T vs iPhone 11 | Specs Comparison

The OnePlus 7T and iPhone 11 are the two best phones if you looking for a price of less than $1000. Both the phone...

Elon Musk unveils Tesla’s Cybertruck

Are you into gaming or love watching sci-fi movies?  What about those back to the future movies which gave us a glimpse of the...

7 Best Hiking Apps you Must Try in 2020

Having the best hiking apps always gives you an advantage if you are going for adventurous trips. These apps can track your workout and activities...

[UPDATED] 9 Best Android Launcher Apps of 2019

Android launcher is best to customize the overall experience of Android for a user in terms of look and act. In apps store, there are...

Related Stories

1 COMMENT

  1. I happen to be commenting to let you know what a really good experience my princess enjoyed browsing the blog. She learned some pieces, which include what it’s like to possess a great teaching mindset to let many more quite simply thoroughly grasp certain complicated topics. You really did more than readers’ expectations.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay on Top - Get the daily news in your inbox