Android Database Tutorial

The Android platform offers a variety of storage options you can use in your apps. If you need a structured data source, you can use an SQLite database. If you have used any SQL database in the past, such as MySQL, you will be able to use SQLite easily as you will already be accustomed to the principles and syntax involved. If you have no experience using SQL or scripting with databases, you can still pick up the essentials of using SQLite databases in your Android projects.

In this tutorial we will work through the process of using an SQLite database in Android. We will create a simple note-keeping app in which the user can add, view and delete notes. The notes will be displayed in a list, with a button to add new notes and the option to delete notes on clicking each one in the list. The tutorial will take you through the procedure of basic SQLite database implementation in Android, so you will be able to reuse the skills and techniques involved in other applications. Here is a preview of the app:

For a broader overview of using SQLite in your Android apps check out the invariably excellent Vogella site, particularly this tutorial: Android SQLite database and content provider - in this tutorial we will use a similar algorithm but will simplify some of the elements and add a little more explanation of the steps involved.

Project Setup
Create a new Android project. You will need a blank main Activity and layout. We will also be using three additional Java classes, but their content will be relatively simple so don't worry if you have only created simple Android projects so far, this one is accessible to beginners.

Layout
Open your app layout file. We are going to use a ListActivity for the app, which requires a layout containing a ListView with a particular ID. Using a setup like this lets you take advantage of some of the automated aspects of the Android platform, reducing the amount of functionality you have to implement yourself. Essentially we will be using the ListActivity and ListView to provide an interface to the database. Enter the following layout:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
 xmlns:tools="http://schemas.android.com/tools"
 android:layout_width="match_parent"
 android:layout_height="match_parent"
 android:background="#FF3366CC"
 android:orientation="vertical"
 tools:context=".MainActivity" >

 <Button
  android:id="@+id/new_btn"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_gravity="center"
  android:layout_margin="5dp"
  android:onClick="addNewNote"
  android:text="New"
  android:textColor="#FFFFFFFF" />

 <ListView
  android:id="@android:id/list"
  android:layout_width="fill_parent"
  android:layout_height="wrap_content"
  android:layout_margin="10dp"
  android:background="#FFFFEE66" />

</LinearLayout>

The layout is simple, it includes a button for the user to add a new note and a ListView displaying the existing notes. We specify a method to execute on clicking the button so will include it in our main Activity class later. The Java code will handle the processing required for displaying the database content in the ListView. For now, add the following import statements to your main Activity:

import java.util.List;

import android.app.AlertDialog;
import android.app.ListActivity;
import android.content.DialogInterface;
import android.view.Menu;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.ListView;

Make the class extend ListActivity for displaying the notes in a list:

public class MainActivity extends ListActivity

Helper Classes
Let's add all of the classes to the app. To use the SQLite database we need an SQLiteOpenHelper class, so add a new class to your application package, naming it "MyDataHelper" and choosing SQLiteOpenHelper is its superclass - the opening line of the declaration should appear as follows:

public class MyDataHelper extends SQLiteOpenHelper

Your class will need the following import statements:

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

We will implement the class details soon. We are also going to use a class dedicated to managing the database connection rather than doing this from the main Activity, so add another new class to your package, naming it "NoteManager". Add the following imports to the class:

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 android.database.sqlite.SQLiteOpenHelper;

The final class we need is to model the notes themselves, so add another new class, naming it "Note". Let's implement this class now. Use the following declaration:

public class Note {

 private long noteID;
 private String noteText;

 public void setNoteText(String userText){
  noteText=userText;
 }
 public String getNoteText(){
  return noteText;
 }
 public void setNoteID(long newID){
  noteID=newID;
 }
 public long getNoteID(){
  return noteID;
 }
 @Override
 public String toString(){
  return noteText;
 }

}

Each note will consist of a unique ID number and a text string representing the note content. These two data items are what we will add to the database for each note, so the Note class models the same data, with standard get and set methods we will use elsewhere in the app code. We implement the toString method because it will be used by Android when displaying the notes in the ListView.

Data Design
Let's turn to the data design now. Open your SQLiteOpenHelper class. Add the following two instance variables to define the name and version of the database:

public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "UserNotes.db";

Define the database table name and text column name in an inner class, implementing the BaseColumns interface so that we can make use of its additional fields for database management:

public static class DBItem implements BaseColumns {
 public static final String TABLE = "notes";
 public static final String NOTE_COL = "note";
}

Add a final instance variable to the SQLiteOpenHelper class in which we define the database table creation string:

private static final String CREATE_STR = "CREATE TABLE " + 
 DBItem.TABLE + " (" + DBItem._ID + 
 " INTEGER PRIMARY KEY AUTOINCREMENT, " +
 DBItem.NOTE_COL + " TEXT);";

Notice that we use the "._ID" field from the BaseColumns interface to define an auto-incrementing ID column in the table. Add a constructor method to the class, calling the superclass constructor:

public MyDataHelper(Context context) {
 super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

Now add the onCreate method, in which we create the table using the string we defined:

public void onCreate(SQLiteDatabase db) {
 db.execSQL(CREATE_STR);
}

Add standard upgrade and downgrade methods:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 db.execSQL("DROP TABLE IF EXISTS " + DBItem.TABLE);
 onCreate(db);
}
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 onUpgrade(db, oldVersion, newVersion);
}

That's the SQLiteOpenHelper class complete!

Database Management
Now let's turn to the database management class "NoteManager". Open it now and add two instance variables, one for the database and one for the helper class:

private SQLiteOpenHelper noteHelper;
private SQLiteDatabase noteDB;

Add a constructor method creating an instance of the SQLiteOpenHelper class with the passed Context parameter:

public NoteManager(Context context){
 noteHelper = new MyDataHelper(context);
}

Next provide a method to retrieve the saved notes from the database:

public List<Note> getNotes(){

}

Inside the method, first create a List of Note objects and open a connection to the database:

List<Note> notes = new ArrayList<Note>();
noteDB = noteHelper.getReadableDatabase();

Execute a query for all rows and columns in the database, retrieving a Cursor which will allow us to access the data:

Cursor noteCursor = noteDB.query(MyDataHelper.DBItem.TABLE, 
 null, null, null, null, null, null);

Notice that we again use a field we defined in the inner class inside the SQLiteOpenHelper class. Loop through the results:

while(noteCursor.moveToNext()){

}

Inside the loop, get the ID and note text for the current rwo, again using the inner class fields:

int newID = noteCursor.getInt(
 noteCursor.getColumnIndexOrThrow(MyDataHelper.DBItem._ID));
String newText = noteCursor.getString(
 noteCursor.getColumnIndexOrThrow(MyDataHelper.DBItem.NOTE_COL));

Create a Note object for the current row of the database, using the set methods we added to set the values of the ID and text:

Note newNote = new Note();
newNote.setNoteID(newID);
newNote.setNoteText(newText);

Add the new note to the list:

notes.add(newNote);

After the while loop but still inside the method, close the Cursor and database, then return the list of notes:

noteCursor.close();
noteDB.close();
return notes;

We will be calling on this method from the main Activity to display saved notes when the app is launched. Now add a method to the data manager class for adding new notes, receiving a Note object as parameter:

public long addNewNote(Note addedNote){

}

Inside the method, get a connection for writing to the database:

noteDB = noteHelper.getWritableDatabase();

Create a ContentValues object, passing the name of the note column and the new note text to it so that we can write to the database:

ContentValues noteValues = new ContentValues();
noteValues.put
 (MyDataHelper.DBItem.NOTE_COL, addedNote.getNoteText());

Attempt to insert the new note value into the table:

long added = noteDB.insertOrThrow
 (MyDataHelper.DBItem.TABLE, null, noteValues);

Close the connection and return the ID value of the newly added column:

noteDB.close();
return added;

We will call this from the main Activity when the user clicks the "New" button and inserts some text. Now add another new method to the data manager class, this time for deleting notes, receiving the note ID as a parameter:

public int deleteNote(long noteID){

}

Inside the method, get a writeable database connection:

noteDB = noteHelper.getWritableDatabase();

The delete query uses a where clause, in which you define the columns you want to delete. We will be deleting the column with the specified ID value. The delete method expects to receive the parameters to the where clause as a string array, so create one with the ID in it:

String[] params = {""+noteID};

Attempt to execute the deletion, passing the name of the ID column and the parameters including the ID of the note we want to delete:

int deleted = noteDB.delete(MyDataHelper.DBItem.TABLE, MyDataHelper.DBItem._ID+" = ?", params);

Close the database and return the integer value indicating how many rows have been affected:

noteDB.close();
return deleted;

We will also call this from the main Activity. That's the database manager class complete.

Interaction
Now we can use all of the resources we have created to display, add and delete notes. Open your main Activity class. Add three instance variables, representing the database manager, the ListView and a helper variable for when we delete notes:

private NoteManager noteMan;
private ListView theList;
private int notePosn;

Inside onCreate your class should already have code setting the main layout as content view. After that, instantiate the database manager:

noteMan = new NoteManager(getApplicationContext());

Retrieve the list of notes from it using the method we defined:

List<Note> existingNotes = noteMan.getNotes();

Create an adapter so that we can map the list items to the ListView, passing the list and a reference to one of Android's standard list item styles:

ArrayAdapter<Note> noteAdapt = new ArrayAdapter<Note>(
 this, android.R.layout.simple_list_item_1, existingNotes);

Set the adapter on the ListActivity:

setListAdapter(noteAdapt);

Get the ListView we added to the layout:

theList = getListView();

Now let's add code to onCreate to set a listener for when users click the individual notes in the list:

theList.setOnItemClickListener(new OnItemClickListener(){
 @Override
 public void onItemClick(AdapterView<?> arg0, View view,
  int position, long id) {

}
});

Each time the user presses a list item, the onItemClick method will fire. Inside it, let's create a Dialog confirming that the user wants to delete the note. First store the note ID in the class variable so that we will still be able to access it inside the Dialog code:

notePosn=position;

Now create an Alert Dialog:

AlertDialog.Builder deleteAlert = new 
 AlertDialog.Builder(MainActivity.this);
deleteAlert.setTitle("Delete Note");
deleteAlert.setMessage("Do you want to delete this note?");

Set the positive button, clicks on which will prompt the note to be deleted:

deleteAlert.setPositiveButton("OK", 
 new DialogInterface.OnClickListener() {
 public void onClick(DialogInterface dialog, int whichButton) {
  ArrayAdapter<Note> noteAdapt = (ArrayAdapter<Note>)
    MainActivity.this.getListAdapter();
  Note clickedNote = (Note)noteAdapt.getItem(notePosn);
  int noteDeleted = noteMan.deleteNote
   (clickedNote.getNoteID());
  noteAdapt.remove(clickedNote);
  noteAdapt.notifyDataSetChanged();
  setListAdapter(noteAdapt);
 }
});

Take a moment to look over the code here. First we retrieve a reference to the adapter, then we get the Note at the position clicked. Next we call on the SQLiteOpenHelper class via the database manager to delete the note by passing its ID using the Note class get method. Then we remove the Note from the ListView adapter, prompting an update of the displayed list so that the item is immediately removed from view.

After setting the positive button, now set a negative button so that the user can cancel if they decide not to delete:

deleteAlert.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
 public void onClick(DialogInterface dialog, int which){
  dialog.cancel();
 }
});

Show the Dialog:

deleteAlert.show();

That's us finished with the onCreate method. Now add the method we specified as onClick attribute for the "New" button in the layout:

public void addNewNote(View v){

}

We will use another Dialog, this time with an editable text field in it so that the user can enter a new note. Inside the new method, create the Dialog:

AlertDialog.Builder addAlert = new AlertDialog.Builder(this);
addAlert.setTitle("New Note");
addAlert.setMessage("Enter your note:");

Add the editable text field:

final EditText noteIn = new EditText(this);
addAlert.setView(noteIn);

Set the behaviour for when the user clicks the OK button after entering their new note:

addAlert.setPositiveButton("OK", 
 new DialogInterface.OnClickListener() {
 public void onClick(DialogInterface dialog, int whichButton) {
  String noteInput = noteIn.getText().toString();
  Note inputNote = new Note();
  inputNote.setNoteText(noteInput);
  long addedID = noteMan.addNewNote(inputNote);
  inputNote.setNoteID(addedID);
  ArrayAdapter<Note> noteAdapt = (ArrayAdapter<Note>)
    MainActivity.this.getListAdapter();
  noteAdapt.add(inputNote);
 }
});

As you can see this uses a similar algorithm to the delete functionality. We get the entered text, create a new note using it and add it using the data manager class. The data manager method returns the ID of the new row added to the database as a long number, which we then set as ID using the Note object set method. Finally we update the display by adding the Note to the adapter.

After setting the positive button, now set a negative one for cancelling:

addAlert.setNegativeButton("Cancel", 
 new DialogInterface.OnClickListener() {
 public void onClick(DialogInterface dialog, int which){
  dialog.cancel();
 }
});

Show the Dialog:

addAlert.show();

That's the database-powered note-keeping app complete! You should be able to run yours now to see it in action. If you want to explore database functionality with SQLite on Android further, try adding the ability to edit notes and to update them.

References

• Android Developers: Saving Data in SQL Databases
• Android Developers: android.database.sqlite