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 )
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. :)