Android Development 12
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 [googleplusone]
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;
}
}
Hello Derek. Thanks for the beautiful tutorials.
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 fromteand press ctrl+space and it will give a list of usefull suggestions.Another example:
android:text="@string/add_contact– Type
tethen ctrl+space forandroid:text=”"– Type
@sthen ctrl+space for@string/– Type
adthen ctrl+space foradd_contactThis is for windows. I don’t know what is the equivalent in Mac.
Thank you.
There are many short-cuts. Just few more:
For
android:layout_weight="1"Just type
wthen Ctrl+Space and it will give you a list of suggestions,so that you can easily pick it out.For
android:padding="@dimen/pendding_5dp"/>– Type
pathen Ctrl+Space forandroid:padding=""– Type
@dthen Ctrl+Space for@dimen/– Type
pthen Ctrl+Space forpendding_5dpHope that help you instead of jumping from place to place to copy things, or jump to Graphical layout just for the purpose of searching for a string value.
Thank you for the great tips 🙂
Thank you for the great tips 🙂 I very much appreciate them!
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!
Thank you 🙂 Yes I’m doing my best to cover as many topics as possible in each app I make. I’m glad you are finding them useful
Thank you for this nice tutorial.:)
You’re very welcome 🙂
One more question: why do we need to close the database after an insert operation but not after a delete?
-Jay
You should actually close it in both situations, but since this was a very simple app I didn’t run into any issues.
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?
I have all of the code in this one package. If you import it into Eclipse it will be easy to see how everything is laid out. I hope that helps 🙂
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!
Thank you for the tips. I know about the shortcuts, but I decided not to use them in tutorials because I can never trust that anyone else knows them. I hope that makes sense 🙂
Hi Derek,
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.
http://blog.softeq.com/2012/12/using-pre-populated-sqlite-database-in.html
Thanks a lot!
Hi,
You basically just save your database to the assets folder in your Android project. Then just use it like it was created in your app.
How do you call a database you’ve already created? And where do you put it in your app? Thank you in advance.
Here you go http://stackoverflow.com/questions/6540906/android-simple-export-and-import-of-sqlite-database
Hi Derek,
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.
Keep up the good work!
Hi Oskar,
Thank you for the very nice compliments 🙂 I’m making a new Android tutorial as well very soon.
What’s a hashmap how it works.
Here is a tutorial on Hash tables which are similar.
Well I see I didn’t got it completely but why did you created DBTools.java in other project than the Address Book Application? Can I create it in the later?
Tip : Do add the next video page link on your website. For example – there should be a link on this page pointing to Part 13 page.
I always try to separate out any classes that I can to keep everything modular. I have every Android video on this page Android video tutorial