Android Database

SQLite offers a powerful SQL database library that provides a robust persistence layer over which you have total control. Using SQLite you can create independent relational databases for your applications. Use them to store and manage complex, structured application data. 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.

Content Providers offer a generic interface to any data source by decoupling the data storage layer from the application layer. Content Providers offer a standard interface your applications can use to share data with and consume data from other applications— including many of the native data stores.

SQLite is a well regarded relational database management system (RDBMS). It is Open-source, Standards-compliant, Lightweight & Single-tier.

CURSORS AND CONTENT VALUES:ContentValues are used to insert new rows into tables. Each Content Values object represents a single
table 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 are pointers to the result set within the underlying data. Cursors provide a managed way of controlling your position (row) in the result set of a database query.
The Cursor class includes a number of navigation functions including, but not limited to, the following:
moveToFirst Moves the cursor to the first row in the query result
moveToNext Moves the cursor to the next row
moveToPrevious Moves the cursor to the previous row
getCount Returns the number of rows in the result set
getColumnIndexOrThrow Returns the index for the column with the specified name (throwing
an exception if no column exists with that name)
getColumnName Returns the name of the specified column index
getColumnNames Returns a string array of all the column names in the current Cursor
moveToPosition Moves the Cursor to the specified row
getPosition Returns the current Cursor position

Android provides a convenient mechanism for simplifying the management of Cursors within your Activities. The startManagingCursor method integrates the Cursor’s lifetime into the calling Activity’s. When you’ve finished with the Cursor, call stopManagingCursor to do just that.

Querying a Database
To execute a query on a database use the query method, 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 passed in through 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’’ filter 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 for the number of returned rows.

// 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);

Extracting values from a Cursor
int GOLD_HOARDED_COLUMN = 2;
Cursor myGold = myDatabase.query("GoldHoards", null, null, null, null, null, null);
float totalHoard = 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());
}
float averageHoard = totalHoard / myGold.getCount();

Because SQLite database columns are loosely typed, you can cast individual values into valid types as
required.

No comments:

Post a Comment