Using the Android SQLite Database

1. SQLite and Android

1.1. What is SQLite

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 .

1.2. SQLite in Android

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 an AsyncTask .
If your application creates an database this database is saved in the directoryDATA/data/APP_NAME/databases/FILENAME . DATA is the path whichEnvironment.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.

1.3. Content Provider and sharing data

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

2. Android Architecture

2.1. Packages

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

2.2. SQLiteOpenHelper

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 an SQLiteDatabase 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 of SQLiteOpenHelper will not get to large even if you have several tables.

2.3. SQLiteDatabase

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 objectContentValues 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.

• String dbName - The table name to compile the query against
• int[] columnNames - A list of which columns to return. Passing null will return all columns.
• String whereClause - Filter for the selection of data without the "WHERE" clause, null will select all
• selectionArgs You may include ?s in the whereClause, which will be replaced by the values from selectionArgs.
• String[] groupBy - A filter declaring how to group rows, null will cause the rows to not be grouped.
• String[] having - Filter for the goups, null means no filter
• String[] orderBy - row which will be used to order the data, null means no ordering

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);
 
  

2.4. Cursor

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 methods moveToFirst() 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 .

2.5. ListViews, ListActivities and SimpleCursorAdapter

ListViews are views (widgets) which makes it easy to display a list of elements. ListActivities are specialized Activities which make the usage of ListViews easier. This tutorial will use ListActivitiesbut 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 . TheSimpleCursorAdapter 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.

3. Command line interface for SQLite

It is possible to access an SQLite database on the emulator or a rooted device via the command line. For this useadb shell to connect to the device and the command "sqlite3" to connect to an database.

4. Prerequisites for this tutorial

The following assumes that you have already basic knowledge in Android development. Please check the Android development tutorial to learn the basics.

5. Creating the Todo application

5.1. Overview

We will create a Todo application which allow the user to maintain tasks for himself. These items will be stored in theSQLite database.
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.

5.2. Project

Create the project de.vogella.android.todos with the Activity called TodosOverviewActivity. . Create also another Activity called TodoDetailsActivity.
Create the package de.vogella.android.todos.database . This package will store the classes for the database handling.

5.3. 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;
 }

}

  

5.4. Resources

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. Create priority.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>

  

5.5. Activities and Layouts

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 secondActivity.

<?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>
  

5.6. Start your application

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.