http://www.vogella.de/articles/AndroidSQLite/article.

html

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