Tutorial: Using Android Database

Creating the application for Different manipulations on Employee table

1. Create Project

Create the project com.mobisys.android.database_sample with the Activity called DatabaseSampleActivity.

Create the another package com.mobisys.android.database_sample.database. This package will store the classes for the database handling.

2.Create Database Tables

We will create a separate class i.e. EmployeeDatabase.java for creating and updating the EMP_TABLE in package com.mobisys.android.database_sample.database. Also we will add some generic method to this class(i.e insert, delete, update, countRows, query).

To create and upgrade a database we will use OpenHelper class that extends SQLiteOpenHelper. In this class you need to override the methods onCreate() to create the database and onUpgrade() to upgrade the database in case of changes in the database schema. Both methods receive an SQLiteDatabase object which represents the database.

When we create the object of SQLiteOpenHelper i.e empHelper for the first time, it will call onCreate() method & will create the employee table. Also if we pass newer database version to SQLiteOpenHelper it will call onUpgrade() method.

SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an SQLiteDatabase object which allows database access either in read or write mode.

For the primary key of the database tables you should always use the identifier “_id” as some of Android functions rely on this standard.
[sourcecode language=”java”]
package com.mobisys.android.database_sample.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class EmployeeDatabase {

public static final String DATABASE_NAME = “empdb.db”;
public static final int DATABASE_VERSION = 1;
public static final String EMP_TABLE=”Employees”;
EmployeeDatabase db=null;
public static final String EMP_ID=”_id”; //must be _id
public static final String EMP_NAME=”EmployeeName”;
public static final String EMP_AGE=”Age”;
public static final String EMP_DEPT=”Dept”;
//————————————————————————–
private final OpenHelper empHelper;
public EmployeeDatabase(Context context){
empHelper=new OpenHelper(context);
}

public long insert(String table, ContentValues values){
return empHelper.getWritableDatabase().insert(table, null, values);
}

public long delete(String table, String where, String[] whereArgs){
return empHelper.getWritableDatabase().delete(table, where, whereArgs);
}

public int update(String table, ContentValues values, String whereClause, String[] whereArgs){
return empHelper.getWritableDatabase().update(table, values, whereClause, whereArgs);
}

public long countRows(String query){
return DatabaseUtils.longForQuery(empHelper.getReadableDatabase(), query, null);
}

public

Cursor query(String table,String[] columns, String selection,String[] selectionArgs,String groupBy,String having,String orderBy){
return empHelper.getReadableDatabase().query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
}

public void close(){
empHelper.close();
}
//——————————————————————————
private static class OpenHelper extends SQLiteOpenHelper {

OpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(“CREATE TABLE “+EMP_TABLE+
” (“+ EMP_ID+” INTEGER PRIMARY KEY AUTOINCREMENT, “+
EMP_NAME+” TEXT, “+
EMP_AGE+” INT, “+
EMP_DEPT+” TEXT”+”)”);
}

@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// TODO Auto-generated method stub
db.execSQL(“DROP TABLE IF EXISTS ” + EMP_TABLE);
onCreate(db);

}
}
}
[/sourcecode]

3. User Inteface

For UI, write the following code in main.xml
[sourcecode language=”xml”]






[/sourcecode]
These Buttons helps to manipulate on EMP_TABLE. i.e

4. Database Manipulation

Firstly we will create the EmpVars.java class for holding data which will represent database schema of the Table EMP_TABLE.
[sourcecode language=”java”]
package com.mobisys.android.database_sample;

public class EmpVars {

public int EMP_ID;
public String EMP_NAME;
public int EMP_AGE;
public String EMP_DEPT;

}
[/sourcecode]

Let’s learn how to insert record into table, delete the record from the table, update existing records, fetch the records and counting number of rows.

Inserting records into table

For inserting values to table we will use hardcoaded values for simplicity. The object ContentValues allow to define key/values for insert and update. The key is the column and the value is the value for this column
[sourcecode language=”java”]
((Button)findViewById(R.id.button01)).setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v) {

EmpVars e=new EmpVars();

e.EMP_NAME=”Vikas”;
e.EMP_AGE=”23″;
e.EMP_DEPT=”Sales”;
ContentValues cv=new ContentValues();
cv.put(EmployeeDatabase.EMP_NAME,e.EMP_NAME);
cv.put(EmployeeDatabase.EMP_AGE,e.EMP_AGE);
cv.put(EmployeeDatabase.EMP_DEPT,e.EMP_DEPT);
if(db.insert(EmployeeDatabase.EMP_TABLE,cv)!=-1)
System.out.println(“Success!!!!!***************”);
else
System.out.println(“Error!!!!!”);

}

});
[/sourcecode]

Deleting Records from Table

Here we will delete the first row in EMP_TABLE which is fetched by cursor.

[sourcecode language=”java”]
((Button)findViewById(R.id.button02)).setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v) {

c=db.query(EmployeeDatabase.EMP_TABLE, null,
null,null,null,null,null);
c.moveToNext();
if(c.getCount()>0)
{
if(db.delete(EmployeeDatabase.EMP_TABLE,EmployeeDatabase.EMP_ID+”=”+c.getString(c.getColumnIndex(EmployeeDatabase.EMP_ID)),null)!=-1)
System.out.println(“Success!!!!!***************”);
}
else
{
System.out.println(“Error!!!!! No records”);
Toast.makeText(DatabaseSampleActivity.this, “No item found”, Toast.LENGTH_SHORT).show();
}

}
});

[/sourcecode]

Updating existing records

Also we will update the first row in EMP_TABLE which is fetched by cursor with hardcoade values.
[sourcecode language=”java”]
((Button)findViewById(R.id.button03)).setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v) {

EmpVars e=new EmpVars();

e.EMP_NAME=”Rushab”;
e.EMP_AGE=”12″;
e.EMP_DEPT=”marketing”;
ContentValues cv=new ContentValues();

cv.put(EmployeeDatabase.EMP_NAME,e.EMP_NAME);
cv.put(EmployeeDatabase.EMP_AGE,e.EMP_AGE);
cv.put(EmployeeDatabase.EMP_DEPT,e.EMP_DEPT);
c=db.query(EmployeeDatabase.EMP_TABLE, null,
null,null,null,null,null);
c.moveToNext();
if(c.getCount()>0)
{
if(db.update(EmployeeDatabase.EMP_TABLE,cv,EmployeeDatabase.EMP_ID+”=”+c.getString(c.getColumnIndex(EmployeeDatabase.EMP_ID)),null)!=-1)

System.out.println(“Success!!!!!***************”);
}
else
{
System.out.println(“Error!!!!!No Records”);
Toast.makeText(DatabaseSampleActivity.this, “No item found”, Toast.LENGTH_SHORT).show();
}
}

});

[/sourcecode]

4. Count number of rows

We can count the number of rows with help of getCount() method.
[sourcecode language=”java”]
((Button)findViewById(R.id.button04)).setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v) {
String query=”Select * from “+EmployeeDatabase.EMP_TABLE;
Toast.makeText(DatabaseSampleActivity.this, “No of Rows in a table: “+db.countRows(query), Toast.LENGTH_SHORT).show();
}
});
[/sourcecode]

Display table records using CursorAdapter

[sourcecode language=”java”]
((Button)findViewById(R.id.button05)).setOnClickListener(new View.OnClickListener(){
@Override
public void onClick(View v) {
Intent intent = new Intent(DatabaseSampleActivity.this, DisplayActivity.class);
startActivity(intent);
}
});

}

}
[/sourcecode]
Finally, for displaying records from table we create another ListActivity i.e DisplayActivity.

So, First we will Create the layout display.xml in the folder res/layout which will be used for the layout of an individual row.
[sourcecode language=”xml”]
[/sourcecode]
& create the DisplayActivity class extending ListActivity with CursorAdapter

DisplayActivity.java will contain
[sourcecode language=”java”]
package com.mobisys.android.database_sample;

import com.mobisys.android.database_sample.database.EmployeeDatabase;

import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.Toast;

public class DisplayActivity extends ListActivity{

Cursor c=null;
EmployeeDatabase db=null;

public void onCreate(Bundle icicle) {
super.onCreate(icicle);
db=new EmployeeDatabase(this);
c=db.query(EmployeeDatabase.EMP_TABLE, null,
null,null,null,null,null);

if(c.moveToFirst())
{
MyDisplayAdapter adapter = new MyDisplayAdapter(this, c);// OWN ADAPTER
setListAdapter(adapter);
}
else
{
Toast.makeText(this, “No item found”, Toast.LENGTH_SHORT).show();

}
}
}
[/sourcecode]

Don't forget to add DisplayActivity created above into AndroidManifest.xml

finally create another class i.e MyDisplayAdapter.java extending CursorAdapter

[sourcecode language=”java”]
package com.mobisys.android.database_sample;

import com.mobisys.android.database_sample.database.EmployeeDatabase;

import android.content.Context;
import android.database.Cursor;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.CursorAdapter;
import android.widget.TextView;

public class MyDisplayAdapter extends CursorAdapter{
private final Context context;
private final Cursor c;

public MyDisplayAdapter(Context context, Cursor c) {
super(context,c);
this.context=context;
this.c=c;
}
@Override
public void bindView(View view, Context context, Cursor cursor) {

String eid=cursor.getString(cursor.getColumnIndex(EmployeeDatabase.EMP_ID));
TextView t4 = (TextView) view.findViewById(R.id.text4);
t4.setText(eid);

String ename=cursor.getString(cursor.getColumnIndex(EmployeeDatabase.EMP_NAME));
TextView t1 = (TextView) view.findViewById(R.id.text1);
t1.setText(ename);

String edept=cursor.getString(cursor.getColumnIndex(EmployeeDatabase.EMP_DEPT));
TextView t2 = (TextView) view.findViewById(R.id.text2);
t2.setText(edept);

String eage=cursor.getString(cursor.getColumnIndex(EmployeeDatabase.EMP_AGE));
TextView t3 = (TextView) view.findViewById(R.id.text3);
t3.setText(eage);

}

@Override
public View newView(Context context, Cursor cursor, ViewGroup parent) {

LayoutInflater inflater = (LayoutInflater) context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
View rowView = inflater.inflate(R.layout.display, null, false);
return rowView;
}

}
[/sourcecode]

which will override 2 methods bindView() and newView().
bindView() will bind the values to TextView and newView() will create the newView by inflating the layout i.e display.xml

Run the application.

Download the Source Code

Click here to download source code.