Best practices for Android databases [closed]

Question

My Android application needs a local database. Which is the best manner for this? which class sub I use, subclass, reimplement, etc. I have found too much information on the net but I still don't know which are the best practices.

Answer 1

It's a pretty broad question and depends a little on your level on experience and usage.

But the common practice is to create your own ContentProvider which will abstract the access to the database. This way you can use Uri to execute select/update/delete/insert queries.

For the SQLite database itself, use SQLiteOpenHelper to abstract the creation and upgrading of your SQLite database. This will allow you to upgrade your database without the user losing all of his data in an easy manner.

I can attach a piece of code I used in one of my older projects which get you started. But implementing the whole thing may be out of scope of a single question/answer.

public class MyServiceProvider extends ContentProvider {
    private SQLiteDatabase db;

    @Override
    public boolean onCreate() {
        // Initialize the database and assign it to the private variable
        MyDatabaseHelper sqlHelper = new MyDatabaseHelper(getContext());
        db = sqlHelper.getReadableDatabase();

        return (db == null)?false:true;
    }

    @override
    Cursor query (Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
        // handle the query here, form it, do your checks and then access the DB
        db.query(....);
    }
}

class MyDatabaseHelper extends SQLiteOpenHelper {
    private static final String LOG_TAG = "MyAppTag";
    private static final String DB_NAME = "Databasename";
    private static final String TABLE_NAME = "Tablename";
    private static final int DATABASE_VERSION = 2;
    public static MyServiceProvider.CONTENT_URI = Uri.parse("content://com.mycompany.myApp.MyAppService/myTableOrIdentifier");

    public MyDatabaseHelper(Context context){
        super(context, DB_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE IF NOT EXISTS ....";
        db.execSQL(sql);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Here you can perform updates when the database structure changes
        // Begin transaction
        db.beginTransaction();

        try {
            if(oldVersion<2){
                // Upgrade database structure from Version 1 to 2
                String alterTable = "ALTER ....";

                db.execSQL(alterTable);
                Log.i(LOG_TAG,"Successfully upgraded to Version 2");
            }
            // This allows you to upgrade from any version to the next most 
            // recent one in multiple steps as you don't know if the user has
            // skipped any of the previous updates
            if(oldVersion<3){
                // Upgrade database structure from Version 2 to 3
                String alterTable = "ALTER ....";

                db.execSQL(alterTable);
                Log.i(LOG_TAG,"Successfully upgraded to Version 3");
            }

            // Only when this code is executed, the changes will be applied 
            // to the database
            db.setTransactionSuccessful();
        } catch(Exception ex){
            ex.printStackTrace();
        } finally {
            // Ends transaction
            // If there was an error, the database won't be altered
            db.endTransaction();
        }
    }
}

and then you can use cursors to interact with the database.

ContentResolver contentResolver = getContentResolver();
...
Cursor c = contentResolver.query(
        // The Uri results in content://com.mycompany.myApp.MyAppService/myTableOrIdentifier/someId
        Uri.withAppendedPath(MyServiceProvider.CONTENT_URI, someId),
        new String[] {
            // only get fields we need!
            "MyDbFieldIneed"
        },
        null, null, null);

This will return a Cursor which will you can iterate through and get the results. This is also how most things in Android are implemented (i.e. obtaining an Address from the address book works via Uri and Cursor too).

Edit: I realized the links are hard to see with the code highlights. Here the link of important classes you will need.

• ContentProvider | Android Developers
• SQLiteOpenHelper | Android Developers
• Cursor | Android Developers
• UriMatcher | Android Developers

Edit 2: Also if you work with multiple tables, UriMatcher is an important source too