Android ContentProvider on SQLite tables without the _id column
Apr 10th, 2009 by paolo
Very often I found on the web ContentProvider examples based on a SQLite table that describes, as a constraint, to have a column named _id to be used as primary key. Also the official Android Dev Guide introducing in Creating a Content Provider tells to
Be sure to include an integer column named "_id" (with the constant _ID) for the IDs of the records.
This way of done it could be considered a good best practice when the Content Provider that I have to implement is based on a single table. It's also very useful because the fact that widgets like ListView works by default with Content Providers that provides an integer identifier called _id, but it couldn't be considered a constraint.
Infact, if the Content Provider you have to implement is not trivial and, for example, is based on data coming from more than one tables, the rule to use for the identifier column the _id name is unconfortable and it makes the phase on design of the data model warped.
Now I try to describe a not so much complicated example where the use of _id as identifier column name is impossible and I'll make an example of how to build a Content Provider to manage this case without leaving the facilities guaranteed to the trivial way of done.
The Data Model
Let's start designing a very simple data model that describes a products catalogue organized by categories and manufacturers. This data model will define only two one-to-many associations between three entities: products, categories and manufacturers. The ER diagram below describes more formally the relations betweens entities and how the whole schema is done.
- categories
- A category has an integer identifier and a name and it can have one or more products associated to it.
- manufacturers
- A manufacturer has an integer identifier and a name and it can produce one or more products.
- products
- A product has an integer identifier, a category associated, a manufacturer and a description.
The sql commands to create this schema on sqlite3 database are:
-
CREATE TABLE categories (
-
category_id INTEGER PRIMARY KEY,
-
name TEXT NOT NULL
-
);
-
CREATE TABLE manufacturers (
-
manufacturer_id INTEGER PRIMARY KEY,
-
name TEXT NOT NULL
-
);
-
CREATE TABLE products (
-
product_id INTEGER PRIMARY KEY,
-
category_id INTEGER NOT NULL,
-
company_id INTEGER NOT NULL,
-
description TEXT NOT NULL
-
);
The Content Provider Example
On the data model described above I want to build a Content Provider for manufacturers that provides three ways to be queried:
- the list of all manufacturers (trivial)
- a specific manufacturer given its identifier (trivial)
- the list of manufacturers that have products for a specific category
Following the NotePad example in the Android SDK I start defining a class of constant that describes the manufacturers table extending BaseColumns:
-
public static final class Manufacturers implements BaseColumns {
-
-
// This class cannot be instantiated
-
private Manufacturers() {}
-
-
public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/manufacturers");
-
}
To build my ManufacturersProvider I have to extends the ContentProvider class and implement its abstract method:
- query()
- insert()
- update()
- delete()
- getType()
- onCreate()
First I define the following constants and static member variables within the ManufacturersProvider class:
-
import static org.casarini.app.Manufacturers.*;
-
-
public class ManufacturersProvider extends ContentProvider {
-
private static HashMap<String, String> sManufacturersProjectionMap;
-
-
private static final int MANUFACTURERS = 1;
-
private static final int MANUFACTURERS_ID = 2;
-
private static final int MANUFACTURERS_BYCATEGORY_ID = 3;
-
-
private static final UriMatcher sUriMatcher;
-
-
static {
-
sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
-
sUriMatcher.addURI(AUTHORITY, "manufacterers", MANUFACTURERS);
-
sUriMatcher.addURI(AUTHORITY, "manufacterers/#", MANUFACTURERS_ID);
-
sUriMatcher.addURI(AUTHORITY, "manufacterers/category/#", MANUFACTURERS_BYCATEGORY_ID);
-
-
sManufacturersProjectionMap = new HashMap<String, String>();
-
sManufacturersProjectionMap.put(_ID, "manufacturers.manufacturer_id as _id");
-
sManufacturersProjectionMap.put(NAME, "manufacturers.name as name");
-
}
-
//...
-
}
Observe from the code above that I define a projection map that maps simple column names defined in the static class Manufacturers, to be used when dealing with business logic, with complete columns name with aliases. This is key point, in fact, mapping to complete columns names will allow me to use complete columns name in, perhaps, GROUP BY clause and aliases will provide right names for the Content Supplier to be used later on widgets.
You can also see that I use a UriMatcher object to parse the content URI that is passed to the content provider through a Content Resolver. For example, the following content URI represents a request for all manufacturers in the content provider:
content://org.casarini.provider.Manufacturers/manufacturers
In contrast, the following represents a request for a particular manufacturer with manufacturer_id=5:
content://org.casarini.provider.Manufacturers/manufacturers/5
Even more, the following represents a request for all manufacturers that have products with category_id=5
content://org.casarini.provider.Manufacturers/manufacturers/category/5
Then I'll need to override the getType() method so it uniquely describes the data type for your content provider. Using the UriMatcher object, I will return vnd.android.cursor.item/vnd.app.manufacturer for a single manufacturer, and vnd.android.cursor.dir/vnd.app.manufacturers for multiple manufacturers:
-
switch (sUriMatcher.match(uri)) {
-
case MANUFACTURERS:
-
case MANUFACTURERS_BYCATEGORY_ID:
-
return CONTENT_TYPE;
-
case MANUFACTURERS_ID:
-
return CONTENT_ITEM_TYPE;
-
default:
-
}
-
}
Next, to allow clients to query for books, override the query() method:
-
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
-
String groupBy = null;
-
-
switch (sUriMatcher.match(uri)) {
-
case MANUFACTURERS:
-
qb.setTables(TABLENAME);
-
qb.setProjectionMap(sManufacturersProjectionMap);
-
break;
-
-
case MANUFACTURERS:
-
qb.setTables(TABLENAME);
-
qb.setProjectionMap(sManufacturersProjectionMap);
-
qb.appendWhere(_ID + "=" + uri.getPathSegments().get(1));
-
break;
-
-
case MANUFACTURERS_BYCATEGORY_ID:
-
qb.setTables("manufacturers, products");
-
qb.setProjectionMap(sManufacturersProjectionMap);
-
qb.appendWhere("manufacturers.manufacturer_id = products.manufacturer_id AND category_id = " + uri.getPathSegments().get(2));
-
groupBy = "manufacterers.manufacterer_id, manufacters.name";
-
break;
-
-
default:
-
}
-
-
// If no sort order is specified use the default
-
String orderBy;
-
if (TextUtils.isEmpty(sortOrder)) {
-
orderBy = DEFAULT_SORT_ORDER;
-
} else {
-
orderBy = sortOrder;
-
}
-
-
// Get the database and run the query
-
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
-
-
// Tell the cursor what uri to watch, so it knows when its source data changes
-
c.setNotificationUri(getContext().getContentResolver(), uri);
-
return c;
-
}
For this example is not so useful to write here an implementation for insert(), update(), delete(), onCreate(), while it's more interesting see how I can use my ManufacterersProvider to populate a ListView. To make an example I'll populate a ListView object with all manufacturers that have products with category_id=10:
Nice article for android sqlite, thanx for write it up..:-)
Can you please state solution for “mOpenHelper cannot be resolved” ?
Do we need to use own DBHelper?
In the query method example I assume a mOpenHelper object as a member variable that is able to provide a SQLiteDatabase object through the getReadableDatabase method.
If you need a class for it, an example could be the following:
[java]
public class SQLiteDatabaseHelper {
private static final String TAG = SQLiteDatabaseHelper.class.getSimpleName();
private final CursorFactory factory;
private final int version;
private final File dbFile;
private SQLiteDatabase database;
private boolean isInitializing;
/**
* Create a helper object to create, open, and/or manage a database
* already built and provide in specific path.
* The database is not actually created or opened until one of
* {@link #getWritableDatabase} or {@link #getReadableDatabase} is called.
*
* @param dbpath the path of the database file
* @param factory to use for creating cursor objects, or null for the default
* @param version number of the database
*/
public SQLiteDatabaseHelper(String dbpath, CursorFactory factory, int version) {
this.factory = factory;
this.version = version;
dbFile = new File(dbpath);
}
public synchronized SQLiteDatabase getWritableDatabase() {
if (database != null && database.isOpen() && !database.isReadOnly()) {
return database; // The database is already open for business
}
if (isInitializing) {
throw new IllegalStateException(“getWritableDatabase called recursively”);
}
boolean success = false;
SQLiteDatabase db = null;
try {
isInitializing = true;
if (dbFile.exists()) {
db = SQLiteDatabase.openDatabase(dbFile.getPath(), factory, SQLiteDatabase.OPEN_READWRITE);
if (db.getVersion() != version) {
throw new SQLiteException(“The database in [" + dbFile.getPath() +
"] has version [" + db.getVersion() + "] while the version needed is [" + version +"]“);
}
} else {
throw new SQLiteException(“Error while opening database: the file [" + dbFile.getPath() + "] does not exist”);
}
success = true;
return db;
} finally {
isInitializing = false;
if (success) {
if (database != null) {
try { database.close(); } catch (Exception e) {}
}
database = db;
} else {
if (db != null) db.close();
}
}
}
public synchronized SQLiteDatabase getReadableDatabase() {
if (database != null && database.isOpen()) {
return database; // The database is already open for business
}
if (isInitializing) {
throw new IllegalStateException(“getReadableDatabase called recursively”);
}
try {
return getWritableDatabase();
} catch (SQLiteException e) {
Log.w(TAG, “Couldn’t open ” + dbFile.getPath() + ” for writing (will try read-only):”, e);
}
SQLiteDatabase db = null;
try {
isInitializing = true;
db = SQLiteDatabase.openDatabase(dbFile.getPath(), factory, SQLiteDatabase.OPEN_READONLY);
if (db.getVersion() != version) {
throw new SQLiteException(“The database in [" + dbFile.getPath() +
"] has version [" + db.getVersion() + "] while the version needed is [" + version +"]“);
}
Log.i(TAG, “Opened ” + dbFile.getPath() + ” in read-only mode”);
database = db;
return database;
} finally {
isInitializing = false;
if (db != null && db != database) db.close();
}
}
public synchronized void close() {
if (database != null) {
try { database.close(); } catch (Exception e) {}
}
}
}
[/java]
please offer source down