How to implement a content provider with more than one table?

Question

update: looking at "vnd.android.cursor.dir/vnd.google.note" and "vnd.android.cursor.item/vnd.google.note" it seemed to me as though the cursor was for one table.

From the examples it appears as though content provider were designed to work with one table. I do know how to use multiple tables in sqlite but it seems to me that the content provider seems to be about picking one row or multiple rows from one table.

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

Also, see the notepad sample in adt-bundle-windows-x86-20131030\sdk\samples\android-19\legacy\NotePad\src\com\example\android\notepad

Suppose I want to have notes by topic.

I would like to have a Topics table with columns _id and Title_text. I would like to have the Notes table with columns _id and foreign key Topic_id and Note_text.

How would one design the Topics and Notes?

But looking at the Notes sample, the content URIs and docs on content providers, it appears as though having multiple related tables is an afterthought and is not obvious to me.

from NotepadProvider.java, Notepad.java:

    public static final String CONTENT_TYPE = "vnd.android.cursor.dir/vnd.google.note";

        /**
         * The MIME type of a {@link #CONTENT_URI} sub-directory of a single
         * note.
         */
        public static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/vnd.google.note";

                public static final Uri CONTENT_ID_URI_BASE
            = Uri.parse(SCHEME + AUTHORITY + PATH_NOTE_ID);

        /**
         * The content URI match pattern for a single note, specified by its ID. Use this to match
         * incoming URIs or to construct an Intent.
         */
        public static final Uri CONTENT_ID_URI_PATTERN
            = Uri.parse(SCHEME + AUTHORITY + PATH_NOTE_ID + "/#");



               @Override
   public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,
           String sortOrder) {
            ...
                   switch (sUriMatcher.match(uri)) {

           // If the incoming URI is for notes, chooses the Notes projection
           case NOTES:
               qb.setProjectionMap(sNotesProjectionMap);
               break;

           /* If the incoming URI is for a single note identified by its ID, chooses the
            * note ID projection, and appends "_ID = <noteID>" to the where clause, so that
            * it selects that single note
            */
           case NOTE_ID:
               qb.setProjectionMap(sNotesProjectionMap);
               qb.appendWhere(
                   NotePad.Notes._ID +    // the name of the ID column
                   "=" +
                   // the position of the note ID itself in the incoming URI
                   uri.getPathSegments().get(NotePad.Notes.NOTE_ID_PATH_POSITION));
               break;

@pskink I certainly do know how to use multiple tables in sqlite. The content provider seems to be about picking one row or multiple rows from one table. — likejudo, May 24 '14 at 15:03
why from one table? just return a Cursor that selects rows from multiple tables, what's the problem here? — pskink, May 24 '14 at 15:10
"How would one design the Topics and Notes?" -- the way the code you pasted in does would be one approach. You might consider looking at the AOSP apps that have providers to see how they are approached in those apps. A Google search on `android contentprovider "multiple tables"` turns up yet more ideas, including some duplicate SO questions, such as http://stackoverflow.com/questions/13572352/own-contentprovider-with-sqlite-and-multiple-tables and http://stackoverflow.com/questions/3814005/best-practices-for-exposing-multiple-tables-using-content-providers-in-android. — CommonsWare, May 24 '14 at 15:17
@pskink looking at `"vnd.android.cursor.dir/vnd.google.note"` and `"vnd.android.cursor.item/vnd.google.note"` it seemed as though the cursor was for one table. — likejudo, May 25 '14 at 01:38

score 12 · Accepted Answer · answered May 24 '14 at 18:38

When creating a ContentProvider, the expectation is that other apps are going to use your database, and with that I mean other people who know nothing about your database scheme. To make things easy for them, you create and document your URIs:

To access all the books

content://org.example.bookprovider/books

to access books by id

content://org.example.bookprovider/books/#

to access books by author name

content://org.example.bookprovider/books/author

Create as many URIs as you need, that’s up to you. This way the user of your Provider can very easily access your database info, and maybe that’s why you are getting the impression that the Provider is designed to work with one table databases, but no, internally is where the work is done.

In your ContentProvider subclass, you can use a UriMatcher to identify those different URIs that are going to be passed to your ContentProvider methods (query, insert, update, delete). If the data the Uri is requesting is stored in several tables, you can actually do the JOINs and GROUP BYs or whatever you need with SQLiteQueryBuilder , e.g.

public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
    SQLiteQueryBuilder mQueryBuilder = new SQLiteQueryBuilder();
    . . .   
    String Joins = " t1 INNER JOIN table2 t2 ON t2._id = t1._id"
    + " INNER JOIN table3 t3 ON t3._id = t1._id";

    switch (mUriMatcher.match(uri)) {
        case DATA_COLLECTION_URI:
            mQueryBuilder.setTables(YourDataContract.TABLE1_NAME + Joins);
            mQueryBuilder.setProjectionMap(. . .);
            break;
        case SINGLE_DATA_URI:
            mQueryBuilder.setTables(YourDataContract.TABLE1_NAME + Joins);
            mQueryBuilder.setProjectionMap(. . .);
            mQueryBuilder.appendWhere(Table1._ID + "=" + uri.getPathSegments().get(1));
            break;
        case . . .
        default:
            throw new IllegalArgumentException("Unknown URI " + uri);
    }
    . . .
    SQLiteDatabase db = mOpenHelper.getReadableDatabase();
    Cursor c = mQueryBuilder.query(db, projection, selection, selectionArgs, groupBy, having, orderBy);
    return c;
}

Hope it helps.

In your example, can the projection map refer to different tables? say, `t1.book, t2.author` — likejudo, May 25 '14 at 02:23
Yes, create one projection map mapping all the columns the user may request from different tables. — ILovemyPoncho, May 25 '14 at 02:54

user2922360 · Answer 2 · 2014-05-24T15:12:13.847

Excuse me, but I don't understand your question.

ContentProvider is designed (a one of it's aims)to wrap access to your tabels. Design of database schema is up to you.

Generally, you need to:

Define your tables/ It should be made by execution of sql command in class which extends SQLiteOpenHelper
Define an uri for them
Define a logic for queries to this tables as it was made for NOTE_ID

Update For JOIN operations SQLiteQueryBuilder is usually used. In setTables() you need to write names of tables with JOIN clause, e.g.

.setTables(NoteColumns.TABLENAME +
            " LEFT OUTER JOIN " + TopicColumns.TABLENAME + " ON " +
            NoteColumns.ID + " = " + TopicColumns.ID);

score 0 · Answer 3 · answered May 02 '17 at 11:57

Here is my code for multiple table query in content provider with projectionMap

//HashMap for Projection
 mGroupImageUri = new HashMap<>();
    mGroupImageUri.put(RosterConstants.JID,RosterProvider.TABLE_ROSTER+"."+RosterConstants.JID);
    mGroupImageUri.put(RosterConstants.USER_NAME,RosterProvider.TABLE_ROSTER+"."+RosterConstants.USER_NAME);
    mGroupImageUri.put(ChatConstants.MESSAGE,"c."+ChatConstants.MESSAGE+ " AS "+ ChatConstants.MESSAGE);
    mGroupImageUri.put(ChatConstants.SENDER,"c."+ChatConstants.SENDER+" AS "+ChatConstants.SENDER);
    mGroupImageUri.put(ChatConstants.URL_LOCAL,"c."+ChatConstants.URL_LOCAL+" AS "+ChatConstants.URL_LOCAL);

//case for content type of uri
  case IMAGE_URI:
            qBuilder.setTables(RosterProvider.TABLE_ROSTER
                    + " LEFT OUTER JOIN "+ TABLE_NAME + " c"
                    + " ON c."+ ChatConstants.JID + "=" + RosterProvider.TABLE_ROSTER + "."+RosterConstants.JID);
            qBuilder.setProjectionMap(mGroupImageUri);
            break;

    //ContentResolver query for Projection form, selection and selection args
String[] PROJECTION_FROM = new String[]{
            RosterConstants.JID,
            RosterConstants.USER_NAME,
            ChatConstants.MESSAGE,
            ChatConstants.SENDER,
            ChatConstants.URL_LOCAL
    };

    String selection = RosterProvider.TABLE_ROSTER +"."+RosterConstants.JID+ "='" + jid + "' AND " + "c."+ChatConstants.FILE_TYPE+"="+ChatConstants.IMAGE;
    String[] selectionArgu = null;
    String order = "c."+ChatConstants.MESSAGE+" ASC";

    Cursor cursor = mContentReolver.query(ChatProvider.CONTENT_URI_GROUP_IMAGE_URI,
            PROJECTION_FROM,selection, null,order);

    //@ChatProvider.CONTENT_URI_GROUP_IMAGE_URI = 'your content type uri'
    //@TABLE_NAME = 'table1'
    //@RosterProvider.TABLE_ROSTER ='table2'

How to implement a content provider with more than one table?

3 Answers3

Linked