Java Guru: 2012

Android Database Connectivity

My Dear Friends,

It is million dollar question, how to perform CRUD operation in Android that means how to persist data in database, how to fetch data from database, and how to update or delete a specific record of database. This blog is solution of this problem and also helpful for creating dialog box in Android.

For Android, SQLite is "baked into" the Android runtime, so every Android application can create SQLite databases.

Android provides its own API to deal with database connectivity. This API is provided in android.database and android.database.sqlite packages. In this package important classes are

1. SQLiteOpenHelper

2. SQLiteDatabase

3. Cursor

4. SQLException

SQLiteOpenHelper class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. It provides a constructor to construct a helper class by subclassing this class and implementing two methods onCreate() an onUpgrade().

Constructor:

SQLiteOpenHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

Commonly used methods:

onCreate (SQLiteDatabase db)

Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen.

onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks. For that purpose it provides several methods

Commonly used methods:

execSQL (String sql)

Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data. It is used generally for creating table.

SQLiteDatabase getWritableDatabase ()

Create and/or open a database that will be used for reading and writing

long insert (String table, String nullColumnHack, ContentValues values)

Convenience method for inserting a row into the database. Returns the row ID of the newly inserted row, or -1 if an error occurred

int update (String table, ContentValues values, String whereClause, String[] whereArgs)

Convenience method for updating rows in the database. Returns the number of rows affected

int delete (String table, String whereClause, String[] whereArgs)

Convenience method for deleting rows in the database. Returns the number of rows affected if a whereClause is passed in, 0 otherwise

Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

Query the given table, returning a Cursor over the result set.

Cursor

This interface provides random read-write access to the result set returned by a database query

Commonly used methods:

boolean moveToNext ()

Move the cursor to the next row. This method will return false if the cursor is already past the last entry in the result set.

getXXX()

Android provide getXXX() Style of methods to get XXX type of values. Likes

getString()

getLong()

getInt()

getFloat()

getDouble()

String getString (int columnIndex)

Returns the value of the requested column as a String.

long getLong (int columnIndex)

Returns the value of the requested column as a long.

SQLException

An exception that indicates there was an error with SQL parsing or execution.

An another class which is used during database transaction is ContentValues. This class is used to store a set of values that the ContentResolver can process.

Now, step by step Example

Step 1: Create a common helper class

package org.nk.db;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

public class CommonDB extends SQLiteOpenHelper{

private static String dbname="users_db.db";

private static int version=1;

private static final String CREATE_TABLE = "create table users (uname char primary key not null, mobno long not null);";

public CommonDB(Context context) {

super(context, dbname, null, version);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

Log.w(CommonDB.class.getName(),"Upgrading database from version which will destroy all old data");

db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE);

onCreate(db);

}

Step 2: Create UserDBto perform CRUD operation for User

package org.nk.db;

import java.util.ArrayList;

import java.util.List;

import org.nk.beans.User;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

public class UserDB {

SQLiteDatabase db=null;

CommonDB cdb=null;

String names[]={"uname"};

String mob[]={"mobno"};

public UserDB(Context context) {

cdb=new CommonDB(context);

}

public void open() throws SQLException

{

db=cdb.getWritableDatabase();

}

public void close(){

db.close();

}

public boolean saveUser(User user) {

ContentValues values = new ContentValues();

values.put("uname", user.getName());

values.put("mobno", user.getMobno());

long id = db.insert("users", null, values);

if (id > 0) {

return true;

} else {

return false;

}

public boolean updateUser(User user) {

ContentValues values = new ContentValues();

String uname[]={user.getName()};

values.put("mobno", user.getMobno());

long id = db.update("users", values,"uname=?",uname);

if (id > 0) {

return true;

} else {

return false;

}

public List<String> getNames(){

List<String> users=new ArrayList<String>();

Cursor c=db.query("users",names, null, null, null, null, null);

while(c.moveToNext()){

String n=c.getString(0);

users.add(n);

}

return users;

}

public long getMobs(String name){

long n=0;

String uname[]={name};

Cursor c=db.query("users",mob, "uname=?",uname, null, null, null);

while(c.moveToNext()){

n=c.getLong(0);

}

return n;

}

public void deleteUser(String name){

String uname[]={name};

db.delete("users","uname=?", uname);

}

Step 3: User bean

package org.nk.beans;

public class User {

String name;

long mobno;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public long getMobno() {

return mobno;

}

public void setMobno(long mobno) {

this.mobno = mobno;

}

Step 4: Activityclasses

a. UserDetailsActivity.java

package org.nk;

import org.nk.beans.User;

import org.nk.db.UserDB;

import android.app.Activity;

import android.app.AlertDialog;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

import android.widget.Button;

import android.widget.EditText;

public class UserDetailsActivity extends Activity {

EditText name,mobno;

Button btnSave,btnShow;

UserDB udb=null;

String saveAlertMessage=null;

Intent showUser;

@Override

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.main);

udb=new UserDB(this);

showUser=new Intent(this,ShowUser.class);

name=(EditText) findViewById(R.id.name);

mobno=(EditText) findViewById(R.id.mobno);

btnSave=(Button) findViewById(R.id.btnSave);

btnShow=(Button) findViewById(R.id.btnShow);

btnSave.setOnClickListener(onSave);

btnShow.setOnClickListener(onShow);

}

private View.OnClickListener onShow=new View.OnClickListener() {

@Override

public void onClick(View v) {

startActivity(showUser);

}

};

private View.OnClickListener onSave = new View.OnClickListener() {

public void onClick(View arg0) {

try{

String n=name.getText().toString();

long mob=Long.parseLong(mobno.getText().toString());

User u=new User();

u.setName(n);

u.setMobno(mob);

udb.open();

boolean flag=udb.saveUser(u);

if(flag==true){

saveAlertMessage="SuccessFully Added";

alertMessage(saveAlertMessage);

}else{

saveAlertMessage="Problem in Insertion";

alertMessage(saveAlertMessage);

}

}catch(Exception e){

e.printStackTrace();

alertMessage("Please provide correct info"); }

finally{

udb.close();

}

};

public void alertMessage(String msg){

AlertDialog.Builder alertBuilder = new AlertDialog.Builder(this);

alertBuilder.setTitle("Message Box");

alertBuilder.setMessage(msg);

alertBuilder.setCancelable(false);

alertBuilder.setPositiveButton("Ok",null);

alertBuilder.show();

}

b. ShowUser.java

package org.nk;

import java.util.ArrayList;

import java.util.List;

import org.nk.db.UserDB;

import android.app.ListActivity;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

import android.widget.ArrayAdapter;

import android.widget.ListView;

public class ShowUser extends ListActivity{

UserDB udb;

List<String> list;

ArrayAdapter<String> ad;

Intent showEdit;

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

showEdit=new Intent(this,EditUser.class);

udb=new UserDB(this);

udb.open();

list= udb.getNames();

ad=new ArrayAdapter<String>(this,

android.R.layout.simple_list_item_1, list);

setListAdapter(ad);

udb.close();

}

protected void onListItemClick(ListView l, View v, int position, long id) {

String s = (String) getListAdapter().getItem(position);

showEdit.putExtra("org.nk.name",s);

startActivity(showEdit);

}

c. EditUser.java

package org.nk;

import org.nk.beans.User;

import org.nk.db.UserDB;

import android.app.Activity;

import android.app.AlertDialog;

import android.content.DialogInterface;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

import android.widget.Button;

import android.widget.EditText;

import android.widget.TextView;

public class EditUser extends Activity{

UserDB udb;

Button btnUpdate,btnDelete, btnShow;

long val=0;

String na;

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.edit);

udb=new UserDB(this);

final Intent showEdit = getIntent();

na=showEdit.getStringExtra("org.nk.name");

TextView n=(TextView) findViewById(R.id.name);

btnUpdate=(Button) findViewById(R.id.btnUpdate);

btnDelete=(Button) findViewById(R.id.btnDelete);

btnShow=(Button)findViewById(R.id.btnShow);

n.setText(na.toUpperCase());

btnUpdate.setOnClickListener(onUpdate);

btnDelete.setOnClickListener(onDelete);

btnShow.setOnClickListener(onShow);

}

View.OnClickListener onShow=new View.OnClickListener() {

@Override

public void onClick(View v) {

udb.open();

long mob=udb.getMobs(na);

udb.close();

alertMessage("Mob No.:"+mob);

}

};

View.OnClickListener onDelete=new View.OnClickListener() {

@Override

public void onClick(View v) {

try{

udb.open();

udb.deleteUser(na);

udb.close();

alertMessage(na+" deleted successfully");

}catch(Exception e){

e.printStackTrace();

alertMessage("Problem in deletion");

}

};

View.OnClickListener onUpdate=new View.OnClickListener() {

@Override

public void onClick(View v) {

updateDialog();

}

};

public void updateDialog(){

AlertDialog.Builder alertBuilder = new AlertDialog.Builder(this);

alertBuilder.setTitle("Update Dialog");

alertBuilder.setMessage("Enter mobile no");

final EditText input = new EditText(this);

alertBuilder.setCancelable(false);

alertBuilder.setView(input);

alertBuilder.setPositiveButton("Update", new DialogInterface.OnClickListener(){

public void onClick(DialogInterface arg0, int arg1) {

val=Long.parseLong(input.getText().toString());

try{

udb.open();

User u=new User();

u.setName(na);

u.setMobno(val);

udb.updateUser(u);

alertMessage("Updated Successfully");

}catch(Exception e){

e.printStackTrace();

alertMessage("Problem in Updation");

}

finally{

udb.close();

}

});

alertBuilder.setNegativeButton("Cancel", null);

alertBuilder.show();

}

public void alertMessage(String msg){

AlertDialog.Builder alertBuilder = new AlertDialog.Builder(this);

alertBuilder.setTitle("Message Box");

alertBuilder.setMessage(msg);

alertBuilder.setCancelable(false);

alertBuilder.setPositiveButton("Ok",null);

alertBuilder.show();

}

5. Layout File

main.xml

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:orientation="vertical" >

<TextView

android:id="@+id/textView1"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Name"

android:textAppearance="?android:attr/textAppearanceMedium" />

<EditText

android:id="@+id/name"

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:ems="10" >

</EditText>

<TextView

android:id="@+id/textView2"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Mob No."

android:textAppearance="?android:attr/textAppearanceMedium" />

<EditText

android:id="@+id/mobno"

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:ems="10" />

<Button

android:id="@+id/btnSave"

style="?android:attr/buttonStyleSmall"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:layout_gravity="center_vertical"

android:text="Save User" />

<Button

android:id="@+id/btnShow"

style="?android:attr/buttonStyleSmall"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Show User" />

</LinearLayout>

edit.xml

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

android:layout_width="match_parent"

android:layout_height="match_parent"

android:orientation="vertical" >

<TextView

android:id="@+id/name"

android:layout_width="276dp"

android:layout_height="wrap_content"

android:text="Medium Text"

android:textAppearance="?android:attr/textAppearanceMedium" />

<Button

android:id="@+id/btnUpdate"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Update" />

<Button

android:id="@+id/btnDelete"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Delete " />

<Button

android:id="@+id/btnShow"

style="?android:attr/buttonStyleSmall"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Show" />

</LinearLayout>

6. AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>

<manifest xmlns:android="http://schemas.android.com/apk/res/android"

package="org.nk"

android:versionCode="1"

android:versionName="1.0" >

<uses-sdk android:minSdkVersion="15" />

<application

android:icon="@drawable/ic_launcher"

android:label="@string/app_name" >

<activity

android:name=".UserDetailsActivity"

android:label="@string/app_name" >

<intent-filter>

</intent-filter>

</activity>

<activity android:name="ShowUser"

android:label="User Details"

></activity>

<activity android:name="EditUser"

android:label="Edit Details"

></activity>

</application>

</manifest>

OutputTrace

When clicked on Save User

When clicked on Show User

When clicked on manoj

When clicked on Update

When clicked on Delete

When clicked on Show

Transactions

SQLite also supports transactions when you need to perform a series of queries that either all complete or all fail. When a SQLite transaction fails an exception will be thrown. The transaction methods are all part of the database object. Start a transaction by calling the beginTransaction() method. Perform the queries and then call the setTransactionSuccessful() when you wish to commit the transaction. Once the transaction is complete call the endTransaction() function.

         db.beginTransaction();

         Cursor cur = null;

         try {

               cur = db.query("tbl_countries",

                       null, null, null, null, null, null);

               cur.moveToPosition(0);

               ContentValues values = new ContentValues();

               values.put("state_name", "bihar");

               values.put("country_id", cur.getString(0));

               long stateId = db.insert("tbl_states", null, values);

               db.setTransactionSuccessful();

               view.append("n" + Long.toString(stateId));

         } catch (Exception e) {

               Log.e("Error in transaction", e.toString());

         } finally {

               db.endTransaction();

               cur.close();

Start off with a call to beginTransaction() to tell SQLite to perform the queries in transaction mode. Initiate a try/catch block to handle exceptions thrown by a transaction failure. Perform the queries and then call setTransactionSuccessful() to tell SQLite that our transaction is complete. If an error isn't thrown then endTransaction() can be called to commit the transaction. Finally close the cursor when we're finished with it.
This topic is described latterly in details.