Creating and Using Databases in Android

Tutorial: Creating and Using Databases in Android
Assumptions
1. You are using eclipse.
2. You know little bit about android application development.
3. You have installed the emulator.


Any type of application you are going to develop, database will be in your project domain. Database system is required which can store your relational data.

Android uses SQLite as database system, which is:
• Open Source
• Stand Alone SQL database
• Widely used by many popular application
Example:
Mozilla Firfox uses SQLite to store configuration data
iPhone also uses SQLite for database storage etc etc

Databases created in android only accessible to its application not outside the application, If you want to share database outside the application you develop content provide for this application. SQLite database store in data/data//databases folder of an android device, e.g.
Lets your package = com.code
Then your database store in data/data/com/code/databases folder of a device

Step1:
Create android project using eclipse and provide the information like



Step 2:
Create a DBAdapter Helper class, encapsulate all the complex code (of Creating and accessing database) so that it will make clear and transparent to the calling code.
So create Helper Class calles DBAdapter.java the basic functionality of DBAdapter class will be open, close and use SQLite databases.
Create DBAdapter class in com.code packge, so file will be in DatabaseApps/src/code/DBAdapter.java in your workspace.


Step 2:
After Creating DBAdapter,java file import all required packages, and code will look like this
package com.coe;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{

}

Step 3:
We are going to create following fileds(_id, isbn, title and publisher), in the table(titles) and in the database(books), like

books(database)
titles(Table)
_id isbn title Publisher




In DBAdapter.java declare the following constants, like that:

package com.coe;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
public static final String KEY_ROWID = "_id";
public static final String KEY_ISBN = "isbn";
public static final String KEY_TITLE = "title";
public static final String KEY_PUBLISHER = "publisher";
private static final String TAG = "DBAdapter";

private static final String DATABASE_NAME = "books";
private static final String DATABASE_TABLE = "titles";
private static final int DATABASE_VERSION = 1;

private static final String DATABASE_CREATE =
"create table titles (_id integer primary key autoincrement, "
+ "isbn text not null, title text not null, "
+ "publisher text not null);";

private final Context context;
}

DATABASE_CREATE stores the sql Create Table command using that command we will create titles table in books database.

Within DBAdapter class, you extend SQLiteOpenHelper class (an android SQLite Helper class for database creation). Basically we are going to override onCreate and onUpgrade method of SQLiteOpenHelper Class.

Create nested class named DatabaseHelper extending SQLietOpenHelper
onCreate Method: it creates new database, if database is not present
onUpgrade Method : This is used to call when we are going to upgrade the database version by using DATABASE_VERSION constant. In Our tutorial we will just drop rhe table and create new table.

The nested DatabaseHelper class would be declare like this:

package com.coe;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
public static final String KEY_ROWID = "_id";
public static final String KEY_ISBN = "isbn";
public static final String KEY_TITLE = "title";
public static final String KEY_PUBLISHER = "publisher";
private static final String TAG = "DBAdapter";

private static final String DATABASE_NAME = "books";
private static final String DATABASE_TABLE = "titles";
private static final int DATABASE_VERSION = 1;

private static final String DATABASE_CREATE =
"create table titles (_id integer primary key autoincrement, "
+ "isbn text not null, title text not null, "
+ "publisher text not null);";

private final Context context;

private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DBAdapter(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(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 oldVersion,
int newVersion)
{
Log.w(TAG, "Upgrading database from version " + oldVersion
+ " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS titles");
onCreate(db);
}
}



}



Step 4:
Now we are ready for develop some of method in DB Adapter Class, These Methods are:
open(): Here we just create new database
close(): Here we close the database refference
insertTitle(): Here we insert a single row of record
deleteTitle(): Here we delete a specific row
getAllTitles(): Here we return row, with out any where closure, it will better iterate by loop in calling function.
getTitles(): its return a just single row.
updateTitle(): its update a single row as SQL Update query

These methods definition and over all DBAdapter Class Defination would be like:


package com.coe;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
public static final String KEY_ROWID = "_id";
public static final String KEY_ISBN = "isbn";
public static final String KEY_TITLE = "title";
public static final String KEY_PUBLISHER = "publisher";
private static final String TAG = "DBAdapter";

private static final String DATABASE_NAME = "books";
private static final String DATABASE_TABLE = "titles";
private static final int DATABASE_VERSION = 1;

private static final String DATABASE_CREATE =
"create table titles (_id integer primary key autoincrement, "
+ "isbn text not null, title text not null, "
+ "publisher text not null);";

private final Context context;

private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DBAdapter(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(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 oldVersion,
int newVersion)
{
Log.w(TAG, "Upgrading database from version " + oldVersion
+ " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS titles");
onCreate(db);
}
}



//---opens the database---
public DBAdapter open() throws SQLException
{
db = DBHelper.getWritableDatabase();
return this;
}

//---closes the database---
public void close()
{
DBHelper.close();
}

//---insert a row into the database---
public long insertTitle(String isbn, String title, String publisher)
{
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_ISBN, isbn);
initialValues.put(KEY_TITLE, title);
initialValues.put(KEY_PUBLISHER, publisher);
return db.insert(DATABASE_TABLE, null, initialValues);
}

//---deletes a particular title---
public boolean deleteTitle(long rowId)
{
return db.delete(DATABASE_TABLE, KEY_ROWID +
"=" + rowId, null) > 0;
}

//---retrieves all the titles---
public Cursor getAllTitles()
{
return db.query(DATABASE_TABLE, new String[] {
KEY_ROWID,
KEY_ISBN,
KEY_TITLE,
KEY_PUBLISHER},
null,
null,
null,
null,
null);
}

//---retrieves a particular title---
public Cursor getTitle(long rowId) throws SQLException
{
Cursor mCursor =
db.query(true, DATABASE_TABLE, new String[] {
KEY_ROWID,
KEY_ISBN,
KEY_TITLE,
KEY_PUBLISHER
},
KEY_ROWID + "=" + rowId,
null,
null,
null,
null,
null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}

//---updates a title---
public boolean updateTitle(long rowId, String isbn,
String title, String publisher)
{
ContentValues args = new ContentValues();
args.put(KEY_ISBN, isbn);
args.put(KEY_TITLE, title);
args.put(KEY_PUBLISHER, publisher);
return db.update(DATABASE_TABLE, args,
KEY_ROWID + "=" + rowId, null) > 0;
}


}


Note:
So far we have develop a class named DBAdapter, its just provide basic help. Now we are going to develop orginal Activity named DatabaseActivity.

Step 5:
First go to main.xml file (res/Layout). Design a LinearLayout having One TextView and four Buttons. Their naming and layout specification like below:


Note: Please Ignore the Starting and Ending Inverted Comas in coding of button in below example

android:orientation="vertical" android:layout_width="fill_parent"
android:layout_height="fill_parent">

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

< Button android:layout_height="wrap_content" android:id="@+id/btnInsertRows"
android:layout_width="fill_parent" android:text="Insert Two Rows">

< Button android:layout_height="wrap_content" android:text="Displaying All Column"
android:id="@+id/btnSelectAllColumn" android:layout_width="fill_parent">

< Button android:layout_height="wrap_content" android:id="@+id/btnSelectColumn"
android:text="Select Single Column" android:layout_width="fill_parent">

< Button android:layout_height="wrap_content" android:id="@+id/btnClose"
android:text="Close Connection and Finish Activity"
android:layout_width="fill_parent">




Step 6:
In this step we create an instance of DBAdapter which will create a database when constructor calls. In this step we also declare the references of Buttons declare in main.xml file
These buttons perform following functions.

Button:name: Insert Two record using method db.insertTitle()

Button2:name: It display all the record using method db.getAllTitles() in a loop

Button3:name: it display a single row using method db.getTitle(), by providing towID

Button4:name: It will close the connection or reference of Database Object

The code will be look like below:

package com.code;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;

public class DatabaseActivity extends Activity {
Button btnInsert;
Button btnSelectAllColumn;
Button btnSelectOneColumn;
Button btnClose;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
final DBAdapter db = new DBAdapter(this);
db.open();
btnInsert = (Button) findViewById(R.id.btnInsertRows);
btnSelectAllColumn = (Button) findViewById(R.id.btnSelectAllColumn);
btnSelectOneColumn = (Button) findViewById(R.id.btnSelectColumn);
btnClose = (Button) findViewById(R.id.btnClose);

}
}


Step 7:
Now we declare the click listener for each button and perform action as mention in step 6. we use two more custom methods i.e. DisplayTitle() and DisplayError();

DisplayTitle(): the method receive a Cursor object, and displays row data using Toast.
DisplayError(): When no row found then its display a error message using Toast.

Over All code of DatabaseActivity would be like below:


package com.code;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;

public class DatabaseActivity extends Activity {
Button btnInsert;
Button btnSelectAllColumn;
Button btnSelectOneColumn;
Button btnClose;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
final DBAdapter db = new DBAdapter(this);
db.open();
btnInsert = (Button) findViewById(R.id.btnInsertRows);
btnSelectAllColumn = (Button) findViewById(R.id.btnSelectAllColumn);
btnSelectOneColumn = (Button) findViewById(R.id.btnSelectColumn);
btnClose = (Button) findViewById(R.id.btnClose);

btnInsert.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
long id;
id = db.insertTitle(
"0470285818",
"C# 2008 Programmer's Reference",
"Wrox");
id = db.insertTitle(
"047017661X",
"Professional Windows Vista Gadgets Programming",
"Wrox");
}
});
btnSelectAllColumn.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
Cursor c = db.getAllTitles();
if (c.moveToFirst())
{
do {
DisplayTitle(c);
} while (c.moveToNext());
}

}
});
btnSelectOneColumn.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
Cursor c = db.getTitle(2);
if (c.moveToFirst())
DisplayTitle(c);
else
DisplayError();

}
});
btnClose.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
db.close();
finish();
}
});
}
public void DisplayTitle(Cursor c)
{
Toast.makeText(this,
"id: " + c.getString(0) + "\n" +
"ISBN: " + c.getString(1) + "\n" +
"TITLE: " + c.getString(2) + "\n" +
"PUBLISHER: " + c.getString(3),
Toast.LENGTH_LONG).show();
}
public void DisplayError()
{
Toast.makeText(this, "No title found",
Toast.LENGTH_LONG).show();
}

}