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.

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 + "

"; 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 + "

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