still under construction, but feel free to explore :)
HOME / Articles/Tutorials / Android Articles/Tutorials / Tutorials on Android programming / Tutorials on Android Storage / Android SQLite database and content provider tutorial

Android SQLite database and content provider tutorial

Guide and examples how to use SQLite database and content provider


Table of Contents




This artcile is based on Android 4.3 API 18

1. What is what?

1.1 What is SQLite

1.1.1 Architecture
  • SQLite is an Open Source embedded SQL database engine.
  • SQLite does not have a separate server process.
  • SQLite reads and writes directly to ordinary disk files.
  • SQLite supports standard relational database features like SQL syntax, transactions and prepared statements.
  • A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
  • The database file format is cross-platform (32/64-bit, big/little-endian).
  • Library size can be less than 500KiB
  • SQLite requires limited memory at runtime and can be made to run in minimal stack space (4KiB) and very little heap (100KiB)

More information about SQLite can be found on the SQLite website: http://www.sqlite.org.

SQLite v3 supported datatypes (storage classes):

  • NULL. The value is a NULL value.

  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

  • BLOB. The value is a blob of data, stored exactly as it was input.

More information about SQLite Datatypes and storage classes can be found on: http://www.sqlite.org/datatype3.html.

As noted above SQLite database reads and writes directly on the file system so it is recommended to perform database operations asynchronously.

In android the default location of a databse is: {ANDROID_DATA}/data/{APP_PACKAGE}/databases/{DB_NAME}
ex: /data/data/com.google.android.email/databases/EmailProvider.db

1.1.2 API

The package android.database.sqlite contains the SQLite database management classes that an application would use to manage its own private database.
If you work with data send by a contant provider you will use android.database package instead.

1.1.3 Tools

There are a number of SQLite database management tools that can be found at https://www.sqlite.org/cvstrac/wiki?p=ManagementTools .
Android SDK also comes with a sqlite3 binary in tools/ directory. This one you can run on your PC and load sqlite3 databases, which can be pulled from the device using "adb pull".
On Eng builds and on Emulators you have sqlite3 binary on the Android filesystem in /system/xbin, however on production builds(the devices on the market) you do not have this client.
You can get sqlite3 binary compiled for ARM from any emulator or download it from our collection of tools. You can push to /data/local/tmp chmod to 777 and execute it there.

1.2 What is Content Provider

  • Content providers are providing content to applications.
  • Content provider is required if you need to share data between multiple applications.
  • Also content providers manage access to the data, encapsulate the data, and provide mechanisms for defining data security.
  • Content provider is not needed if you don't intend to share your data with other applications. However, you do need your own provider to provide custom search suggestions in your own application.

1.3 What is a Cursor

  • A database query returns a Cursor object
  • A Cursor provides random read-write access to the result set returned by a database query
  • A Cursor is basically links to a "list" of rows so it is easy to display its data in a ListView using CursorAdapter
  • A Cursor points to a single row in the returned result set, so not all the data is loaded into memory
  • When returned, a Cursor points before the first row (position -1)

1.4 What is a Loader and CursorLoader

  • CursorLoader executes queries asynchronously on a separate thread, while the ContentResolver.query() is happening on the main/"UI" thread
  • CursorLoader is indirect subclass of Loader
  • Loaders are available to every Activity and Fragment
  • While active Loaders monitor the source of their data and deliver new results when the contents change
  • Calls to a Loader should be done from the main thread as while the loading of data is happening asynchronously on a separate thread, delivering the results should be done on the main thread also
  • Loaders automatically reconnect to the last cursor when being recreated after a configuration change, so thjere is not need to re-query.
  • Loaders are introdced from API level 11 and also part of compatibily libraries from API 4

2. Overview of SQLite for Android

2.1 Main API Classes

2.1.1 android.database.sqlite.SQLiteOpenHelper

This class is normally extended and takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary.

2.1.2 android.database.sqlite.SQLiteDatabase

This class is used to create, delete, execute SQL commands, and perform other common database management tasks.

2.1.3 android.database.Cursor

A Cursor holds the result of a database query.

2.1.4 android.widget.AdapterView

This class and its Subclasses normally have children which are deternined from Adapter, which can be created using a Cursor object.
AdapterView and its Subclasses such as AdapterViewFlipper, ExpandableListView, Gallery, GridView, ListView, Spinner, StackView are the natural way to display data from SQLite database.

2.2 Main Structures for a SQLite database app

2.2.1 Extended class of SQLiteOpenHelper

This class should implement onCreate() and onUpgrade() methods. Here can be specified the database name and current version.

2.2.2 Class for every database table

These are not needed to be separated but it is recommended for clean and easy to maintain code. All table classes can be nested in a single "contract" class
These should define the table name, URI, column names and types and also the whole CREATE and UPDATE statement. This can be used in the SQLiteOpenHelper implementation and helps when generating sql commands.

2.2.3 DbUtils class

Contains utilities to interact with SQLite database This is also not needed to be a separate class structure but also is better for more readable and easy to maintain code.

2.2.4 Activity or Fragment

Of course you will need an (List)Activity/Fragment with probably some AdapterView to display and interact with the data.

2.3 Creating/updating Database (SQLiteOpenHelper class)

2.3.1 onCreate()

Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen.
Here each table's CREATE string can be executed. Note that this is done only once per installation, even if the app is reinstalled it wont be done unless the database is deleted.
However if the database version is changed the onUpgrade will be called.

2.3.2 onUpgrade()

Whenever there is a change in the database schema the version number should be incremented so onUpgrade will be called and appropriate sql statements will be executed to perform the upgrade.
This implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

2.4 Working with the database (SQLiteDatabase class)

To do some actual transactions we can use the SQLiteDatabase object which can be obtained from SQLiteOpenHelper.getWritableDatabase().

2.4.1 insert()

Convenience method for inserting a row into the database.

2.4.2 update()

Convenience method for updating rows in the database.

2.4.3 delete()

Convenience method for deleting rows in the database.

2.4.4 execSQL()

This is more general method to execute a single SQL statement which is not meant to return any data.
It can be used to execute statements like:

  • ALTER TABLE
  • CREATE or DROP table / trigger / view / index / virtual table
  • REINDEX
  • RELEASE
  • SAVEPOINT
  • PRAGMA that returns no data

2.5 Query the database (SQLiteDatabase)

By querying the database we return data linked to a Cursor object.
Sqlite database has two main methods to do this.

2.5.1 rawQuery()

Runs SQL statement. This is more general and you can provide your own raw SQL query.

2.5.2 query()

This is a convenience method that accepts as arguments different parts of an sql query and compiles the full query for you.

2.6 Diplaying the info (AdapterView classes)

The data obtained from a Cursor can be displayed in many ways however the most common ones are by using AdapaterViews.

3. Simple SQLite database app

3.1 AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="org.djodjo.example.SQLiteSimple"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="11"
        android:targetSdkVersion="18" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="org.djodjo.example.SQLiteSimple.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

3.2 DbHelper.java

package org.djodjo.example.SQLiteSimple;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author DjoDjo
 *
 * Helper class for the SQLite database
 */
public class DbHelper extends SQLiteOpenHelper {
	public static final String TAG = DbHelper.class.getSimpleName();
	
	private static final String DATABASE_NAME = "activities.db";
	
	private static final int DATABASE_VERSION = 1;
	
	public DbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		tblActivity.onCreate(db);
	}

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

3.3 DbUtils.java

/**
 * 
 */
package org.djodjo.example.SQLiteSimple;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

/**
 * @author djodjo
 * 
 * Utilities to access data from SQLite database.
 */
public class DbUtils {

	public static final String TAG = DbUtils.class.getSimpleName();

	private SQLiteDatabase database;
	private DbHelper dbHelper;
	private String[] activityColumns = { tblActivity.C_ID, tblActivity.C_Name + " || ' ' || datetime(" + tblActivity.C_When + ", 'unixepoch') as name_datetime"};
	
	public DbUtils(Context context) {
		dbHelper = new DbHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public long addActivity(String name) {
		long newId = -1;
		ContentValues values = new ContentValues();
		values.put(tblActivity.C_Name, name);
		values.put(tblActivity.C_When, Math.round(System.currentTimeMillis()/1000));
		newId = database.insert(tblActivity.TABLE_NAME, null, values);
		return newId;
	}

	public void removeActivity(long id) {
		database.delete(tblActivity.TABLE_NAME,  tblActivity.C_ID + " = " + id, null);
	}

	public Cursor getAllActivitiesCursor() {

	    Cursor cursor = database.query(tblActivity.TABLE_NAME,
	    		activityColumns, null, null, null, null, null);

	    return cursor;
	  }
	
	

}

3.4 MainActivity.java

package org.djodjo.example.SQLiteSimple;

import android.app.ListActivity;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView.AdapterContextMenuInfo;
import android.widget.EditText;
import android.widget.SimpleCursorAdapter; //support lib: android.support.v4.widget.SimpleCursorAdapter;

public class MainActivity extends ListActivity {

	private DbUtils dbUtils;
	SimpleCursorAdapter adapter;
	EditText txtActName;
	
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        dbUtils = new DbUtils(this);
        dbUtils.open();
        
        String[] from = new String[] {tblActivity.C_ID, "name_datetime"};
        int[] to = new int[] {android.R.id.text1, android.R.id.text2};
        adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2, dbUtils.getAllActivitiesCursor(), from, to, 0);
        setListAdapter(adapter);
        
        txtActName = (EditText)findViewById(R.id.txtActName);
        
       registerForContextMenu(getListView());
    }

    
    public void execAdd(View v) {
    	dbUtils.addActivity(txtActName.getText().toString());
    	adapter.changeCursor(dbUtils.getAllActivitiesCursor());
    	txtActName.setText("");
    }
	
    @Override
    public void onCreateContextMenu(ContextMenu menu, View v, ContextMenuInfo menuInfo) {
        super.onCreateContextMenu(menu, v, menuInfo);
        menu.setHeaderTitle("Context Menu");  
        menu.add(Menu.NONE, 0, 0, "Delete"); 
    }
    
    @Override
    public boolean onContextItemSelected(MenuItem item) {
        AdapterContextMenuInfo info = (AdapterContextMenuInfo) item.getMenuInfo();
        switch (item.getItemId()) {
            case 0:
                dbUtils.removeActivity(info.id);
                adapter.changeCursor(dbUtils.getAllActivitiesCursor());
                return true;
            default:
                return super.onContextItemSelected(item);
        }
    }
    
}

3.5 tblActivity.java

package org.djodjo.example.SQLiteSimple;


import android.database.sqlite.SQLiteDatabase;
import android.provider.BaseColumns;
import android.util.Log;


public class tblActivity {


	public static final String TAG = tblActivity.class.getSimpleName();

	public static String TABLE_NAME = tblActivity.class.getSimpleName();
	
	
	//activity ID column
	public static String C_ID = BaseColumns._ID; //eq "_id";
	//activity name column
	public static String C_Name = "activity_name";
	//activity datetime column saved as unix time
	public static String C_When = "activity_datetime";
	
	//public static String[] availableColums = { _ID, C_temp1, C_temp2};
	
	//database structure
	private static final String DATABASE_CREATE = "create table " 
			+ TABLE_NAME
			+ "(" 
			+ "'" + C_ID +  "'" + " integer primary key autoincrement, "
			+ "'" + C_Name + "'" + " text not null,"  
			+ "'" + C_When + "'" + " integer not null"  
			+ ");";
	
	//executed on database creation
	public static void onCreate(SQLiteDatabase database) {
		database.execSQL(DATABASE_CREATE);
	}
	
	//executed on database upgrade to a newer version
	public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
		Log.i(TABLE_NAME, "Upgrading database from version "
				+ oldVersion + " to " + newVersion
				+ ", which will destroy all old data");
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(database);
	}
	
}

4. Overview of ContentProvider

This is a brief overview of Android Content Providers for details check Android Content Providers

4.1 Main Related Classes

4.1.1 ContentProvider
android.content.ContentProvider: providing content to applications.
4.1.2 ContentResolver
android.content.ContentResolver: provides applications access to the content model.
4.1.3 Cursor
android.database.Cursor: holds the result of a content query.
4.1.4 Uri
android.net.Uri: Builds and parses URI references which conform to RFC 2396.

4.2 Access ContentProvider

The data from a content provider is accessed via ContentResolver object. Essentially this object provides and calls the basic "CRUD" (create, retrieve, update, and delete) methods in the ContentProvider object.
The data(table) to work on is accessible via content URI. Content URI is a URI that identifies data in a provider. Normally it is a concatenation of:
  • the string "content://"
  • the Provider's Authority defined in the AndroidManifest.xml file via the android:authorities property
  • the Table Name
For example: "content://org.djodjo.template.example.SQLiteContentProvider/tblActivity"

4.3 Get data from ContentProvider

4.4 Insert, Update, Delete data from ContentProvider

4.5 Create own ContentProvider

4.6 ContentProvider Threading

4.7 ContentProvider Security

5. Create app with own ContentProvider

5.1 AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="org.djodjo.example.SQLiteContentProvider"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="18" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="org.djodjo.example.SQLiteContentProvider.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
         <provider
      android:name="org.djodjo.example.SQLiteContentProvider.DbContentProvider"
      android:authorities="org.djodjo.example.SQLiteContentProvider" >
   </provider>
    </application>

</manifest>

5.2 DbContentProvider.java

package org.djodjo.example.SQLiteContentProvider;

import java.util.List;

import android.content.ContentProvider;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.support.v4.app.LoaderManager.LoaderCallbacks;
import android.text.TextUtils;

/**
 * @author djodjo
 *
 * Provides access to a example database. 
 * There is one table "tblActivity"
 */
public class DbContentProvider extends ContentProvider{

	public static final String TAG = DbContentProvider.class.getName();

	// database
	private DbHelper database;

	public static final String AUTHORITY = "org.djodjo.example.SQLiteContentProvider";
	private static final String DATABASE_NAME = "activities.db";
	
	private static final int DATABASE_VERSION = 1;

	// UriMacher indexes for each table (the table itself, a particular item from that table) 
	private static final int _tblActivity = 1;
	private static final int _tblActivity_ID = 2;

	private static final UriMatcher uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
	static {
		//uri matcher for every table and item to their respective index 
		uriMatcher.addURI(AUTHORITY, tblActivity.TABLE_NAME, _tblActivity);
		uriMatcher.addURI(AUTHORITY, tblActivity.TABLE_NAME + "/#", _tblActivity_ID);
	}

	/**
	 * @author DjoDjo
	 *
	 * Helper class for the SQLite database
	 */
	static class DbHelper extends SQLiteOpenHelper {
		public static final String TAG = DbHelper.class.getName();
		
		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			tblActivity.onCreate(db);
		}

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

	}

	@Override
	public boolean onCreate() {
		database = new DbHelper(getContext());
		return true;
	}

	@Override
	public String getType(Uri uri) {
		switch (uriMatcher.match(uri)) {
		case _tblActivity: return tblActivity.CONTENT_TYPE;
		case _tblActivity_ID: return tblActivity.CONTENT_ITEMTYPE;
		default:
			throw new IllegalArgumentException("Unknown URI " + uri);
		}
	}

	@Override
	public Cursor query(Uri uri, String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
		// Uisng SQLiteQueryBuilder instead of query() method
		SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();

		// Check if the caller has requested a column which does not exists
		//checkColumns(projection);

		int uriType = uriMatcher.match(uri);
		switch (uriType) {
		case _tblActivity:
			queryBuilder.setTables(tblActivity.TABLE_NAME);
			break;
		case _tblActivity_ID:
			queryBuilder.setTables(tblActivity.TABLE_NAME);
			queryBuilder.appendWhere(tblActivity.C_ID + "="
					+ uri.getLastPathSegment());
			break;
		default:
			throw new IllegalArgumentException("Unknown URI: " + uri);
		}

		SQLiteDatabase db = database.getWritableDatabase();
		Cursor cursor = queryBuilder.query(db, projection, selection,
				selectionArgs, null, null, sortOrder);
		
		// Make sure that potential listeners are getting notified
		cursor.setNotificationUri(getContext().getContentResolver(), uri);

		return cursor;
	}

	@Override
	public int delete(Uri uri, String selection, String[] selectionArgs) {
		int uriType = uriMatcher.match(uri);
		SQLiteDatabase sqlDB = database.getWritableDatabase();
		int rowsDeleted = 0;
		String foundTable = "";
		String whereClause = "";
		switch (uriType) {
		case _tblActivity:
			foundTable = tblActivity.TABLE_NAME;
			whereClause = selection;
			break;
		case _tblActivity_ID:
			String id = uri.getLastPathSegment();
			foundTable = tblActivity.TABLE_NAME;
			whereClause = tblActivity.C_ID + "=" + id; 
			if (TextUtils.isEmpty(selection)) {
				selectionArgs = null;
			} else {
				whereClause += " and " + selection;
			}
			break;
		default:
			throw new IllegalArgumentException("Unknown URI: " + uri);
		}
		rowsDeleted = sqlDB.delete( foundTable, whereClause, selectionArgs);
		getContext().getContentResolver().notifyChange(uri, null);
		return rowsDeleted;

	}

	@Override
	public Uri insert(Uri uri, ContentValues values) {
		int uriType = uriMatcher.match(uri);
		SQLiteDatabase db = database.getWritableDatabase();
		String foundTable = "";
		long id = 0;
		switch (uriType) {
		case _tblActivity:
			foundTable = tblActivity.TABLE_NAME;
			break;
		default:
			throw new IllegalArgumentException("Unknown URI: " + uri);
		}
		id = db.insert(foundTable, null, values);
		getContext().getContentResolver().notifyChange(uri, null);
		return Uri.parse(foundTable + "/" + id);
	}


	@Override
	public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
		int uriType = uriMatcher.match(uri);
		SQLiteDatabase sqlDB = database.getWritableDatabase();
		int rowsUpdated = 0;
		String foundTable = "";
		String whereClause = "";
		switch (uriType) {
		case _tblActivity:
			foundTable = tblActivity.TABLE_NAME;
			whereClause = selection;
			break;
		case _tblActivity_ID:
			String id = uri.getLastPathSegment();
			foundTable = tblActivity.TABLE_NAME;
			whereClause = tblActivity.C_ID + "=" + id; ;
			if (TextUtils.isEmpty(selection)) {
				selectionArgs = null;
			} else {
				whereClause += " and " + selection;
			}
			break;
		default:
			throw new IllegalArgumentException("Unknown URI: " + uri);
		}
		rowsUpdated = sqlDB.update(foundTable, values, whereClause, selectionArgs);
		getContext().getContentResolver().notifyChange(uri, null);
		return rowsUpdated;

	}

	//	private void checkColumns(String[] projection) {
	//		
	//		if (projection != null) {
	//			HashSet<String> requestedColumns = new HashSet<String>(
	//					Arrays.asList(projection));
	//			HashSet<String> availableColumns = new HashSet<String>(
	//					Arrays.asList(available));
	//			// Check if all columns which are requested are available
	//			if (!availableColumns.containsAll(requestedColumns)) {
	//				throw new IllegalArgumentException(
	//						"Unknown columns in projection");
	//			}
	//		}
	//	}


}

5.3 DbUtils.java

/**
 * 
 */
package org.djodjo.example.SQLiteContentProvider;

import java.net.URI;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.net.Uri;

/**
 * @author djodjo
 * 
 * Utilities to access data from SQLite database.
 */
public class DbUtils {

	public static final String TAG = DbUtils.class.getSimpleName();

	private String[] activityColumns = { tblActivity.C_ID, tblActivity.C_Name + " || ' ' || datetime(" + tblActivity.C_When + ", 'unixepoch') as name_datetime"};

	ContentResolver mContntResolver;
	Uri mUri;
	
	public DbUtils(Context context, Uri uri) {
		mContntResolver = context.getContentResolver();
		mUri = uri;
	}

	public Uri addActivity(String name) {
		Uri newId;
		ContentValues values = new ContentValues();
		values.put(tblActivity.C_Name, name);
		values.put(tblActivity.C_When, Math.round(System.currentTimeMillis()/1000));
		newId = mContntResolver.insert(mUri, values);
		return newId;
	}

	public void removeActivity(long id) {
		mContntResolver.delete(mUri, tblActivity.C_ID + " = " + id, null);
	}

	public Cursor getAllActivitiesCursor() {

	    Cursor cursor = mContntResolver.query(mUri,
	    		activityColumns, null, null, null);

	    return cursor;
	  }
}

5.4 MainActivity.java

package org.djodjo.example.SQLiteContentProvider;




import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v4.app.LoaderManager.LoaderCallbacks;
import android.support.v4.content.Loader;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView.AdapterContextMenuInfo;
import android.widget.EditText;
import android.widget.SimpleCursorAdapter; //support lib: android.support.v4.widget.SimpleCursorAdapter;

public class MainActivity extends ListActivity {

	private DbUtils dbUtils;
	SimpleCursorAdapter adapter;
	EditText txtActName;
	
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        dbUtils = new DbUtils(this, tblActivity.CONTENT_URI);
       

        String[] from = new String[] {tblActivity.C_ID, "name_datetime"};
        int[] to = new int[] {android.R.id.text1, android.R.id.text2};
        adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_2, dbUtils.getAllActivitiesCursor(), from, to, 0);
        setListAdapter(adapter);
        
        txtActName = (EditText)findViewById(R.id.txtActName);
        
       registerForContextMenu(getListView());
    }

    
    public void execAdd(View v) {
    	dbUtils.addActivity(txtActName.getText().toString());
    	adapter.changeCursor(dbUtils.getAllActivitiesCursor());
    	txtActName.setText("");
    }
	
    @Override
    public void onCreateContextMenu(ContextMenu menu, View v, ContextMenuInfo menuInfo) {
        super.onCreateContextMenu(menu, v, menuInfo);
        menu.setHeaderTitle("Context Menu");  
        menu.add(Menu.NONE, 0, 0, "Delete"); 
    }
    
    @Override
    public boolean onContextItemSelected(MenuItem item) {
        AdapterContextMenuInfo info = (AdapterContextMenuInfo) item.getMenuInfo();
        switch (item.getItemId()) {
            case 0:
                dbUtils.removeActivity(info.id);
                adapter.changeCursor(dbUtils.getAllActivitiesCursor());
                return true;
            default:
                return super.onContextItemSelected(item);
        }
    }


    
}

5.5 tblActivity.java

package org.djodjo.example.SQLiteContentProvider;


import android.content.ContentResolver;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.provider.BaseColumns;
import android.util.Log;


public class tblActivity {


	public static String TABLE_NAME = tblActivity.class.getSimpleName();
	
  
    
	//uri and MIME type definitions. needed only if own provider used
    public static final Uri CONTENT_URI = Uri.parse("content://" + DbContentProvider.AUTHORITY + "/" + TABLE_NAME);
    public static final String CONTENT_TYPE = ContentResolver.CURSOR_DIR_BASE_TYPE + "/" + DbContentProvider.AUTHORITY + "." + TABLE_NAME;
	public static final String CONTENT_ITEMTYPE = ContentResolver.CURSOR_ITEM_BASE_TYPE + "/" + DbContentProvider.AUTHORITY + "." + TABLE_NAME;
	
	
	//activity ID column
	public static String C_ID = BaseColumns._ID; //eq "_id";
	//activity name column
	public static String C_Name = "activity_name";
	//activity datetime column saved as unix time
	public static String C_When = "activity_datetime";
	
	public static final String DEFAULT_SORT_ORDER = C_ID;
	
	
	//database structure
	private static final String DATABASE_CREATE = "create table " 
			+ TABLE_NAME
			+ "(" 
			+ "'" + C_ID +  "'" + " integer primary key autoincrement, "
			+ "'" + C_Name + "'" + " text not null,"  
			+ "'" + C_When + "'" + " integer not null"  
			+ ");";
	
	//executed on database creation
	public static void onCreate(SQLiteDatabase database) {
		database.execSQL(DATABASE_CREATE);
	}
	
	//executed on database upgrade to a newer version
	public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
		Log.i(TABLE_NAME, "Upgrading database from version "
				+ oldVersion + " to " + newVersion
				+ ", which will destroy all old data");
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(database);
	}
	
}

6. Overview of Cursor and CursorLoader

7. Performance

8. Links

Author: DjoDjo