Content Providers

http://developer.android.com/guide/topics/providers/content-providers.html

 Content providers expose their data as a simple table on a database model, where each row is a record and each column is data of a particular type and meaning. So you can create Content Provider that will work with database stored under /data/data/package_name/databases directory.

External Tools for database review

Sometimes it may be helpful to inspect content of database with external tools.

I would recommend the following tools:

-       SQLite Database Browser (http://sqlitebrowser.sourceforge.net/) is a freeware, public domain, open source visual tool used to create, design and edit database files compatible with SQLite.

 

-       CellObject Android DevTools (http://www.questoid.com/)  is an Eclipse plug-in tool built on top of Dalvik Debug Monitor Server (DDMS), intended for Android application developers to browse SQLite and shared preferences XML files on Android device emulator.

You can select database in File Explorer View and press SQLite Browser icon (marked with red circle)

 

 

Questoid SQLite Browser View will be opened:

Some examples of query requests to Content Provider

Query() function in Content Provider has the following signature:

public abstract Cursor query (Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) 

Parameters

uri	The URI to query. This will be the full URI sent by the client; if the client is requesting a specific record, the URI will end in a record number that the implementation should parse and add to a WHERE or HAVING clause, specifying that _id value.
projection	The list of columns to put into the cursor. If null all columns are included.
selection	A selection criteria to apply when filtering rows. If null then all rows are included.
selectionArgs	You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
sortOrder	How the rows in the cursor should be sorted. If null then the provider is free to define the sort order.

Using SUM function 

SUM function should be placed in projection parameter:

String[] projection = new String[] { "sum(" + columnName + ")" };

Cursor cur = getContentResolver().query(                  DataContentProvider.CONTENT_URI, projection, selection, null,

                null);

int columnSum = cur.getInt(0);

Using GROUP BY clause

GROUP BY should be placed in selection parameter:

String[] projection = new String[] { columnNameA,

                "sum(" + columnNameB + ") AS total_B" };

String  selection = columnNameA + " LIKE 'A'"

                + ") GROUP BY (" + columnNameA;

or

String selection = "0 == 0) GROUP BY (" + columnNameA;

See reference page: http://zengyan2012.iteye.com/blog/1118963

Let’s assume we have the following table:

The SQL query

SELECT CATEGORY, SUM(ON_HAND) AS TOTAL_ON_HAND

 FROM COMPACT_DISC_STOCK

 GROUP BY CATEGORY;

Could be written using Content Provider as:

String[] projection = new String[] { DataContentProvider.CATEGORY,

                "SUM(" + DataContentProvider.ON_HAND + ") AS TOTAL_ON_HAND" };

        String selection = "0 == 0) GROUP BY (" + DataContentProvider.CATEGORY;

        Cursor cur = getContentResolver().query(

                DataContentProvider.CONTENT_URI, projection, selection, null,

                null);

and we will get the following result table:

CATEGORY         TOTAL_ON_HAND

Instrumental                  34

Vocal                               26

The SQL query:

SELECT CATEGORY, SUM(ON_HAND) AS TOTAL_ON_HAND

 FROM COMPACT_DISC_STOCK

 WHERE PRICE < 11.00

 GROUP BY CATEGORY;

 

Could be written using Content Provider as:

String[] projection = new String[] { DataContentProvider.CATEGORY,

                "SUM(" + DataContentProvider.ON_HAND + ") AS TOTAL_ON_HAND" };

        String selection = DataContentProvider.PRICE + " < 11.00) GROUP BY (" + DataContentProvider.CATEGORY;

        Cursor cur = getContentResolver().query(

                DataContentProvider.CONTENT_URI, projection, selection, null,

                null);

and we will get the following result table:

CATEGORY         TOTAL_ON_HAND

Instrumental                  23

Vocal                               8

Using Date and Time in queries

Reference link: http://sberka.blogspot.com/2009/07/date-time-sqlite-and-android.html

For example we have table where one of the columns has type DATE

db.execSQL("CREATE TABLE " + ACTIVITIES_TABLE_NAME + " (" + _ID

                    + " INTEGER PRIMARY KEY AUTOINCREMENT, "

                    + ACTIVITY_NAME + " TEXT NOT NULL, "

                    + START_DATE + " DATE DEFAULT CURRENT_TIMESTAMP, "

                    + DURATION + " INTEGER" + ");");


We have cursor:

Cursor cur = getContentResolver().query(

                DataContentProvider.CONTENT_URI, null, null, null, null);

We could receive date with the following methods:

String dateTime = cur.getString(2);

Date date;

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

try {

    date = sdf.parse(dateTime);

} catch (ParseException e) {

    Log.e(TAG, "Parsing datetime failed", e);

}