Android Development 12

Android SQLiteIn 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”

  1. Saleh says:

    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 from te and press ctrl+space and it will give a list of usefull suggestions.

    Another example: android:text="@string/add_contact
    - Type te then ctrl+space for android:text=”"
    - Type @s then ctrl+space for @string/
    - Type ad then ctrl+space for add_contact

    This is for windows. I don’t know what is the equivalent in Mac.

    Thank you.

    • Saleh says:

      There are many short-cuts. Just few more:
      For android:layout_weight="1"
      Just type w then 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 pa then Ctrl+Space for android:padding=""
      - Type @d then Ctrl+Space for @dimen/
      - Type p then Ctrl+Space for pendding_5dp

      Hope 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.

    • Derek Banas says:

      Thank you for the great tips :) I very much appreciate them!

  2. Derek M says:

    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!

  3. Subah says:

    Thank you for this nice tutorial.:)

  4. Jay says:

    One more question: why do we need to close the database after an insert operation but not after a delete?

    -Jay

  5. Alexandre says:

    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?

  6. Oliver Cartea says:

    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!

    • Derek Banas says:

      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 :)

  7. Fong says:

    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!

  8. Joy says:

    How do you call a database you’ve already created? And where do you put it in your app? Thank you in advance.

  9. Oskar says:

    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!

  10. waqas ahmed says:

    What’s a hashmap how it works.

  11. 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.

  12. Jay says:

    OK, so I guess the trick is to use a contract class for this:
    http://developer.android.com/training/basics/data-storage/databases.html#DefineContract

    Pretty basic, but it should get the job done and remove duplication of hard coded strings from the db helper class.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Google+