Android - To Do\Shopping List [Tip/Trick] - Part 2

Introduction

This tip shows how a Shopping\To do list application is created. It gives an idea of the following:

1. Adding a list Item in Application
2. Opening Dialog from Android Activity
3. Working with SQlite
4. How to return data from one activity to another

In this tip, we would be discussing about the third point. The first two points are discussed here.

Using the Code

Working with SQLite

SQLite

A software library that implements a SQL database engine. It is fast and lightweight, hence best for Mobile devices. The database is stored within a single file. Every Android application can have its own private database. It is being used by many well-known groups/brands like Adobe, Airbus, Apple, Dropbox, General Electric, Google, Intuit, McAfee, Microsoft, php, python, skype, Toshiba, etc.

Database Fundamentals

Database is a collection of structured data organized in the form of tables. Table contains columns to define the attributes of data and rows contain the data records. We can inspect and manipulate database data especially in 4 ways:

• Add – using INSERT command
• Delete - using DELETE command
• Modify - using UPDATE command
• View – using SELECT command

Using SQLite in Android

Android provides us with a few classes, which can be used while communicating with SQLite:

1. SQLiteOpenHelper
2. SQLitedatabase
3. Cursor

Steps

1. Derive a helper class from SQLiteOpenHelper.
2. Override OnCreate method and write query for creating the database and its schema.
3. In the helper class, write methods for the following:
• Inserting new records
• Deleting records
• Updating records
• Reading Records

It's done !!!

Here, in this example, we are using one table (NoteTable) with 4 columns (Note id, date/time, name and content).

Here is the sample query for onCreate method for creating database schema.

 String sCreateSQL = "CREATE TABLE IF NOT EXISTS " + 
CommonClass.TABLE_NAME + "( " + CommonClass.COL_SRNO
                + " INTEGER PRIMARY KEY, " + 
                CommonClass.COL_DATETIME + " TEXT, "
                + CommonClass.COL_NOTENAME + " TEXT, " + 
                CommonClass.COL_NOTECONTENT + " TEXT )";
        db.execSQL(sCreateSQL);

db is an object of type SQLiteDatabase, which is passed as a parameter in <code>onCreate function.

For performing any of the operations on data (like insert, update, delete), we need to get an object of SQLiteDatabase. In the derived helper class, by using this object, we can call either getWritableDatabase or getReadabledatabase (depending on our requirement) for getting the database object.

Insert\Update\Delete Records

1. Get the database object using getWritableDatabase.
2. Use ContentValues for adding data corresponding to the columns.
3. Execute using insert\update\delete function.
4. Don't forget to close the database.

Sample Code

SQLiteDatabase db = this.getWritableDatabase();  //get database Object

//Use contentValues for Insert and update

ContentValues contentvalue = new ContentValues();
contentvalue.put(CommonClass.COL_DATETIME, objShoWData.get_DateTime());
contentvalue.put(CommonClass.COL_NOTECONTENT, objShoWData.get_NoteData());
contentvalue.put(CommonClass.COL_NOTENAME, objShoWData.get_NoteName());

db.insert(CommonClass.TABLE_NAME, null, contentvalue); 

OR

db.update(CommonClass.TABLE_NAME, 
contentvalue, CommonClass.COL_SRNO + " = ?",
new String[] { String.valueOf(objShowdata.get_NoteId()) });

OR

db.delete(CommonClass.TABLE_NAME, CommonClass.COL_SRNO + " = ?",
new String[] { String.valueOf(nId) });

db.close(); //Close database

View Existing Records

1. Get the database object using getReadableDatabase.
2. Write the SELECT statement.
3. Use rawQuery function to execute the SELECT Query.
4. Iterate the cursor object returned by the rawQuery function.
5. Retrieve values using getString and getInt, etc.
6. Don't forget to close the database.

It's done!!!

SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

if (cursor.moveToFirst()) {
do {
    ShowData objShowData = new ShowData();
    objShowData.set_NoteId(cursor.getInt(0));
    objShowData.set_DateTime(cursor.getString(1));
...  ... //Read values from cursor....
    listShowData.add(objShowData);
    } while (cursor.moveToNext());
}
db.close();

History

• 12^th June 2013: Initial release