Friday, April 10, 2009

Database Creation in Android

Steps for creating a database , creating table into it, inserting value and fetching the values and displaying in a listActivity

Step-I

First of all, create an object of a database, using,

SQLiteDatabase myDB = null;
myDB = databaseAct.this.openOrCreateDatabase("MyDatabase",
MODE_PRIVATE, null);



Here, "MyDatabase" is the name of database. MODE_PRIVATE is file creation mode. In this mode, only created file can be accessed by the calling application or all application sharing the same userID.

Step - II

Next step is, creating a table in the database. Here, our table name is "MyTable". The command is,

myDB.execSQL("CREATE TABLE IF NOT EXISTS MyTable (lastName varchar, "
+ "firstName varchar, age INT(3));");

Now, we created a table with three fields such as lastName, FirstName and Age.

Step - III

Inserting values into the table using the function, execSQL() executes a SQL statement, not a query

myDB.execSQL("INSERT INTO MyTable VALUES ('Guna', 'Boss', 23)");


Include two more sql statements,

myDB.execSQL("INSERT INTO MyTable VALUES ('Sibi', 'Crazy', 21)");
myDB.execSQL("INSERT INTO MyTable VALUES ('Linodh', 'Shaktiman', 20)");



Step - IV

Now we have to fetch data from database and display in a listActivity.

Cursor cursor = myDB.query("MyTable", null, null, null, null, null, null);


Here, query() function executes a SQL query and return a cursor over the resultSet. And we are passing only table name, second argument is string array, where we can specify which all field we need like this,

Cursor cursor = myDB.query("MyTable", {"firstName", "age"}, null, null, null, null, null);


Step - V

Now we have to store the values in arraylist for displaying.
the code snippet is,

int count = 0;
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
count++;
//fetching from database and adding to arrayList
results.add(count+" :"+cursor.getString(cursor.getColumnIndex("lastName"))
+" "+cursor.getString(cursor.getColumnIndex("firstName"))
+" ("+cursor.getInt(cursor.getColumnIndex("age"))+")");

}


Step - VI

last step is displaying in the screen is,

this.setListAdapter(new ArrayAdapter(databaseAct.this,
android.R.layout.simple_list_item_1, results));


The full source code is,

public class databaseAct extends ListActivity {

SQLiteDatabase myDB = null;
ArrayList results = new ArrayList();

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);


//creating database
myDB = databaseAct.this.openOrCreateDatabase("MyDatabase",
MODE_PRIVATE, null);
//creating table
myDB.execSQL("CREATE TABLE IF NOT EXISTS MyTable (lastName varchar, "
+ "firstName varchar, age INT(3));");
//inserting into table
myDB.execSQL("INSERT INTO MyTable VALUES ('Guna', 'Boss', 23)");
myDB.execSQL("INSERT INTO MyTable VALUES ('Sibi', 'Crazy', 22)");
myDB.execSQL("INSERT INTO MyTable VALUES ('Linodh', 'Shakthiman', 20)");

Cursor cursor = myDB.query("MyTable", null, null, null, null, null,
null);
int count = 0;
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
count++;
//fetching from database and adding to arrayList
results.add(count+" :"+cursor.getString(cursor.getColumnIndex("lastName"))
+" "+cursor.getString(cursor.getColumnIndex("firstName"))
+" ("+cursor.getInt(cursor.getColumnIndex("age"))+")");
}

//display in screen
this.setListAdapter(new ArrayAdapter(databaseAct.this,
android.R.layout.simple_list_item_1, results));

}
}

Androidians