Advanced Android Database

Even though this tutorial is named as Advanced Android Database, there is nothing advanced in Android database. The last Basic Android Database tutorial explains the quick and dirty way of using SQLite database in your application. This tutorial uses the same problem discussed in the last article, but to explain the recommended way of database access in Android.


The Create, Read, Update and Delete (CRUD) related methods are not explained in this article since those are already explained in the Basic Android tutorial. If you are new to Android Database, please read the Basic Android Database article first and then continue this article in order to learn the best practices.
Step 1:
Download the provided template from this link.

Step 2:
Right click on the db package and create a new class “DatabaseOpenHelper”.

Step 3:
Modify the class as shown below.
package com.javahelps.sample.advanceddatabase.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseOpenHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "contacts.db";
    private static final int DB_VERSION = 1;

    public DatabaseOpenHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE Contact(first_name TEXT, last_name TEXT, phone TEXT PRIMARY KEY, email TEXT);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}
The abstract class  SQLiteOpenHelper has two abstract methods and two overloaded constructors. The super class constructor used in this class needs four parameters. The first parameter is the context of the application, which has to be passed from an Activity class. Second parameter is the name of database file and the third parameter is CursorFactory, which is optionally requested if you need any custom Cursor objects. The null CursorFactory reference makes the Android to use the default CursorFactory. Last parameter is the version of your database.

Database version and application version are two different concepts. For example assume that we released the version 1 of this application as a Contact Manager. For the first time our database version would be 1. Later we enhanced the user interface and released the version 2 of the application. Since there were no changes in the database, still the database version would be 1. Database version will be changed only if there are any significant modifications in the database schema.

When an object of SQLiteOpenHelper is created, it checks for an existing database with the provided name. If the database file is not available, Android creates the database and call the onCreate method of the SQLiteOpenHelper instance. If you want to do anything on that time, write the logic inside the onCreate method. According to the provided code a new table is created in the onCreate method.

The second method onUpgrade is used to update the database when a new database is released.

Step 4:
Create another class 'DatabaseAccess.java' in the 'com.javahelps.sample.advanceddatabase.db' package.

Step 5:
Add the following code into the class.
private SQLiteOpenHelper openHelper;
private SQLiteDatabase database;
private static DatabaseAccess instance;

/**
 * Private constructor to aboid object creation from outside classes.
 *
 * @param context
 */
private DatabaseAccess(Context context) {
    this.openHelper = new DatabaseOpenHelper(context);
}

/**
 * Return a singleton instance of DatabaseAccess.
 *
 * @param context the Context
 * @return the instance of DabaseAccess
 */
public static DatabaseAccess getInstance(Context context) {
    if (instance == null) {
        instance = new DatabaseAccess(context);
    }
    return instance;
}

/**
 * Open the database connection.
 */
public void open() {
    this.database = openHelper.getWritableDatabase();
}

/**
 * Close the database connection.
 */
public void close() {
    if (database != null) {
        this.database.close();
    }
}
The SQLiteOpenHelper is used to open the database connection. Singleton design pattern is used in this class to avoid multiple connections to the database.
The SQLiteOpenHelper.getWritableDatabase() method returns a readable and writable connection to the underlying database. If you need to read the content only, you can use the getReadableDatabase() method.

Step 6:
Add a new method insertContact to perform the Create operation.
/**
 * Insert a contact into the database.
 *
 * @param contact the contact to be inserted
 */
public void insertContact(Contact contact) {
    ContentValues values = new ContentValues();
    values.put("first_name", contact.getFirstName());
    values.put("last_name", contact.getLastName());
    values.put("phone", contact.getPhone());
    values.put("email", contact.getEmail());
    database.insert("Contact", null, values);
}

Step 7:
Following method is used to retrieve all the Contacts from the database.
/**
 * Read all contacts from the database.
 *
 * @return a List of Contacts
 */
public List<Contact> getContacts() {
    List<Contact> list = new ArrayList<>();
    Cursor cursor = database.rawQuery("SELECT * FROM Contact", null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        Contact contact = new Contact();
        contact.setFirstName(cursor.getString(0));
        contact.setLastName(cursor.getString(1));
        contact.setPhone(cursor.getString(2));
        contact.setEmail(cursor.getString(3));
        list.add(contact);
        cursor.moveToNext();
    }
    cursor.close();
    return list;
}

Step 8:
Add an updateContact method as shown below.
/**
 * Update the contact details.
 *
 * @param oldContact the old contact to be replaced
 * @param newContact the new contact to replace
 */
public void updateContact(Contact oldContact, Contact newContact) {
    ContentValues values = new ContentValues();
    values.put("first_name", newContact.getFirstName());
    values.put("last_name", newContact.getLastName());
    values.put("phone", newContact.getPhone());
    values.put("email", newContact.getEmail());
    database.update("Contact", values, "phone = ?", new String[]{oldContact.getPhone()});
}

Step 9:
Create a deleteContact method as provided below.
/**
 * Delete the provided contact.
 *
 * @param contact the contact to delete
 */
public void deleteContact(Contact contact) {
    database.delete("Contact", "phone = ?", new String[]{contact.getPhone()});
    database.close();
}

More details about these CRUD related methods are already explained in the Basic Android Database tutorial.

After the modification, final DatabaseAccess class must look like this.
package com.javahelps.sample.advanceddatabase.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.javahelps.sample.advanceddatabase.model.Contact;

import java.util.ArrayList;
import java.util.List;

public class DatabaseAccess {
    private SQLiteOpenHelper openHelper;
    private SQLiteDatabase database;
    private static DatabaseAccess instance;

    /**
     * Private constructor to aboid object creation from outside classes.
     *
     * @param context
     */
    private DatabaseAccess(Context context) {
        this.openHelper = new DatabaseOpenHelper(context);
    }

    /**
     * Return a singleton instance of DatabaseAccess.
     *
     * @param context the Context
     * @return the instance of DabaseAccess
     */
    public static DatabaseAccess getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseAccess(context);
        }
        return instance;
    }

    /**
     * Open the database connection.
     */
    public void open() {
        this.database = openHelper.getWritableDatabase();
    }

    /**
     * Close the database connection.
     */
    public void close() {
        if (database != null) {
            this.database.close();
        }
    }

    /**
     * Insert a contact into the database.
     *
     * @param contact the contact to be inserted
     */
    public void insertContact(Contact contact) {
        ContentValues values = new ContentValues();
        values.put("first_name", contact.getFirstName());
        values.put("last_name", contact.getLastName());
        values.put("phone", contact.getPhone());
        values.put("email", contact.getEmail());
        database.insert("Contact", null, values);
    }

    /**
     * Read all contacts from the database.
     *
     * @return a List of Contacts
     */
    public List<Contact> getContacts() {
        List<Contact> list = new ArrayList<>();
        Cursor cursor = database.rawQuery("SELECT * FROM Contact", null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Contact contact = new Contact();
            contact.setFirstName(cursor.getString(0));
            contact.setLastName(cursor.getString(1));
            contact.setPhone(cursor.getString(2));
            contact.setEmail(cursor.getString(3));
            list.add(contact);
            cursor.moveToNext();
        }
        cursor.close();
        return list;
    }

    /**
     * Update the contact details.
     *
     * @param oldContact the old contact to be replaced
     * @param newContact the new contact to replace
     */
    public void updateContact(Contact oldContact, Contact newContact) {
        ContentValues values = new ContentValues();
        values.put("first_name", newContact.getFirstName());
        values.put("last_name", newContact.getLastName());
        values.put("phone", newContact.getPhone());
        values.put("email", newContact.getEmail());
        database.update("Contact", values, "phone = ?", new String[]{oldContact.getPhone()});
    }

    /**
     * Delete the provided contact.
     *
     * @param contact the contact to delete
     */
    public void deleteContact(Contact contact) {
        database.delete("Contact", "phone = ?", new String[]{contact.getPhone()});
        database.close();
    }
}

Step 10:
In the Mainactivity.java, create a new instance variable of DatabaseAccess and assign the object to it, from the onCreate method.
public class MainActivity extends ActionBarActivity {
    // Other instance variables
    private DatabaseAccess databaseAccess;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // Find the GUI components
      
        this.databaseAccess = DatabaseAccess.getInstance(getApplicationContext());

        //Set event listener to Button
      
        // Set event listener to ListView
    }
}

Step 11:
Modify the getContacts method as given below.
/**
 * Read all the contacts
 *
 * @return List of Contacts
 */
private List<Contact> getContacts() {
    databaseAccess.open();
    List<Contact> list = databaseAccess.getContacts();
    databaseAccess.close();
    return list;
}

Step 12:
Create an instance variable of DatabaseAccess inside the ViewActivity.java.
public class ViewActivity extends ActionBarActivity {
    // Other instance variables
    private DatabaseAccess databaseAccess;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_view);

        // Find the GUI components
        this.databaseAccess = DatabaseAccess.getInstance(getApplicationContext());

        // Set on click listeners
    }
}

Step 13:
Modify the inserContact as shown below.
private void insertContact() {
    databaseAccess.open();
    Contact newContact = new Contact();
    newContact.setFirstName(etFirstName.getText().toString());
    newContact.setLastName(etLastName.getText().toString());
    newContact.setPhone(etPhone.getText().toString());
    newContact.setEmail(etEmail.getText().toString());

    databaseAccess.insertContact(newContact);
    databaseAccess.close();
    this.finish();
}

Step 14:
Modify the updateContact method to update an existing Contact.
private void updateContact() {
    databaseAccess.open();
    Contact newContact = new Contact();
    newContact.setFirstName(etFirstName.getText().toString());
    newContact.setLastName(etLastName.getText().toString());
    newContact.setPhone(etPhone.getText().toString());
    newContact.setEmail(etEmail.getText().toString());

    databaseAccess.updateContact(contact, newContact);
    databaseAccess.close();
    this.finish();
}

Step 15:
Finally, modify the deleteContact method as shown below.
private void deleteContact() {
    databaseAccess.open();
    databaseAccess.deleteContact(contact);
    databaseAccess.close();
    this.finish();
}

Step 16:
Save all the changes and run the application.


Find the completed project at Git Hub.


Troubleshooting Database Applications
In database related applications, it is a common practice to manually read the content of the database to troubleshoot any issues with database connections. For example, assume that even after you insert a Contact detail it is not displayed in the ListView. In this scenario, the bug can be either in Create or Read operation. The easiest way to check the problem is, opening the database manually and check whether the data is inserted successfully or not.

To manually open the database of Android application, you need to create the database first. According to this code, once you run the application, a database will be created. Once the database file is available, it can be exported to your computer using Android Device Monitor.

Step 1:
Click on the Android Device Monitor button.


Step 2:
In the opened Android Device Monitor, select the device on the left side and goto the FileExplorer tab. In the folder hierarchy, go to the following location:
data → data → com.javahelps.sample.advanceddatabase → databases



Step 3:
Select the contacts.db and click on the “Pull a file from the device” button.



Step 4:
Save the file in your desktop.

Step 5:
Open the file using any SQLite browsers. (DB Browser for SQLite is a portable cross platform freeware, which can be used to open SQLite databases)


Previous
Next Post »

15 comments

Write comments
sLYPHEN
AUTHOR
June 16, 2016 at 12:23 PM delete

unable to download the git repository

Reply
avatar
Gobinath
AUTHOR
June 16, 2016 at 1:52 PM delete

Hi,
You cannot download this specific project. However, if you download the complete repository, you will get all my Android projects including this one. To download all the Android projects click on this link: Java-Helps-Android

Reply
avatar
mukesh223
AUTHOR
June 16, 2016 at 8:13 PM delete

Hello, I was able to create a new database using CRUD operations. I am now trying to save this database on a SD card or any location. Do you have suggestions on how to go about doing this? I have tried looking that up but I was not able to get it done succesfully. Please help. Thank you in advance.

Reply
avatar
mukesh223
AUTHOR
June 23, 2016 at 7:52 PM delete

Thanks. It worked. Is there a way I can view these files that are being exported?

Reply
avatar
Meshileya Israel
AUTHOR
August 4, 2016 at 5:19 PM delete

which part of the code am i to call this???

Reply
avatar
Meshileya Israel
AUTHOR
August 4, 2016 at 5:32 PM delete

how did you call this exporttoSD???

Reply
avatar
Gobinath
AUTHOR
August 6, 2016 at 11:20 AM delete

Hi,
Add a new button to your application and call this method within the onclick event of that button.

Reply
avatar
Meshileya Israel
AUTHOR
August 7, 2016 at 4:21 AM delete

Is there no way I can update this file???....like...after every update or...data been saved...it should automatically save in this file.

Reply
avatar
Gobinath
AUTHOR
August 7, 2016 at 8:53 AM delete

If that is your requirement, call this method from the create, update and delete methods of DatabaseOpenHelper. There is no any life cycle methods in Android to do something when a database is modified.

Reply
avatar
Meshileya Israel
AUTHOR
August 8, 2016 at 3:25 PM delete

in DatabaseOpenHelper, it's just the onCreate and onUpgrade that are there...I also tried adding it to the DatabaseAccess, it isn't working..i tried databaseOpenHelper.exportSD in the ViewActivty too...still the same thing..the application is actually crashing...

I don't know, if it will be possible just to update this external file, which will serve as a back up..for every update...without having to create a button that will export all the data that are already saved in the database.

Reply
avatar
Gobinath
AUTHOR
August 8, 2016 at 4:37 PM delete

Sorry its my mistake, the class is DatabaseAccess. Add this method in DatabaseAccess and then try to call this method within the close method (After closing the connection).

Reply
avatar
Meshileya Israel
AUTHOR
August 8, 2016 at 5:00 PM delete

thanks so much..it worked at last.... i am so excited now :) :) :)

Reply
avatar

Contact Form

Name

Email *

Message *