Second Android App: Beerverter

Share it!

Quick note about building this Android App called Beerverter. It is a shopping app to compare prices of beer, wine, and soft drinks. Download Link.

This app is the first time that I used sqlite database to store data in a persistent data storage area.

Normally you need to setup a CursorAdapter to display database table rows in a ListView. However, it was very confusing for me to figure this out. Googling this topic showed that I am not alone.

What I did was to use SQLiteAdapter class with SQLiteHelper inner class. The way it works is that we create an object from SQLiteAdapter, and call openToRead() method on it. If the database does not exist, it will be created by the SQLiteHelper, and onCreate() method will be called. That is the method we create our database tables with db.execSQL(SCRIPT_CREATE_TABLE).

There are subtle differences between mysql and sqlite when defining database tables. I had used mysql in the past and assumed that sqlite would work the same way. These differences stalled the project for several days until I figured out the following differences.

In mysql, most tables will have a column that look like this:

id int not null auto_increment,

This column defines an integer column named id that will have a unique value for each row. This does not work in sqlite. In sqlite, this column is automatically created for each table and it is accessed as _row. If you want to give it your own name, you can define this column as follows:

id INTEGER PRIMARY KEY,

When defined in this manner, this column is accessible as _row or id. There is another difference between mysql and sqlite. In mysql, when you insert a record, if you give id a value of 0, it will be incremented automatically. In sqlite, do not set id to any value. If you set it to 0, it will throw an exception.
Here is a working SQLiteAdapter.java that I used for this project. If you want the file, click on the Download link at the top right.

Download

    public class SQLiteHelper extends SQLiteOpenHelper {
 
        public SQLiteHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);
        }
 
        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            db.execSQL(SCRIPT_CREATE_TABLE);
        }
 
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
 
        }
 
    }

This is how we get data out of the database:

                // get data from sqlite db
                SQLiteAdapter mySQLiteAdapter = new SQLiteAdapter(c); // c is context
                mySQLiteAdapter.openToRead();
                model = mySQLiteAdapter.queueAll(model);
                mySQLiteAdapter.close();  // don't forget to close

Model is defined as an ArrayList of objects, each holding a single row of data. It is defined as follows:

    List<Bev> model = new ArrayList<Bev>();

Now that we have an array of rows, how do we display it on the screen? We create a ListView, then we create an adapter object, and do setAdapter on ListView object as shown below. You can download the working version by clicking on the Download link at the top right.

Download

                // create an adapter, attach it to a ListView
                ListView lv = new ListView(c);
                BevAdapter adapter = new BevAdapter(c);
                lv.setAdapter(adapter);

BevAdapter is subclassed from ArrayAdapter as shown starting from line 74. We override the getView() method to paint each row. This is not the most efficient way of doing this, but works fine because in this case there are only a total of 20 lines.

I am posting this info to remind myself how I did this, in case I need something like this in the future. If it helps you, the reader, that is great. I understand that the whole project is not completely documented here as it would take me too long to explain each step in detail. But the two files I shared above is the bulk of this project.

Share it!
This entry was posted in Projects and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published.

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

[keycaptcha]