26 July 2016

Introduction to Android SqLite

Introduction:

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.

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:

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

</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();

Conclusion:

So, this is the basic idea of how to create SQlite database and perform many operations in this with the help of DataBaseHelper class. It is used to store the user data. This is a simple example of Android SQlite database which is used in Android applications to preserve the app and user data.

Output: