How to use SQLite Database in Android

 

What is SQLite:

In Android, SQLite is used as database. So before we start coding our app, let’s understand few things about SQLite. SQLite is used as a Local Database in android.

Four Major things to understand about SQLite:

1. SQLite is RDBMS (Relational Database Management System)
2. SQLite is written in C programming language
3. SQLite is embedded within the Android operating System, so you don’t need anything external on Android to use SQLite
4. To manipulate data (insert, update, delete) in SQLite database – we’ll use SQL (Structured Query Language)

SQLite is used as a database for android application development. If you would like to learn more about the above 4 points, please refer the following link:

• http://www.theappguruz.com/blog/use-sqlite-database-swift

To get more details about SQLite in general, please refer the following link:

• http://www.sqlite.org

Android SQLite Example:

We are going to create a project where you will be able to insert/ update/ delete the user records from database.

The final output should look something like following:

[SCREENSHOT – FIRST SCREEN]

As you can see from the screenshots above initially user will see a list (which would be empty right now, since there are no records) and button called ‘Add‘.

[SCREENSHOT – SECOND SCREEN]

When user press the Add button, we’ll show him the form where user can add two fields

1. First Name
2. Last Name

[SCREENSHOT – FIRST SCREEN AFTER ADDING THE RECORD]

By clicking the Insert button, record will be saved into SQLite database and user can see added record on the first screen.

[SCREENSHOT – LONG CLICK]

By long pressing on record you can Delete or Update record.

• When you press on delete record the particular record will be deleted from SQLite database.
• When you press on update record you, you would be taken back to the screen where you inserted your record. And from that screen you can update the record.

Let’s start coding our Android app.

Step 1 Create Layouts

We will need to create three layouts for our project

Layout 1: activity_main.xml

This layout holds all the records and an Add button to add records. When there are no contacts, we are showing the “No Records” and when user inserts the data, we hide that section.

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity">

    <ScrollView
        android:id="@+id/scrollViewDisplay"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight="1">

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="vertical">

            <LinearLayout
                android:id="@+id/layoutDisplayPeople"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:orientation="vertical">

                <TextView
                    android:layout_width="match_parent"
                    android:layout_height="wrap_content"
                    android:gravity="center"
                    android:text="People"
                    android:textColor="@color/color_black"
                    android:textSize="25dp"
                    android:textStyle="bold" />

                <TextView
                    android:id="@+id/tvNoRecordsFound"
                    android:layout_width="match_parent"
                    android:layout_height="wrap_content"
                    android:layout_marginBottom="15dp"
                    android:layout_marginLeft="15dp"
                    android:layout_marginTop="15dp"
                    android:gravity="center"
                    android:text="No Records Found"
                    android:textColor="@color/color_black"
                    android:textSize="15dp" />


                <LinearLayout
                    android:id="@+id/parentLayout"
                    android:layout_width="match_parent"
                    android:layout_height="match_parent"
                    android:orientation="vertical"></LinearLayout>


            </LinearLayout>

        </LinearLayout>
    </ScrollView>

    <com.rey.material.widget.Button
        android:id="@+id/btnAddNewRecord"
        style="@style/RaiseWaveColorButtonRippleStyle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@color/color_blue"
        android:text="ADD"
        android:textColor="@color/color_white"
        android:textStyle="bold" />
</LinearLayout>

Layout 2: inflate_record layout

We are using this layout to inflate the records we are inserting.

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:orientation="vertical"
    android:gravity="center_horizontal">

    <com.rey.material.widget.LinearLayout
        android:id="@+id/inflateParentView"
        style="@style/FlatWaveButtonRippleStyle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="center"
        >

        <com.rey.material.widget.TextView
            
            android:id="@+id/tvFullName"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginBottom="15dp"
            android:layout_marginTop="15dp"
            android:layout_marginLeft="15dp"
            android:layout_weight="1"
            android:text="Name"
            android:textSize="15sp"
            android:gravity="left"

            android:textColor="@color/color_black"
            android:textStyle="bold"
            />
    </com.rey.material.widget.LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="2dp"
        android:background="@color/color_grey"></LinearLayout>

</LinearLayout>

Layout 3: table_manipulation.xml

This xml file for two DML (Data Manipulation Language – used for insert/ update/ delete queries) statements of SQLite Database i.e. insert and update.

Here, we have two fieds:

1. EditText for FirstName and LastName
2. A Button for Insert/ Update records

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/layoutAddUpdate"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <EditText
        android:id="@+id/etFirstName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="First Name"
        android:inputType="textCapWords" />


    <EditText
        android:id="@+id/etLastname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Last Name"
        android:inputType="textCapWords" />

    <com.rey.material.widget.Button
        android:id="@+id/btnDML"
        style="@style/RaiseWaveColorButtonRippleStyle"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:background="@color/color_blue"
        android:text="Save"
        android:textColor="@color/color_white"
        android:textStyle="bold"
       />

</LinearLayout>

Let’s learn how to create database and table in SQLite database and do the operations (select/insert/update/delete) on table records.

Let’s get into java files now.

Step 2 Database Creation (SQLiteHelper.java)

2.1 Make SQLiteHelper class

public class SQLiteHelper extends SQLiteOpenHelper

We are creating a java file called SQLiteHelper and extending SQLiteOpenHelper class and It is used to create a bridge between android and SQLite.

To perform basic SQL operations we need to extend SQLiteOpenHelper class.
		   
private static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "SQLiteDatabase.db";

public SQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

Here, SQLiteDatabase.db is the name of your database, and database version is 1. When you make an object of SQLiteHelper class, it calls super class constructor and creates the SQLite database.

SQLiteOpenHelper class has two abstract methods that you need to override in SQLiteHelper class.

1. onCreate()
2. onUpgrade()

2.1.1 onCreate() method

Let’s check the code and understand these methods in detail:

public static final String TABLE_NAME = "PEOPLE";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_FIRST_NAME = "FIRST_NAME";
public static final String COLUMN_LAST_NAME = "LAST_NAME";

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + TABLE_NAME + " ( " + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_FIRST_NAME + " VARCHAR, " + COLUMN_LAST_NAME + " VARCHAR);");
}

This method is called when database is created for the first time. This is where the creation of tables should happen. If you wish to insert records at the time of creating tables, even the code to insert the records should be written here.

Parameters:

db	The Database

Here, we are making a table named PEOPLE in SQLite android.

Column Name	Data Type	Constraints
Id	Integer	auto increment
FirstName	varchar
LastName	varchar

db.execSQL() Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

For more details about execSQL() method please refer the following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String)

2.1.2 onUpgrade() method

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

This method is called when the database needs to be upgraded (Database version changed). The implementation should use this method to drop tables/ add tables or to do anything else it needs to upgrade to the new schema version.

Parameters:

db	The Database
oldVersion	The old database version
newVersion	The new database version

That’s all we are going to do in our SQLiteHelper class.

If you are interested in knowing more about SQLiteOpenHelper class, please refer following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html

We are done with two things now:

1. We have created a database - SQLiteDemo.db
2. We have create a table - People

The structure for People table:

Column Name	Data Type	Constraints
ID	int	Auto Increment
FIRST_NAME	varchar
LAST_NAME	varchar

If you would like to see your database in action, you can download the FireFox plugin from the following link:

• https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

2.1.3 Steps For How to use SQLite Manager (SQLite manager Firefox tutorial)

1. In Android Studio, go to Tools Menu >> DDMS or You can directly click on the Android Device Monitor icon appears on menu bar
2. Device Monitor window will open. Select your connected device/emulator from device tab and then go to File Explorer tab. In File Explorer tab go to data >> data >> select your project. Now Click on the Pull a file from the device icon(top right corner) and save it with .db extension.
3. Open Mozilla Firefox and then go to Tools Menu >> SQLite Manager.
4. Now In SQLite Manger Go to Database Menu >> Connect Database.Then select your database.

P.S.: You can directly create database using the plugin mentioned above. And then you have to import that database in your project directory.

Step 3 Create model class for contact (ContactModel.java)

You’re doing awesome work till now! Now sit back and relax a bit.

We’ll do all the manipulation of data through a model class. So let’s first see our model class (Model is kind of skeleton of the data we want to use). We are working with contacts over here, so I have named my class as ContactModel.

We need following three things for the contact:

1. ID
2. First Name
3. Last Name

We have declared them as String variables in our model class and we have generated getter and setter methods for ID, First Name and Last Name.

public class ContactModel {

    private String ID, firstName, lastName;

    public String getID() {
        return ID;
    }

    public void setID(String ID) {
        this.ID = ID;
    }

    public String getFirstName() {
        return firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
}

Step 4 CRUD Operations (Insert/ Update/ Delete/ Select Records)

Now we will learn about Inset, Update, Delete and display data from android SQLite database.

Please open SQLiteHelper.java class

4.1 Insert Records

I would be showing you 2 ways to do each of the operations; there is not much difference in them but its better if you learn both the ways.

>> Method 1:

• Our first task would be to insert records in the database which we have just created. To do that, check out the following code. We’ll be using object of SQLiteDatabase class in all the operations, so I have declared it as a class level object.

private SQLiteDatabase database;

public void insertRecord(ContactModel contact) {
    database = this.getReadableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_FIRST_NAME, contact.getFirstName());
    contentValues.put(COLUMN_LAST_NAME, contact.getLastName());
    database.insert(TABLE_NAME, null, contentValues);
    database.close();
}

To insert the record, first we are getting SQLiteDatabase object by calling getReadableDatabase() method of SQLiteOpenHelper class. We have set ContactModel class as an argument such that we can get the values of First Name and Last Name from that model.

4.1.1 Why do we need SQLiteDatabase Object?

database = this.getReadableDatabase();

SQLiteDatabase will help you to insert, update and delete the records from table and it also helps you to display table records.

For more details about SQLiteDatabase please refer following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

If you would like to learn more about getReadbleDatabase() method, please refer following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#getReadableDatabase()

4.1.2 ContentValues

ContentValues contentValues = new ContentValues();
contentValues.put(COLUMN_FIRST_NAME, contact.getFirstName());
contentValues.put(COLUMN_LAST_NAME, contact.getLastName());

If you want to insert / update records, you can do it through ContentValues class by using the put() method.

If you would like to learn more about ContentValues, please refer following link:

• http://developer.android.com/reference/android/content/ContentValues.html

4.1.3 Insert Row

>> Method 1:

database.insert( TABLE_NAME, null, contentValues);

This is a convenient method for inserting a row into the database.

Parameters:

TABLE_NAME	String	In which table you want insert data.
nullColumnHack	String	Optional, may be null. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided values are empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values are empty.
Values		This map contains the initial column values for the row. The keys should be the column names and the values the column values.

Returns:

• The row ID of the newly inserted row
• -1 if an error occurred

For more details about database.insert() method please refer following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insert(java.lang.String,java.lang.String, android.content.ContentValues)

For safe coding, it is better to close the database once we are done with our operation. We are closing the database at the end with following code.

database.close();

>> Method 2:

public void insertRecordAlternate(ContactModel contact) {
    database = this.getReadableDatabase();
    database.execSQL("INSERT INTO " + TABLE_NAME + "(" + COLUMN_FIRST_NAME + "," + COLUMN_LAST_NAME + ") VALUES('" + contact.getFirstName() + "','" + contact.getLastName() + "')");
    database.close();
}

Difference between both the methods:

1. We are using ContentValues in this method
2. We are using database.execSQL() method to execute the Query

database.execSQL(): Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

For more details about execSQL() method please refer following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String

4.2 Update Rows

>> Method 1:

public void updateRecord(ContactModel contact) {
    database = this.getReadableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_FIRST_NAME, contact.getFirstName());
    contentValues.put(COLUMN_LAST_NAME, contact.getLastName());
    database.update(TABLE_NAME, contentValues, COLUMN_ID + " = ?", new String[]{contact.getID()});
    database.close();
}

public int update (String table, ContentValues values, String whereClause, String[] whereArgs)

update() is the convenient method for updating rows in the database.

Parameters:

Table	The table to update in
Value	A map from column names to new column values. null is a valid value that will be translated to NULL.
whereClause	The optional WHERE clause to apply when updating. Passing null will update all rows.
whereArgs	You may include is in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Returns:

• The number of rows affected.

For more details about update() method please refer the following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#update(java.lang.String, android.content.ContentValues, java.lang.String, java.lang.String[])

>> Method 2:

public void updateRecordAlternate(ContactModel contact) {
    database = this.getReadableDatabase();
    database.execSQL("update " + TABLE_NAME + " set " + COLUMN_FIRST_NAME + " = '" + contact.getFirstName() + "', " + COLUMN_LAST_NAME + " = '" + contact.getLastName() + "' where " + COLUMN_ID + " = '" + contact.getID() + "'");
    database.close();
}

Here, we are using database.execSQL() instead of database.update() method. And in the end we are closing the database.

4.3 Delete Records

>> Method 1:

public void deleteRecord(ContactModel contact) {
    database = this.getReadableDatabase();
    database.delete(TABLE_NAME, COLUMN_ID + " = ?", new String[]{contact.getID()});
    database.close();
}

public int delete (String table, String whereClause, String[] whereArgs)

database.delete() is convenient method for deleting rows in the database.

Parameters:

Table	the table to delete from
whereClause	the optional WHERE clause to apply when deleting. Passing null will delete all rows.
whereArgs	You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Returns

• The number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass 1 as the whereClause.

For more details about delete() method please refer the folloiwng link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#delete(java.lang.String, java.lang.String, java.lang.String[])

>> Method 2:

public void deleteRecordAlternate(ContactModel contact) {
    database = this.getReadableDatabase();
    database.execSQL("delete from " + TABLE_NAME + " where " + COLUMN_ID + " = '" + contact.getID() + "'");
    database.close();
}

Here, again we are using database.execSQL() method to run the delete query and to delete the record.

Challenge: Can you write a query to delete the whole table and not just all the records

4.4 Select Records

Before we get into the actual code of selecting the records, we need to understand the concept of Cursor which is very important in this context.

4.4.1 What is Cursor?

When we query the database in Android, we get the result in cursor object containing the result of the query

Two important points to remember about cursor:

• Cursors store query result records in rows and there are many methods which enables you to access the data stored in cursor.
• We should use Cursor.close() method when Cursors is no longer used.

For more details about Cursor please refer following link:

• http://developer.android.com/reference/android/database/Cursor.html

Now we are getting back to fetching the records from database.

4.4.2 Fetching Records from database

I’ll show you two ways to achieve this.

>> Method 1:

public ArrayList<ContactModel> getAllRecords() {
    database = this.getReadableDatabase();
Cursor cursor = database.query(TABLE_NAME, null, null, null, null, null, null);

ArrayList<ContactModel> contacts = new ArrayList<ContactModel>();
    ContactModel contactModel;
    if (cursor.getCount() > 0) {
        for (int i = 0; i < cursor.getCount(); i++) {
            cursor.moveToNext();

            contactModel = new ContactModel();
            contactModel.setID(cursor.getString(0));
            contactModel.setFirstName(cursor.getString(1));
            contactModel.setLastName(cursor.getString(2));

            contacts.add(contactModel);
        }
    }
    cursor.close();
    database.close();

    return contacts;
}

Cursor cursor = database.query(TABLE_NAME, null, null, null, null, null, null);

First of all, we are querying the database and fetching the records into cursor.

What is query() method?

• The query() method is highly overloaded, and there are variations of the method.

The one we are using is given below:

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

The method Queries the given table, and returns a cursor over the result set.

Parameters:

table	The table name to compile the query against.
columns	A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
selection	A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs	You may include is in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy	A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
Having	A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
oerderBy	How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.

For more details about database.query() method please refer the following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query(java.lang.String, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String)

Now coming back to rest of the code of getAllRecrods() method:

• if (cursor.getCount() > 0): First, we are checking if we have got any records in Cursor or not.
• for (int i = 0; i < cursor.getCount(); i++): If yes, than we fetch all the records by iterating through the for loop.
• cursor.moveToNext(): Since, the initial position of the cursor is at -1.

We have to move cursor to the next record by using following code:

• cursor.getString(0) It retrieves first column value (i.e. ID)
• cursor.getString(1) It retrieves second column value(i.e. First Name)
• cursor.getString(2) It retrieves third column value (i.e. Last Name)

Then we are fetching data column wise

• cursor.close() After fetching all the records we are closing the cursor since it is very important to close the cursor for safe coding.

>> Method 2:

Now we are fetching records using rawQuery() method that is quite similar to query() method.

public ArrayList<ContactModel> getAllRecordsAlternate() {
    database = this.getReadableDatabase();
    Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME, null);

    ArrayList<ContactModel> contacts = new ArrayList<ContactModel>();
    ContactModel contactModel;
    if (cursor.getCount() > 0) {
        for (int i = 0; i < cursor.getCount(); i++) {
            cursor.moveToNext();

            contactModel = new ContactModel();
            contactModel.setID(cursor.getString(0));
            contactModel.setFirstName(cursor.getString(1));
            contactModel.setLastName(cursor.getString(2));

            contacts.add(contactModel);
        }
    }
    cursor.close();
    database.close();

    return contacts;
}

The only thing which is different in both the methods is following line of code.

Cursor cursor = database.rawQuery("SELECT * FROM " + TABLE_NAME, null);

We are using this method:

public Cursor rawQuery (String sql, String[] selectionArgs)

Parameters:

sql	The SQL query. The SQL string must not be ; terminated
selectionArgs	You may include is in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

Returns

• Cursor

For more information about database.rawQuery() please refer following link:

• http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String, java.lang.String[])

Step 5 Calling SQLiteHelper class methods

5.1 Making SQLiteHelper class object

sQLiteHelper = new SQLiteHelper(MainActivity.this);

Here, we are making an object of SQLiteHelper class. While calling parameterized constructor of SQLiteHelper class it calls super class

i.e.

• SQLiteOpenHelper class constructor.

SQLiteOpenHelper class will create/open the database that we have discussed above.

5.2 Add Record

By pressing add button it calls an onAddRecord() method. onAddRecord() method Starts new activity called TableManipulationActivity with request code ADD_RECORD.

private void onAddRecord() {
    Intent intent = new Intent(MainActivity.this, TableManipulationActivity.class);
    intent.putExtra(Constants.DML_TYPE, Constants.INSERT);
    startActivityForResult(intent, Constants.ADD_RECORD);
}

>> Make an intent object to go from MainActivity to TableManipulationActivity.

>> Put extra values to intent

• Key: DML_TYPE
• Value: Insert

>> And start an activity for getting result for ADD_RECORD.

5.2.1 Start TableManipulationActivity with ADD_RECORD request

In TableManipulationActivity get Extra value that we have set on last activity.

String request = getIntent().getExtras().get(Constants.DML_TYPE).toString();

If request is for inserting the record, then we set the text of the button as Insert.

btnDML.setText(Constants.INSERT);

5.2.2 onButtonClick() method

private void onButtonClick() {
    if (etFirstname.getText().toString().equals("") || etLastname.getText().toString().equals("")) {
        Toast.makeText(getApplicationContext(), "Add Both Fields", Toast.LENGTH_LONG).show();
    } else {
        Intent intent = new Intent();
        intent.putExtra(Constants.FIRST_NAME, etFirstname.getText().toString());
        intent.putExtra(Constants.LAST_NAME, etLastname.getText().toString());
        setResult(RESULT_OK, intent);
        finish();
    }
}

>> First it will check if Firstname or Lastname is empty. If one of the fields is empty then we’ll not move forward.

>> Otherwise, we will make an object of intent and put First name and Last name as an extra values of intent.

>> Now set the result RESULT_OK. (Standard activity result: operation succeeded).

>> finish() the current activity.

5.2.3 onActivityResult(int requestCode, int resultCode, Intent data)

When we start any activity for result (Remember: startActivityForResult() method), after finishing or getting back from that particular activity onActivityResult() method will called from the last activity (Here, MainActivity).

@Override
public void onActivityResult(int requestCode, int resultCode, Intent data) {
    super.onActivityResult(requestCode, resultCode, data);

if (resultCode == RESULT_OK) {
    String firstname = data.getStringExtra(Constants.FIRST_NAME);
    String lastname = data.getStringExtra(Constants.LAST_NAME);


ContactModel contact = new ContactModel();
contact.setFirstName(firstname);
contact.setLastName(lastname);


if (requestCode == Constants.ADD_RECORD) {
	sQLiteHelper.insertRecord(contact);
} 
}

>> Here, we are checking for resultCode. If resultCode is RESULT_OK then we are getting extra values that we have set on last activity (i.e. Firstname and Lastname).

>> After getting First name and Last name holding that data by ContactModel class.

>> Now check for requestCode. It requestCode is ADD_RECORD then insert record to the table.

sQLiteHelper.insertRecord(contact);

5.2.4 Display all records

After calling insertRecord() method we will call displayAllRecords() method to display all the inserted records.

private void displayAllRecords() {

    com.rey.material.widget.LinearLayout inflateParentView;
    parentLayout.removeAllViews();

    ArrayList<ContactModel> contacts = sQLiteHelper.getAllRecords();

    if (contacts.size() > 0) {
        tvNoRecordsFound.setVisibility(View.GONE);
        ContactModel contactModel;
        for (int i = 0; i < contacts.size(); i++) {

            contactModel = contacts.get(i);

            final Holder holder = new Holder();
            final View view = LayoutInflater.from(this).inflate(R.layout.inflate_record, null);
            inflateParentView = (com.rey.material.widget.LinearLayout) view.findViewById(R.id.inflateParentView);
            holder.tvName = (TextView) view.findViewById(R.id.tvName);


            view.setTag(contactModel.getID());
            holder.firstname = contactModel.getFirstName();
            holder.lastname = contactModel.getLastName();
            String personName = holder.firstname + " " + holder.lastname;
            holder.tvName.setText(personName);

            final CharSequence[] items = {Constants.UPDATE, Constants.DELETE};
            inflateParentView.setOnLongClickListener(new View.OnLongClickListener() {
                @Override
                public boolean onLongClick(View v) {

                    AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
                    builder.setItems(items, new DialogInterface.OnClickListener() {
                        @Override
                        public void onClick(DialogInterface dialog, int which) {
                            if (which == 0) {

                                rowID = view.getTag().toString();
                                onUpdateRecord(holder.firstname, holder.lastname.toString());
                            } else {
                                AlertDialog.Builder deleteDialogOk = new AlertDialog.Builder(MainActivity.this);
                                deleteDialogOk.setTitle("Delete Contact?");
                                deleteDialogOk.setPositiveButton("Ok", new DialogInterface.OnClickListener() {
                                            @Override
                                            public void onClick(DialogInterface dialog, int which) {
                                                //sQLiteHelper.deleteRecord(view.getTag().toString());
                                                ContactModel contact = new ContactModel();
                                                contact.setID(view.getTag().toString());
                                                sQLiteHelper.deleteRecord(contact);
                                                displayAllRecords();
                                            }
                                        }
                                );
                                deleteDialogOk.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
                                    @Override
                                    public void onClick(DialogInterface dialog, int which) {

                                    }
                                });
                                deleteDialogOk.show();
                            }
                        }
                    });
                    AlertDialog alertDialog = builder.create();
                    alertDialog.show();
                    return true;
                }
            });
            parentLayout.addView(view);
        }
    } else {
        tvNoRecordsFound.setVisibility(View.VISIBLE);
    }
}

Get all records from Table.

ArrayList contacts = sQLiteHelper.getAllRecords();

• Getting list of contacts by calling getAllRecords() method of SQLiteHelper Class.
• Here, we know that SQLiteHelper.getAllRecords() method returns ArrayList of ContactModel Class.

Use Holder class to display records:

Holder class contains one TextView to display full name and two String values for first name and last name holding.

private class Holder {
    TextView tvName;
    String firstname;        
    String lastname;
}

final Holder holder = new Holder();
final View view = LayoutInflater.from(this).inflate(R.layout.inflate_record, null);
inflateParentView = (com.rey.material.widget.LinearLayout) view.findViewById(R.id.inflateParentView);
holder.tvFullName = (TextView) view.findViewById(R.id.tvFullName);


view.setTag(contactModel.getID());
holder.firstname = contactModel.getFirstName();
holder.lastname = contactModel.getLastName();
String personName = holder.firstname + " " + holder.lastname;
holder.tvFullName.setText(personName);

>> Make an instance of Holder class.

>> Get layout as a view using inflate method.

>> Get TextView reference from layout.

>> Now setTag view which is ID.

>> Set First Name and Last Name of holder class from ContactModel class.

>> Finaly, set person name to TextView by merging first name and last name.

5.3 Update Record

By long pressing on records you will ask for update or delete record

final CharSequence[] items = {Constants.UPDATE, Constants.DELETE};
inflateParentView.setOnLongClickListener(new View.OnLongClickListener() {
    @Override
    public boolean onLongClick(View v) {

        AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
        builder.setItems(items, new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
                if (which == 0) {

                    rowID = view.getTag().toString();
                    onUpdateRecord(holder.firstname, holder.lastname.toString());
                }

>> First set values for alert dialog (Update, Delete)

>> Set long click listener on inflated view.

>> Make AlertDialog.Builder for alert dialog and set both the items to alert dialog.

>> Now set click listener for both the items that we have added to alert dialog.

• DialogInterface: dialog reference
• which: index of the item that is clicked.

>> Update index is 0, when it is called then rowID will be store by calling getTag() method view and onUpdateRecord() method will be called with first name and last name.

5.3.1 onUpdateRecord()

private void onUpdateRecord(String firstname, String lastname) {
    Intent intent = new Intent(MainActivity.this, TableManipulationActivity.class);
    intent.putExtra(Constants.FIRST_NAME, firstname);
    intent.putExtra(Constants.LAST_NAME, lastname);
    intent.putExtra(Constants.DML_TYPE, Constants.UPDATE);
    startActivityForResult(intent, Constants.UPDATE_RECORD);
}

Here, we are doing same that we have done on onAddRecord() for making instance of an intent.

• We are passing first name and last name with intent to show it on edit text of TableManipulationActivity.
• And at the end we are calling startActivityForResult() method with the request of UPDATE_RECORD.

>> Start TableManipulationActivity with UPDATE_RECORD request.

>> In TableManipulationActivity get Extra value that we have set on last activity.

• String request = getIntent().getExtras().get(Constants.DML_TYPE).toString();

>> If request is for updating the record then set the text of the button as Update.

• btnDML.setText(Constants.UPDATE);

>> onButtonClick() method:

• onButtonClick() method is the same method that we have shown at the time of AddRecord.

>> onActivityResult(int requestCode, int resultCode, Intent data):

• Here, onActivityResult() method will called with UPDATE_RECORD request.

else if (requestCode == Constants.UPDATE_RECORD) {

    contact.setID(rowID);
    sQLiteHelper.updateRecord(contact);
}

>> Here, we already stored FirstName and LastName into ContactModel’s contact object.

>> In update we also need rowID which is being affected. So here we also setting rowID.

>> And Finally, calling updateRecord() method of SQLiteHelper class.

5.4 Delete Record

>> For deleting a record long press on record and select Delete.

>> You will be asked for confirmation if you would like to delete the record.

AlertDialog.Builder deleteDialogOk = new AlertDialog.Builder(MainActivity.this);
deleteDialogOk.setTitle("Delete Contact?");
deleteDialogOk.setPositiveButton("Ok", new DialogInterface.OnClickListener() {
            @Override
            public void onClick(DialogInterface dialog, int which) {
ContactModel contact = new ContactModel();
contact.setID(view.getTag().toString());
sQLiteHelper.deleteRecord(contact);

            }
        }
);

>> Here, we are making alert dialog for confirmation of deleting record.

>> We are deleting the row based rowID, so we have just set the rowID to ContactModel class.

>> And call deleteRecord() method of SQLiteHelper class by passing object of ContactModel class.

Step 6 Get all table name list into array from sqlite database

To get all the table names from the current database.

public ArrayList<String> getAllTableName()
{
    database = this.getReadableDatabase();
    ArrayList<String> allTableNames=new ArrayList<String>();
    Cursor cursor=database.rawQuery("SELECT name FROM sqlite_master WHERE type='table'",null);
    if(cursor.getCount()>0)
    {
        for(int i=0;i<cursor.getCount();i++)
        {
            cursor.moveToNext();
            allTableNames.add(cursor.getString(cursor.getColumnIndex("name")));
        }
    }
    cursor.close();
    database.close();
    return allTableNames;
}

I hope you find this blog post very helpful while with How to use SQLite Database in Android. Let me know in comment if you have any questions regarding How to use SQLite Database in Android. I will reply you ASAP.

Learning Android sounds fun, right? Why not check out our other Android Tutorials?

Got an Idea of Android App Development? What are you still waiting for? Contact us now and see the Idea live soon. Our company has been named as one of the best Android App Development Company in India.