In this tutorial I’ll continue making my Android SQLite Address Book app. I hope this code is useful enough to use as a cheat sheet when you need to use an SQLite database in Android.

I cover a ton of topics: How to Create a SQLite Database and Tables, How to Issue Queries, How to Insert Data, How to Update Data, How to Delete Data, SQLiteOpenHelper, execSQL, SQLiteDatabase, ContentValues, rawQuery, Cursor, and more. The code below will help.

If you like tutorials like this, it helps to tell Google+ with a click here

Code From the Video

DBTools.java

// DBTools.java
package com.newthinktank.contactsapp;
import java.util.ArrayList;
import java.util.HashMap;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
// SQLiteOpenHelper helps you open or create a database
public class DBTools extends SQLiteOpenHelper {
// Context : provides access to application-specific resources and classes
public DBTools(Context applicationcontext) {
// Call use the database or to create it
super(applicationcontext, "contactbook.db", null, 1);
}
// onCreate is called the first time the database is created
public void onCreate(SQLiteDatabase database) {
// How to create a table in SQLite
// Make sure you don't put a ; at the end of the query
String query = "CREATE TABLE contacts ( contactId INTEGER PRIMARY KEY, firstName TEXT, " +
"lastName TEXT, phoneNumber TEXT, emailAddress TEXT, homeAddress TEXT)";
// Executes the query provided as long as the query isn't a select
// or if the query doesn't return any data
database.execSQL(query);
}
// onUpgrade is used to drop tables, add tables, or do anything
// else it needs to upgrade
// This is droping the table to delete the data and then calling
// onCreate to make an empty table
public void onUpgrade(SQLiteDatabase database, int version_old, int current_version) {
String query = "DROP TABLE IF EXISTS contacts";
// Executes the query provided as long as the query isn't a select
// or if the query doesn't return any data
database.execSQL(query);
onCreate(database);
}
public void insertContact(HashMap<String, String> queryValues) {
// Open a database for reading and writing
SQLiteDatabase database = this.getWritableDatabase();
// Stores key value pairs being the column name and the data
// ContentValues data type is needed because the database
// requires its data type to be passed
ContentValues values = new ContentValues();
values.put("firstName", queryValues.get("firstName"));
values.put("lastName", queryValues.get("lastName"));
values.put("phoneNumber", queryValues.get("phoneNumber"));
values.put("emailAddress", queryValues.get("emailAddress"));
values.put("homeAddress", queryValues.get("homeAddress"));
// Inserts the data in the form of ContentValues into the
// table name provided
database.insert("contacts", null, values);
// Release the reference to the SQLiteDatabase object
database.close();
}
public int updateContact(HashMap<String, String> queryValues) {
// Open a database for reading and writing
SQLiteDatabase database = this.getWritableDatabase();
// Stores key value pairs being the column name and the data
ContentValues values = new ContentValues();
values.put("firstName", queryValues.get("firstName"));
values.put("lastName", queryValues.get("lastName"));
values.put("phoneNumber", queryValues.get("phoneNumber"));
values.put("emailAddress", queryValues.get("emailAddress"));
values.put("homeAddress", queryValues.get("homeAddress"));
// update(TableName, ContentValueForTable, WhereClause, ArgumentForWhereClause)
return database.update("contacts", values, "contactId" + " = ?", new String[] { queryValues.get("contactId") });
}
// Used to delete a contact with the matching contactId
public void deleteContact(String id) {
// Open a database for reading and writing
SQLiteDatabase database = this.getWritableDatabase();
String deleteQuery = "DELETE FROM contacts where contactId='"+ id +"'";
// Executes the query provided as long as the query isn't a select
// or if the query doesn't return any data
database.execSQL(deleteQuery);
}
public ArrayList<HashMap<String, String>> getAllContacts() {
// ArrayList that contains every row in the database
// and each row key / value stored in a HashMap
ArrayList<HashMap<String, String>> contactArrayList;
contactArrayList = new ArrayList<HashMap<String, String>>();
String selectQuery = "SELECT * FROM contacts";
// Open a database for reading and writing
SQLiteDatabase database = this.getWritableDatabase();
// Cursor provides read and write access for the
// data returned from a database query
// rawQuery executes the query and returns the result as a Cursor
Cursor cursor = database.rawQuery(selectQuery, null);
// Move to the first row
if (cursor.moveToFirst()) {
do {
HashMap<String, String> contactMap = new HashMap<String, String>();
// Store the key / value pairs in a HashMap
// Access the Cursor data by index that is in the same order
// as used when creating the table
contactMap.put("contactId", cursor.getString(0));
contactMap.put("firstName", cursor.getString(1));
contactMap.put("lastName", cursor.getString(2));
contactMap.put("phoneNumber", cursor.getString(3));
contactMap.put("emailAddress", cursor.getString(4));
contactMap.put("homeAddress", cursor.getString(5));
contactArrayList.add(contactMap);
} while (cursor.moveToNext()); // Move Cursor to the next row
}
// return contact list
return contactArrayList;
}
public HashMap<String, String> getContactInfo(String id) {
HashMap<String, String> contactMap = new HashMap<String, String>();
// Open a database for reading only
SQLiteDatabase database = this.getReadableDatabase();
String selectQuery = "SELECT * FROM contacts where contactId='"+id+"'";
// rawQuery executes the query and returns the result as a Cursor
Cursor cursor = database.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
contactMap.put("firstName", cursor.getString(1));
contactMap.put("lastName", cursor.getString(2));
contactMap.put("phoneNumber", cursor.getString(3));
contactMap.put("emailAddress", cursor.getString(4));
contactMap.put("homeAddress", cursor.getString(5));
} while (cursor.moveToNext());
}
return contactMap;
}
}

25 Responses to “Android Development 12”

Just want to pay your attention to a small issue about auto_complete in eclipse. When you want to type for example android:textColor="" you can start from te and press ctrl+space and it will give a list of usefull suggestions.

Wow, the range of things you are covering with this ContactsApp is great, learning so much in just this 5 part series alone!
I’m a bit of a slow learner but the way you present this is brilliant. Derek, thanks x 100 for the wealth of information on your site, and for sharing your expertise!

Hi Derek, I managed to solve the problems I had with the interface and the positioning of the buttons. I just uninstalled and jdk for Eclipes reinstalled following the tutorial Install Android Development Tools. However now I have a problem, for the tutorial 12 I am not able to make a file that is named DBTools.java. Will I need to install additional plug-in?

Great tutorials, I can give you another shortcut tip.
When you have to import any library just press: CTRL + SHIFT + O(the letter), and if you want to format and indent your code just press: CTRL + SHIFT + F.
Have a great day!

Have you worked on using a pre-populated database e.g. android.db is your database name. You created all the tables and inserted data using SQLite Database browser or from command line. You saved it to assets/android.db directory. From there, your program can copy it over to android database location /data/data//database/.

I tried the solution from the following article, however, I was not successful. It always fails because a database table already exists. Do you know how to do this? Please share your solution.

Thanks a lot for these tutorials, they are really much more helpful than any book on the topic that i have read. I tend to get bored very fast with java books, they simply contain to much nonsense between the lines. Actually, the only good programming book that i have read were about Phyton (Python Programming for the Absolute Beginner). In each chapter you creat a program from start to finish, with no unanswered questions after the chapter. Similar to what you are doing with your tutorials! Ive been looking for such a java book for years now, and 6 java books later i found your tutorials by chance. The only regret is that i were trying so long with the books.