Basic Android Database

Android has a built in SQLite database, which is used to store local information. This article shows you, the quick and dirty way to use database in your application.


Step 1:
Download and open the Android project provided at this link.
This application is a partially completed application, where all the GUI and other GUI related functionalities are already developed. Only the database related Create, Read, Update and Delete (CRUD) operations are left uncompleted.

Step 2: Create Database
Goto the MainActivity.java, createDatabase method. This is the place to create a database and a table if not exist. Modify the method as shown below.
private void createDatabase() {
    SQLiteDatabase database = openOrCreateDatabase("contacts.db", Context.MODE_PRIVATE, null);
    database.execSQL("CREATE TABLE IF NOT EXISTS Contact(first_name TEXT, last_name TEXT, phone TEXT PRIMARY KEY, email TEXT);");
    database.close();
}
ContextWrapper.openOrCreateDatabase method is used to create a new database if it is not exists, or to open the existing database at the provided location. Context.MODE_PRIVATE is used to declare that the created database file must be local to the application only. It is recommended to keep the database file as a private file of the application. The third parameter is a reference for CursorFactory object. This object will be used to create custom Cursor objects. This value is passed as null reference, since there are no needs for a custom Cursor object (We will receive a default Cursor object when reading from database).

SQLiteDatabase.execSQL method is used to execute an SQL query on the database. The SQLite database queries are much similar to MySQL database queries. However, there are some features which are not supported by SQLite database. For more details about SQLite database, visit to this official link.

The most important thing is, closing the opened connection. Whenever, a resource is opened, it is recommended to close. In Android, if a database connection is not closed, you will get a runtime error.

Step 3: Insert
Open the ViewActivity.java and goto the insertContact method. In this method, we need to write the code to insert a Contact detail into the database.
Modify the insertContact method as shown here.
private void insertContact() {
    SQLiteDatabase database = openOrCreateDatabase("contacts.db", Context.MODE_PRIVATE, null);
    ContentValues values = new ContentValues();
    values.put("first_name", etFirstName.getText().toString());
    values.put("last_name", etLastName.getText().toString());
    values.put("phone", etPhone.getText().toString());
    values.put("email", etEmail.getText().toString());
    database.insert("Contact", null, values);
    database.close();
    this.finish();
}
ContentValues is a wrapper object, used to keep a set of key-value pairs. Here the key must be a valid column name of the database table and the value can be any valid data to be stored.

SQLiteDatabase.insert method receives the table name as the first parameter and the ContentValues as the third parameter. The second parameter nullColumnHack, is used to provide a nullable column name in case of empty row insertion. More details about nullColumnHack is taken from the API documentation and provided below.
SQL doesn't allow inserting a completely empty row without naming at least one column name.  If your provided 'values' is 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' is empty.

Step 4: Read
Open the MainActivity.java's getContacts method and modify it as shown below.
private List<Contact> getContacts() {
    List<Contact> list = new ArrayList<>();
    SQLiteDatabase database = openOrCreateDatabase("contacts.db", Context.MODE_PRIVATE, null);
    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();
    database.close();
    return list;
}
The rawQuery method is same as execSQL, used to execute an SQL query on the database, but a result is expected from the rawQuery method. The returned result is a Cursor object, which is a wrapper object of the returned rows. This code iterates through the cursor and creates new contacts using the returned details.

rawQuery method has two parameters. The first one is the SQL query as String and the second one is the parameters for SQL query as an array of String. To avoid SQL injection, it is recommended to use prepared statements. The second parameter is used for prepared statement's parameters. (updateContact method uses this feature)

Step 5: Update
Open the ViewActivity.java's updateContact method and modify it as shown below.
private void updateContact() {
    SQLiteDatabase database = openOrCreateDatabase("contacts.db", Context.MODE_PRIVATE, null);
    ContentValues values = new ContentValues();
    values.put("first_name", etFirstName.getText().toString());
    values.put("last_name", etLastName.getText().toString());
    values.put("phone", etPhone.getText().toString());
    values.put("email", etEmail.getText().toString());
    database.update("Contact", values, "phone = ?", new String[]{contact.getPhone()});
    database.close();
    this.finish();
}
In this code, the first parameter of  SQLiteDatabase.update method is the table name. The second parameter is the ContentValues. The third parameter is the 'where clause' and the last parameter is an array of parameters for prepared statement. In this code, the returned value of contact.getPhone() is used as the where clause parameter.

Step 6: Delete
Modify the deleteContact method of the ViewActivity.java as provided here.
private void deleteContact() {
    SQLiteDatabase database = openOrCreateDatabase("contacts.db", Context.MODE_PRIVATE, null);
    database.delete("Contact", "phone = ?", new String[]{contact.getPhone()});
    database.close();
    this.finish();
}
The SQLiteDatabase.delete method needs the table name, where clause and the parameters for prepared statement respectively.

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


Note:
Since this is a quick and dirty tip for beginners, all the database related codes are written inside the activity classes. The recommended design will be discussed in another tutorial.

Find the projects at Git Hub.
Previous
Next Post »

Contact Form

Name

Email *

Message *