Android Database tutorial

Create the following class "TodoDatabaseHelper".

package de.vogella.android.todos.database;

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

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

 private static final int DATABASE_VERSION = 1;

 // 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 TodoDatabaseHelper(Context context) {
  super(context, DATABASE_NAME, null, DATABASE_VERSION);
 }

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

 // 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) {
  Log.w(TodoDatabaseHelper.class.getName(),
    "Upgrading database from version " + oldVersion + " to "
      + newVersion + ", which will destroy all old data");
  database.execSQL("DROP TABLE IF EXISTS todo");
  onCreate(database);
 }
}

Based on this helper class we can write the class
   "TodoDbAdapter" which
   will provide the functionality to query, create
   and update todos. The method open() will open the
   database via the
   helper class. For updating and creating values we
   use the
   "android.content.ContentValues" class. This class allows to
   store
   key/values. You use the column names as the key in
   ContentValues and
   pass the object to the insert or update method
   of your database. 

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 DATABASE_TABLE = "todo";
 private Context context;
 private SQLiteDatabase database;
 private TodoDatabaseHelper dbHelper;

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

 public TodoDbAdapter open() throws SQLException {
  dbHelper = new TodoDatabaseHelper(context);
  database = 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 initialValues = createContentValues(category, summary,
    description);

  return database.insert(DATABASE_TABLE, null, initialValues);
 }

 /**
  * Update the todo
  */
 public boolean updateTodo(long rowId, String category, String summary,
   String description) {
  ContentValues updateValues = createContentValues(category, summary,
    description);

  return database.update(DATABASE_TABLE, updateValues, KEY_ROWID + "="
    + rowId, null) > 0;
 }

 /**
  * Deletes todo
  */
 public boolean deleteTodo(long rowId) {
  return database.delete(DATABASE_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 database.query(DATABASE_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 = database.query(true, DATABASE_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;
 }
} 

Create create a few resources which we will later use.
Also define a menu "listmenu.xml" which will later use create a new todo. 

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

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>

We create another resource for the other strings in 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 todo.
Please note that the row layout refers to an icon. Please replace this with a icon of your choice. To find icons have a lock at: Free icons for your application .
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:orientation="vertical" android:layout_width="fill_parent"
 android:layout_height="fill_parent" android:background="@color/listcolor">
 <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 "reminder" into your "res/layout" folder which
   will be used in the next layout. Alternatively remove the icon
   from
   the row layout. Afterwards create the layout "todo_row.xml" 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_height="wrap_content" android:layout_width="fill_parent">
 <ImageView android:id="@+id/icon" android:src="@drawable/reminder"
  android:layout_marginLeft="4px" android:layout_marginRight="8px"
  android:layout_height="40px" android:layout_marginTop="8px"
  android:layout_width="30px">
 </ImageView>
 <TextView android:text="@+id/TextView01" android:layout_height="wrap_content" android:id="@+id/label"
  android:textSize="40px" android:layout_marginTop="6px" android:layout_width="fill_parent" android:textColor="@color/black"></TextView>
</LinearLayout> 

Create the layout "todo_edit". This layout will be used later
   to display and edit an
   individual todo.  

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


 <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_height="wrap_content" android:layout_width="fill_parent">
  <EditText android:layout_height="wrap_content" android:id="@+id/todo_edit_summary"
   android:layout_weight="1" android:layout_width="wrap_content"
   android:hint="Summary"></EditText>

 </LinearLayout>


 <EditText android:layout_width="fill_parent"
  android:layout_height="fill_parent" android:layout_weight="1"
  android:id="@+id/todo_edit_description" android:hint="Description" android:gravity="top"></EditText>
 <Button android:layout_width="wrap_content"
  android:layout_height="wrap_content" android:id="@+id/todo_edit_button"
  android:text="@string/todo_edit_confirm"></Button>


</LinearLayout> 

Finally change the coding of your activities to the following.
   First "TodoOverview.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 TodosOverview 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, TodoDetails.class);
  startActivityForResult(i, ACTIVITY_CREATE);
 }

 // ListView and view (row) on which was clicked, position and
 @Override
 protected void onListItemClick(ListView l, View v, int position, long id) {
  super.onListItemClick(l, v, position, id);
  Intent i = new Intent(this, TodoDetails.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 use to get some data from the caller
 @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);
 }
}

And then "TodoDetails.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 TodoDetails 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:name=".TodosOverview" android:label="@string/app_name">
   <intent-filter>
    <action android:name="android.intent.action.MAIN" />
    <category android:name="android.intent.category.LAUNCHER" />
   </intent-filter>
  </activity>

  <activity android:name=".TodoDetails"
   android:windowSoftInputMode="stateVisible|adjustResize">
   >
    
    </activity>
 </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