Android SQLite Database - Tutorial

SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, you can write an integer into a string column.

More information about SQLite is available on http://www.sqlite.org .

SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration. You specify the SQL for working with the database and the database is automatically managed for you.

Working with databases can be slow. Therefore is it recommended to perform these task in the background, for example via anAsyncTask.

If your application creates an database this database is saved in the directoryDATA/data/APP_NAME/databases/FILENAME. DATA is the path which Environment.getDataDirectory()returns, APP_NAME is your application name and FILENAME is the name you give the database during creation.Environment.getDataDirectory() usually return the SD card as location.

A SQLite database is private to the application which creates it. If you want to share data with other applications you can use aContentProvider. If data is not shared it typically easier to work directly with the database. ContentProviders are not part of this tutorial.

The package android.database contains all general classes for working with databases. android.database.sqlitecontains the SQLite specific classes.

To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper. In this class you need to override the methods onCreate() to create the database and onUpgrade() to upgrade the database in case of changes in the database schema. Both methods receive an SQLiteDatabase object which represents the database.

SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to anSQLiteDatabase object which allows database access either in read or write mode.

For the primary key of the database tables you should always use the identifier _id as some of Android functions rely on this standard.

A best practice is to create per table a separate class which define static onCreate() and onUpdate() methods. These methods are then called in the corresponding methods of SQLiteOpenHelper . This way your implementation ofSQLiteOpenHelper will not get to large even if you have several tables.

SQLiteDatabase is the base class for working with an SQLite database in Android and provides methods to open, query, update and close the database. More specifically SQLiteDatabase provides the insert(), update() and delete() methods. The execSQL() method allows to execute directly SQL. The object ContentValues allow to define key/values for insert and update. The key is the column and the value is the value for this column.

Queries can be created via the method rawQuery() which accepts SQL or query() which provides an interface for specifying dynamic data or SQLiteQueryBuilder.

For example to run a rawQuery() you can do the following:

Cursor cursor = getReadableDatabase().rawQuery("select * from todo where _id = ?", new String[] { id });

The method query() has the following parameters.

If all data should be selected you can pass null as the where clause. The where clause is specified without where, for example _id=19 and summary=? . If several values are required via ? you pass them in the valuesForWhereClause array to the query. In general if something is not required you can pass null, e.g. for the group by clause.

For example to run a query() you can do the following:

return database.query(DATABASE_TABLE, 
	new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION }, 
	null, null, null, null, null);

A query returns always a Cursor. A Cursor represents the result of a query and basically points always to one row of the database. This way Android can buffer the results efficiently as it does not have to load all data into memory.

To get the number of elements use the method getCount(). To move between individual data rows, you can use the methodsmoveToFirst() and moveToNext(). Via the method isAfterLast() you can check if there is still some data.

To access data Cursor provides typed get methods, e.g. getLong(columnIndex), getString(columnIndex) whereby the columnIndex is the number of the column you are accessing.

A Cursor can be directly used via the SimpleCursorAdapter in ListViews.

ListViews are views (widgets) which makes it easy to display a list of elements. ListActivities are specialized Activitieswhich make the usage of ListViews easier. This tutorial will use ListActivities but not look into the details of them. Please see http://www.vogella.de/articles/AndroidListView/article.html for an introduction into ListViews and ListActivities.

To work with databases and ListViews you can use the SimpleCursorAdapter. The SimpleCursorAdapter allows to set a layout for each row of the ListViews. You also define an array column names and an array of view Ids. The adapter will map the columns to the views based on the Cursor passed to it.

We will create a Todo application which allow the user to maintain tasks for himself. These items will be stored in the SQLitedatabase.

The application will consists out of two Activities, one for seeing a list of all todo items and one for creating / maintaining a specific todo. Both Activities will be communicating via Intents.

The resulting application will look similar to the following.

Create the project de.vogella.android.todos with the Activity called TodosOverviewActivity. . Create also anotherActivity called TodoDetailsActivity.

Create the package de.vogella.android.todos.database. This package will store the classes for the database handling.

We will create a separate class for creating and updating the todo table.

package de.vogella.android.todos.database;

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

public class TodoTable {
	// Database creation SQL statement
	private static final String DATABASE_CREATE = "create table todo "
			+ "(_id integer primary key autoincrement, "
			+ "category text not null, " + "summary text not null,"
			+ " description text not null);";

	public static void onCreate(SQLiteDatabase database) {
		database.execSQL(DATABASE_CREATE);
	}

	public static void onUpgrade(SQLiteDatabase database, int oldVersion,
			int newVersion) {
		Log.w(TodoTable.class.getName(), "Upgrading database from version "
				+ oldVersion + " to " + newVersion
				+ ", which will destroy all old data");
		database.execSQL("DROP TABLE IF EXISTS todo");
		onCreate(database);
	}
}

Create the following TodoDatabaseHelper class. This class extends SQLiteOpenHelper and call the static methods of the TodoTable helper class.

package de.vogella.android.todos.database;

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

public class TodoDatabaseHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "applicationdata";

	private static final int DATABASE_VERSION = 1;

	public TodoDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Method is called during creation of the database
	@Override
	public void onCreate(SQLiteDatabase database) {
		TodoTable.onCreate(database);
	}

	// Method is called during an upgrade of the database,
	// e.g. if you increase the database version
	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion,
			int newVersion) {
		TodoTable.onUpgrade(database, oldVersion, newVersion);
	}
}

Based on TodoDatabaseHelper class we can write the TodoDbAdapter class which will provide the functionality to query, create and update todos.

The method open() will open the database via the TodoDatabaseHelper lass. For updating and creating values we use the android.content.ContentValues class as described earlier.

package de.vogella.android.todos.database;

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

public class TodoDbAdapter {

	// Database fields
	public static final String KEY_ROWID = "_id";
	public static final String KEY_CATEGORY = "category";
	public static final String KEY_SUMMARY = "summary";
	public static final String KEY_DESCRIPTION = "description";
	private static final String DB_TABLE = "todo";
	private Context context;
	private SQLiteDatabase db;
	private TodoDatabaseHelper dbHelper;

	public TodoDbAdapter(Context context) {
		this.context = context;
	}

	public TodoDbAdapter open() throws SQLException {
		dbHelper = new TodoDatabaseHelper(context);
		db = dbHelper.getWritableDatabase();
		return this;
	}

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

	
/**
	 * Create a new todo If the todo is successfully created return the new
	 * rowId for that note, otherwise return a -1 to indicate failure.
	 */

	public long createTodo(String category, String summary, String description) {
		ContentValues values = createContentValues(category, summary,
				description);

		return db.insert(DB_TABLE, null, values);
	}

	
/**
	 * Update the todo
	 */

	public boolean updateTodo(long rowId, String category, String summary,
			String description) {
		ContentValues values = createContentValues(category, summary,
				description);

		return db.update(DB_TABLE, values, KEY_ROWID + "=" + rowId, null) > 0;
	}

	
/**
	 * Deletes todo
	 */

	public boolean deleteTodo(long rowId) {
		return db.delete(DB_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	
/**
	 * Return a Cursor over the list of all todo in the database
	 * 
	 * @return Cursor over all notes
	 */

	public Cursor fetchAllTodos() {
		return db.query(DB_TABLE, new String[] { KEY_ROWID, KEY_CATEGORY,
				KEY_SUMMARY, KEY_DESCRIPTION }, null, null, null, null, null);
	}

	
/**
	 * Return a Cursor positioned at the defined todo
	 */

	public Cursor fetchTodo(long rowId) throws SQLException {
		Cursor mCursor = db.query(true, DB_TABLE, new String[] { KEY_ROWID,
				KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION }, KEY_ROWID + "="
				+ rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	private ContentValues createContentValues(String category, String summary,
			String description) {
		ContentValues values = new ContentValues();
		values.put(KEY_CATEGORY, category);
		values.put(KEY_SUMMARY, summary);
		values.put(KEY_DESCRIPTION, description);
		return values;
	}

}

In the following we will create several resources which we will later use in our application.

First define a menu listmenu.xml in the folder res/menu. This XML file will be used to define the option menu in our application.

<?xml version="1.0" encoding="utf-8"?>
<menu
  xmlns:android="http://schemas.android.com/apk/res/android">
<item android:id="@+id/insert" android:title="Insert"></item>
</menu>

The user will be able to select for priority for the tasks he maintains. For the priorities we create an string array. Createpriority.xml under res/values.

<?xml version="1.0" encoding="utf-8"?>
<resources>
<string-array name="priorities"><item>Urgent</item>
<item>Reminder</item>
</string-array>
</resources>

Define also additional strings for our application. Edit strings.xml under res/values.

<?xml version="1.0" encoding="utf-8"?>
<resources>
	<string name="hello">Hello World, Todo!</string>
	<string name="app_name">Todo</string>
	<string name="no_todos">Currently there are no Todo items maintained</string>
	<string name="menu_insert">Add Item</string>
	<string name="menu_delete">Delete Todo</string>
	<string name="todo_summary">Summary</string>
	<string name="todo_description">Delete Todo</string>
	<string name="todo_edit_summary">Summary</string>
	<string name="todo_edit_description">Description</string>
	<string name="todo_edit_confirm">Confirm</string>
	<color name="listcolor">#FFE87C</color>
	<color name="black">#000000</color>
</resources>

We defined three layouts, one for the list, one for the rows of the list and one for the maintenance of an individual task.

Please note that the row layout refers to an icon. Please replace this with an icon of your choice.

Create the layout todo_list.xml. This layout will define how the list looks like.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:background="@color/listcolor"
    android:orientation="vertical" >

    <ListView
        android:id="@android:id/list"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >
    </ListView>

    <TextView
        android:id="@android:id/empty"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/no_todos" />

</LinearLayout>

Paste an icon called reminder into your res/drawable folders ( drawable-hdpi, drawable-mdpi, drawable-ldpi ) This icon will be used in the row layout. If you don't want to use an icon you can alternatively you could remove the icon definition from the following layout definition.

Create the layout todo_row.xml in the folder res/layout which will be used for the layout of an individual row.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content" >

    <ImageView
        android:id="@+id/icon"
        android:layout_width="30px"
        android:layout_height="40px"
        android:layout_marginLeft="4px"
        android:layout_marginRight="8px"
        android:layout_marginTop="8px"
        android:src="@drawable/reminder" >
    </ImageView>

    <TextView
        android:id="@+id/label"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="6px"
        android:lines="1"
        android:text="@+id/TextView01"
        android:textColor="@color/black"
        android:textSize="40px" >
    </TextView>

</LinearLayout>

Create the layout todo_edit. This layout will be used to display and edit an individual task in the second Activity.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:background="@color/listcolor"
    android:orientation="vertical" >

    <Spinner
        android:id="@+id/category"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:entries="@array/priorities" >
    </Spinner>

    <LinearLayout
        android:id="@+id/LinearLayout01"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <EditText
            android:id="@+id/todo_edit_summary"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:hint="Summary"
            android:imeOptions="actionNext" >
        </EditText>
    </LinearLayout>

    <EditText
        android:id="@+id/todo_edit_description"
        android:layout_width="fill_parent"
        android:layout_height="fill_parent"
        android:layout_weight="1"
        android:gravity="top"
        android:hint="Description"
        android:imeOptions="actionNext" >
    </EditText>

    <Button
        android:id="@+id/todo_edit_button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/todo_edit_confirm" >
    </Button>

</LinearLayout>

Finally change the coding of your activities to the following. First TodosOverviewActivity.java.

package de.vogella.android.todos;

import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView.AdapterContextMenuInfo;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import de.vogella.android.todos.database.TodoDbAdapter;

public class TodosOverviewActivity extends ListActivity {
	private TodoDbAdapter dbHelper;
	private static final int ACTIVITY_CREATE = 0;
	private static final int ACTIVITY_EDIT = 1;
	private static final int DELETE_ID = Menu.FIRST + 1;
	private Cursor cursor;

	
/** Called when the activity is first created. */

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.todo_list);
		this.getListView().setDividerHeight(2);
		dbHelper = new TodoDbAdapter(this);
		dbHelper.open();
		fillData();
		registerForContextMenu(getListView());
	}

	// Create the menu based on the XML defintion
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		MenuInflater inflater = getMenuInflater();
		inflater.inflate(R.menu.listmenu, menu);
		return true;
	}

	// Reaction to the menu selection
	@Override
	public boolean onMenuItemSelected(int featureId, MenuItem item) {
		switch (item.getItemId()) {
		case R.id.insert:
			createTodo();
			return true;
		}
		return super.onMenuItemSelected(featureId, item);
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		switch (item.getItemId()) {
		case R.id.insert:
			createTodo();
			return true;
		}
		return super.onOptionsItemSelected(item);
	}

	@Override
	public boolean onContextItemSelected(MenuItem item) {
		switch (item.getItemId()) {
		case DELETE_ID:
			AdapterContextMenuInfo info = (AdapterContextMenuInfo) item
					.getMenuInfo();
			dbHelper.deleteTodo(info.id);
			fillData();
			return true;
		}
		return super.onContextItemSelected(item);
	}

	private void createTodo() {
		Intent i = new Intent(this, TodoDetailActivity.class);
		startActivityForResult(i, ACTIVITY_CREATE);
	}

	// Opens the second activity if an entry is clicked
	@Override
	protected void onListItemClick(ListView l, View v, int position, long id) {
		super.onListItemClick(l, v, position, id);
		Intent i = new Intent(this, TodoDetailActivity.class);
		i.putExtra(TodoDbAdapter.KEY_ROWID, id);
		// Activity returns an result if called with startActivityForResult

		startActivityForResult(i, ACTIVITY_EDIT);
	}

	// Called with the result of the other activity
	// requestCode was the origin request code send to the activity
	// resultCode is the return code, 0 is everything is ok
	// intend can be used to get data
	@Override
	protected void onActivityResult(int requestCode, int resultCode,
			Intent intent) {
		super.onActivityResult(requestCode, resultCode, intent);
		fillData();

	}

	private void fillData() {
		cursor = dbHelper.fetchAllTodos();
		startManagingCursor(cursor);

		String[] from = new String[] { TodoDbAdapter.KEY_SUMMARY };
		int[] to = new int[] { R.id.label };

		// Now create an array adapter and set it to display using our row
		SimpleCursorAdapter notes = new SimpleCursorAdapter(this,
				R.layout.todo_row, cursor, from, to);
		setListAdapter(notes);
	}

	@Override
	public void onCreateContextMenu(ContextMenu menu, View v,
			ContextMenuInfo menuInfo) {
		super.onCreateContextMenu(menu, v, menuInfo);
		menu.add(0, DELETE_ID, 0, R.string.menu_delete);
	}

	@Override
	protected void onDestroy() {
		super.onDestroy();
		if (dbHelper != null) {
			dbHelper.close();
		}
	}
}

And then TodoDetailsActivity.java

package de.vogella.android.todos;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import de.vogella.android.todos.database.TodoDbAdapter;

public class TodoDetailActivity extends Activity {
	private EditText mTitleText;
	private EditText mBodyText;
	private Long mRowId;
	private TodoDbAdapter mDbHelper;
	private Spinner mCategory;

	@Override
	protected void onCreate(Bundle bundle) {
		super.onCreate(bundle);
		mDbHelper = new TodoDbAdapter(this);
		mDbHelper.open();
		setContentView(R.layout.todo_edit);
		mCategory = (Spinner) findViewById(R.id.category);
		mTitleText = (EditText) findViewById(R.id.todo_edit_summary);
		mBodyText = (EditText) findViewById(R.id.todo_edit_description);

		Button confirmButton = (Button) findViewById(R.id.todo_edit_button);
		mRowId = null;
		Bundle extras = getIntent().getExtras();
		mRowId = (bundle == null) ? null : (Long) bundle
				.getSerializable(TodoDbAdapter.KEY_ROWID);
		if (extras != null) {
			mRowId = extras.getLong(TodoDbAdapter.KEY_ROWID);
		}
		populateFields();
		confirmButton.setOnClickListener(new View.OnClickListener() {
			public void onClick(View view) {
				setResult(RESULT_OK);
				finish();
			}

		});
	}

	private void populateFields() {
		if (mRowId != null) {
			Cursor todo = mDbHelper.fetchTodo(mRowId);
			startManagingCursor(todo);
			String category = todo.getString(todo
					.getColumnIndexOrThrow(TodoDbAdapter.KEY_CATEGORY));

			for (int i = 0; i < mCategory.getCount(); i++) {

				String s = (String) mCategory.getItemAtPosition(i);
				Log.e(null, s + " " + category);
				if (s.equalsIgnoreCase(category)) {
					mCategory.setSelection(i);
				}
			}

			mTitleText.setText(todo.getString(todo
					.getColumnIndexOrThrow(TodoDbAdapter.KEY_SUMMARY)));
			mBodyText.setText(todo.getString(todo
					.getColumnIndexOrThrow(TodoDbAdapter.KEY_DESCRIPTION)));
		}
	}

	protected void onSaveInstanceState(Bundle outState) {
		super.onSaveInstanceState(outState);
		saveState();
		outState.putSerializable(TodoDbAdapter.KEY_ROWID, mRowId);
	}

	@Override
	protected void onPause() {
		super.onPause();
		saveState();
	}

	@Override
	protected void onResume() {
		super.onResume();
		populateFields();
	}

	private void saveState() {
		String category = (String) mCategory.getSelectedItem();
		String summary = mTitleText.getText().toString();
		String description = mBodyText.getText().toString();

		if (mRowId == null) {
			long id = mDbHelper.createTodo(category, summary, description);
			if (id > 0) {
				mRowId = id;
			}
		} else {
			mDbHelper.updateTodo(mRowId, category, summary, description);
		}
	}
}

The resulting AndroidManifest.xml looks like the following.

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="de.vogella.android.todos"
    android:versionCode="1"
    android:versionName="1.0" >

    <application
        android:icon="@drawable/todo"
        android:label="@string/app_name" >
        <activity
            android:label="@string/app_name"
            android:name=".TodosOverviewActivity" >
            <intent-filter >
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity
            android:name=".TodoDetailActivity"
            android:windowSoftInputMode="stateVisible|adjustResize" >
        </activity>

        <provider
            android:authorities="de.vogella.android.todos.provider"
            android:name="MyTodoContentProvider" >
        </provider>
    </application>

    <uses-sdk android:minSdkVersion="9" />

</manifest>

Start your application. You should be able to maintain new Todos via the menu. An existing todo can be deleted on the list via a long press.

Source Code of Examples

SQlite website

SQL Tutorial

Android Tutorial

ListView Tutorial

Intents

Android Background Processing Tutorial

Android Location API and Google Maps

Android and Networking

Eclipse RCP Training (German) Eclipse RCP Training with Lars Vogel

Android Tutorial Introduction to Android Programming

GWT Tutorial Program in Java and compile to JavaScript and HTML

Eclipse RCP Tutorial Create native applications in Java

JUnit Tutorial Test your application

Git Tutorial Put everything you have under distributed version control system