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));

}
}

Comments

  1. Great It is very simple example of database creation in android and very helpful for beginners.

    ReplyDelete
  2. Hi.. great example.. will benefit many, as it is clear with code..

    your explanation line by line is superb.. keep up the work

    ReplyDelete

Post a Comment

Popular posts from this blog

AlarmManager and Notification in Android

Swipe Action and ViewFlipper in Android

Difference between SurfaceView and View in Android