There are 4 ways of storing data on the android
platform:
- 1.
Preferences
- 2.
SQLite Database
- 3.
Files
- 4.
Network
A word about each of them here and then I will
move on to an example that shows how to work with SQLite DB that comes along
with the android platform.
Preferences
–
Basically used for storing user preferences for a
single application or across applications for a mobile. This is typically
name-value pairs accessible to the context.
Databases –
Android supports creating of databases based on SQLite db.
Each database is private to the applications that creates it
Files –
Files can be directly stored on the mobile or on
to an extended storage medium. By default other applications cannot access it.
Network –
Data can be stored and retrieved from the network
too depending on the availability.
If an application wants to store and retrieve data
for its own use, without having to share the data across applications, it can
access the SQLite DB directly. There is no need of a content provider. We have
seen in an earlier
post how to use content providers.
In this example, we will do the following:
1.
Create a database (typically a one
time activity)
2.
Create a table (typically a one time
activity)
3.
Insert values into the table
4.
Retrieve the values from the table
5.
Display the retrieved values as a List
view
6.
Delete all the records from the table
before closing the connection to the database
Step 1: Create a database:
sampleDB =
this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);
This opens a database defined in the constant SAMPLE_DB_NAME,
if it already exists. Else it creates a database and opens it. The second parameter
is operating mode : MODE_PRIVATE meaning it is
accessible to only this context. The other modes are and MODE_WORLD_WRITABLE. MODE_WORLD_READABLE
Step 2: Create a Table:
sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
SAMPLE_TABLE_NAME +
" (LastName VARCHAR, FirstName VARCHAR," +
" Country VARCHAR, Age INT(3));");
Step 3: Insert values into the table:
sampleDB.execSQL("INSERT INTO " +
SAMPLE_TABLE_NAME +
" Values ('Makam','Sai Geetha','India',25);");
Step 4: Retrieve values
Cursor c =
sampleDB.rawQuery("SELECT FirstName, Age FROM " +
SAMPLE_TABLE_NAME +
" where Age > 10 LIMIT 5", null);
if (c != null ) {
if
(c.moveToFirst()) {
do {
String firstName = c.getString(c.getColumnIndex("FirstName"));
int age = c.getInt(c.getColumnIndex("Age"));
results.add("" + firstName + ",Age:
" + age);
}while (c.moveToNext());
}
}
Step 5: Display the values as a list
this.setListAdapter(new
ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));
The statement displays it as a list as the class
extends a ListActivity.
Step 6: Delete the values from the table in the
finally part of the try block
finally {
if (sampleDB != null)
sampleDB.execSQL("DELETE FROM " + SAMPLE_TABLE_NAME);
sampleDB.close();
}
It is as simple as this to work with the SQLite DB
even in android. No different from a desktop application. However, there are various
overloaded methods of query() provided by the
SQLIteDatabase class which can be more optimally used instead of execSQL.
The complete code for this example is downloadable
here.
