Android SQLite Example – the TODO application

Today we are going to show you how to use Android SQLite database in your applications. Our sample application stores TODO Tasks. It displays a list of TODOs, when an element of the list is clicked it is removed from the database. Our application also allows to add new TODO tasks to the list. Here are our application screens.

Android SQLite sample application - TODO List

Android SQLite sample application – TODO List

Android SQLite sample application - Add new TODO Task

Android SQLite sample application – Add new TODO Task

TodoSQLiteHelper.java

package com.itcuties.android.apps.itcsqliteexample.sql;

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

/**
* Creates application database.
*
* @author itcuties
*
*/
public class TodoSQLiteHelper extends SQLiteOpenHelper {

   public TodoSQLiteHelper(Context context) {
       // Databse: todos_db, Version: 1
       super(context, "todos_db", null, 1);
   }

   /**
    * Create simple table
    * todos
    *         _id     - key
    *         todo    - todo text
    */
   @Override
   public void onCreate(SQLiteDatabase db) {
       // Execute create table SQL
       db.execSQL("CREATE TABLE todos (_id INTEGER PRIMARY KEY AUTOINCREMENT, todo TEXT NOT NULL);");
   }

   /**
    * Recreates table
    */
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
       // DROP table
       db.execSQL("DROP TABLE IF EXISTS todos");
       // Recreate table
       onCreate(db);
   }

}

This class is responsible for creating (onCreate) and recreating (onUpdate) the database. By default database is created in data/data/[YOUR_APP_NAME]/databases/ directory. We are going to call getWriteableDatabase() method on this class object in our DAO class which is the method of SQLiteOpenHelper class. This method returns SQLiteDatabase object on which execSQL, insert, update, delete methods can be called. There is also getReadableDatabase() method available which returns SQLiteDatabase object in the “read-only” mode.
In our example we create a simple todos table.

CREATE TABLE todos (_id INTEGER PRIMARY KEY AUTOINCREMENT, todo TEXT NOT NULL)

_id column is the INTEGER primary key which is automatically incremented. Using ‘_id’ for the primary key of your table is considered a good practice, because several Android SQLite functions rely on this standard.
todo is a TEXT column that contains the TODO text.

Todo.java

package com.itcuties.android.apps.itcsqliteexample.data;

public class Todo {

   private int id;

   private String text;

   public int getId() {
       return id;
   }

   public void setId(int id) {
       this.id = id;
   }

   public String getText() {
       return text;
   }

   public void setText(String text) {
       this.text = text;
   }

}

This class represents data that is maintained in our application. Each TODO object consists of id and text attributes.

TodoDAO.java

package com.itcuties.android.apps.itcsqliteexample.dao;

import java.util.ArrayList;
import java.util.List;

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

import com.itcuties.android.apps.itcsqliteexample.data.Todo;
import com.itcuties.android.apps.itcsqliteexample.sql.TodoSQLiteHelper;

/**
* TODOs DAO object.
*
* @author itcuties
*
*/
public class TodoDAO {

   private SQLiteDatabase db;
   private TodoSQLiteHelper dbHelper;

   public TodoDAO(Context context) {
       dbHelper = new TodoSQLiteHelper(context);
       db = dbHelper.getWritableDatabase();
   }

   // Close the db
   public void close() {
       db.close();
   }

   /**
    * Create new TODO object
    * @param todoText
    */
   public void createTodo(String todoText) {
       ContentValues contentValues = new ContentValues();
       contentValues.put("todo", todoText);
       // Insert into DB
       db.insert("todos", null, contentValues);
   }

   /**
    * Delete TODO object
    * @param todoId
    */
   public void deleteTodo(int todoId) {
       // Delete from DB where id match
       db.delete("todos", "_id = " + todoId, null);
   }

   /**
    * Get all TODOs.
    * @return
    */
   public List getTodos() {
       List todoList = new ArrayList();

       // Name of the columns we want to select
       String[] tableColumns = new String[] {"_id","todo"};

       // Query the database
       Cursor cursor = db.query("todos", tableColumns, null, null, null, null, null);
       cursor.moveToFirst();

       // Iterate the results
       while (!cursor.isAfterLast()) {
           Todo todo = new Todo();
           // Take values from the DB
           todo.setId(cursor.getInt(0));
           todo.setText(cursor.getString(1));

           // Add to the DB
           todoList.add(todo);

           // Move to the next result
           cursor.moveToNext();
       }

       return todoList;
   }

}

This is a DAO layer of the application. Here are the methods implementations that run on the database. This DAO class allows to create, delete and list all the TODOs stored in the database.

layout – activity_main.xml

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

    <TextView
      android:id="@+id/todoText"
      android:layout_width="fill_parent"
      android:layout_height="wrap_content"
      android:textColor="#000"
      android:text="TODO TEXT GOES_HERE">
    </TextView>
</LinearLayout>

This is each list rows layout. For more information on how to display data in a ListView please refer to our other tutorial – How to get running process list and traffic statistics

menu – activity_main.xml

<menu xmlns:android="http://schemas.android.com/apk/res/android" >
    <item
		android:id="@+id/menu"
		android:orderInCategory="100"
		android:showAsAction="never"
		android:title="@string/menu_addnew"/>
</menu>

This is the application menu.

MainActivity.java

package com.itcuties.android.apps.itcsqliteexample;

import android.app.ListActivity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ListView;
import android.widget.Toast;

import com.itcuties.android.apps.itcsqliteexample.dao.TodoDAO;
import com.itcuties.android.apps.itcsqliteexample.data.Todo;

/**
* Main activity which displays a list of TODOs.
*
* @author itcuties
*
*/
public class MainActivity extends ListActivity {

   // DAO
   private TodoDAO dao;

   @Override
   protected void onCreate(Bundle savedInstanceState) {
       super.onCreate(savedInstanceState);

       // Create DAO object
       dao = new TodoDAO(this);

       // Set the list adapter and get TODOs list via DAO
       setListAdapter(new ListAdapter(this, dao.getTodos()));

   }

   @Override
   protected void onListItemClick(ListView l, View v, int position, long id) {
       // TODO item that was clicked
       Todo todo = (Todo)getListAdapter().getItem(position);

       // Delete TODO object from the database
       dao.deleteTodo(todo.getId());

       // Set the list adapter and get TODOs list via DAO
       setListAdapter(new ListAdapter(this, dao.getTodos()));

       // Display success information
       Toast.makeText(getApplicationContext(), "Deleted!", Toast.LENGTH_LONG).show();

   }

   /* ************************************************************* *
    * Menu service methods
    * ************************************************************* */
   @Override
   public boolean onCreateOptionsMenu(Menu menu) {
       // Inflate the menu; this adds items to the action bar if it is present.
       getMenuInflater().inflate(R.menu.activity_main, menu);
       return true;
   }

   @Override
   public boolean onOptionsItemSelected(MenuItem item) {
       // Since we have only ONE option this code is not complicated :)

       // Create an intent
       Intent intent = new Intent(this, AddTodoActivity.class);
       // Start activity
       startActivity(intent);
       // Finish this activity
       this.finish();

       // Close the database
       dao.close();

       return super.onOptionsItemSelected(item);
   }
}

This activity’s code displays the list of todos in the ListView. When a list item is clicked, the clicked element is being deleted by calling deleteTodo method of our TodoDAO class – onListItemClick method does that. onOptionsItemSelected method is called when a menu is clicked. Since we have only ONE option this code is not complicated :), we start a new AddTodoActivity here and close the database by calling the close() method of our TodoDAOclass.

ListAdapter.java

package com.itcuties.android.apps.itcsqliteexample;

import java.util.List;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.TextView;

import com.itcuties.android.apps.itcsqliteexample.data.Todo;

/**
* List adapter for storing TODOs data
*
* @author itcuties
*
*/
public class ListAdapter extends ArrayAdapter {

   // List context
    private final Context context;
    // List values
    private final List todoList;

   public ListAdapter(Context context, List todoList) {
       super(context, R.layout.activity_main, todoList);
       this.context = context;
       this.todoList = todoList;
   }

   @Override
   public View getView(int position, View convertView, ViewGroup parent) {
       LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);

       View rowView = inflater.inflate(R.layout.activity_main, parent, false);

       TextView todoText = (TextView) rowView.findViewById(R.id.todoText);
       todoText.setText(todoList.get(position).getText());

       return rowView;
   }

}

This is a list adapter responsible for displaying each list row. For more information on how to display data in a ListView please refer to our other tutorial – How to get running process list and traffic statistics

layout – activity_add_todo.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent" >

    <LinearLayout
      android:id="@+id/formLayout"
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:layout_centerHorizontal="true"
      android:layout_centerVertical="true" >
      <EditText
          android:id="@+id/newTodoText"
          android:layout_width="220dp"
          android:layout_height="wrap_content"
          android:ems="10" >
          <requestFocus />
      </EditText>

      <Button
          android:id="@+id/addNewTodoButton"
          android:layout_width="wrap_content"
          android:layout_height="wrap_content"
          android:text="@string/button_addnew" />
    </LinearLayout>

    <Button
      android:id="@+id/menuGoBackButton"
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:layout_alignLeft="@+id/formLayout"
      android:layout_alignParentTop="true"
      android:layout_marginTop="15dp"
      android:text="@string/button_menu_goback" />
</RelativeLayout>

This is our add new TODO activity layout.

AddTodoActivity.java

package com.itcuties.android.apps.itcsqliteexample;

import com.itcuties.android.apps.itcsqliteexample.dao.TodoDAO;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class AddTodoActivity extends Activity implements OnClickListener {

   // GUI components
   private EditText todoText;        // Text field
   private Button addNewButton;    // Add new button
   private Button backButton;        // Back button

   // DAO
   private TodoDAO dao;

   @Override
   protected void onCreate(Bundle savedInstanceState) {
       super.onCreate(savedInstanceState);
       setContentView(R.layout.activity_add_todo);

       // Create DAO object
       dao = new TodoDAO(this);

       todoText         = (EditText)findViewById(R.id.newTodoText);
       addNewButton     = (Button)findViewById(R.id.addNewTodoButton);
       backButton        = (Button)findViewById(R.id.menuGoBackButton);

       addNewButton.setOnClickListener(this);
       backButton.setOnClickListener(this);

   }

   @Override
   public void onClick(View v) {
       // If add button was clicked
       if (addNewButton.isPressed()) {
           // Get entered text
           String todoTextValue = todoText.getText().toString();
           todoText.setText("");

           // Add text to the database
           dao.createTodo(todoTextValue);

           // Display success information
           Toast.makeText(getApplicationContext(), "New TODO added!", Toast.LENGTH_LONG).show();

       } else if (backButton.isPressed()) {
           // When back button is pressed
           // Create an intent
           Intent intent = new Intent(this, MainActivity.class);
           // Start activity
           startActivity(intent);
           // Finish this activity
           this.finish();

           // Close the database
           dao.close();
       }

   }

}

This activity class implements OnClickListener and adds a new TODO to the database when a addNewButton button is clicked. We are calling TodoDAO createTodo method to add new TODO to the database. When user clicks the backButton a MainActivity is started and this activity is finished. In this case a database needs to be closed to by calling the close() method of our TodoDAOclass.

AndroidManifest.xml

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

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

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        
        <activity
            android:name="com.itcuties.android.apps.itcsqliteexample.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>
        
        <activity
            android:name="com.itcuties.android.apps.itcsqliteexample.AddTodoActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
            </intent-filter>
        </activity>
        
    </application>

</manifest>

As you can see there are no special privileges needed to use Android SQLite database in your application.

Download this sample code here.

This code is available on our GitHub repository as well.

22 Responses to "Android SQLite Example – the TODO application"

  1. ralphie says:

    Hi,

    I am a totally beginner in Android as well in java. I am trying to make a todo list just as the example above but with a minimal change.

    I wanted to an item number in the list box shown. How do I do this?

    My other questions are based on the code and wanted to know more about it.

    When the add button is clicked. Does the todo item add to the database first or to the listview?

    If in the database, when is the list being populated to the list box?

    Thank you very much and hope to hear from you soon. =)

    Reply
    • itcuties says:

      Hello Ralphie,
      If you want to add a number to the list you need to extend the main layout in activity_main.xml file. Just add another TextView element like this:

      <?xml version="1.0" encoding="utf-8"?>
      <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android&quot;
          android:layout_width="fill_parent"
          android:layout_height="fill_parent">
          
          <TextView
            android:id="@+id/todoId"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:textColor="#000"
            android:text="TODO ID GOES HERE">
          </TextView>
      
          <TextView
            android:id="@+id/todoText"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:textColor="#000"
            android:text="TODO TEXT GOES_HERE">
          </TextView>
      </LinearLayout>
      

      Then, in the acitivity Java code you will be able to get this element like this:

      package com.itcuties.android.apps.itcsqliteexample;
       
      import java.util.List;
       
      import android.content.Context;
      import android.view.LayoutInflater;
      import android.view.View;
      import android.view.ViewGroup;
      import android.widget.ArrayAdapter;
      import android.widget.TextView;
       
      import com.itcuties.android.apps.itcsqliteexample.data.Todo;
       
      /**
      * List adapter for storing TODOs data
      *
      * @author itcuties
      *
      */
      public class ListAdapter extends ArrayAdapter {
       
         // List context
          private final Context context;
          // List values
          private final List todoList;
       
         public ListAdapter(Context context, List todoList) {
             super(context, R.layout.activity_main, todoList);
             this.context = context;
             this.todoList = todoList;
         }
       
         @Override
         public View getView(int position, View convertView, ViewGroup parent) {
             LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
       
             View rowView = inflater.inflate(R.layout.activity_main, parent, false);
       
             TextView todoText = (TextView) rowView.findViewById(R.id.todoText);
             todoText.setText(todoList.get(position).getText());
      
      
      	   ///////////////////////////////////////////////////////////////
             // Set the ID value
             ///////////////////////////////////////////////////////////////
      	
      	   TextView todoId = (TextView) rowView.findViewById(R.id.todoId);
             todoId.setText(todoList.get(position).getId());
       
             return rowView;
         }
       
      }
      
      

      As for the second question a todo item is added to the database and when you click the Back button the main activity is created which reads database contents and stores it in the list. In this application you have one active Activity at a time.

      Have a nice day,
      itcuties

      Reply
  2. ralphie says:

    I did find my work around to get the ID by doing the following

    int rowID = position + 1;
    TextView todoText = (TextView) rowView.findViewById(R.id.todoText);
    todoText.setText(rowID + todoList.get(position).getText());
    

    I tried changing based on your recommendation above but got an error on the activit_man.xml file. From the original xml file I just added the TextView for row ID

    Reply
  3. itcuties says:

    Hi Ralphie,

    there is a mistake in our answer, since todoList.get(position).getId() returns an int value and TextView.setText call needs a String parameter.
    The correct code is:

    TextView todoId = (TextView) rowView.findViewById(R.id.todoId);
    todoId.setText(String.valueOf(todoList.get(position).getId()));
    

    Sorry about that :)

    Have a nice day,
    itcuties

    Reply
  4. Ralphie says:

    Big thank you!!!

    Reply
  5. Donnie Emanuel says:

    Thanks a lot.

    Reply
  6. ralphie says:

    I’m back again.. I’ve been trying to modify the program to basically open a context menu after a long press however after so many tries I still failed.

    Can you give me an insight on how to replace this code to longpress instead. Thank you very much

    protected void onListItemClick(ListView l, View v, int position, long id) {
    		// TODO item that was clicked
    		Todo todo = (Todo)getListAdapter().getItem(position);
    		
    		// Delete TODO object from the database
    		dao.deleteTodo(todo.getId());
    		
    		// Set the list adapter and get TODOs list via DAO
    		setListAdapter(new ListAdapter(this, dao.getTodos()));
    		
    		// Display success information
    		Toast.makeText(getApplicationContext(), "Deleted!", Toast.LENGTH_LONG).show();
    		
    	}
    
    Reply
    • itcuties says:

      Hello Ralphie,

      you might find this solution interesting. When user taps on the TODO element the confirmation dialog is being displayed. When user confirmes the delete operation then the TODO element is deleted from the list.

      package com.itcuties.android.apps.itcsqliteexample;
      
      import android.app.AlertDialog;
      import android.app.ListActivity;
      import android.content.Context;
      import android.content.DialogInterface;
      import android.content.Intent;
      import android.os.Bundle;
      import android.view.Menu;
      import android.view.MenuItem;
      import android.view.View;
      import android.widget.ListView;
      import android.widget.Toast;
      
      import com.itcuties.android.apps.itcsqliteexample.dao.TodoDAO;
      import com.itcuties.android.apps.itcsqliteexample.data.Todo;
      
      /**
       * Main activity which displays a list of TODOs.
       * 
       * @author itcuties
       *
       */
      public class MainActivity extends ListActivity {
      
      	// DAO
      	private TodoDAO dao;
      	
      	// Todo selected for deletion
      	private Todo selectedTodo;
      	// This object's context
      	private Context context = this;
      	
      	@Override
      	protected void onCreate(Bundle savedInstanceState) {
      		super.onCreate(savedInstanceState);
      		
      		// Create DAO object
      		dao = new TodoDAO(this);
      		
      		// Set the list adapter and get TODOs list via DAO
      		setListAdapter(new ListAdapter(this, dao.getTodos()));
      		
      	}
      
      	@Override
      	protected void onListItemClick(ListView l, View v, int position, long id) {
      		// TODO item that was clicked
      		selectedTodo = (Todo)getListAdapter().getItem(position);
      		
      		// Display the dialog
      		new AlertDialog.Builder(this)
      		.setTitle("Delete")
      		.setMessage("Do you really want to delete this TODO?")
      		.setIcon(android.R.drawable.ic_dialog_alert)
      		.setPositiveButton(android.R.string.yes, new DialogInterface.OnClickListener() {
      			public void onClick(DialogInterface dialog, int whichButton) {
      		        
      				// Delete TODO object from the database
      				dao.deleteTodo(selectedTodo.getId());
      				
      				// Set the list adapter and get TODOs list via DAO
      				setListAdapter(new ListAdapter(context, dao.getTodos()));
      				
      				// Display success information
      				Toast.makeText(getApplicationContext(), "Deleted!", Toast.LENGTH_LONG).show();
      				
      		    }})
      		 .setNegativeButton(android.R.string.no, null).show();
      		
      	}
      	
      	
      	
      	/* ************************************************************* *
      	 * Menu service methods
      	 * ************************************************************* */ 
      	@Override
      	public boolean onCreateOptionsMenu(Menu menu) {
      		// Inflate the menu; this adds items to the action bar if it is present.
      		getMenuInflater().inflate(R.menu.activity_main, menu);
      		return true;
      	}
      	
      	@Override
      	public boolean onOptionsItemSelected(MenuItem item) {
      		// Since we have only ONE option this code is not complicated :)
      		
      		// Create an intent
      		Intent intent = new Intent(this, AddTodoActivity.class);
      		// Start activity
      		startActivity(intent);
      		// Finish this activity
      		this.finish();
      		
      		// Close the database
      		dao.close();
      		
      		return super.onOptionsItemSelected(item);
      	}
      }
      

      Cheers,
      itcuties

      Reply
  7. fabricio says:

    hello, what IDE you are using?, I mean, i download the code, but how can i open it?

    PD. sorry about my english..

    Reply
    • itcuties says:

      We are using Eclipse IDE. You need to import (right click in the Package Explorer panel, pick Import... and then Existing Android Code Into Workspace) our codes and you are ready to play with them :)

      Reply
  8. Michael says:

    I am working through your example but my MainActivity already extends FragmentActivity (I have 3 tabs in my app with a different fragment for each tab). Is there a suggested implementation since I cannot extend ListActivity? Should I have MainActivity call another activity that handles the list? Thanks

    Reply
  9. Tofuw says:

    OMG !!! Thank you so much for this marvellous application !
    Now, all seems to be clear !

    Thanks a lot !

    Reply
  10. Esa Dewa says:

    Hi I’m student and beginner in android..

    How if I want to add reminder for this application..?

    Reply
    • itcuties says:

      You should focus on writing service which will monitor your database and give notifications in the action bar.

      Reply
  11. Edgar Lopez says:

    How do you apply that theme to the textedit field, I’m new at android and I’m curios how to make textedit looks diferent than the tipical square white box, and so the buttons how do you make your owns. Thanks for your work.

    Reply
  12. Malikster says:

    I adapted your tutorial to my app but in ListAdapter it gives me an error:
    Unknown method 'getText' in 'java.lang.Object'.
    My line is:
    elementText.setText(elementList.get(position).getText());

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Java by Example App is available at Google Play Store NOW