Android SQLite Database Tutorial
Last Updated: April 11, 2013
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
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
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”]
Database disappear when I add new rows and columns
LS-103
Android SQLite Database Tutorial
Great tutorial, thank you! Could someone please tell me how to implement the above with 3 tables? I actually tried but the app crashes.. Any help would be much appreciated!
Shuka
Android SQLite Database Tutorial
Nice Tutorial.Really useful to me.Thank you.
Stephen Smith
Android SQLite Database Tutorial
I did attempt. By the way, only white screen. What should I do?
Brian
Android SQLite Database Tutorial