Android SQLite Database Tutorial

In this tutorial, you will learn how to create a SQLite Database that allows you to store data in your internal device memory. Android devices come with a built- in SQLite Database that has methods to create, update, delete, execute SQL commands, and perform other common database management tasks. We will create a simple application that allows users to create, update, delete and show a list of data. So lets begin…

Create a new project in Eclipse File > New > Android Application Project. Fill in the details and name your project DatabaseTutorial.

Application Name : DatabaseTutorial

Project Name : DatabaseTutorial

Package Name : com.androidbegin.databasetutorial

Open your MainActivity.java and paste the following code.

MainActivity.java

package com.androidbegin.databasetutorial;

import android.os.AsyncTask;
import android.os.Bundle;
import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.MenuItem.OnMenuItemClickListener;
import android.widget.AdapterView;
import android.widget.CursorAdapter;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.AdapterView.OnItemClickListener;

public class MainActivity extends ListActivity {

	// Declare Variables
	public static final String ROW_ID = "row_id";
	private static final String TITLE = "title";
	private ListView noteListView;
	private CursorAdapter noteAdapter;

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

		// Locate ListView
		noteListView = getListView();

		// Prepare ListView Item Click Listener
		noteListView.setOnItemClickListener(viewNoteListener);

		// Map all the titles into the ViewTitleNotes TextView
		String[] from = new String[] { TITLE };
		int[] to = new int[] { R.id.ViewTitleNotes };

		// Create a SimpleCursorAdapter
		noteAdapter = new SimpleCursorAdapter(MainActivity.this,
				R.layout.list_note, null, from, to);

		// Set the Adapter into SimpleCursorAdapter
		setListAdapter(noteAdapter);
	}

	// Capture ListView item click
	OnItemClickListener viewNoteListener = new OnItemClickListener() {
		public void onItemClick(AdapterView arg0, View arg1, int arg2,
				long arg3) {

			// Open ViewNote activity
			Intent viewnote = new Intent(MainActivity.this, ViewNote.class);

			// Pass the ROW_ID to ViewNote activity
			viewnote.putExtra(ROW_ID, arg3);
			startActivity(viewnote);
		}
	};

	@Override
	protected void onResume() {
		super.onResume();

		// Execute GetNotes Asynctask on return to MainActivity
		new GetNotes().execute((Object[]) null);
	}

	@Override
	protected void onStop() {
		Cursor cursor = noteAdapter.getCursor();

		// Deactivates the Cursor
		if (cursor != null)
			cursor.deactivate();

		noteAdapter.changeCursor(null);
		super.onStop();
	}

	// Create an Actionbar menu
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {

		// Menu Title
		menu.add("Add New Notes")
				.setOnMenuItemClickListener(this.SaveButtonClickListener)
				.setShowAsAction(MenuItem.SHOW_AS_ACTION_IF_ROOM);

		return super.onCreateOptionsMenu(menu);
	}

	// Capture menu item click
	OnMenuItemClickListener SaveButtonClickListener = new OnMenuItemClickListener() {
		public boolean onMenuItemClick(MenuItem item) {

			// Open AddEditNotes activity
			Intent addnote = new Intent(MainActivity.this, AddEditNotes.class);
			startActivity(addnote);

			return false;

		}
	};

	// GetNotes AsyncTask
	private class GetNotes extends AsyncTask {
		DatabaseConnector dbConnector = new DatabaseConnector(MainActivity.this);

		@Override
		protected Cursor doInBackground(Object... params) {
			// Open the database
			dbConnector.open();

			return dbConnector.ListAllNotes();
		}

		@Override
		protected void onPostExecute(Cursor result) {
			noteAdapter.changeCursor(result);

			// Close Database
			dbConnector.close();
		}
	}
}

In this activity, we will show the list of items in a listview from the SQLite database by using an AsyncTask to call the ListAllNotes function from the Database Connector class. On listview item click will pass the row id into ViewNote class. Then we have created an options menu to open AddEditNotes class that allow users to add items into the listview.

Next, create a XML graphical layout for your MainActivity. Go to res > layout > Right Click on layout > New > Android XML File

Name your new XML file list_note.xml and paste the following code.

list_note.xml

Output:

Next, create the database functions in a new class called DatabaseConnector.java. Go to File > New > Class and name it DatabaseConnector.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your DatabaseConnector.java and paste the following codes.

DatabaseConnector.java

package com.androidbegin.databasetutorial;

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

public class DatabaseConnector {

	// Declare Variables
	private static final String DB_NAME = "MyNotes";
	private static final String TABLE_NAME = "tablenotes";
	private static final String TITLE = "title";
	private static final String ID = "_id";
	private static final String NOTE = "note";
	private static final int DATABASE_VERSION = 1;
	private SQLiteDatabase database;
	private DatabaseHelper dbOpenHelper;

	public DatabaseConnector(Context context) {
		dbOpenHelper = new DatabaseHelper(context, DB_NAME, null,
				DATABASE_VERSION);

	}

	// Open Database function
	public void open() throws SQLException {
		// Allow database to be in writable mode
		database = dbOpenHelper.getWritableDatabase();
	}

	// Close Database function
	public void close() {
		if (database != null)
			database.close();
	}

	// Create Database function
	public void InsertNote(String title, String note) {
		ContentValues newCon = new ContentValues();
		newCon.put(TITLE, title);
		newCon.put(NOTE, note);

		open();
		database.insert(TABLE_NAME, null, newCon);
		close();
	}

	// Update Database function
	public void UpdateNote(long id, String title, String note) {
		ContentValues editCon = new ContentValues();
		editCon.put(TITLE, title);
		editCon.put(NOTE, note);

		open();
		database.update(TABLE_NAME, editCon, ID + "=" + id, null);
		close();
	}

	// Delete Database function
	public void DeleteNote(long id) {
		open();
		database.delete(TABLE_NAME, ID + "=" + id, null);
		close();
	}

	// List all data function
	public Cursor ListAllNotes() {
		return database.query(TABLE_NAME, new String[] { ID, TITLE }, null,
				null, null, null, TITLE);
	}

	// Capture single data by ID
	public Cursor GetOneNote(long id) {
		return database.query(TABLE_NAME, null, ID + "=" + id, null, null,
				null, null);
	}

}

In this class, we have some basic database functions such as create, update, delete and list data. All functions in DatabaseConnector class can be called from other classes.

NOTE : To reset or wipe the database, just change the version number.

private static final int DATABASE_VERSION = 1;

Next, create a SQlite Database Helper to help manage database creation and version management. Go to File > New > Class and name it DatabaseHelper.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your DatabaseHelper.java and paste the following codes.

DatabaseHelper.java

package com.androidbegin.databasetutorial;

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

public class DatabaseHelper extends SQLiteOpenHelper {

	// Declare Variables
	private static final String DB_NAME = "MyNotes";
	public static final String TABLE_NAME = "tablenotes";
	public static final String TITLE = "title";
	public static final String NOTE = "note";

	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, DB_NAME, factory, version);
	}

	@Override	
	public void onCreate(SQLiteDatabase db) {
		// Create a database table
		String createQuery = "CREATE TABLE " + TABLE_NAME
				+ " (_id integer primary key autoincrement," + TITLE + ", "
				+ NOTE + ");";
		db.execSQL(createQuery);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Database will be wipe on version change
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}

}

We have created a database helper using a simple SQLite Query. We have set integer “id” as a PRIMARY KEY and is used to uniquely identify each record in a database table. The database will automatically be wiped or dropped if there are changes made to the version number as mentioned above.

Next, create an activity for the ListView item click that shows a single database data. Go to File > New > Class and name it ViewNote.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your ViewNote.java and paste the following code.

ViewNote.java

package com.androidbegin.databasetutorial;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.os.AsyncTask;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.MenuItem.OnMenuItemClickListener;
import android.widget.TextView;

public class ViewNote extends Activity {

	// Declare Variables
	private long rowID;
	private TextView TitleTv;
	private TextView NoteTv;
	private static final String TITLE = "title";
	private static final String NOTE = "note";

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.view_note);

		// Locate the TextView in view_note.xml
		TitleTv = (TextView) findViewById(R.id.TitleText);
		NoteTv = (TextView) findViewById(R.id.NoteText);

		// Retrieve the ROW ID from MainActivity.java
		Bundle extras = getIntent().getExtras();
		rowID = extras.getLong(MainActivity.ROW_ID);
	}

	@Override
	protected void onResume() {
		super.onResume();

		// Execute LoadNotes() AsyncTask
		new LoadNotes().execute(rowID);
	}

	// LoadNotes() AsyncTask
	private class LoadNotes extends AsyncTask {
		// Calls DatabaseConnector.java class
		DatabaseConnector dbConnector = new DatabaseConnector(ViewNote.this);

		@Override
		protected Cursor doInBackground(Long... params) {
			// Pass the Row ID into GetOneNote function in
			// DatabaseConnector.java class
			dbConnector.open();
			return dbConnector.GetOneNote(params[0]);
		}

		@Override
		protected void onPostExecute(Cursor result) {
			super.onPostExecute(result);

			result.moveToFirst();
			// Retrieve the column index for each data item
			int TitleIndex = result.getColumnIndex(TITLE);
			int NoteIndex = result.getColumnIndex(NOTE);

			// Set the Text in TextView
			TitleTv.setText(result.getString(TitleIndex));
			NoteTv.setText(result.getString(NoteIndex));

			result.close();
			dbConnector.close();
		}
	}

	// Create an options menu
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		menu.add("Edit Note")
				.setOnMenuItemClickListener(this.EditButtonClickListener)
				.setShowAsAction(MenuItem.SHOW_AS_ACTION_IF_ROOM);

		menu.add("Delete Notes")
				.setOnMenuItemClickListener(this.DeleteButtonClickListener)
				.setShowAsAction(MenuItem.SHOW_AS_ACTION_IF_ROOM);

		return super.onCreateOptionsMenu(menu);
	}

	// Capture edit menu item click
	OnMenuItemClickListener EditButtonClickListener = new OnMenuItemClickListener() {
		public boolean onMenuItemClick(MenuItem item) {

			// Pass Row ID and data to AddEditNotes.java
			Intent addeditnotes = new Intent(ViewNote.this, AddEditNotes.class);

			addeditnotes.putExtra(MainActivity.ROW_ID, rowID);
			addeditnotes.putExtra(TITLE, TitleTv.getText());
			addeditnotes.putExtra(NOTE, NoteTv.getText());
			startActivity(addeditnotes);

			return false;

		}
	};

	// Capture delete menu item click
	OnMenuItemClickListener DeleteButtonClickListener = new OnMenuItemClickListener() {
		public boolean onMenuItemClick(MenuItem item) {

			// Calls DeleteNote() Function
			DeleteNote();

			return false;

		}
	};

	private void DeleteNote() {

		// Display a simple alert dialog to reconfirm the deletion
		AlertDialog.Builder alert = new AlertDialog.Builder(ViewNote.this);
		alert.setTitle("Delete Item");
		alert.setMessage("Do you really want to delete this note?");

		alert.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
			public void onClick(DialogInterface dialog, int button) {
				final DatabaseConnector dbConnector = new DatabaseConnector(
						ViewNote.this);

				AsyncTask deleteTask = new AsyncTask() {
					@Override
					protected Object doInBackground(Long... params) {
						// Passes the Row ID to DeleteNote function in
						// DatabaseConnector.java
						dbConnector.DeleteNote(params[0]);
						return null;
					}

					@Override
					protected void onPostExecute(Object result) {
						// Close this activity
						finish();
					}
				};
				// Execute the deleteTask AsyncTask above
				deleteTask.execute(new Long[] { rowID });
			}
		});

		// Do nothing on No button click
		alert.setNegativeButton("No", null).show();
	}
}

In this activity, we have retrieved the Row ID from the listview item click in the MainActivity class and show it on ViewNote class. Then we used the Row ID as a primary key to update, edit or delete the data by using Database Connector functions.

Next, create a view to show results on ViewNote. Go to res > layout > Right Click on layout > New > Android XML File

Name your new XML file view_note.xml and paste the following code.

view_note.xml

Output:

Next, create a new activity for create and update data in the database. Go to File > New > Class and name it AddEditNotes.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your AddEditNotes.java and paste the following code.

AddEditNotes.java

package com.androidbegin.databasetutorial;

import android.app.Activity;
import android.app.AlertDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.EditText;
import android.view.MenuItem.OnMenuItemClickListener;

public class AddEditNotes extends Activity {

	// Declare Variables
	private long rowID;
	private EditText title_edit;
	private EditText note_edit;
	private static final String TITLE = "title";
	private static final String NOTE = "note";

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.add_note);

		// Locate the EditText in add_note.xml
		title_edit = (EditText) findViewById(R.id.titleEdit);
		note_edit = (EditText) findViewById(R.id.noteEdit);

		// Retrieve the Row ID from ViewNote.java
		Bundle extras = getIntent().getExtras();
		if (extras != null) {
			rowID = extras.getLong("row_id");
			title_edit.setText(extras.getString(TITLE));
			note_edit.setText(extras.getString(NOTE));
		}
	}

	// Create an ActionBar menu
	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		menu.add("Save Note")
				.setOnMenuItemClickListener(this.SaveButtonClickListener)
				.setShowAsAction(MenuItem.SHOW_AS_ACTION_IF_ROOM);

		return super.onCreateOptionsMenu(menu);
	}

	// Capture save menu item click
	OnMenuItemClickListener SaveButtonClickListener = new OnMenuItemClickListener() {
		public boolean onMenuItemClick(MenuItem item) {

			// Passes the data into saveNote() function
			if (title_edit.getText().length() != 0) {
				AsyncTask saveNoteAsyncTask = new AsyncTask() {
					@Override
					protected Object doInBackground(Object... params) {
						saveNote();
						return null;
					}

					@Override
					protected void onPostExecute(Object result) {
						// Close this activity
						finish();
					}
				};
				// Execute the saveNoteAsyncTask AsyncTask above
				saveNoteAsyncTask.execute((Object[]) null);
			}

			else {
				// Display a simple alert dialog that forces user to put in a title
				AlertDialog.Builder alert = new AlertDialog.Builder(
						AddEditNotes.this);
				alert.setTitle("Title is required");
				alert.setMessage("Put in a title for this note");
				alert.setPositiveButton("Okay", null);
				alert.show();
			}

			return false;

		}
	};
	// saveNote() function
	private void saveNote() {
		DatabaseConnector dbConnector = new DatabaseConnector(this);

		if (getIntent().getExtras() == null) {
			// Passes the data to InsertNote in DatabaseConnector.java
			dbConnector.InsertNote(title_edit.getText().toString(), note_edit
					.getText().toString());
		} else {
			// Passes the Row ID and data to UpdateNote in DatabaseConnector.java
			dbConnector.UpdateNote(rowID, title_edit.getText().toString(),
					note_edit.getText().toString());
		}
	}
}

In this activity, we have retrieved the Row ID and EditTexts from ViewNote class. Then the users are allowed to make modifications to the data and use the InsertNote() and UpdateNote() to insert or update the data in the database.

Next, create a XML graphical layout for AddEditNote class. Go to res > layout > Right Click on layout > New > Android XML File

Name your new XML file add_note.xml and paste the following code.

add_note.xml

Output:

Next, change the application name and texts. Open your strings.xml in your res > values folder and paste the following code.

strings.xml

    SQLite Database Tutorial
    Hello world!
    Settings
    Title
    Notes
    Title : 
    Notes : 

In your AndroidManifest.xml, we need to declare some activities. Open your AndroidManifest.xml and paste the following code.

AndroidManifest.xml

Output:

Source Code 

[purchase_link id=”7894″ text=”Purchase to Download Source Code” style=”button” color=”green”]