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.
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 TodoDAO
class.
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 TodoDAO
class.
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.
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. =)
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 anotherTextView
element like this:Then, in the acitivity Java code you will be able to get this element like this:
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
2Z38
I KNOW the programing of sqlitedatabase browser but i dont know how to execute plz clarify my details
Hello Lingamurthy,
you need to by more specific :) and ask a question. It would be great if you could ask your questions at http://www.itcuties.com/answers :)
Cheers,
itcuties
I did find my work around to get the ID by doing the following
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
Hi Ralphie,
there is a mistake in our answer, since
todoList.get(position).getId()
returns anint
value andTextView.setText
call needs aString
parameter.The correct code is:
Sorry about that :)
Have a nice day,
itcuties
Big thank you!!!
Thanks a lot.
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
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.
Cheers,
itcuties
WOW!!!!!
You are wonderful.. Thank you very much..
Tell your friends about us ;)
Take care,
itcuties
hello, what IDE you are using?, I mean, i download the code, but how can i open it?
PD. sorry about my english..
We are using Eclipse IDE. You need to import (right click in the
Package Explorer
panel, pickImport...
and thenExisting Android Code Into Workspace
) our codes and you are ready to play with them :)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
Hello Michael,
We need to see your code. Please describe your case in more details and post your code on http://www.itcuties.com/answers.
Have a nice day,
itcuties
OMG !!! Thank you so much for this marvellous application !
Now, all seems to be clear !
Thanks a lot !
Hi I’m student and beginner in android..
How if I want to add reminder for this application..?
You should focus on writing service which will monitor your database and give notifications in the action bar.
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.
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());
For me worked with:
elementText.setText((Element)this.getItem(position).get text());
hey brother i also have the same problem
todoText.setText(todoList.get(position).getText());
can u please help me with the right code.
ThanQ :)
Sir im a student im starting to study about androidz prog….. what is the first step that i do..
i am having a problem in opening new activity by clicking a button via intent. when i click the button it doesnot works why?
can u give me a suggestion?
Hello,
I am working on this code and it’s done. Now I want to add notification of remaining task in notification bar to user. Then how can i do this..?
11-23 16:56:14.342 3843-3843/com.itsniv.todolist E/SQLiteLog: (1) no such table: todos
11-23 16:56:14.344 3843-3843/com.itsniv.todolist E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.itsniv.todolist, PID: 3843
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.itsniv.todolist/com.itsniv.todolist.MainActivity}: android.database.sqlite.SQLiteException: no such table: todos (code 1): , while compiling: SELECT _id, todo FROM todos
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2426)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2490)
at android.app.ActivityThread.-wrap11(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1354)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:5443)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:728)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
Caused by: android.database.sqlite.SQLiteException: no such table: todos (code 1): , while compiling: SELECT _id, todo FROM todos
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)
at com.itsniv.todolist.TodoDAO.getTodos(TodoDAO.java:44)
at com.itsniv.todolist.MainActivity.onCreate(MainActivity.java:23)
at android.app.Activity.performCreate(Activity.java:6259)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1130)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2379)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2490)
at android.app.ActivityThread.-wrap11(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1354)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:5443)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:728)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
please help me