Thursday, 13 June 2013

To Do\Shopping List



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 3rd points. The first two pints 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 attribures 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 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.  
Its Done !!!  

Here, In this examples, we are using one table (NoteTable) with 4 columns  (Note id, date/time, name and content)    
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 onCreate function.
For preforming 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. Dont 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. Retreive values using getString and getInt etc.
  6. Dont 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();

No comments:

Post a Comment