SQLite Database with ContentProvider in Android

ContentProvider is one of the main component of Android Application. Content Providers are the wrappers to expose data stored locally or remotely. ContentProviders are written like REST like URIs for example to retrieve n image from the Gallery, the URI to create might be like this:

content://media/external/images/media/123

Android comes with built in Providers for storage, media, contacts. Let’s see in this post how to create ContentProvider for SQLite backed storage. In order to create ContentProvider you need to extend ContentProvider and implement methods like insert, query, delete, update etc. We will also extend SQLiteOpenHelper that will implement helper functions to create SQLite database.

We will be creating a basic Notes app with functionalities to create, delete and update notes.

Init ContentProvider

AndroidStudio provides a direct menu option to create bare bone ContentProvider class. Create an empty Android project and open FIle -> New -> Other and select Content Provider

Replace URI Authorities with your unique id. The common approach is to replace package id with your project package id. After finishing the configuration AndroidStudio will create ContentProvider class with details passed in the previous step.

public class MyContentProvider extends ContentProvider {
    public MyContentProvider() {
    }

    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {
        // Implement this to handle requests to delete one or more rows.
        throw new UnsupportedOperationException("Not yet implemented");
    }

    @Override
    public String getType(Uri uri) {
        // TODO: Implement this to handle requests for the MIME type of the data
        // at the given URI.
        throw new UnsupportedOperationException("Not yet implemented");
    }

    @Override
    public Uri insert(Uri uri, ContentValues values) {
        // TODO: Implement this to handle requests to insert a new row.
        throw new UnsupportedOperationException("Not yet implemented");
    }

    @Override
    public boolean onCreate() {
        // TODO: Implement this to initialize your content provider on startup.
        return false;
    }

    @Override
    public Cursor query(Uri uri, String[] projection, String selection,
                        String[] selectionArgs, String sortOrder) {
        // TODO: Implement this to handle query requests from clients.
        throw new UnsupportedOperationException("Not yet implemented");
    }

    @Override
    public int update(Uri uri, ContentValues values, String selection,
                      String[] selectionArgs) {
        // TODO: Implement this to handle requests to update one or more rows.
        throw new UnsupportedOperationException("Not yet implemented");
    }

}

Create Database

Next step is to initialize and create SQLite database. Create a new class and extend it from SQLiteOpenHelper

public class DatabaseHelper extends SQLiteOpenHelper {

    public DatabaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

Now we have our class ready to create database for notes. The next step is to design the database and define table to store note object.

Open DatabaseHelper class and create Notes database table.

package com.codeexa.com.sqliteexample;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;


public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "notes.db";
    public static final int DATABASE_VERSION = 1;
    public static final String NOTES_TABLE_NAME = "notes";
    public static final String NOTES_ID = "_ID";
    public static final String NOTES_TITLE = "title";
    public static final String NOTES_CONTENT = "content";
    public static final String NOTES_UPDATED_TIME = "updatedAt";

    public DatabaseHelper(@Nullable Context context) {
        super(context,
                DATABASE_NAME,
                null,
                DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " +  NOTES_TABLE_NAME + "( " +
                NOTES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                NOTES_TITLE	 + " TEXT ,"  +
                NOTES_CONTENT	 + " TEXT, " +
                NOTES_UPDATED_TIME	 + " INTEGER);" );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

DatabaseHelper constructor create database for us and implement onCreate function to create tables. Once DatabaseHelper is ready we can override onCreate function of ContentProvider to initialize our DatabaseHelper class.

Define our AUTHORITY and CONTENT TYPES in ContentProvider.

    public static final String AUTHORITY
            = "com.codeexa.com.sqliteexample.provider";
    public static final Uri CONTENT_URI =
            Uri.parse("content://" + AUTHORITY + "/notes");

    public static final String CONTENT_COLLECTION_TYPE = "vnd.android.cursor.dir/vnd.notes.event";

    /**
     * The MIME type of a {@link #CONTENT_URI} sub-directory of a single note.
     */
    public static final String CONTENT_ITEM_TYPE = "vnd.android.cursor.item/vnd.notes.event";

Create URI Matcher that is used to distinguish one URI from other. We will also define projection map which is similar to SQL “as” command. ProjectionMaps are used to rename the columns in the query results.

    private static UriMatcher mNotesUriMatcher;
    private static HashMap<String, String> sNotesProjectionMap;

    private static final int NOTES_COLLECTION = 1;
    private static final int SINGLE_NOTE = 2;

    static {
        mNotesUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
        sNotesProjectionMap = new HashMap<>();

        mNotesUriMatcher.addURI(AUTHORITY, "notes", NOTES_COLLECTION);
        mNotesUriMatcher.addURI(AUTHORITY, "notes/#", SINGLE_NOTE);


        sNotesProjectionMap = new HashMap<>();
        sNotesProjectionMap.put(NOTES_ID, NOTES_ID);
        sNotesProjectionMap.put(NOTES_TITLE, NOTES_TITLE);
        sNotesProjectionMap.put(NOTES_CONTENT, NOTES_CONTENT);
        sNotesProjectionMap.put(NOTES_UPDATED_TIME, NOTES_UPDATED_TIME);

    }

Override getType function to handle requests for the MIME type of the data.

    @Override
    public String getType(Uri uri) {
       
        switch(mNotesUriMatcher.match(uri)){
            case 1:
                return CONTENT_COLLECTION_TYPE;
            case 2:
                return CONTENT_ITEM_TYPE;
            default:
                throw new IllegalArgumentException("URI not supported");
        }
    }

Override onCreate to initialize database. This will create database with version and tables when the app is run for the first time.

    DatabaseHelper mDatabaseHelper;

    public MyContentProvider() {
    }

    @Override
    public boolean onCreate() {
        // TODO: Implement this to initialize your content provider on startup.
        mDatabaseHelper = new DatabaseHelper(getContext());
        return true;
    }

Update insert function  which takes ContentValues as the parameter.  We take the reference of database and executes insert query to add a new row in the database. If new row is created then we notify all the observers.

    @Override
    public Uri insert(Uri uri, ContentValues values) {
        try {
            long now = System.currentTimeMillis();
            values.put(NOTES_UPDATED_TIME, now);
            SQLiteDatabase db = mDatabaseHelper.getWritableDatabase();
            long rowId = db.insert(NOTES_TABLE_NAME,
                    null, values);
            if (rowId > 0) {
                Uri insertedBookUri =
                        ContentUris.withAppendedId(CONTENT_URI, rowId);
                getContext().getContentResolver()
                        .notifyChange(insertedBookUri, null);
                return insertedBookUri;
            }
        }
        catch (SQLException e ){

        }
        throw new SQLException("Failed to insert");
    }

Now we can jump to query and see how to query all the notes or get details of a particular note.

    @Override
    public Cursor query(Uri uri, String[] projection, String selection,
                        String[] selectionArgs, String sortOrder) {
        // TODO: Implement this to handle query requests from clients.
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(NOTES_TABLE_NAME);

        switch (mNotesUriMatcher.match(uri)) {
            case NOTES_COLLECTION:
                qb.setProjectionMap(sNotesProjectionMap);
                break;
            case SINGLE_NOTE:
                qb.setProjectionMap(sNotesProjectionMap);
                qb.appendWhere(DatabaseHelper.NOTES_ID + "="
                        + uri.getPathSegments().get(1));
                break;
        }

        SQLiteDatabase db = mDatabaseHelper.getReadableDatabase();
        Cursor cursor = qb.query(db, projection, selection,
                selectionArgs, null, null, sortOrder);
        cursor.setNotificationUri(getContext().getContentResolver(),uri);
        return cursor;
    }

Now our content provider and database is ready for actual query operations. Lets see the code to query and insert notes from activity.

Create Notes object.

package com.codeexa.com.sqliteexample;

public class Notes {

    private int id;
    private String title;
    private String content;
    private long updatedAt;

    public Notes(int id, String title, String content, long updatedAt) {
        this.id = id;
        this.title = title;
        this.content = content;
        this.updatedAt = updatedAt;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public long getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(long updatedAt) {
        this.updatedAt = updatedAt;
    }
}

I am providing code snippets to add and list notes from the database.

    private void addNote(Context context, String title, String noteContent){
        ContentValues cv = new ContentValues();
        cv.put(DatabaseHelper.NOTES_TITLE, title);
        cv.put(DatabaseHelper.NOTES_CONTENT, noteContent);

        ContentResolver cr = context.getContentResolver();
        Uri uri
                =  MyContentProvider.CONTENT_URI;
        Uri insertedUri = cr.insert(uri, cv);
        Log.i("TAG", "Note saved. URI:"+ insertedUri);
    }


    private void listNotes(Context context){
        Uri uri
                = MyContentProvider.CONTENT_URI;
        Cursor cursor = null;
        try {
            cursor = context.getContentResolver().query(uri,
                    null,
                    null,
                    null,
                    null);
            List<Notes> notesList = new ArrayList<>();
            if(cursor.moveToFirst()){
                do{
                    int id = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.NOTES_ID));
                    String title = cursor.getString(cursor.getColumnIndex(DatabaseHelper.NOTES_TITLE));
                    String content = cursor.getString(cursor.getColumnIndex(DatabaseHelper.NOTES_CONTENT));
                    long time = cursor.getLong(cursor.getColumnIndex(DatabaseHelper.NOTES_UPDATED_TIME));
                    notesList.add(new Notes(id, title, content, time));
                }while(cursor.moveToNext());
            }
        }finally {
            // close cursor
            if(cursor!=null)
                cursor.close();

        }
    }

Pre-Populated SQLite Database

So far we have seen how to create our database in Android Application. One may ask, what if I need to use a pre populated database in the app?

We can also ship our app with a pre filled database. In such scenario we need to keep the pre filled database inside assets folder and copy your database from local assets folder to apps newly created empty database in the system’s folder.

Let’s say you have saved your local database inside assets folder as notes.db

Update DatabaseHelper class to support copying local database to system database.

package com.codeexa.com.sqliteexample;

import android.content.Context;
import android.content.res.AssetManager;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;


public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "notes.db";
    public static final int DATABASE_VERSION = 1;
    public static final String NOTES_TABLE_NAME = "notes";
    public static final String NOTES_ID = "_ID";
    public static final String NOTES_TITLE = "title";
    public static final String NOTES_CONTENT = "content";
    public static final String NOTES_UPDATED_TIME = "updatedAt";
    private final Context myContext;
    private final String DB_PATH;


    private boolean createDatabase = false;

    public DatabaseHelper(@Nullable Context context) {
        super(context,
                DATABASE_NAME,
                null,
                DATABASE_VERSION);
        myContext = context;
        DB_PATH = myContext.getDatabasePath(DATABASE_NAME).getAbsolutePath();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       /* db.execSQL("CREATE TABLE " +  NOTES_TABLE_NAME + "( " +
                NOTES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                NOTES_TITLE	 + " TEXT ,"  +
                NOTES_CONTENT	 + " TEXT, " +
                NOTES_UPDATED_TIME	 + " INTEGER);" );*/
        createDatabase = true;

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    public void initializeDataBase() {
        
        getWritableDatabase();

        if (createDatabase) {
           
            try {
              
                copyDataBase();
            } catch (IOException e) {
                e.printStackTrace();
                throw new Error("Error copying database");
            }
        }
    }

    
    private void copyDataBase() throws IOException {
        /*
         * Close SQLiteOpenHelper so it will commit the created empty database
         * to internal storage.
         */
        try {
            close();
        } catch (RuntimeException e) {

        }
        AssetManager am = myContext.getAssets();
        OutputStream os = new FileOutputStream(DB_PATH);
        byte []b = new byte[1024];
        int r;


        InputStream is = am.open(DATABASE_NAME);
        while((r = is.read(b)) != -1)
            os.write(b, 0, r);
        is.close();

        os.close();


        getWritableDatabase().close();
    }
}

Update ContentProvider to initialize database helper.

    @Override
    public boolean onCreate() {
        mDatabaseHelper = new DatabaseHelper(getContext());
        mDatabaseHelper.initializeDataBase();
        return true;
    }

 

 

Leave a Reply