CRUD operation in Android


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.

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

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

}
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.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" >

        <requestFocus />
    </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>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity android:name="ShowUser"
            android:label="User Details"          
         ></activity>
         <activity android:name="EditUser"
            android:label="Edit Details"          
         ></activity>
    </application>

</manifest>





When clicked on Save User




When clicked on Show User



When clicked on manoj



When clicked on Update


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.

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. can u provide me with the download link of the source code of this project

      Delete
  2. Can you please mail this working example to my id i.e anupkumar842@gmail.com

    I need it very urgently .

    Thanks in advance

    ReplyDelete