Android SQLite Example – the TODO application

• Tweet

• Tweet

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