Saturday 7 September 2013

Android-Populate ListView from SQLite Database (Insert, Update,Delete)

Hello Everyone.. I am going to post this after long time. I created this tutorial before 2 month but today I got time to post here.

Today in this tutorial I will show you:

  1. Create table in SQLite
  2. Insert data into SQLite
  3. Update data into SQLite
  4. Delete data from SQLite
What to do ?
  • In this project I want to create a table in SQLite database. And store first name and last name for a person.
  • Then I can edit/update them by clicking on it.
  • Also, I can delete a person by long press on it.
How to do ?
  • Create one SQLiteOpenHelper class to create table in database.
  •  Create two Activities, one for adding data and second for displaying list of data
  • Create BaseAdapter to bind data in listview
  • Some xml files for layouts
What is Result ?

At the end of this tutorial we will get output as shown in following screenshots.
In this project we can add a person's first name and last name, edit it and delete it.

Special Thanks

Before moving towards coding I want to Thanks to Hiral Kanojiya. She wrote most of the code for this tutorial, I just guide her and then polished the code. Really without her help I could never get time to post this.

Thank You very much Hiral Kanojiya.

Screenshots










//Create table into SQLite Database
DbHelper.java

package com.hk.sqlitedemo;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {
    static String DATABASE_NAME="userdata";
    public static final String TABLE_NAME="user";
    public static final String KEY_FNAME="fname";
    public static final String KEY_LNAME="lname";
    public static final String KEY_ID="id";
    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
       
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE="CREATE TABLE "+TABLE_NAME+" ("+KEY_ID+" INTEGER PRIMARY KEY, "+KEY_FNAME+" TEXT, "+KEY_LNAME+" TEXT)";
        db.execSQL(CREATE_TABLE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
        onCreate(db);

    }

}


//activity to add recored
AddActivity.java

package com.hk.sqlitedemo;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.DialogInterface;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class AddActivity extends Activity implements OnClickListener {
private Button btn_save;
private EditText edit_first,edit_last;
private DbHelper mHelper;
private SQLiteDatabase dataBase;
private String id,fname,lname;
private boolean isUpdate;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.add_activity);
       
        btn_save=(Button)findViewById(R.id.save_btn);
        edit_first=(EditText)findViewById(R.id.frst_editTxt);
        edit_last=(EditText)findViewById(R.id.last_editTxt);
        
       isUpdate=getIntent().getExtras().getBoolean("update");
        if(isUpdate)
        {
            id=getIntent().getExtras().getString("ID");
            fname=getIntent().getExtras().getString("Fname");
            lname=getIntent().getExtras().getString("Lname");
            edit_first.setText(fname);
            edit_last.setText(lname);
           
        }
        
         btn_save.setOnClickListener(this);
        
         mHelper=new DbHelper(this);
       
    }

    // saveButton click event
    public void onClick(View v) {
        fname=edit_first.getText().toString().trim();
        lname=edit_last.getText().toString().trim();
        if(fname.length()>0 && lname.length()>0)
        {
            saveData();
        }
        else
        {
            AlertDialog.Builder alertBuilder=new AlertDialog.Builder(AddActivity.this);
            alertBuilder.setTitle("Invalid Data");
            alertBuilder.setMessage("Please, Enter valid data");
            alertBuilder.setPositiveButton("Ok", new DialogInterface.OnClickListener() {
               
                public void onClick(DialogInterface dialog, int which) {
                       dialog.cancel();
                   
                }
            });
            alertBuilder.create().show();
        }
       
    }

    /**
     * save data into SQLite
     */
    private void saveData(){
        dataBase=mHelper.getWritableDatabase();
        ContentValues values=new ContentValues();
       
        values.put(DbHelper.KEY_FNAME,fname);
        values.put(DbHelper.KEY_LNAME,lname );
       
        System.out.println("");
        if(isUpdate)
        {   
            //update database with new data
            dataBase.update(DbHelper.TABLE_NAME, values, DbHelper.KEY_ID+"="+id, null);
        }
        else
        {
            //insert data into database
            dataBase.insert(DbHelper.TABLE_NAME, null, values);
        }
        //close database
        dataBase.close();
        finish();
       
       
    }

}

//activity to display record in list
DisplayActivity.java

package com.hk.sqlitedemo;

import java.util.ArrayList;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.AdapterView.OnItemLongClickListener;
import android.widget.ListView;
import android.widget.Toast;

public class DisplayActivity extends Activity {

    private DbHelper mHelper;
    private SQLiteDatabase dataBase;

    private ArrayList<String> userId = new ArrayList<String>();
    private ArrayList<String> user_fName = new ArrayList<String>();
    private ArrayList<String> user_lName = new ArrayList<String>();

    private ListView userList;
    private AlertDialog.Builder build;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.display_activity);

        userList = (ListView) findViewById(R.id.List);

        mHelper = new DbHelper(this);
       
        //add new record
        findViewById(R.id.btnAdd).setOnClickListener(new OnClickListener() {

            public void onClick(View v) {

                Intent i = new Intent(getApplicationContext(), AddActivity.class);
                i.putExtra("update", false);
                startActivity(i);

            }
        });
       
        //click to update data
        userList.setOnItemClickListener(new OnItemClickListener() {

            public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {

                Intent i = new Intent(getApplicationContext(), AddActivity.class);
                i.putExtra("Fname", user_fName.get(arg2));
                i.putExtra("Lname", user_lName.get(arg2));
                i.putExtra("ID", userId.get(arg2));
                i.putExtra("update", true);
                startActivity(i);

            }
        });
       
        //long click to delete data
        userList.setOnItemLongClickListener(new OnItemLongClickListener() {

            public boolean onItemLongClick(AdapterView<?> arg0, View arg1, final int arg2, long arg3) {

                build = new AlertDialog.Builder(DisplayActivity.this);
                build.setTitle("Delete " + user_fName.get(arg2) + " " + user_lName.get(arg2));
                build.setMessage("Do you want to delete ?");
                build.setPositiveButton("Yes",new DialogInterface.OnClickListener() {

                            public void onClick(DialogInterface dialog, int which) {

                                Toast.makeText( getApplicationContext(),
                                        user_fName.get(arg2) + " "
                                                + user_lName.get(arg2)
                                                + " is deleted.", 3000).show();

                                dataBase.delete(
                                        DbHelper.TABLE_NAME,
                                        DbHelper.KEY_ID + "="
                                                + userId.get(arg2), null);
                                displayData();
                                dialog.cancel();
                            }
                        });

                build.setNegativeButton("No", new DialogInterface.OnClickListener() {

                            public void onClick(DialogInterface dialog, int which) {
                                    dialog.cancel();
                            }
                        });
                AlertDialog alert = build.create();
                alert.show();

                return true;
            }
        });
    }

    @Override
    protected void onResume() {
        displayData();
        super.onResume();
    }

    /**
     * displays data from SQLite
     */
    private void displayData() {
        dataBase = mHelper.getWritableDatabase();
        Cursor mCursor = dataBase.rawQuery("SELECT * FROM " + DbHelper.TABLE_NAME, null);

        userId.clear();
        user_fName.clear();
        user_lName.clear();
        if (mCursor.moveToFirst()) {
            do {
                userId.add(mCursor.getString(mCursor.getColumnIndex(DbHelper.KEY_ID)));
                user_fName.add(mCursor.getString(mCursor.getColumnIndex(DbHelper.KEY_FNAME)));
                user_lName.add(mCursor.getString(mCursor.getColumnIndex(DbHelper.KEY_LNAME)));

            } while (mCursor.moveToNext());
        }
        DisplayAdapter disadpt = new DisplayAdapter(DisplayActivity.this,userId, user_fName, user_lName);
        userList.setAdapter(disadpt);
        mCursor.close();
    }

   

}

//adapter to load data into list
DisplayAdapter.java

package com.hk.sqlitedemo;

import java.util.ArrayList;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;

public class DisplayAdapter extends BaseAdapter {
    private Context mContext;
    private ArrayList<String> id;
    private ArrayList<String> firstName;
    private ArrayList<String> lastName;
   

    public DisplayAdapter(Context c, ArrayList<String> id,ArrayList<String> fname, ArrayList<String> lname) {
        this.mContext = c;

        this.id = id;
        this.firstName = fname;
        this.lastName = lname;
    }

    public int getCount() {
        // TODO Auto-generated method stub
        return id.size();
    }

    public Object getItem(int position) {
        // TODO Auto-generated method stub
        return null;
    }

    public long getItemId(int position) {
        // TODO Auto-generated method stub
        return 0;
    }

    public View getView(int pos, View child, ViewGroup parent) {
        Holder mHolder;
        LayoutInflater layoutInflater;
        if (child == null) {
            layoutInflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            child = layoutInflater.inflate(R.layout.listcell, null);
            mHolder = new Holder();
            mHolder.txt_id = (TextView) child.findViewById(R.id.txt_id);
            mHolder.txt_fName = (TextView) child.findViewById(R.id.txt_fName);
            mHolder.txt_lName = (TextView) child.findViewById(R.id.txt_lName);
            child.setTag(mHolder);
        } else {
            mHolder = (Holder) child.getTag();
        }
        mHolder.txt_id.setText(id.get(pos));
        mHolder.txt_fName.setText(firstName.get(pos));
        mHolder.txt_lName.setText(lastName.get(pos));

        return child;
    }

    public class Holder {
        TextView txt_id;
        TextView txt_fName;
        TextView txt_lName;
    }

}

You can download source code from Here

97 comments:

  1. The above program is not working for more than 2 attributes.
    It is leading to application failure.So please give your email id so that i can share my code with you.

    ReplyDelete
    Replies
    1. You can share your code on google drive with me. Here is my email ktnbh281991@gmail.com

      Delete
    2. Please share your code with me. :) here is my email trij.estrelles@gmail.com. Thanks!

      Delete
    3. me please! i badly need this one. it'll be a great help for me. super thanks. jen24doringo@gmail.com

      Delete
    4. can you share your code with me too ? here is my email janinegenodipa@gmail.com , thank you

      Delete
    5. please share your code with me..i really need it and thanks. Here is my email Akimichi_scorp811@live.com

      Delete
    6. Hi,its me again..i just want to know is it possible if we use sqlite database for updating the record in list view without using the button when i tried to plug in usb to the phone?example of database are charge log and it will display the date, time and status.

      Delete
    7. @jen, Trij, Janine, Boulette
      You can download full source from link provided at the end of tutorial. And please let me know if you found any problem.

      Delete
    8. It works for More than two attributes..

      Delete
    9. Please give me source code. kyawthuya.thuta@gmail.com

      Delete
    10. Please give me source code.. sonnynorca@gmail.com

      Delete
    11. Are you still sharing your source? If yes please, could you please send it to me to?
      oiproks.nemo@gmail.com

      Delete
    12. Hey there, I am new to android and I wish to add a spinner for occupation selection, and together with the names field they will be save to database, but I dont kow how I can do the spinner to get it's value . Please Help!

      Delete
    13. Please give me source code . My email Jyotirajai_203@yahoo.in . Thanks !

      Delete
    14. Hi, please kindly share the source code to me.. Thanks alot! My email: jocelyn_cys@hotmail.com

      Delete
    15. You can download full source from link provided at the end of tutorial. And please let me know if you found any problem.

      Delete
    16. Please share your code with me. :) here is my email mgyavega@hotmail.com. Thanks!

      Delete
    17. Please share your code with me. :) here is my email 123choy123@gmail.com Thanks!

      Delete
    18. You can download full source from link provided at the end of tutorial. And please let me know if you found any problem.

      Delete
  2. Hello, how to fix this problem with more attributes? please

    ReplyDelete
    Replies
    1. Can you explain ? What is your problem ?

      Delete
  3. hello
    your code is really helpful and I really want it !
    I tried it and it didn't work for more than 2 attributes !
    how can I fix that ???
    please help me
    thank you :)

    ReplyDelete
    Replies
    1. I mean for the database ..
      should I email you my code so you can see it ?

      Delete
    2. Okay.. Mail your code with explanation (what you want) . I will try to sort out your problem as soon as possible.

      Delete
    3. I just send it to : ktnbh281991@gmail.com
      thank youuu sooo much !
      hope you will find the problem :)

      Delete
    4. Hi Lama,
      I have sent code in email. Please give it a try.
      The problem was in your manifest.xml and key name for data in intent.
      I changed and it's working fine. Uninstall old one and then install this code. Let me know if you found any problem.

      Delete
    5. thank you so much !
      I send you another email ..
      can you check it for me please ?
      sorry for bothering ,,
      thanks again !

      Delete
  4. Hello, thank yhu for this tutorial, it qas the last thing I needed to qet my app donw. However, I modified your code to my project, which was a "Add to favorite" for a webview. AI qot it setup, the title shows in the listview, but I'm having problems under the onitemclick for the listview, I've been trying in vain to make the bookmarked item in the listview open the url by linking it to my webview activity. Please advuse, my reqardz to Hiral!

    ReplyDelete
  5. Hiii ur tutorial is really great.but i want to ask you a question that now if i want to update this records in to mysql databse onserver then how can i do it ?

    ReplyDelete
    Replies
    1. Hi Jalpa thanks.
      If you want to update records on server then there should be some web service. You just need to send this data using that web service and then web service developer will handle your data on server database.

      Delete
  6. why pass in "this" to constructor for DBHelper?

    ReplyDelete
    Replies
    1. I am passing "this' because constructor accepts one parameter (context)
      public DbHelper(Context context) {
      super(context, DATABASE_NAME, null, 1);

      }

      Here this refers to context so we have to pass "context" to constructor. You can also write as following
      mHelper = new DbHelper(DisplayActivity.this); or mHelper = new DbHelper(context);
      Hope now it clears your doubt..

      -Ketan

      Delete
  7. Do you have a code for the layout of this application? Because I'm just a new programmer and I don't have any background or study about android and SQLite. I just read on the internet. Thank you :)

    ReplyDelete
  8. @Saurabh, redangel143
    Sorry I just forgot to add xml files. But you can download whole project from link provided at the end of tutorial.

    Thanks
    -Ketan

    ReplyDelete
  9. Excellent, perfect ! easy to understand tutorial. Exactly what i was looking for. Worked fine with no error whatsoever.

    ReplyDelete
  10. Thanks, it works fine. wanted to know if one needs to save date picker and time picker values, how can i go about it? can email me at 4c.brial@gmail.com. Thank you

    ReplyDelete
  11. Please can you share your code with me at the earliest my email id is ranasachin81@gmail.com !! Please send your .xml code as well it will be a great help !! Thank you

    ReplyDelete
    Replies
    1. Hi Sachin
      You can download full source from link provided at the end of tutorial. And please let me know if you found any problem.

      Delete
  12. Sir, can you mail me the code working for more than two attributes.. my Email id is mayurirane1291@gmail.com

    ReplyDelete
    Replies
    1. Hi Mayuri
      Jeevan said: It works for More than two attributes.
      You may doing something wrong. Please explain what is your problem or error you get. So someone can help you.

      Delete
  13. Hello Sir,
    Please mail me a simple Sqlite Insert, display, Update, Delete example without Listview.
    my email is chintan.gpt@gmail.com

    ReplyDelete
    Replies
    1. Without listview means what ? How do you want to display ?

      Delete
  14. fabulous work i loved it.....

    ReplyDelete
  15. Hi kethan;
    can you please help me work where i try to add more than two attributes but its not working!
    Been trying and doing it whole night still cannot fix. Do please help me i will send my code on google is that?
    thank you very much

    ReplyDelete
  16. Thanks a bunch Ketan and Hiral Kanojiya. It was quite helpful.

    ReplyDelete
  17. Please share your code with me. here is my email jmariel1114@gmail.com. Thanks!

    ReplyDelete
  18. Please, send to me a complete code by e-mail. My e-mail is vanger.carneiro@gmail.com

    ReplyDelete
  19. It was great tutorial, I enjoyed from that.
    Thank you very much.

    ReplyDelete
    Replies
    1. Hey there, do you know how I can add a spinner together with these data into a database?
      I am Stuck and do not know I can add spinner selected value into database.

      Delete
  20. Hi, Instead of long press to delete, is there any way to multiselect the records and delete using delete button. I searched in google, there are ways to do using ArrayAdapter but did not find the same with Base Adapter. As i am new to Programming not able to do R & D. it would be useful if you can help on how to do this.

    ReplyDelete
  21. hey, I am new to android and base on this example I wish to add a spinner, to allow users to select their occupation and save everything into database. HELP ! please anyone can assist me with that .Thanks in advance

    ReplyDelete
  22. Hi Ketan,

    I tried your code and it works perfect. I want to add search name with editText to ListView. I could'nt do that. Can you help me for this? Or can you add your code this also?

    ReplyDelete
  23. Sir, can you mail me the code working for more than two attributes.. my Email id is manojihturs@gmail.com

    ReplyDelete
    Replies
    1. Sorry.. Now code is not handy and I can not find time. But you can download it from link provided at the end of tutorial and try to add more attributes. If you get any problem then let me know.

      Delete
  24. i wanted to know if one needs to save date picker and time picker values, how can i go about it? can email me at rotana.meshall@gmail.com. help me please, Thank you

    ReplyDelete
    Replies
    1. What is difficult ? Add two columns in table for date and time.
      Then get values from DatePicker, TimePicker and save same as other fields in this tutorial.

      Delete
  25. hey m using same code .thanks it works but it giving me two times result in listview .after click on save it will added twice .and i want to insert image path in db n retrieve another activity .but it giving me problem for retrieving image, please give me a solution...Thank you

    ReplyDelete
  26. Please share your code with me. savani.dharmik92@gmail.com

    ReplyDelete
  27. Thank you very much! (^_^)
    I think it's really useful for me to improve my project.

    ReplyDelete
  28. thank u very much for ur code i really need that part
    i am trying to add that long press delete with my already created list view and i am facing some error will u please help me in this
    that will be very helpful if u see my updated code and give me suggestion how to solve that please
    can anyone help me out ....

    ReplyDelete
  29. 07-15 09:20:05.098: E/AndroidRuntime(1725): java.lang.IndexOutOfBoundsException: Invalid index 3, size is 0

    getting this error while using delete code please help me out

    ReplyDelete
  30. Very good buddy finally I go solution from here...
    It helps me lot to self learing

    ReplyDelete
  31. Dear Ketan,
    i manage to get your code up with more the 2 value but when display the data out not all are displaying it out.
    pls contact me @ G_Freedom@hotmail.com

    ReplyDelete
    Replies
    1. You can download full source from link provided at the end of tutorial. And please let me know if you found any problem.

      Delete
  32. Hello i need help with my code...i need to refresh listview after deleting the data from database how to do that using cursor adapter?

    ReplyDelete
  33. please share your updated code ashkkumar1246@gmail.com

    ReplyDelete
  34. hello. thanks for your helpfull code. i use your it in my app project. i have 6 fields but as i run it, just 2 of them work correctly. would you please help me to find out the problem. is it possible to send you my code for resolving?thanks alot.

    ReplyDelete
  35. @KetanAhir. i use your code in my app, but there is problem(bug) in mine. how can i send you my code for resolving? please help me.

    ReplyDelete
  36. i send you a Gmail message with my app proje. would you please help me yo find out the problem? thanks for your help.

    ReplyDelete
  37. good bt can u sent me .xml code for my email id is chauhankinjal411@gmail.com
    please share fast pleaseee

    ReplyDelete
    Replies
    1. You can download full source from link provided at the end of tutorial. And please let me know if you found any problem.

      Delete
  38. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  39. Hi sir, how to get Spinner data and image into database? Hope you can help me thanks you.

    ReplyDelete
    Replies
    1. Assuming your spinner data is string type.
      String value=(String)spinner.getSelectedItem();
      Now you can store value in database.
      For images I suggest you to store image on sdcard and then store it's path to database.

      Delete
  40. i cannot see the data base for the program

    ReplyDelete
  41. thanks given this,
    is working

    ReplyDelete
  42. tkq for this tutorial
    this is very helpfull to me
    tkq so much

    ReplyDelete
  43. Hello

    It is a great tutorial. I downloaded the source code from the link provided. But where is the delete option?
    Please tell me.

    ReplyDelete
    Replies
    1. Hi Atul,
      You can long press on list item and it will show alert dialog to delete record.

      Delete
  44. hello
    your code is really helpful and I really want it !
    I tried it and it didn't work for more than 2 attributes !
    how can I fix that ???
    please help me
    thank you :)

    ReplyDelete
  45. I want code for more than 2 attributes can u mail the code at jagriti.maurya95@gmail.com....Its urgent can you please help me out..

    ReplyDelete
  46. How to edit the INSERTED DATAS

    ReplyDelete
  47. Hi its a very good Tutorial, but i want to add more Attributes then 2. Can you please help me

    ReplyDelete
  48. I want above code pls mail me at deepali.bajare26@gmail.com
    thanks

    ReplyDelete
  49. plz,I want above code pls mail me at sunilmane_93@rediffmail.com
    thanks

    ReplyDelete
  50. Hi can you help me putting a serchview with this activity.I want to filter all data i was going to input. Thank you in advance.

    ReplyDelete

  51. watch records to storing database in an array. list of type student and passing this list to the adapter .update and delete record .
    http://blog.e-logicsense.com/android-sqlite-database-crud-operation/

    ReplyDelete
  52. Hi i'm new here. but i have a problem running this project.
    it auto stops for no reason. can you help me?

    thnx in advance

    ReplyDelete