Thursday, 11 December 2014

Database CRUD Operation in Android Application Development

SQliteDataBaseActivity.java

package com.rakesh.tiwari.sqlitedatabase;

import java.util.ArrayList;
import java.util.HashMap;

import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class SQliteDataBaseActivity extends Activity {
    Contact contact;
    ListView listView;

    ArrayList<HashMap<String, String>> contactList;
    AyArrayAdapter ayArrayAdapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        contactList = new ArrayList<HashMap<String, String>>();

        final Button btnInsert = (Button) findViewById(R.id.btn_insert);
        final Button btnSelect = (Button) findViewById(R.id.btn_select);
        final Button btnSelectall = (Button) findViewById(R.id.btn_selectall);
        final Button btnUpdate = (Button) findViewById(R.id.btn_update);
        final Button btnDelete = (Button) findViewById(R.id.btn_delete);

        final EditText edtId = (EditText) findViewById(R.id.editTextId);
        final EditText edtName = (EditText) findViewById(R.id.editTextName);
        final EditText edtEmail = (EditText) findViewById(R.id.editTextEmail);

        final DBAdapter dba = new DBAdapter(this);

        // Insert
        // Contact------------------------------------------------------------------------

        btnInsert.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                dba.open();
                contact = new Contact();
                contact.setName(edtName.getText().toString());
                contact.setAddress(edtEmail.getText().toString());
                dba.insertContact(contact);
                dba.close();
                edtName.setText("");
                edtEmail.setText("");
                Toast.makeText(getApplicationContext(), "Inserted",
                        Toast.LENGTH_LONG).show();
                Intent i = new Intent(SQliteDataBaseActivity.this,
                        SQliteDataBaseActivity.class);
                startActivity(i);
            }
        });
        // Select all
        // contacts----------------------------------------------------------------------------

        btnSelectall.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                getData();
                /*
                 * dba.open(); Cursor c=dba.getAllContacts();
                 * if(c.moveToFirst()){ do{ DisplayContact(c);
                 * }while(c.moveToNext()); } dba.close();
                 */
            }

            /*
             * private void DisplayContact(Cursor c){
             * Toast.makeText(getApplicationContext(),
             * "id: "+c.getString(0)+"\n"+"Name: "+c.getString(1)+"\n"+
             * "Email: "+c.getString(2), Toast.LENGTH_LONG).show();
             *
             * }
             */

            /*
             * private void DisplayContact(Cursor c){
             * Toast.makeText(getApplicationContext(),
             * "Name: "+c.getString(0)+"\n"+ "Email: "+c.getString(1),
             * Toast.LENGTH_LONG).show();
             *
             * }
             */
        });

        // Select a
        // contact----------------------------------------------------------------------

        btnSelect.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                dba.open();
                Cursor c = dba.getContact(Integer.parseInt(edtId.getText()
                        .toString()));
                if (c.moveToFirst()) {
                    DisplayContact(c);
                } else {
                    Toast.makeText(getApplicationContext(), "No contact found",
                            Toast.LENGTH_LONG).show();
                }
                dba.close();

            }

            private void DisplayContact(Cursor c) {
                Toast.makeText(
                        getApplicationContext(),
                        "id: " + c.getString(0) + "\n" + "Name: "
                                + c.getString(1) + "\n" + "Email: "
                                + c.getString(2), Toast.LENGTH_LONG).show();
            }
        });

        // Update a
        // contact----------------------------------------------------------------------

        btnUpdate.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                dba.open();
                if (dba.updateContact(Integer.parseInt(edtId.getText()
                        .toString()), edtName.getText().toString(), edtEmail
                        .getText().toString()))
                    Toast.makeText(getApplicationContext(),
                            "Update successful", Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(getApplicationContext(), "Update failed",
                            Toast.LENGTH_LONG).show();
                dba.close();
                Intent i = new Intent(SQliteDataBaseActivity.this,
                        SQliteDataBaseActivity.class);
                startActivity(i);
            }
        });

        // Delete
        // contact------------------------------------------------------------------------------

        btnDelete.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                dba.open();
                dba.deleteContact(Integer.parseInt(edtId.getText().toString()));
                dba.close();
            }
        });
    }

    public void getData() {

        DBAdapter dbAdapter = new DBAdapter(SQliteDataBaseActivity.this);
        dbAdapter.open();

        Cursor c = dbAdapter.getAllContacts();
        c.moveToFirst();

        while (!c.isAfterLast()) {
            HashMap<String, String> map = new HashMap<String, String>();

            map.put("name", c.getString(c.getColumnIndex(DBAdapter.KEY_NAME)));
            map.put("address",
                    c.getString(c.getColumnIndex(DBAdapter.KEY_EMAIL)));

            c.moveToNext();

            contactList.add(map);
        }

        c.close();
        dbAdapter.close();

        listView = (ListView) findViewById(R.id.Listview);
        ayArrayAdapter = new AyArrayAdapter(SQliteDataBaseActivity.this,
                R.layout.name_list, contactList);
        listView.setAdapter(ayArrayAdapter);
    }

    public class AyArrayAdapter extends ArrayAdapter<HashMap<String, String>> {
        public AyArrayAdapter(Context handler, int textViewResourceId,
                ArrayList<HashMap<String, String>> contactList) {
            super(handler, textViewResourceId, contactList);
        }

        public View getView(final int position, View convertView,
                ViewGroup parent) {

            LayoutInflater inflater = (LayoutInflater) getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            View rowView = inflater.inflate(R.layout.name_list, null);

            TextView name = (TextView) rowView.findViewById(R.id.textViewname);
            name.setText(contactList.get(position).get("name"));

            TextView modelenumber = (TextView) rowView
                    .findViewById(R.id.textViewaddresas);
            modelenumber.setText(contactList.get(position).get("address"));

            return rowView;
        }
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.sqlite_data_base, menu);
        return true;
    }

}


Contact.java

package com.rakesh.tiwari.sqlitedatabase;

import java.io.Serializable;

@SuppressWarnings("serial")
public class Contact implements Serializable {
    private String name, address;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

}

DBAdapter.java

package com.rakesh.tiwari.sqlitedatabase;

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_NAME = "name";
    public static final String KEY_EMAIL = "email";
    private static final String TAG = "DBAdapter";
    private static final String DATABASE_NAME = "myDB";
    private static final String DATABASE_TABLE = "contacts";
    private static final int DATABASE_VERSION = 1;
    /*public static final String DATABASE_CREATE = "create table contacts(id integer primary key autoincrement,"
            + "" + " name text not null,email text not null);";*/
    private static final String DATABASE_CREATE = "create table contacts(id integer primary key autoincrement, name text not null, email text not null);";

    private final Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    // Create construstor...............

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

    }

    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            // TODO Auto-generated constructor stub
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            try {
                db.execSQL(DATABASE_CREATE);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            Log.w(TAG, "Upgrading Database from version" + oldVersion + "to"
                    + newVersion + ",which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS contacts");
            onCreate(db);
        }

    }

    // --- open the database----------------

    public DBAdapter open() throws SQLException {

        db = DBHelper.getWritableDatabase();
        return this;

    }

    // --- close database---------------------------------------------------

    public void close() {
        DBHelper.close();
    }

    // ----- Insert a contact into the
    // database---------------------------------------
    public long insertContact(Contact contact) {

        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, contact.getName());
        initialValues.put(KEY_EMAIL, contact.getAddress());
        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    // Delete a particular
    // contact--------------------------------------------------------------

    public boolean deleteContact(long rowId) {

        return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    // Retrieve all
    // contacts.....................................................................

    public Cursor getAllContacts() {
        /*return db.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_NAME ,KEY_EMAIL
                }, null, null, null, null, null);*/
        return db.query(DATABASE_TABLE, new String[] { KEY_NAME ,KEY_EMAIL
        }, null, null, null, null, null);


    }

    // retrieve a particular
    // contact.........................................................................

    public Cursor getContact(long rowId) throws SQLException {

        Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] {
                KEY_ROWID, KEY_NAME, KEY_EMAIL }, KEY_ROWID + "=" + rowId,
                null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();

        }
        return mCursor;
    }

    // Update a
    // contact-----------------------------------------------------------------------------------

    public boolean updateContact(long rowID, String name, String email) {
        ContentValues cValue = new ContentValues();
        cValue.put(KEY_NAME, name);
        cValue.put(KEY_EMAIL, email);
        return db.update(DATABASE_TABLE, cValue, KEY_ROWID + "=+rowId", null) > 0;

    }

}

main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".SQliteDataBaseActivity" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="20dp"
        android:text="SQLite DataBase"
        android:textColor="#336633"
        android:textSize="20dp"
        android:textStyle="bold" />

    <EditText
        android:id="@+id/editTextId"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/textView1"
        android:layout_marginTop="10dp"
       android:inputType="number"
       android:hint="ID"
        android:ems="5" >

        <requestFocus />
    </EditText>

    <EditText
        android:id="@+id/editTextName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentRight="true"
        android:layout_below="@+id/textView1"
        android:layout_marginTop="10dp"
        android:inputType="textPersonName"
        android:hint="Name"
        android:ems="5" />

    <EditText
        android:id="@+id/editTextEmail"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/textView1"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="60dp"
        android:inputType="textEmailAddress"
        android:hint="Email"
        android:ems="8" />

    <Button
        android:id="@+id/btn_insert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/editTextEmail"
        android:layout_marginTop="22dp"
        android:text="Insert" />
    <ListView
        android:id="@+id/Listview"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/btn_insert"
        >
    </ListView>

    <Button
        android:id="@+id/btn_select"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/btn_insert"
        android:layout_alignBottom="@+id/btn_insert"
        android:layout_alignLeft="@+id/editTextName"
        android:text="Select" />

    <Button
        android:id="@+id/btn_selectall"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignRight="@+id/btn_insert"
        android:layout_below="@+id/btn_insert"
        android:layout_marginTop="58dp"
        android:textSize="15sp"
        android:text="Select All" />
  

    <Button
        android:id="@+id/btn_update"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/btn_selectall"
        android:layout_alignLeft="@+id/btn_select"
        android:text="Update" />

    <Button
        android:id="@+id/btn_delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/btn_update"
        android:layout_marginTop="34dp"
        android:layout_toLeftOf="@+id/btn_update"
        android:text="Delete" />

</RelativeLayout>

name_list.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/textViewname"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="" />

    <TextView
        android:id="@+id/textViewaddresas"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="3dp"
        android:text="" />

</LinearLayout>