Introduction
This tip shows how a Shopping\To do
list application is created. It gives an idea of the following:
- Adding a list Item in Application.
- Opening Dialog from Android Activity.
- Working with SQlite.
- 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
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:
- SQLiteOpenHelper
- SQLitedatabase
- Cursor
Steps:
- Derive a helper class from SQLiteOpenHelper.
- Override OnCreate method and write query for creating the database and its schema.
- 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:
- Get the database object using getWritableDatabase.
- Use ContentValues for adding data corresponding to the columns.
- Execute using insert\update\delete function.
- 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:
- Get the database object using getReadableDatabase.
- Write the SELECT statement.
- Use rawQuery function to execute the SELECT Query.
- Iterate the cursor object returned by the rawQuery function.
- Retreive values using getString and getInt etc.
- 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();
