Friday, January 18, 2008

Android Database


Database is important thing in programming. Many of our code always use data to be processed and saved. Just like any other programming environtment, Android support database programming too. You can use default database supported by android, SQLiteDatabase.

Database in SQLiteDatabase can contains more than one table, assume that we have one database PERSONALDB, and have one table BIODATA. The structure of BIODATA is:

_id integer
code string
name string
gender string

_id is for key increment,
code, name, and gender is for description of person.

When program called in the first time, we have to make sure that the database and table opened if it is exist. if not, than we have to create the database and tabel. As an example from Android notepad sample, here the class PersonDbHelper for manipulating table Biodata.

import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class PersonDbHelper {
    class Row extends Object {
        public long _Id;
        public String code;
        public String name;
        public String gender;
    }

    private static final String DATABASE_CREATE =
        "create table BIODATA(_id integer primary key autoincrement, "
            + "code text not null,"
            + "name text not null"
            +");";

    private static final String DATABASE_NAME = "PERSONALDB";

    private static final String DATABASE_TABLE = "BIODATA";

    private static final int DATABASE_VERSION = 1;

    private SQLiteDatabase db;

    public PersonDbHelper(Context ctx) {
        try {
            db = ctx.openDatabase(DATABASE_NAME, null);
        } catch (FileNotFoundException e) {
            try {
                db =
                    ctx.createDatabase(DATABASE_NAME, DATABASE_VERSION, 0,
                        null);
                db.execSQL(DATABASE_CREATE);
            } catch (FileNotFoundException e1) {
                db = null;
            }
        }
    }

    public void close() {
        db.close();
    }

    public void createRow(String code, String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put("code", code);
        initialValues.put("name", name);
        db.insert(DATABASE_TABLE, null, initialValues);
    }

    public void deleteRow(long rowId) {
        db.delete(DATABASE_TABLE, "_id=" + rowId, null);
    }

    public List<Row> fetchAllRows() {
        ArrayList<Row> ret = new ArrayList<Row>();
        try {
            Cursor c =
                db.query(DATABASE_TABLE, new String[] {
                    "_id", "code", "name"}, null, null, null, null, null);
            int numRows = c.count();
            c.first();
            for (int i = 0; i < numRows; ++i) {
                Row row = new Row();
                row._Id = c.getLong(0);
                row.code = c.getString(1);
                row.name = c.getString(2);
                ret.add(row);
                c.next();
            }
        } catch (SQLException e) {
            Log.e("Exception on query", e.toString());
        }
        return ret;
    }

    public Row fetchRow(long rowId) {
        Row row = new Row();
        Cursor c =
            db.query(true, DATABASE_TABLE, new String[] {
                "_id", "code", "name"}, "_id=" + rowId, null, null,
                null, null);
        if (c.count() > 0) {
            c.first();
            row._Id = c.getLong(0);
            row.code = c.getString(1);
            row.name = c.getString(2);
            return row;
        } else {
            row.rowId = -1;
            row.code = row.name= null;
        }
        return row;
    }

    public void updateRow(long rowId, String code, String name) {
        ContentValues args = new ContentValues();
        args.put("code", code);
        args.put("name", name);
        db.update(DATABASE_TABLE, args, "_id=" + rowId, null);
    }
    public Cursor GetAllRows() {
        try {
            return db.query(DATABASE_TABLE, new String[] {
                    "_id", "code", "name"}, null, null, null, null, null);
        } catch (SQLException e) {
            Log.e("Exception on query", e.toString());
            return null;
        }
    }
}

in Method onCreate Activity you just put single command below to initialize the database :
...
Db = new PersonDbHelper(this);
...

it will try opening PersonalDB first, if it is not exist, than it will create the database. in this PersonDbHelper class, you have method for inserting, deleting, updating, querying table.

Ok, have a nice try

Do you want to learn more ?
Learning with sample code ?
Learning by Doing ?
Just Visit http://learncodes.googlepages.com/
and there is Android UI Design at there.

8 comments:

Wasabi said...

I had some errors using this code. And, had some difficulty debugging that error. I posted a fix for "source not found" error here :
http://moazzam-khan.com/blog/

Unknown said...

how to completely remove the database ?

Anonymous said...

Try this, it is really simple
Simple Android Database

Anonymous said...

simple 1

http://sarangasl.blogspot.com/2009/09/create-android-database.html

Anonymous said...

Try this,

Very Simple android database example

Anonymous said...

How do you execute this code??

Anonymous said...

Thanks, after searching the net for a day your code finally worked for me. To everybody else, don't forget the "ListView android:id="@id/android:list""-line in your xml.

Helen Neely said...

Thanks for this nice and quick intro to using SQLite database on Android. I just started and your resources have proved very useful.

Thanks again.

Helen Neely