How To Implement Complex Queries Using A Content Provider?

I am asking this because I am not quite sure of how to work with Android Content Providers. I have a subset of my database with 8 tables and I need to create complex queries to get

Solution 1:

I actually found the answer to my question in the most obvious place: the android documentation.

First Question: Implement a rawQuery. Did it like this:

Inside of my switch-case in the content provider I added a new URI, which for me is a JOIN between to tables, so I created a new ContentUri constant for it, a new ID, and registered it on the UriMatcher and then wrote the rawQuery. So MyProvider now looks a litte bit like this:

publicclassMyProviderextendsContentProvider{
...
// JOIN pathsprivatestaticfinalString PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = 
            "relationship_join_person_get_relatives";
...
publicstaticfinal Uri CONTENT_URI_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = Uri
            .parse("content://" + AUTHORITY + "/"
                    + PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES);
...
    privatestaticfinalint RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = 21;
privatestaticfinal UriMatcher sURIMatcher = new UriMatcher(
            UriMatcher.NO_MATCH);
    static {
...
//JOINS
        sURIMatcher.addURI(AUTHORITY, PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES + "/#",
                RELATIONSHIP_JOIN_PERSON_GET_RELATIVES);
...

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

        // Uisng SQLiteQueryBuilder instead of query() method
        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();

        // check if the caller has requested a column which does not exists//checkColumns(projection);int uriType = sURIMatcher.match(uri);

        switch (uriType) {
        ...
        case RELATIONSHIP_JOIN_PERSON_GET_RELATIVES:
            db = dbHelper.getWritableDatabase();
            String[] args = {String.valueOf(uri.getLastPathSegment())};
            Cursor cursor = db.rawQuery(
                    "SELECT p1.first_name, p1.last_name " +
                    "FROM Person p1, Person p2, Relationship r " +
                    "WHERE p1.id = r.relative_id AND " +
                    "p2.id = r.related_id AND " + 
                    "p2.id = ?", args);
            cursor.setNotificationUri(getContext().getContentResolver(), uri);
            return cursor;
        ...
}

And to call the query() method and pass the id ad a parameter I did this in my controller:

String[] projection = { PersonModel.C_FIRST_NAME,
                PersonModel.C_LAST_NAME };
        Cursorcursor= context.getContentResolver().query(
                ContentUris.withAppendedId(
                        AkdemiaProvider.CONTENT_URI_RELATIONSHIP_JOIN_PERSON_GET_RELATED, id), 
                        projection, null, null, null);

Second question: Having the TABLE_ID constant is useful to have a query for each table passing an id as a parameter, I didn't know how to call the query method passing such id and this is how the Android Developer Documentation explains how to do so using ContentUris.withAppendedId

// Request a specific record.CursormanagedCursor= managedQuery(
                ContentUris.withAppendedId(Contacts.People.CONTENT_URI, 2),
                projection,    // Which columns to return.null,          // WHERE clause.null,          // WHERE clause value substitution
                People.NAME + " ASC");   // Sort order.

I you guys want to see the whole documentation go to this link.

Hope this helps to anyone else having the same problem to understand ContentProvider, ContentUris and all that :)

Solution 2:

Below code worked for me. Inside your Application's Content Provider:

publicstaticfinalStringPATH_JOIN_TWO_TABLES="my_path";

    publicstaticfinalUriURI_JOIN_TWO_TABLES=
            Uri.parse("content://" + AUTHORITY + "/" + PATH_JOIN_TWO_TABLES);

    privatestaticfinalintID_JOIN_TWO_TABLES=1001;

    privatestaticfinalUriMatchersURIMatcher=newUriMatcher(
            UriMatcher.NO_MATCH);

    static {
        sURIMatcher.addURI(AUTHORITY,
                PATH_JOIN_TWO_TABLES + "/#", ID_JOIN_TWO_TABLES );
    }

    @Nullable@Overridepublic Cursor query(@NonNull Uri uri, String[] projection, String selection,String[] selectionArgs,
                        String sortOrder, CancellationSignal cancellationSignal) {

        inturiType= sURIMatcher.match(uri);
            switch (uriType) {

                case ID_JOIN_TWO_TABLES:
                    return getWritableDatabase()
                            .rawQuery("select * from " +
                                    "table_one" + " LEFT OUTER JOIN "
                                    + "table_two" + " ON ("
                                    + "table_one.ID"
                                    + " = " + "table_two.id" + ")", null);
            }
        returnsuper.query(uri, projection, selection, selectionArgs, sortOrder, cancellationSignal);
    }

And while making the Query inside your Activity or Fragment:

Cursorcursor= getActivity().getContentResolver()
                .query(ContentUris.withAppendedId(MYContentProvider.URI_JOIN_TWO_TABLES, MyContentProvider.ID_JOIN_TWO_TABLES), null, null, null, null);

Hope it works for you.

Solution 3:

For simple queries use selectionArgs in ContentProvider. It works like below

String[] args = { "first string", "second@string.com" };
Cursorcursor= db.query("TABLE_NAME", null, "name=? AND email=?", args, null);

Having the TABLE_ID inside the to create a different queries for each table.

Refer following class for all multiple table in content providers

1. Vogella Tutorial 1
2. Vogella Tutorial 2
3. Best practices for exposing multiple tables using content providers in Android