When an Android app gets into more complex with AsyncTasks to manipulate the SQLite database, we faced with the database locking issues.
So I googled around and found
this discussion, then switched to
ContentProvider and it solves the problem.
To implement
ContentProvider, the first thing to do is adding authorities to
AndroidManifest.xml
<provider android:authorities="com.mycompany.androidapp.contentprovider.authorities"
android:name="com.mycompany.androidapp.data.ContentProviderDb"/>
The
android:authorities="com.mycompany.androidapp.contentprovider.authorities" is just an attribute - whatever unique value - which will be used for the URI to access the data
The class
com.mycompany.androidapp.data.ContentProviderDb is extended from
android.content.ContentProvider, must implement following
required methods:
public boolean onCreate();
public String getType(Uri uri);
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder);
public Uri insert(Uri uri, ContentValues values);
public int update(Uri uri, ContentValues values, String selection,
String[] selectionArgs);
public int delete(Uri uri, String selection, String[] selectionArgs);
Because we need to access the database from different threads, so we must implement the access thread-safe. The simple way is to use
synchronized for those query/insert/update/delete methods.
Below is an example
public class ContentProviderDb extends ContentProvider {
private SQLiteOpenHelper openHelper = null;
/****************************************
* ContentProvider implementation methods
* **************************************/
@Override
public boolean onCreate() {
openHelper = new SQLiteOpenHelper(getContext(), Constant.DATABASE_NAME, null , 1){
public void onCreate(SQLiteDatabase db){
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
}
};
return true;
}
@Override
public String getType(Uri uri) {
// TODO Auto-generated method stub
return null;
}
/**Execute query: raw query (select/insert/update/delete), create, select data from table
* @return Cursor or null
*/
@Override
public synchronized Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
SQLiteDatabase database = openHelper.getReadableDatabase();
Cursor cursor = null;
String query = getQuery(uri);
String lcQuery = query.toLowerCase();
if (lcQuery.startsWith("select ") || lcQuery.startsWith("insert ") ||
lcQuery.startsWith("update ") || lcQuery.startsWith("delete ") ) { //raw queries
cursor = database.rawQuery(query, null);
} else if (lcQuery.startsWith("create ")) { //create tables
database.execSQL(query);
} else { //query is a table name -> normal select from table
cursor = database.query(query, projection, selection, selectionArgs, null, null, sortOrder);
}
return cursor;
}
@Override
public synchronized Uri insert(Uri uri, ContentValues values) {
String table = getQuery(uri);
SQLiteDatabase database = openHelper.getWritableDatabase();
long insertedId = database.insert(table, null, values);
return Uri.parse("content://" + getAuthority() + "/" + table + "/" + insertedId);
}
@Override
public synchronized int update(Uri uri, ContentValues values, String selection,
String[] selectionArgs) {
String table = getQuery(uri);
SQLiteDatabase database = openHelper.getWritableDatabase();
return database.update(table, values, selection, selectionArgs);
}
@Override
public synchronized int delete(Uri uri, String selection, String[] selectionArgs) {
String table = getQuery(uri);
SQLiteDatabase database = openHelper.getWritableDatabase();
return database.delete(table, selection, selectionArgs);
}
/***********************************************
* ContentProvider implementation methods - END
* *********************************************/
/**Get authority
* @return authority string
*/
private String getAuthority() {
return "com.mycompany.androidapp.contentprovider.authorities";
}
/**Extract query from uri
* @param uri
* @return extracted query/table name
*/
private String getQuery(Uri uri){
return uri.getPath().replace("/", "");//remove '/'
}
}
To create tables and populate the database, we can either add some methods to onCreate of SQLiteOpenHelper in onCreate of ContentProviderDb:
openHelper = new SQLiteOpenHelper(getContext(), Constant.DATABASE_NAME, null , 1){
public void onCreate(SQLiteDatabase db){
createTables(); //your own implementation
autoPopulate(); //your own implementation
Or in the activity (with copy from existing database):
private String database_path = "";
if(android.os.Build.VERSION.SDK_INT >= 4.2){
database_path = getBaseContext().getApplicationInfo().dataDir + "/databases/";
} else {
database_path = "/data/data/" + getBaseContext().getPackageName() + "/databases/";
}
if(!existDataBase()) {
try {
if (!copyDataBase()) {
getContext().getContentResolver();//init the ContentProviderDb to create the database
createTables();
autoPopulate();
}
} catch (IOException e) {
Log.e("IOException", "exception in copyDataBase() method");
}
}
/**Check if database exist
* @return true if the database is existed, false if not
*/
private boolean existDataBase() {
File dbFile = new File(getDatabasePath());
return dbFile.exists();
}
/**Get database path
* @return path string
*/
private String getDatabasePath() {
return database_path + Constant.DATABASE_NAME;
}
/**Copy the existing database from the data directory
* @return true if the database is copied, false if not
* @throws IOException
*/
private boolean copyDataBase() throws IOException {
File dbFile = getSourceDatabaseFile();// your own implementation
if (dbFile.exists()) { //copy the database
InputStream in = new FileInputStream(dbFile);
OutputStream out = new FileOutputStream(getDatabasePath());
byte[] mBuffer = new byte[1024];
int mLength;
while ((mLength = in.read(mBuffer))>0) {
out.write(mBuffer, 0, mLength);
}
in.close();
in = null;
out.flush();
out.close();
out = null;
return true;
}
return false;
}
To manipulate the data in the activity, we use
ContentResolver instead ContentProvider.
/**
* Creates tables
*/
private void createTables(){
for(String sql : getTableCreationSQLs()){
execSQL(sql); //query starts with "create table "
}
}
/**Used for executing query, start with create (not select/insert/delete/update)
* @param query
*/
private void execSQL(String query) {
query(query, null, null, null);
}
/**Execute query or select data from table
* @param query : query or table name
* @param columns
* @param whereClause
* @param orderBy
* @return cursor
*/
private Cursor query(String query, String[] columns, String whereClause, String orderBy){
return getBaseContext().getContentResolver().query(getUri(query), columns, whereClause, null, orderBy);
}
private Uri getUri(String query){
return Uri.parse("content://" + getAuthority() + "/" + query);
}
