Databases connectivity in android
....................................................................................
....................................................................................
Android provides full relational database capabilities through the SQLite library, without imposing any additional limitations.
Using SQLite, you can create independent, relational databases for each application. Use them to store and manage complex, structured application data.
All Android databases are stored in the /data/data/<package_name>/databases folder on your device (or emulator). By default, all databases are private, accessible only by the application that created them. To share a database across applications, use Content Providers, as shown later in this chapter.Database design is a vast topic that deserves more thorough coverage than is possible within this book.However, it’s worth highlighting that standard database best practices still apply. In particular, when creating databases for resource-constrained devices, it’s important to reduce data redundancy using normalization.
The following sections focus on the practicalities of creating and managing SQLite databases in Android.
Introducing SQLite
SQLite is a relational database management system (RDBMS). It is well regarded, being:
- Open source
- Standards-compliant
- Lightweight
- Single-tier
It has been implemented as a compact C library that’s included as part of the Android software stack.By providing functionality through a library, rather than as a separate process, each database becomes an integrated part of the application that created it. This reduces external dependencies, minimizes latency, and simplifies transaction locking and synchronization.
SQLite has a reputation of being extremely reliable and is the database system of choice for many consumer electronic devices, including several MP3 players, the iPhone, and the iPod Touch.
Lightweight and powerful, SQLite differs from many conventional database engines by using a loosely typed approach to column definitions. Rather than requiring column values to conform to a single type, the values in each row for each column are individually typed. As a result, there’s no strict type checking when assigning or extracting values from each column within a row.
Cursors and Content Values
ContentValuesobjects are used to insert new rows into database tables (and Content Providers). Each Content Values object represents a single row, as a map of column names to values.
Queries in Android are returned as Cursor objects. Rather than extracting and returning a copy of the result values, Cursors act as pointers to a subset of the underlying data. Cursors are a managed way of controlling your position (row) in the result set of a database query.
The Cursor class includes several functions to navigate query results including, but not limited to, the following:
- moveToFirstMoves the cursor to the fi rst row in the query result.
- moveToNextMoves the cursor to the next row.
- moveToPreviousMoves the cursor to the previous row.
- getCountReturns the number of rows in the result set.
- getColumnIndexOrThrowReturns an index for the column with the specified name (throwing an exception if no column exists with that name).
- getColumnNameReturns the name of the specified column index.
- getColumnNamesReturns a String array of all the column names in the current cursor.
- moveToPositionMoves the cursor to the specified row.
- getPositionReturns the current cursor position.
Android provides a mechanism to manage Cursor resources within your Activities. The startManagingCursormethod integrates the Cursor’s lifetime into the parent Activity’s lifetime management. When you’ve fi nished with the Cursor, call stopManagingCursorto do just that.Later in this chapter, you’ll learn how to query a database and how to extract specifi c row/column values from the resulting Cursor objects.
Working with Android Databases
It’s good practice to create a helper class to simplify your database interactions.
Consider creating a database adapter, which adds an abstraction layer that encapsulates database interactions.
It should provide intuitive, strongly typed methods for adding, removing, and updating items.A database adapter should also handle queries and wrap creating, opening, and closing the database.It’s often also used as a convenient location from which to publish static database constants, including table names, column names, and column indexes.
The following snippet shows the skeleton code for a standard database adapter class. It includes an extension of the SQLiteOpenHelperclass, used to simplify opening, creating, and upgrading the database.
import android.content.Context;
import android.database.*;
import android.database.sqlite.*;
import android.database.sqlite.
SQLiteDatabase.CursorFactory;
import android.util.Log;
public class MyDBAdapter {
private static final String DATABASE_NAME = “myDatabase.db”;
private static final String DATABASE_TABLE = “mainTable”;
private static final int DATABASE_VERSION = 1;
// The index (key) column name for use in where clauses.
public static final String KEY_ID=”_id”;
// The name and column index of each column in your database.
public static final String KEY_NAME=”name”;
public static final int NAME_COLUMN = 1;
// TODO: Create public field for each column in your table.
// SQL Statement to create a new database.
private static final String DATABASE_CREATE = “create table “ +
DATABASE_TABLE + “ (“ + KEY_ID +
“ integer primary key autoincrement, “ +
KEY_NAME + “ text not null);”;
// Variable to hold the database instanceprivate SQLiteDatabase db;
// Context of the application using the database.
private final Context context;
// Database open/upgrade helper
private myDbHelper dbHelper;
public MyDBAdapter(Context _context) {
context = _context;
dbHelper = new myDbHelper(context, DATABASE_NAME, null,
DATABASE_VERSION);
}
public MyDBAdapter open() throws SQLException {
db = dbHelper.getWritableDatabase();
return this;
}
public void close() {
db.close();
}
public long insertEntry(MyObject _myObject) {
ContentValuescontentValues = new ContentValues();
// TODO fill in ContentValues to represent the new row
return db.insert(DATABASE_TABLE, null, contentValues);
}
public boolean removeEntry(long _rowIndex) {
return db.delete(DATABASE_TABLE, KEY_ID +
“=” + _rowIndex, null) > 0;
}
public Cursor getAllEntries () {
return db.query(DATABASE_TABLE, new String[] {KEY_ID, KEY_NAME},
null, null, null, null, null);
}
public MyObject getEntry(long _rowIndex) {
MyObjectobjectInstance = new MyObject();
// TODO Return a cursor to a row from the database and
// use the values to populate an instance of MyObject
return objectInstance;
}
public int updateEntry(long _rowIndex, MyObject _myObject) {
String where = KEY_ID + “=” + _rowIndex;
Content ValuescontentValues = new ContentValues();
// TODO fill in the ContentValue based on the new object
return db.update(DATABASE_TABLE, contentValues, where, null);
}
private static class myDbHelper extends SQLiteOpenHelper {
super(context, name, factory, version);
}
// Called when no database exists in
// disk and the helper class needs
// to create a new one.
@Override
public void onCreate(SQLiteDatabase _db) {
_db.execSQL(DATABASE_CREATE);
}
// Called when there is a database version mismatch meaning that
// the version of the database on disk needs to be upgraded to
// the current version.
@Override
public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
int _newVersion) {
// Log the version upgrade.
Log.w(“TaskDBAdapter”, “Upgrading from version “ +
_oldVersion + “ to “ +
_newVersion +
“, which will destroy all old data”);
// Upgrade the existing database to conform to the new version.
// Multiple previous versions can be handled by comparing
// _oldVersion and _newVersion values.
// The simplest case is to drop the old table and create a
// new one.
_db.execSQL(“DROP TABLE IF EXISTS “ + DATABASE_TABLE);
// Create a new one.
onCreate(_db);
}
}
}
import android.database.*;
import android.database.sqlite.*;
import android.database.sqlite.
SQLiteDatabase.CursorFactory;
import android.util.Log;
public class MyDBAdapter {
private static final String DATABASE_NAME = “myDatabase.db”;
private static final String DATABASE_TABLE = “mainTable”;
private static final int DATABASE_VERSION = 1;
// The index (key) column name for use in where clauses.
public static final String KEY_ID=”_id”;
// The name and column index of each column in your database.
public static final String KEY_NAME=”name”;
public static final int NAME_COLUMN = 1;
// TODO: Create public field for each column in your table.
// SQL Statement to create a new database.
private static final String DATABASE_CREATE = “create table “ +
DATABASE_TABLE + “ (“ + KEY_ID +
“ integer primary key autoincrement, “ +
KEY_NAME + “ text not null);”;
// Variable to hold the database instanceprivate SQLiteDatabase db;
// Context of the application using the database.
private final Context context;
// Database open/upgrade helper
private myDbHelper dbHelper;
public MyDBAdapter(Context _context) {
context = _context;
dbHelper = new myDbHelper(context, DATABASE_NAME, null,
DATABASE_VERSION);
}
public MyDBAdapter open() throws SQLException {
db = dbHelper.getWritableDatabase();
return this;
}
public void close() {
db.close();
}
public long insertEntry(MyObject _myObject) {
ContentValuescontentValues = new ContentValues();
// TODO fill in ContentValues to represent the new row
return db.insert(DATABASE_TABLE, null, contentValues);
}
public boolean removeEntry(long _rowIndex) {
return db.delete(DATABASE_TABLE, KEY_ID +
“=” + _rowIndex, null) > 0;
}
public Cursor getAllEntries () {
return db.query(DATABASE_TABLE, new String[] {KEY_ID, KEY_NAME},
null, null, null, null, null);
}
public MyObject getEntry(long _rowIndex) {
MyObjectobjectInstance = new MyObject();
// TODO Return a cursor to a row from the database and
// use the values to populate an instance of MyObject
return objectInstance;
}
public int updateEntry(long _rowIndex, MyObject _myObject) {
String where = KEY_ID + “=” + _rowIndex;
Content ValuescontentValues = new ContentValues();
// TODO fill in the ContentValue based on the new object
return db.update(DATABASE_TABLE, contentValues, where, null);
}
private static class myDbHelper extends SQLiteOpenHelper {
super(context, name, factory, version);
}
// Called when no database exists in
// disk and the helper class needs
// to create a new one.
@Override
public void onCreate(SQLiteDatabase _db) {
_db.execSQL(DATABASE_CREATE);
}
// Called when there is a database version mismatch meaning that
// the version of the database on disk needs to be upgraded to
// the current version.
@Override
public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
int _newVersion) {
// Log the version upgrade.
Log.w(“TaskDBAdapter”, “Upgrading from version “ +
_oldVersion + “ to “ +
_newVersion +
“, which will destroy all old data”);
// Upgrade the existing database to conform to the new version.
// Multiple previous versions can be handled by comparing
// _oldVersion and _newVersion values.
// The simplest case is to drop the old table and create a
// new one.
_db.execSQL(“DROP TABLE IF EXISTS “ + DATABASE_TABLE);
// Create a new one.
onCreate(_db);
}
}
}
Using the SQLiteOpenHelper
SQLiteOpenHelperis an abstract class that wraps up the best practice pattern for creating, opening,and upgrading databases. By implementing and using anSQLiteOpenHelper, you hide the logic used to decide if a database needs to be created or upgraded before it’s opened.
The code snippet above shows how to extend the SQLiteOpenHelperclass by overriding the constructor,onCreate, and onUpgrademethods to handle the creation of a new database and upgrading to a new version, respectively.
In the previous example, onUpgradesimply drops the existing table and replaces it with the new definition. In practice, a better solution is to migrate existing data into the new table.To use an implementation of the helper class, create a new instance, passing in the context, database name, current version, and a CursorFactory(if you’re using one)
Call getReadableDatabase or getWriteableDatabase to open and return a readable/writable instance of the database.
A call to getWriteableDatabasecan fail because of disk space or permission issues, so it’s good practice to provide fallback to the getReadableDatabasemethod as shown below:
dbHelper = new myDbHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
SQLiteDatabasedb;
try {
db = dbHelper.getWritableDatabase();
}
catch (SQLiteException ex){
db = dbHelper.getReadableDatabase();
}
SQLiteDatabasedb;
try {
db = dbHelper.getWritableDatabase();
}
catch (SQLiteException ex){
db = dbHelper.getReadableDatabase();
}
Behind the scenes, if the database doesn’t exist, the helper executes its onCreatehandler. If the database version has changed, the onUpgradehandler will fi re. In both cases, the get <read/write> ableDatabase call will return the existing, newly created, or upgraded database as appropriate.
Opening and Creating Databases without the SQLiteHelper
You can create and open databases without using the SQLiteHelperclass with the openOrCreateDatabasemethod on the application Context.
Setting up a database is a two-step process. First, call openOrCreateDatabaseto create the new database.Then, call execSQLon the resulting database instance to run the SQL commands that will create your tables and their relationships. The general process is shown in the snippet below:
private static final String DATABASE_NAME = “myDatabase.db”;
private static final String DATABASE_TABLE = “mainTable”;
private static final String DATABASE_CREATE =
“create table “ + DATABASE_TABLE +
“ ( _id integer primary key autoincrement,” +
“column_one text not null);”;
SQLiteDatabasemyDatabase;
private void createDatabase() {
myDatabase = openOrCreateDatabase(DATABASE_
NAME, Context.MODE_PRIVATE, null);
myDatabase.execSQL(DATABASE_
CREATE); }
private static final String DATABASE_TABLE = “mainTable”;
private static final String DATABASE_CREATE =
“create table “ + DATABASE_TABLE +
“ ( _id integer primary key autoincrement,” +
“column_one text not null);”;
SQLiteDatabasemyDatabase;
private void createDatabase() {
myDatabase = openOrCreateDatabase(DATABASE_
NAME, Context.MODE_PRIVATE, null);
myDatabase.execSQL(DATABASE_
CREATE); }
Querying Your Database
All database queries are returned as a Cursor to a result set. This lets Android manage resources more efficiently by retrieving and releasing row and column values on demand.
To execute a query on a database, use the query method on the database object, passing in:
- An optional Boolean that specifies if the result set should contain only unique values
- The name of the table to query
- A projection, as an array of Strings, that lists the columns to include in the result set
- A “where” clause that defines the rows to be returned. You can include ?wildcards that will be replaced by the values stored in the selection argument parameter.
- An array of selection argument strings that will replace the ?’s in the “where” clause
- A “group by” clause that defines how the resulting rows will be grouped
- A “having” fi lter that defines which row groups to include if you specified a “group by” clause
- A String that describes the order of the returned rows
- An optional String that defines a limit to the returned rows The following skeleton code shows snippets for returning some, and all, of the rows in a particular table:
// Return all rows for columns one and three, no duplicates
String[] result_columns = new String[] {KEY_ID, KEY_COL1, KEY_COL3};
Cursor allRows = myDatabase.query(true, DATABASE_TABLE, result_columns,
null, null, null, null, null, null);
// Return all columns for rows where column 3 equals a set value
// and the rows are ordered by column 5.
String where = KEY_COL3 + “=” + requiredValue;
String order = KEY_COL5;
Cursor myResult = myDatabase.query(DATABASE_TABLE, null, where,
null, null, null, order);
String[] result_columns = new String[] {KEY_ID, KEY_COL1, KEY_COL3};
Cursor allRows = myDatabase.query(true, DATABASE_TABLE, result_columns,
null, null, null, null, null, null);
// Return all columns for rows where column 3 equals a set value
// and the rows are ordered by column 5.
String where = KEY_COL3 + “=” + requiredValue;
String order = KEY_COL5;
Cursor myResult = myDatabase.query(DATABASE_TABLE, null, where,
null, null, null, order);
In practice, it’s often useful to abstract these query commands within an adapter class to simplify data access.
Extracting Results from a Cursor
To extract actual values from a result Cursor, fi rst use the moveTo <location> methods described previously to position the Cursor at the correct row of the result set.
With the Cursor at the desired row, use the type-safe getmethods (passing in a column index) to return the value stored at the current row for the specified column, as shown in the following snippet:
String columnValue = myResult.getString(columnIndex);
Database implementations should publish static constants that provide the column indexes using more easily recognizable variables based on the column names. They are generally exposed within a database adapter as described previously.
The following example shows how to iterate over a result cursor, extracting and summing a column offl oats:
int GOLD_HOARDED_COLUMN = 2;
Cursor myGold = myDatabase.query(“GoldHoards”, null, null, null, null,
null, null);
floattotalHoard = 0f;
// Make sure there is at least one row.
if (myGold.moveToFirst()) {
// Iterate over each cursor.
do {
float hoard = myGold.getFloat(GOLD_HOARDED_
COLUMN); totalHoard += hoard;
} while(myGold.moveToNext());
}
floataverageHoard = totalHoard / myGold.getCount();
Cursor myGold = myDatabase.query(“GoldHoards”, null, null, null, null,
null, null);
floattotalHoard = 0f;
// Make sure there is at least one row.
if (myGold.moveToFirst()) {
// Iterate over each cursor.
do {
float hoard = myGold.getFloat(GOLD_HOARDED_
COLUMN); totalHoard += hoard;
} while(myGold.moveToNext());
}
floataverageHoard = totalHoard / myGold.getCount();
Because SQLite database columns are loosely typed, you can cast individual values into valid types as required. For example, values stored as floats can be read back as Strings.
Adding, Updating, and Removing Rows
The SQLiteDatabaseclass exposes specialized insert, delete, and update methods to encapsulate the SQL statements required to perform these actions. Nonetheless, the execSQLmethod lets you execute any valid SQL on your database tables should you want to execute these operations manually. Any time you modify the underlying database values, you should call refreshQueryon any Cursors that currently have a view on the table
Inserting New Rows
To create a new row, construct a ContentValuesobject, and use its put methods to supply values for each column. Insert the new row by passing the Content Values object into the insert method called on the target database object — along with the table name — as shown in the snippet below:
// Create a new row of values to insert.
ContentValuesnewValues = new ContentValues();
// Assign values for each row.
newValues.put(COLUMN_NAME, newValue);
[ ... Repeat for each column ... ]
// Insert the row into your table
myDatabase.insert(DATABASE_TABLE, null, newValues);
ContentValuesnewValues = new ContentValues();
// Assign values for each row.
newValues.put(COLUMN_NAME, newValue);
[ ... Repeat for each column ... ]
// Insert the row into your table
myDatabase.insert(DATABASE_TABLE, null, newValues);
Create a new ContentValuesobject, using the put methods to assign new values to each column you want to update. Call update on the database object, passing in the table name, the updated Content Values object, and a where statement that returns the row(s) to update. The update process is demonstrated in the snippet below:
// Define the updated row content.
ContentValuesupdatedValues = new ContentValues();
// Assign values for each row.
updatedValues.put(COLUMN_NAME, newValue);
[ ... Repeat for each column ... ]
String where = KEY_ID + “=” + rowId;
// Update the row with the specified index with the new values.
myDatabase.update(DATABASE_TABLE, updatedValues, where, null);
ContentValuesupdatedValues = new ContentValues();
// Assign values for each row.
updatedValues.put(COLUMN_NAME, newValue);
[ ... Repeat for each column ... ]
String where = KEY_ID + “=” + rowId;
// Update the row with the specified index with the new values.
myDatabase.update(DATABASE_TABLE, updatedValues, where, null);
Deleting Rows
To delete a row, simply call delete on your database object, specifying the table name and a where clause that returns the rows you want to delete, as shown in the code below:
myDatabase.delete(DATABASE_TABLE, KEY_ID + “=” + rowId, null);
