Introduction to Android SqLite

What is SQLite database in Android?

Android SQlite is an open source database which comes with Android OS.
It is a lightweight database as it requires limited memory at runtime. SQlite is different from other databases.
It supports relational database features.
It is used to store the user data.
We can perform many operations in SQlite database like Create, Read, Update, Delete i.e (CRUD) operations.

SQlite Operations:

Let’s see How we can do it.
Methods:
onCreate(): This is one of the important methods of DataBaseHelper class. It is called when a database is created for the first time.
OnUpgrade(): It is also an important method of DataBaseHelper class, it allows us to upgrade the database, that is we can update the existing database.
Example with Code:

1. Create an Application AndroidSqliteExample.

Requirements:
Here in this example, we are going to create the database of Students. So,
3 classes are required one is StudentsModel, DataBaseHelper, and MainActivity.
1 The XML file is required for our MainActivity i.e our main layout.
StudentsModel.java:
Create this class in the application for SQlite table.
Add the code
Here we initiate the id, name and phone number of a student

public int id;
public String name;
public String phone_number;
// create constructor

DataBaseHelper.java:
Here in this class, we can do all the CRUD operations
We have to extend this class with SQliteOpenHelper.
Add the code
Initialize the database name, version, table name, and all the keys.

// Database Name
	public static String DATABASE_NAME = "student_database";
	// Current version of database
	private static final int DATABASE_VERSION = 1;
	// Name of table
	private static final String TABLE_STUDENTS = "students";
	// All Keys used in table
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_PHONENUMBER = "phone_number";
	public static String TAG = "tag";

Add the methods for CRUD operations

CREATE Table:
This is student table create query

private static final String CREATE_TABLE_STUDENTS = "CREATE TABLE "
			+ TABLE_STUDENTS + "(" + KEY_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME + " TEXT,"
			+ KEY_PHONENUMBER + " TEXT );";

Insert operation:

This method is used to add the student detail in students table

public long addStudentDetail(StudentsModel student) {
		SQLiteDatabase db = this.getWritableDatabase();
		// Creating content values
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, student.name);
		values.put(KEY_PHONENUMBER, student.phone_number);
		// insert row in students table
		long insert = db.insert(TABLE_STUDENTS, null, values);
		return insert;
	}

In this method, ContentValues define Key / Value pairs where the key is the column name and value is the content of that column.
Read operation:

This operation is used to read the particular entry of a student or the whole database.

public StudentsModel getStudent(long id) {
		SQLiteDatabase db = this.getReadableDatabase();
		// SELECT * FROM students WHERE id = ?;
		String selectQuery = "SELECT  * FROM " + TABLE_STUDENTS + " WHERE "
				+ KEY_ID + " = " + id;
		Log.d(TAG, selectQuery);
		Cursor c = db.rawQuery(selectQuery, null);
		if (c != null)
			c.moveToFirst();
		StudentsModel students = new StudentsModel();
		students.id = c.getInt(c.getColumnIndex(KEY_ID));
		students.phone_number = c.getString(c.getColumnIndex(KEY_PHONENUMBER));
		students.name = c.getString(c.getColumnIndex(KEY_NAME));
		return students;
	}

Here, in this method, we can read the particular student details by using a cursor which points to a single row at a time.
And to get all the database student record just loop it and add in an ArrayList.This way, we can get the whole data.

Update operation:

To update a single row, we can use this operation. Inside this with the help of key id we will update a row.

public int updateEntry(StudentsModel student) {
		SQLiteDatabase db = this.getWritableDatabase();
		// Creating content values
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, student.name);
		values.put(KEY_PHONENUMBER, student.phone_number);
		// update row in students table base on students.is value
		return db.update(TABLE_STUDENTS, values, KEY_ID + " = ?",
				new String[] { String.valueOf(student.id) });
	}

Delete operation:

Similarly, by using a key id we can delete the particular row in this operation.

public void deleteEntry(long id) {
		// delete row in students table based on id
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_STUDENTS, KEY_ID + " = ?",
				new String[] { String.valueOf(id) });
	}

So, these are the CRUD operations in SQlite database. Let’s see how we can perform these actions from our MainActivity.
activity_main.xml:
Add the code in our main layout file.

<EditText
        android:id="@+id/editText1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentRight="true"
        android:layout_alignParentTop="true"
        android:layout_marginTop="19dp"
        android:ems="10" >
        <requestFocus />
    </EditText>
    <EditText
        android:id="@+id/editText2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentRight="true"
        android:layout_below="@+id/editText1"
        android:layout_marginTop="22dp"
        android:ems="10" />
    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/editText1"
        android:layout_alignBottom="@+id/editText1"
        android:layout_alignParentLeft="true"
        android:layout_marginLeft="16dp"
        android:text="Name:" />
    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/editText2"
        android:layout_alignBottom="@+id/editText2"
        android:layout_alignLeft="@+id/textView1"
        android:text="Phone no:" />
    <Button
        android:id="@+id/add"
        android:layout_width="150dp"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText2"
        android:layout_marginLeft="15dp"
        android:layout_marginTop="15dp"
        android:background="#ADD8E6"
        android:text="Add" />
    <TextView
        android:id="@+id/textView3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/add"
        android:layout_marginLeft="16dp"
        android:layout_marginTop="15dp"
        android:text="Enter Student Id to delete" />
    <EditText
        android:id="@+id/editText3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentRight="true"
        android:layout_below="@+id/add"
        android:layout_marginLeft="16dp"
        android:layout_marginTop="25dp"
        android:ems="10" />
    <Button
        android:id="@+id/delete"
        android:layout_width="150dp"
        android:layout_height="wrap_content"
        android:layout_below="@+id/editText3"
        android:layout_marginLeft="15dp"
        android:layout_marginTop="15dp"
        android:background="#ADD8E6"
        android:text="Delete" />
    <TextView
        android:id="@+id/tv"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/textView2"
        android:layout_below="@+id/editText3"
        android:layout_marginTop="92dp"
        android:text="" />

MainActivity.java:
Initialize the database and other variables.

EditText name, phone_no, id;
Button addButton, deleteButton;
TextView tv;
List<StudentsModel> list = new ArrayList<StudentsModel>();
DataBaseHelper db;

Add the code in onCreate(){..}

db = new DataBaseHelper(getApplicationContext());
		name = (EditText) findViewById(R.id.editText1);
		phone_no = (EditText) findViewById(R.id.editText2);
		id = (EditText) findViewById(R.id.editText3);
		addButton = (Button) findViewById(R.id.add);
		deleteButton = (Button) findViewById(R.id.delete);
		tv = (TextView) findViewById(R.id.tv);
		addButton.setOnClickListener(this);
		deleteButton.setOnClickListener(this);

Add some students data :

StudentsModel student = new StudentsModel();
		student.name = "Shiva";
		student.phone_number = "1111111111";
		db.addStudentDetail(student);
		student.name = "Rahul";
		student.phone_number = "2222222222";
		db.addStudentDetail(student);
		list = db.getAllStudentsList();

To add the data of a student by clicking on add button like

StudentsModel student = new StudentsModel();
			student.name = name.getText().toString();
			student.phone_number = phone_no.getText().toString();
			db.addStudentDetail(student);
			list = db.getAllStudentsList();

And to delete

String student_id = id.getText().toString();
			db.deleteEntry(Integer.parseInt(student_id));
			list = db.getAllStudentsList();