Friday, December 16, 2011

Content Providers



 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;


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

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






 


9 comments:

  1. Perfect, I couldn't find how to use the "GROUP BY" for SMS content provider until reading this, worked perfectly. Thanks!

    ReplyDelete
  2. You so totally saved my day! Thank you so much!

    ReplyDelete
  3. Great work Thanks a lot for share it!!!

    ReplyDelete
  4. Thannk you very much....i had searched every where but
    no one explained like u....

    ReplyDelete
  5. How to get all the records with group by in cotentprovider, not just those two categories. Please share it

    ReplyDelete
  6. Interesting approach about content provider..Keep sharing..Android Training in velachery

    ReplyDelete
  7. It is really a great work and the way in which u r sharing the knowledge is excellent.Thanks for helping me to understand content provider concepts. As a beginner in android programming your post help me a lot.Thanks for your informative article.
    Android Training in velachery | Android Training institute in chennai

    ReplyDelete