It seems the way to access a SQLite database is to use a Content provider (Not sure why yet), following the notepad example in the google tutorial you end up with a very verbose class NotePad example
Rather than have a lots of boiler plate for each project and each table it would be better to provide an adapter class that allows us to simple supplier the pertinent information for each table in the database.
This abstract class could be added to a common library (Once some of its problems are ironed out) at some point.
A key part is the Database URI matcher class, this is used to match the Uri to the correct table.
Currently adding access to a database table using the AbstractDataBaseProvider requires the following steps.
1. Extend the AbstractDatabaseProvider.DatabaseBaseColumns and implement all the unimplemented methods this supplies the content URI, table name etc.
public class Favourite implements DatabaseBaseColumns {
/** * The MIME type of {@link #CONTENT_URI} providing a directory of
* favorites. */
private static final String CONTENT_TYPE = "vnd.android.cursor.dir/vnd.example.favourites";
/**
* The MIME type of a {@link #CONTENT_URI} sub-directory of a single
* fav. */
private static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/vnd.favourites";
/**
* The default sort order for this table */
private static final String DEFAULT_SORT_ORDER = "viewCount DESC";
private static final String TABLE_NAME = "favorites";
/**
* The requesters name
* <P>
* Type: TEXT
* </P>
*/
public static final String NAME = "name";
public String getAuthority() { return AUTHORITY; }
public String getContentItemType() { return CONTENT_ITEM_TYPE; }
public String getContentType() { return CONTENT_TYPE; }
public Uri getContentUri() { return CONTENT_URI; }
public String getDefaultSortOrder() { return DEFAULT_SORT_ORDER; }
public String getNullHackField() { return NAME; }
public String getTableName() { return TABLE_NAME; } }
2. Extend the AbstractDatabaseProvider and implement the following
* preInsert - Chance to populate a row with default values
* onCreate - Set things up
* getOpenHelper - Return a sub class of SQLiteOpen Helper this is responsible for creating and upgrading your table
* getUriMatcher - a DataBaseUriMatcher to which you add Uris to match by adding each DatabaseBaseColumns derived class.
3. Add a static block in which you add all the Projection mappings
4. Add a provider tag to the manifest like this: <provider android:name=".favourites.DataProvider" android:authorities="com.favourites"> </provider>
Thats all! Still quite verbose, though, to access the database use the content provider methods : Cursor cursor = getContentResolver().query(Favourites.CONTENT_URI, PROJECTION, null, null, Favourites.DEFAULT_SORT_ORDER);
The projection field is an array of strings that represent the fields in the query.
Code for the abstract database provider
public abstract class AbstractDataBaseProvider extends ContentProvider {
/* String that are useful for building SQL statements */
private final static String TAG = AbstractDataBaseProvider.class.getName();
private static HashMap<String, String> sProjectionMap = new HashMap<String, String>();
protected final static int SUB_CODE_TABLE = 1;
protected final static int SUB_CODE_TABLE_ID = 2;
/**
* Implementing classes must provide a database helper to create / open the
* database
*
* @return
*/
protected abstract SQLiteOpenHelper getOpenHelper();
/**
* Return a URI matcher that matches Uri request to the correct table
*
* @return
*/
protected abstract DataBaseUriMatcher getUriMatcher();
/**
* Called before values are inserted in to the database to allow added of
* default and automatic values
*
* @param values
*/
protected abstract void preInsert(final ContentValues values);
public static void addMapping(final String field1, final String field2) {
sProjectionMap.put(field1, field2);
}
/*
* (non-Javadoc)
*
* @see android.content.ContentProvider#delete(android.net.Uri,
* java.lang.String, java.lang.String[])
*/
@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
SQLiteDatabase db = getOpenHelper().getWritableDatabase();
int count;
DataBaseUriMatcher matcher = getUriMatcher();
int code = matcher.match(uri);
switch (matcher.getSubCode(code)) {
case SUB_CODE_TABLE:
count = db.delete(matcher.getDbCols(code).getTableName(),
selection, selectionArgs);
break;
case SUB_CODE_TABLE_ID:
String id = uri.getPathSegments().get(1);
count = db.delete(matcher.getDbCols(code).getTableName(),
BaseColumns._ID
+ "="
+ id
+ (!TextUtils.isEmpty(selection) ? " AND ("
+ selection + ')' : ""), selectionArgs);
break;
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
getContext().getContentResolver().notifyChange(uri, null);
return count;
}
/*
* (non-Javadoc)
*
* @see android.content.ContentProvider#getType(android.net.Uri)
*/
@Override
public String getType(Uri uri) {
int code = getUriMatcher().match(uri);
switch (getUriMatcher().getSubCode(code)) {
case SUB_CODE_TABLE:
return getUriMatcher().getDbCols(code).getContentType();
case SUB_CODE_TABLE_ID:
return getUriMatcher().getDbCols(code).getContentItemType();
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
}
/*
* (non-Javadoc)
*
* @see android.content.ContentProvider#insert(android.net.Uri,
* android.content.ContentValues)
*/
@Override
public Uri insert(Uri uri, ContentValues initialValues) {
Log.i(TAG, "Insert " + uri);
// Validate the requested uri
int code = getUriMatcher().match(uri);
if (getUriMatcher().getSubCode(code) != SUB_CODE_TABLE) {
throw new IllegalArgumentException("Unknown URI " + uri);
}
DatabaseBaseColumns dbCols = getUriMatcher().getDbCols(code);
ContentValues values;
if (initialValues != null) {
values = new ContentValues(initialValues);
} else {
values = new ContentValues();
}
preInsert(values);
SQLiteDatabase db = getOpenHelper().getWritableDatabase();
long rowId = db.insert(dbCols.getTableName(),
dbCols.getNullHackField(), values);
if (rowId > 0) {
Uri reqUri = ContentUris.withAppendedId(dbCols.getContentUri(),
rowId);
getContext().getContentResolver().notifyChange(reqUri, null);
return reqUri;
}
throw new SQLException("Failed to insert row into " + uri);
}
/*
* (non-Javadoc)
*
* @see android.content.ContentProvider#query(android.net.Uri,
* java.lang.String[], java.lang.String, java.lang.String[],
* java.lang.String)
*/
@Override
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
int code = getUriMatcher().match(uri);
String tableName = getUriMatcher().getDbCols(code).getTableName();
switch (getUriMatcher().getSubCode(code)) {
case SUB_CODE_TABLE:
qb.setTables(tableName);
qb.setProjectionMap(sProjectionMap);
break;
case SUB_CODE_TABLE_ID:
qb.setTables(tableName);
qb.setProjectionMap(sProjectionMap);
qb
.appendWhere(BaseColumns._ID + "="
+ uri.getPathSegments().get(1));
break;
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
// If no sort order is specified use the default
String orderBy;
if (TextUtils.isEmpty(sortOrder)) {
orderBy = getUriMatcher().getDbCols(code).getDefaultSortOrder();
} else {
orderBy = sortOrder;
}
// Get the database and run the query
SQLiteDatabase db = getOpenHelper().getReadableDatabase();
Cursor c = qb.query(db, projection, selection, selectionArgs, null,
null, orderBy);
// Tell the cursor what uri to watch, so it knows when its source data
// changes
c.setNotificationUri(getContext().getContentResolver(), uri);
return c;
}
/*
* (non-Javadoc)
*
* @see android.content.ContentProvider#update(android.net.Uri,
* android.content.ContentValues, java.lang.String, java.lang.String[])
*/
@Override
public int update(Uri uri, ContentValues values, String selection,
String[] selectionArgs) {
SQLiteDatabase db = getOpenHelper().getWritableDatabase();
int count;
int code = getUriMatcher().match(uri);
String tableName = getUriMatcher().getDbCols(code).getTableName();
switch (getUriMatcher().getSubCode(code)) {
case SUB_CODE_TABLE:
count = db.update(tableName, values, selection, selectionArgs);
break;
case SUB_CODE_TABLE_ID:
String id = uri.getPathSegments().get(1);
count = db.update(tableName, values, BaseColumns._ID
+ "="
+ id
+ (!TextUtils.isEmpty(selection) ? " AND (" + selection
+ ')' : ""), selectionArgs);
break;
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
getContext().getContentResolver().notifyChange(uri, null);
return count;
}
/**
*
*
*
* Extends the BaseColums interface to include information to fully
* describe a set of columns in a database
*
* Effectively each DatabaseColumns class represents a table in a
* database
*
*/
public interface DatabaseBaseColumns extends BaseColumns {
/**
*
* @return The name of the table in the database
*/
public String getTableName();
/**
*
* @return Field that is populated with null in the case that an empty
* row is added
*/
public String getNullHackField();
/**
*
* @return Gets the authority for this table, this should be the package
* name of the class that matches the tables row data
*/
public String getAuthority();
/**
*
* @return the content Uri for this set of columns
*/
public Uri getContentUri();
/**
*
* @return The mime type for this table
*/
public String getContentType();
/**
*
* @return The mime type for an entry in this table
*/
public String getContentItemType();
/**
*
* @return The default sort order for this table
*/
public String getDefaultSortOrder();
}
/*
* Next code is used to give each URI added a unique id, dont see the point
* in exposing this to the caller class
*/
private static int nextCode = 1;
private static int getNextCode() {
return ++nextCode;
}
/**
* Extends the standad URiMatcher adding the ability to return the sub code
* and table name and DatabaseBaseColoumns
*
*
*
*/
public static class DataBaseUriMatcher extends UriMatcher {
private final Map<Integer, TableID> tableMap = new HashMap<Integer, TableID>();
public DataBaseUriMatcher(int code) {
super(code);
}
/**
* Add a uri to match and the code and table to return
*
* @param authority
* @param path
* @param code
* @param table
*/
public void addUri(final DatabaseBaseColumns tdbCols) {
int code = getNextCode();
super.addURI(tdbCols.getAuthority(), tdbCols.getTableName(), code);
tableMap.put(code, new TableID(SUB_CODE_TABLE, tdbCols));
code = getNextCode();
super.addURI(tdbCols.getAuthority(), tdbCols.getTableName() + "/#",
code);
tableMap.put(code, new TableID(SUB_CODE_TABLE_ID, tdbCols));
}
public DatabaseBaseColumns getDbCols(final int code) {
return tableMap.get(code).getDbCols();
}
public int getSubCode(final int code) {
return tableMap.get(code).getSubcode();
}
private class TableID {
final int subcode;
final DatabaseBaseColumns dbCols;
public TableID(final int subcode, final DatabaseBaseColumns dbCols) {
this.subcode = subcode;
this.dbCols = dbCols;
}
public int getSubcode() {
return subcode;
}
public DatabaseBaseColumns getDbCols() {
return dbCols;
}
}
}
}
Forum
