implement the android content provider's query method,with multiple tables in use

Question

I have three tables and would like to use a content provider to manage them.Below is code from my content provider:

private static final UriMatcher sURIMatcher = new UriMatcher(
        UriMatcher.NO_MATCH);
static {
    sURIMatcher.addURI(AUTHORITY, METER_PATH, all_meters);
    sURIMatcher.addURI(AUTHORITY, METER_PATH + "/#", single_meter);

    sURIMatcher.addURI(AUTHORITY, CUSTOMERS_PATH, all_customers);
    sURIMatcher.addURI(AUTHORITY, CUSTOMERS_PATH + "/#", single_customer);

    sURIMatcher.addURI(AUTHORITY, BILL_PATH, all_bills);
    sURIMatcher.addURI(AUTHORITY, BILL_PATH + "/#", single_bill);

}

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

    SQLiteDatabase db = database.getWritableDatabase();

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

queryBuilder
            .setTables(MeterTableDetails.TABLE_METERS
                    + " as meters "
                    + " INNER JOIN "
                    + CustomerTableDetails.TABLE_CUSTOMERS
                    + " as customers "
                    + " ON "
                    + (MeterTableDetails.METER_ID = CustomerTableDetails.KEY_METER_ID)
                    + " INNER JOIN "
                    + WaterBillTableDetails.TABLE_WATER_BILL
                    + " as waterbills "
                    + " ON "
                    + (CustomerTableDetails.KEY_METER_ID = WaterBillTableDetails.BILL_METER_ID));
    int uriType = sURIMatcher.match(uri);
    switch (uriType) {

    case all_meters:
        break;
    case single_meter:
        // Adding the ID to the original query

        String id = uri.getPathSegments().get(1);
        queryBuilder.appendWhere(MeterTableDetails.METER_ID + "=" + id);

        break;

    case all_customers:
        break;
    case single_customer:
        // Adding the ID to the original query
        String id1 = uri.getPathSegments().get(1);
        queryBuilder.appendWhere(CustomerTableDetails.KEY_CUSTOMER_ID + "="
                + id1);
        break;

    case all_bills:
        break;
    case single_bill:
        // Adding the ID to the original query
        String id2 = uri.getPathSegments().get(1);
        queryBuilder.appendWhere(WaterBillTableDetails.BILL_ID + "=" + id2);
        break;

    default:
        throw new IllegalArgumentException("Unknown URI: " + uri);
    }

    Cursor cursor = queryBuilder.query(db, projection, selection,
            selectionArgs, null, null, sortOrder);
    // Make sure that potential listeners are getting notified
    cursor.setNotificationUri(getContext().getContentResolver(), uri);

    return cursor;
} 

I have three tables,and have created some joins in the querybuilder.setTables method.I'm trying to display meter items in a list from the meters table.I also have a SimpleCursorAdapter with loaderCallbacks implementation. Currently i get the following error in my logcat and i think it's because of the joins and the query:

03-20 15:11:59.692: E/SQLiteCursor(2001): requesting column name with table name -- meters._id
03-20 15:11:59.692: E/SQLiteCursor(2001): java.lang.Exception
03-20 15:11:59.692: E/SQLiteCursor(2001):   at android.database.sqlite.SQLiteCursor.getColumnIndex(SQLiteCursor.java:180)
03-20 15:11:59.692: E/SQLiteCursor(2001):   at android.database.AbstractCursor.getColumnIndexOrThrow(AbstractCursor.java:301)
03-20 15:11:59.692: E/SQLiteCursor(2001):   at android.database.CursorWrapper.getColumnIndexOrThrow(CursorWrapper.java:78)
03-20 15:11:59.692: E/SQLiteCursor(2001):   at android.support.v4.widget.SimpleCursorAdapter.findColumns(SimpleCursorAdapter.java:317)
03-20 15:11:59.692: E/SQLiteCursor(2001):   at android.support.v4.widget.SimpleCursorAdapter.swapCursor(SimpleCursorAdapter.java:328)
03-20 15:11:59.692: E/SQLiteCursor(2001):   at com.isys.waterbillingsystem.MetersActivity.onLoadFinished(MetersActivity.java:180)
03-20 15:11:59.692: E/SQLiteCursor(2001):   at com.isys.waterbillingsystem.MetersActivity.onLoadFinished(MetersActivity.java:1)

EDIT

private static final String CREATE_CUSTOMER_VIEW = ""
        + "CREATE VIEW " + TABLE_CUSTOMER_VIEW 
        + " AS SELECT "+MeterTableDetails.TABLE_METERS+"."+MeterTableDetails.METER_ID+" AS "+ MeterTableDetails.TABLE_METERS+"."+MeterTableDetails.METER_ID +","+
        " "+CustomerTableDetails.KEY_FIRST_NAME+","+
        " "+CustomerTableDetails.KEY_LAST_NAME+","+
        " "+CustomerTableDetails.KEY_METER_ID+","+
        " "+CustomerTableDetails.KEY_METER_NUMBER+","+
        " "+CustomerTableDetails.KEY_PLOT_NUMBER+","+
        " "+CustomerTableDetails.TABLE_CUSTOMERS+"."+ CustomerTableDetails.KEY_CUSTOMER_ID+
        " FROM "+CustomerTableDetails.TABLE_CUSTOMERS+" AS customers "+" INNER JOIN "+MeterTableDetails.TABLE_METERS+" AS meters"+
        " ON "+CustomerTableDetails.KEY_METER_ID+" = "+MeterTableDetails.TABLE_METERS+"."+MeterTableDetails.METER_ID;


 public static TableDescriptor getDescriptor() {
    TableDescriptor descriptor = new TableDescriptor();
    descriptor.setTableName(TABLE_CUSTOMER_VIEW);
    descriptor.setColumnId(CUSTOMER_VIEW_ID);
    String[] available = { ViewCustomers.CUSTOMER_VIEW_ID,
            ViewCustomers.CUSTOMER_VIEW_LASTNAME,
            ViewCustomers.CUSTOMER_VIEW_LASTNAME,
            ViewCustomers.CUSTOMER_VIEW_KEY_METER_ID,
            ViewCustomers.CUSTOMER_VIEW_METER,
            ViewCustomers.CUSTOMER_VIEW_PLOT};

    descriptor.setAvailableColumns(available);
    return descriptor;
    }    

EDIT 2

private static final String CREATE_METER_READING_VIEW = ""
        + "CREATE VIEW " + TABLE_METER_READING_VIEW
        + " AS SELECT " + WaterBillTableDetails.TABLE_WATER_BILL+ ".*"
        + ", " +CustomerTableDetails.TABLE_CUSTOMERS+"."+CustomerTableDetails.KEY_METER_NUMBER+","
        +" "+CustomerTableDetails.TABLE_CUSTOMERS+"."+CustomerTableDetails.KEY_PLOT_NUMBER+","
        +" "+CustomerTableDetails.TABLE_CUSTOMERS+"."+CustomerTableDetails.KEY_ACCOUNT_NUMBER+","
         +" "+CustomerTableDetails.TABLE_CUSTOMERS+"."+CustomerTableDetails.KEY_METER_ID+""
        +" FROM "+WaterBillTableDetails.TABLE_WATER_BILL+" AS waterbills "+" JOIN "+CustomerTableDetails.TABLE_CUSTOMERS+" AS customers"
        +" ON "+WaterBillTableDetails.BILL_CUSTOMER_ID+" ="+CustomerTableDetails.TABLE_CUSTOMERS+"."+CustomerTableDetails.KEY_CUSTOMER_ID;

Logcat error

03-25 10:45:03.476: E/AndroidRuntime(1144): FATAL EXCEPTION: main
03-25 10:45:03.476: E/AndroidRuntime(1144): java.lang.RuntimeException: Unable to start activity  ComponentInfo{com.isys.waterbillingsystem/com.isys.waterbillingsystem.CustomerDetailsAccountsActivity}:   java.lang.NullPointerException
03-25 10:45:03.476: E/AndroidRuntime(1144): Caused by: java.lang.NullPointerException
03-25 10:45:03.476: E/AndroidRuntime(1144):     at  com.isys.waterbillingsystem.CustomerDetailsAccountsActivity.onCreate(CustomerDetailsAccountsActivity.java:48 )

Answer 1

Here is a quick example for Views:

public class HektorDatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "hektor.db";
    private static final int DATABASE_VERSION = 91;

    public HektorDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Method is called during creation of the database
    @Override
    public void onCreate(SQLiteDatabase database) {
        AppointmentTypesTable.onCreate(database);
    }

    // Method is called during an upgrade of the database,
    // e.g. if you increase the database version
    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion,
            int newVersion) {
        AppointmentTypesTable.onUpgrade(database, oldVersion, newVersion);
    }


}

This is a standard SQLiteOpenHelper.

    public class AppointmentWithTypeAndContactsView {

    public static final String TABLE_NAME = "appointments_with_type_and_contacts";

    public static final String COLUMN_ID = AppointmentsTable.COLUMN_ID;
    public static final String COLUMN_EXTERNAL_ID = AppointmentsTable.COLUMN_EXTERNAL_ID;
    public static final String COLUMN_START_DATE = AppointmentsTable.COLUMN_START_DATE;

    private static final String DATABASE_CREATE = ""
            + "CREATE VIEW " + TABLE_NAME 
            + " AS SELECT " + AppointmentsTable.TABLE_NAME + ".*"
            + ", " + AppointmentTypesTable.TABLE_NAME + "." + AppointmentTypesTable.COLUMN_NAME
            + ", " + BuyersTable.TABLE_NAME + "." + BuyersTable.COLUMN_TITLE + " || ' ' || " + BuyersTable.TABLE_NAME + "." + BuyersTable.COLUMN_LAST_NAME + " || ' ' || " + BuyersTable.TABLE_NAME + "." + BuyersTable.COLUMN_FIRST_NAME + " AS " + BuyersTable.COLUMN_LAST_NAME
            + ", " + SellersDetailsTable.TABLE_NAME + "." + SellersDetailsTable.COLUMN_TITLE + " || ' ' || " + SellersDetailsTable.TABLE_NAME + "." + SellersDetailsTable.COLUMN_LAST_NAME + " || ' ' || " + SellersDetailsTable.TABLE_NAME + "." + SellersDetailsTable.COLUMN_FIRST_NAME + " AS " + SellersDetailsTable.COLUMN_LAST_NAME
            + " FROM " + AppointmentsTable.TABLE_NAME + " LEFT OUTER JOIN " + AppointmentTypesTable.TABLE_NAME
            + " ON " + AppointmentsTable.TABLE_NAME + "." + AppointmentsTable.COLUMN_TYPE 
            + " = " + AppointmentTypesTable.TABLE_NAME + "." + AppointmentTypesTable.COLUMN_EXTERNAL_ID
            + " LEFT OUTER JOIN " + BuyersTable.TABLE_NAME
            + " ON " +  AppointmentsTable.TABLE_NAME + "." + AppointmentsTable.COLUMN_BUYER
            + " = " + BuyersTable.TABLE_NAME + "." + BuyersTable.COLUMN_EXTERNAL_ID
            + " LEFT OUTER JOIN " + SellersDetailsTable.TABLE_NAME
            + " ON " +  AppointmentsTable.TABLE_NAME + "." + AppointmentsTable.COLUMN_SELLER
            + " = " + SellersDetailsTable.TABLE_NAME + "." + SellersDetailsTable.COLUMN_EXTERNAL_ID;



    public static void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    public static void onUpgrade(SQLiteDatabase database, int oldVersion,
            int newVersion) {
        Log.w(AppointmentWithTypeAndContactsView.class.getName(), "Upgrading database from version "
                + oldVersion + " to " + newVersion
                + ", which will destroy all old data");
        database.execSQL("DROP VIEW IF EXISTS " + TABLE_NAME);
        onCreate(database);
    }

    public static TableDescriptor getDescriptor() {
        TableDescriptor descriptor = new TableDescriptor();
        descriptor.setTableName(TABLE_NAME);
        descriptor.setColumnId(COLUMN_ID);

        String[] appointmentsAvailableColumns = AppointmentsTable.getDescriptor().getAvailableColumns();
        String[] typesAvailableColumns = new String[] {AppointmentTypesTable.COLUMN_NAME};
        String[] buyersAvailableColumns = new String[] {BuyersTable.COLUMN_LAST_NAME};
        String[] sellerssAvailableColumns = new String[] {SellersDetailsTable.COLUMN_LAST_NAME};

        descriptor.setAvailableColumns(ArrayUtils.concatAll(appointmentsAvailableColumns, typesAvailableColumns, buyersAvailableColumns, sellerssAvailableColumns));
        return descriptor;
    }

}

I've updated the Table class to include some utility methods.

public class TableDescriptor {
    private String tableName;
    private String columnId;
    private String[] availableColumns;

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public String getColumnId() {
        return columnId;
    }

    public void setColumnId(String columnId) {
        this.columnId = columnId;
    }

    public String[] getAvailableColumns() {
        return availableColumns;
    }

    public void setAvailableColumns(String[] availableColumns) {
        this.availableColumns = availableColumns;
    }
}

TableDescriptor is just a container class.

public final class HektorContentProviderContract {
    public static final String AUTHORITY = "fr.intuitiv.hektor.contentprovider";

    public static final String APPOINTMENT_WITH_TYPE_BASE_PATH = "appointment_with_type";
    public static final Uri APPOINTMENT_WITH_TYPE_CONTENT_URI = Uri.parse("content://" + AUTHORITY
            + "/" + APPOINTMENT_WITH_TYPE_BASE_PATH);
    public static final String APPOINTMENT_WITH_TYPE_CONTENT_TYPE = ContentResolver.CURSOR_DIR_BASE_TYPE
            + "/vnd." + AUTHORITY + "." + APPOINTMENT_WITH_TYPE_BASE_PATH;
    public static final String APPOINTMENT_WITH_TYPE_CONTENT_ITEM_TYPE = ContentResolver.CURSOR_ITEM_BASE_TYPE
            + "/vnd." + AUTHORITY + "." + APPOINTMENT_WITH_TYPE_BASE_PATH;
}

I usually create some "Contract" classes to store any public constants.

public class ContentProviderHelper {
    private Context context;

    public Context getContext() {
        return context;
    }

    public void setContext(Context context) {
        this.context = context;
    }

    public ContentProviderHelper(Context context) {
        this.setContext(context);
    }

    public Cursor query(SQLiteOpenHelper database, TableDescriptor table,
            boolean isSingular, Uri uri, String[] projection, String selection,
            String[] selectionArgs, String sortOrder) {

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

        // Set the table
        queryBuilder.setTables(table.getTableName());

        if (isSingular) {
            queryBuilder.appendWhere(table.getColumnId() + "="
                    + uri.getLastPathSegment());
        }

        SQLiteDatabase db = database.getWritableDatabase();
        Cursor cursor = queryBuilder.query(db, projection, selection,
                selectionArgs, null, null, sortOrder);
        // Make sure that potential listeners are getting notified
        cursor.setNotificationUri(getContext().getContentResolver(), uri);

        return cursor;
    }

    public Uri insert(SQLiteOpenHelper database, TableDescriptor table, Uri uri, ContentValues values) {
        SQLiteDatabase sqlDB = database.getWritableDatabase();
        long id = 0;
        id = sqlDB.insertWithOnConflict(table.getTableName(), null, values, SQLiteDatabase.CONFLICT_REPLACE);
        getContext().getContentResolver().notifyChange(uri, null);
        return Uri.withAppendedPath(getTableUri(table), Long.toString(id));
    }

    public int delete(SQLiteOpenHelper database, TableDescriptor table, boolean isSingular, Uri uri, String selection, String[] selectionArgs) {
        int rowsDeleted = 0;
        SQLiteDatabase sqlDB = database.getWritableDatabase();
        if (!isSingular) {
            rowsDeleted = sqlDB.delete(table.getTableName(), selection,
                    selectionArgs);
        } else {
            String id = uri.getLastPathSegment();
            if (TextUtils.isEmpty(selection)) {
                rowsDeleted = sqlDB.delete(table.getTableName(),
                        table.getColumnId() + "=" + id, null);
            } else {
                rowsDeleted = sqlDB.delete(table.getTableName(),
                        table.getColumnId() + "=" + id + " and " + selection,
                        selectionArgs);
            }
        }
        getContext().getContentResolver().notifyChange(uri, null);
        return rowsDeleted;
    }

    public int update(SQLiteOpenHelper database, TableDescriptor table, boolean isSingular, Uri uri, ContentValues values, String selection,
            String[] selectionArgs) {

        SQLiteDatabase sqlDB = database.getWritableDatabase();
        int rowsUpdated = 0;
        if (!isSingular) {
            rowsUpdated = sqlDB.update(table.getTableName(), values, selection,
                    selectionArgs);
        } else {
            String id = uri.getLastPathSegment();
            if (TextUtils.isEmpty(selection)) {
                rowsUpdated = sqlDB.update(table.getTableName(), values,
                        table.getColumnId() + "=" + id, null);
            } else {
                rowsUpdated = sqlDB.update(table.getTableName(), values,
                        table.getColumnId() + "=" + id + " and " + selection,
                        selectionArgs);
            }
        }
        getContext().getContentResolver().notifyChange(uri, null);
        return rowsUpdated;
    }

    public void checkColumns(TableDescriptor table, String[] projection) {
        String[] available = table.getAvailableColumns();
        if (projection != null) {
            HashSet<String> requestedColumns = new HashSet<String>(
                    Arrays.asList(projection));
            HashSet<String> availableColumns = new HashSet<String>(
                    Arrays.asList(available));
            // Check if all columns which are requested are available
            if (!availableColumns.containsAll(requestedColumns)) {
                throw new IllegalArgumentException(
                        "Unknown columns in projection");
            }
        }
    }

    protected Uri getTableUri(TableDescriptor table) {
        Uri result = null;

        String tableName = table.getTableName();
        if (AppointmentWithTypeView.TABLE_NAME.equals(tableName)) {
            result = HektorContentProviderContract.APPOINTMENT_WITH_TYPE_CONTENT_URI;
        }

        return result;
    }
}

Here is another utility class I'm using when working with ContentProviders. It simplifies the database management operations. It is quite handy if you have a lot of tables to manage.

public class UriDescriptor {
    private TableDescriptor table;
    private boolean singular;

    public boolean isSingular() {
        return singular;
    }

    public void setSingular(boolean singular) {
        this.singular = singular;
    }

    public TableDescriptor getTable() {
        return table;
    }

    public void setTable(TableDescriptor table) {
        this.table = table;
    }
}

This is again just a container class - not really interesting.

public class HektorContentProvider extends ContentProvider {

    private ContentProviderHelper helper;

    // database
    private HektorDatabaseHelper database;

    // Used for the UriMacher
    private static final int APPOINTMENT_WITH_TYPE = 290;
    private static final int APPOINTMENT_WITH_TYPE_ID = 300;

    private static final UriMatcher sURIMatcher = new UriMatcher(
            UriMatcher.NO_MATCH);
    static {
        sURIMatcher.addURI(HektorContentProviderContract.AUTHORITY,
                HektorContentProviderContract.APPOINTMENT_WITH_TYPE_BASE_PATH, APPOINTMENT_WITH_TYPE);
        sURIMatcher.addURI(HektorContentProviderContract.AUTHORITY,
                HektorContentProviderContract.APPOINTMENT_WITH_TYPE_BASE_PATH + "/#",
                APPOINTMENT_WITH_TYPE_ID);
    }

    @Override
    public boolean onCreate() {
        database = new HektorDatabaseHelper(getContext());
        helper = new ContentProviderHelper(getContext());
        return false;
    }

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

        UriDescriptor descriptor = getDescriptor(uri);
        helper.checkColumns(descriptor.getTable(), projection);

        Cursor cursor = helper.query(database, descriptor.getTable(),
                descriptor.isSingular(), uri, projection, selection,
                selectionArgs, sortOrder);

        return cursor;
    }

    @Override
    public String getType(Uri uri) {
        return null;
    }

    @Override
    public Uri insert(Uri uri, ContentValues values) {
        UriDescriptor descriptor = getDescriptor(uri);
        Uri result = helper
                .insert(database, descriptor.getTable(), uri, values);
        return result;
    }

    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {
        int rowsDeleted = 0;
        UriDescriptor descriptor = getDescriptor(uri);
        rowsDeleted = helper.delete(database, descriptor.getTable(),
                descriptor.isSingular(), uri, selection, selectionArgs);
        return rowsDeleted;
    }

    @Override
    public int update(Uri uri, ContentValues values, String selection,
            String[] selectionArgs) {

        UriDescriptor descriptor = getDescriptor(uri);
        int rowsUpdated = helper.update(database, descriptor.getTable(),
                descriptor.isSingular(), uri, values, selection, selectionArgs);
        return rowsUpdated;
    }

    protected UriDescriptor getDescriptor(Uri uri) {
        UriDescriptor descriptor = new UriDescriptor();

        int uriType = sURIMatcher.match(uri);
        switch (uriType) {
        case APPOINTMENT_WITH_TYPE:
            descriptor.setSingular(false);
            descriptor.setTable(AppointmentWithTypeView.getDescriptor());
            break;
        case APPOINTMENT_WITH_TYPE_ID:
            descriptor.setSingular(true);
            descriptor.setTable(AppointmentWithTypeView.getDescriptor());
            break;
        default:
            throw new IllegalArgumentException("Unknown URI: " + uri);
        }

        return descriptor;
    }

}

That's the ContentProvider class. It is quite simple, since most of the work is done in the ContentProviderHelper class.

This is a class representing a View. I write such classes for each View or Table I want to create in my database. Both entities are pretty much used the same way - you just have to change the SQL statement from CREATE VIEW to CREATE TABLE. As you can see, views can be created based on a SELECT statement - so they are very useful if you want to join several tables. When inserting to any of the AppointmentsTable / AppointmentTypesTable tables the data would be available via the View too. So I prefer to create such View and a ContentProvider working over it. SELECT queries are simple (just read from the View). You would have to handle INSERT / DELETES differently though - i.e. insert the data to the associated table.

My application loads its data from web service, so I'm doing it on the background. I've create CONTENT_URL in the ContentProvider for each table (i.e. AppointmentsTable and AppointmentTypesTable). Those are used by the background process to insert / update the data. The UI uses only the CONTENT_URLs connected to the Views, since they just need to read the data.

Let me know if you've got the idea. I could share some more code if needed. :)