Shopper App | local storage (SQLite) #11


Shopper app does not have many data which could be stored in a local database, because it gets most of the data from our webservices. I make use of the android built-in SQLite database for one purpose. Many items are going to be reused, respectively, you can imagine that for instance a member of a flatshare will often buy milk. And maybe the user will buy this milk everytime at the same store. So retyping the same item again and a gain would be annoying. For this case the user has the opportunity to just use (create) an item for a purchase OR to use & save a item while he is making his purchase. For example the user buys milk, and clicks on the use&save button the milk item is saved into the “My Items” section. The next time the user is going to buy some milk (the same milk) he is able to show his stored item “patterns” and choose one of these, so there is no need to retype the name and the price for the item. So this is a quick way to assign items to a purchase.

When we take a look at the implemantation is the same game again. The MyItems screen is again a list which receives it’s data from a custom ArrayAdapter. Ok. The more interesting part is now the SQLite story. For comfortable use of the SQLite API i encapsulate the most important things in an Object called DataHelper. Let’s take a look at this class:


import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.berndwarmuth.client.shopper.Item;

public class DataHelper {

	private static final String DATABASE_NAME = "shopper.db";
	private static final int DATABASE_VERSION = 1;
	private static final String TABLE_NAME = "items";

	private Context context;
	private SQLiteDatabase db;

	private SQLiteStatement insertStmt;
	private static final String INSERT = "insert into " + TABLE_NAME
			+ "(label,price) values (?,?)";

	public DataHelper(Context context) {
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();
		this.insertStmt = this.db.compileStatement(INSERT);

	public long insert(String label, Double price) {
		this.insertStmt.bindString(1, label);
		this.insertStmt.bindDouble(2, price);
		return this.insertStmt.executeInsert();

	public void deleteAll() {
		this.db.delete(TABLE_NAME, null, null);

	public List<Item> selectAll() {
		List<Item> list = new ArrayList<Item>();
		Cursor cursor = this.db.query(TABLE_NAME, new String[] { "label",
				"price" }, null, null, null, null, "label desc");
		if (cursor.moveToFirst()) {
			do {
				list.add(new Item(cursor.getString(0),cursor.getDouble(1)));
			} while (cursor.moveToNext());
		if (cursor != null && !cursor.isClosed()) {
		return list;

	private static class OpenHelper extends SQLiteOpenHelper {

		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);

		public void onCreate(SQLiteDatabase db) {
					+ "(id INTEGER PRIMARY KEY, label TEXT, price FLOAT)");

		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
					"Upgrading database, this will drop tables and recreate.");

At the beginning we see some constants for the databasename, version and tablename. The DataHelper class makes use of the SQLiteOpenHelper class which is responsible for managing database creation and version management. More precisely we define our own OpenHelper by extending the SQLiteOpenHelper class and overriding the methods onCreate() and onUpgrade(). As the names say, in these methods we create our custom database tables and upgrading our db with simple SQL statements. Moreover our DataHelper class provides CRUDS methods for manipulating the database. The databasetable consists of two simple fields (with the id field its 3 ) namely the ItamLabelname and the price. That’s all.

So, querying our database is very very easy. We do so by filling up the ListActivity for “MyItems” in the OnCreate() method of the activity. take a look:

dh = new DataHelper(MyItemsActivity.this);
miaa = new MyItemsArrayAdapter(MyItemsActivity.this, dh.selectAll());

Whats up next?

In the next post, i will discuss the most complex activity, which makes use of lot of the things we discussed before :)

stay tuned,


, , , ,
  • Delicious
  • Facebook
  • Digg
  • Reddit
  • StumbleUpon
  • Twitter

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>