Simple Android Database Application

Today we are going to build a simple database application in Android. First see the project resources screenshot indicated below.

Download Complete Project

This application uses SQLite database which stores data as a file. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. This is highly recommended for mobile applications as they share little memory compared to general desktop applications. When the application creates a database, it is saved in the directory data/data/APP_NAME/databases/FILENAME. (Find this using DDMS perspective in Eclipse)

We need a DBHelper class that inherits from SQLiteOpenHelper super class. This allows to create database, upgrade database,  get a writable database, get a readable database and many more.

DBAdapter class consists of several user-defined wrapper methods to handle database operations like add,update,delete,fetch data …etc. Inside these methods we call built-in methods in SQLiteDatabase class to accomplish  our tasks.

The Biz2Activity class is bind to main.xml and it creates the only GUI interface in the application. The interface  has a ListView to display business items as a list. User can add, update or delete a business using the options menu. Android DatePicker tool is used to get the date. Business categories are listed using Android  Spinner which uses an ArrayAdapter to get and display data. The data comes from a String array defined in Strings.xml in Resources folder.

This is the final application

main.xml

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:orientation="vertical" >

<TextView

android:layout_width="fill_parent"

android:layout_height="wrap_content"

android:text="@string/hello" />

<EditText

android:id="@+id/txtTask"

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:layout_marginBottom="10dp">

<requestFocus />

</EditText>

<DatePicker

android:id="@+id/dtpDate"

android:layout_width="wrap_content"

android:layout_height="79dp" android:layout_marginBottom="10dp"/>

<Spinner

android:id="@+id/spCategory"

android:layout_width="fill_parent"

android:layout_height="wrap_content" android:layout_marginBottom="10dp"/>

<ListView

android:id="@+id/android:list"

android:layout_width="match_parent"

android:layout_height="wrap_content" >

</ListView>

</LinearLayout>

row.xml

<?xml version="1.0" encoding="utf-8"?>

<TextView xmlns:android="http://schemas.android.com/apk/res/android"

android:id = "@+id/text1"

android:layout_width="match_parent"

android:layout_height="match_parent" >

</TextView>

Strings.xml

<?xml version="1.0" encoding="utf-8"?>

<resources>

<string name="hello">Hello World, Biz2Activity!</string>

<string name="app_name">Biz2</string>

<string name="add">Add</string>

<string name="update">Update</string>

<string name="delete">Delete</string>

<string name="add_biz">Add New Business</string>

<string name="update_biz">Update Business</string>

<string name="title">Title</string>

<string name="start_date">Start Date</string>

<string name="category">Category</string>

<string name="save">Save</string>

<string-array name="biz_categories">

<item >Real Estate</item>

<item >Foods</item>

<item >Housing</item>

<item >Health Care</item>

</string-array>

</resources>

DBHelper.java

package com.my.biz2;
 
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class DBHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "biz";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE = "CREATE TABLE biz_data (_id integer primary key autoincrement,title text not null,start_date text,category text not null );";
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
db.execSQL("DROP TABLE IF EXISTS biz_data");
onCreate(db);
}
}

DBAdapter.java

package com.my.biz2;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
 
public class DBAdapter
{
    private static final String DATABASE_TABLE = "biz_data";
    public static final String KEY_ROW_ID = "_id";
    public static final String KEY_TITLE = "title";
    public static final String KEY_START_DATE = "start_date";
    public static final String KEY_CATEGORY = "category";
 
    SQLiteDatabase mDb;
    Context mCtx;
    DBHelper mDbHelper;
 
    public DBAdapter(Context context)
    {
        this.mCtx = context;
    }
 
    public DBAdapter open() throws SQLException
    {
        mDbHelper = new DBHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
 
    public void close()
    {
        mDbHelper.close();
    }
 
    public long createBiz(String title,String date,String category)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_START_DATE, date);
        initialValues.put(KEY_CATEGORY, category);
        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }
 
    public boolean deleteBiz(long id)
    {
        return mDb.delete(DATABASE_TABLE, KEY_ROW_ID + " = " + id, null) > 0;
    }
 
    public boolean updateBiz(long id,String title,String date,String category)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_START_DATE, date);
        initialValues.put(KEY_CATEGORY, category);
        return mDb.update(DATABASE_TABLE, initialValues, KEY_ROW_ID + " = " + id, null) > 0;
    }
 
    public Cursor fetchAllBiz()
    {
        return mDb.query(DATABASE_TABLE, new String[]{KEY_ROW_ID,KEY_TITLE,KEY_START_DATE,KEY_CATEGORY}, null, null, null, null, null);
    }
 
    public Cursor fetchBiz(long id)
    {
        Cursor c = mDb.query(DATABASE_TABLE, new String[]{KEY_ROW_ID,KEY_TITLE,KEY_START_DATE,KEY_CATEGORY}, KEY_ROW_ID + " = " + id, null, null, null, null);
        if(c != null)
        {
            c.moveToFirst();
        }
        return c;
    }
}

Biz2Activity.java

package com.my.biz2;
 
import java.util.StringTokenizer;
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.DatePicker;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Spinner;
 
public class Biz2Activity extends ListActivity {
    DBAdapter mDbAdapter;
    EditText mTitle;
    DatePicker mDate;
    Spinner mCategory;
    ArrayAdapter  adapter;
    public static final int INSERT_ID = Menu.FIRST;
    public static final int UPDATE_ID = Menu.FIRST + 1;
    public static final int DELETE_ID = Menu.FIRST + 2;
    private long id;
 
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
 
        mDbAdapter = new DBAdapter(this);
        mDbAdapter.open();
        fillData();
 
        mTitle = (EditText)findViewById(R.id.txtTask);
        mDate = (DatePicker)findViewById(R.id.dtpDate);
        mCategory = (Spinner)findViewById(R.id.spCategory);
        adapter = ArrayAdapter.createFromResource(this, R.array.biz_categories, android.R.layout.simple_spinner_item);
        adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        mCategory.setAdapter(adapter);
    }
 
    public void fillData()
    {
        Cursor c = mDbAdapter.fetchAllBiz();
        startManagingCursor(c);
        String []from = new String[]{DBAdapter.KEY_TITLE,DBAdapter.KEY_START_DATE,DBAdapter.KEY_CATEGORY};
        int [] to = new int[]{R.id.text1};
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.row, c, from, to);
        setListAdapter(adapter);
    }
 
    public String getDate()
    {
        int year = mDate.getYear();
        int month = mDate.getMonth();
        int day = mDate.getDayOfMonth();
 
        String a  = String.valueOf(year);
        String b  = String.valueOf(month);
        String c  = String.valueOf(day);
 
        return  a + "/" + b + "/" + c;
    }
    public void create()
    {
        String title = mTitle.getText().toString();
        String date = getDate();
        String category = mCategory.getSelectedItem().toString();
        mDbAdapter.createBiz(title, date, category);
        fillData();
    }
 
    public void update()
    {
        String title = mTitle.getText().toString();
        String date = getDate();
        String category = mCategory.getSelectedItem().toString();
        mDbAdapter.updateBiz(id, title, date, category);
        fillData();
    }
 
    public void delete()
    {
        mDbAdapter.deleteBiz(id);
        fillData();
    }
 
    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        super.onCreateOptionsMenu(menu);
        menu.add(0, INSERT_ID, 0, R.string.add);
        menu.add(0, UPDATE_ID, 0, R.string.update);
        menu.add(0, DELETE_ID, 0, R.string.delete);
        return true;
    }
 
    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        switch(item.getItemId())
        {
        case INSERT_ID:
            create();
            return true;
        case UPDATE_ID:
            update();
            return true;
        case DELETE_ID:
            delete();
            return true;
        }
 
        return super.onOptionsItemSelected(item);
    }
 
    @Override
    protected void onListItemClick(ListView l, View v, int position, long id) {
        super.onListItemClick(l, v, position, id);
        this.id = id;
        Cursor c = mDbAdapter.fetchBiz(id);
        mTitle.setText(c.getString(
                c.getColumnIndexOrThrow(DBAdapter.KEY_TITLE)));
 
        String category = c.getString(
                c.getColumnIndexOrThrow(DBAdapter.KEY_CATEGORY));
        ArrayAdapter myAdap = (ArrayAdapter) mCategory.getAdapter(); //cast to an ArrayAdapter
 
        int spinnerPosition = myAdap.getPosition(category);
        mCategory.setSelection(spinnerPosition);
 
        //display date
        String date = c.getString(c.getColumnIndexOrThrow(DBAdapter.KEY_START_DATE));
        StringTokenizer tokens = new StringTokenizer(date,"/");
        int arr[] = new int[3];
        int i=0;
 
        while(tokens.hasMoreTokens())
        {
            arr[i] = Integer.valueOf(tokens.nextToken());
            i++;
        }
 
        mDate.updateDate(arr[0], arr[1], arr[2]);
    }
}