Android SQLite DataBase With CursorAdapter

By | November 16, 2013

The SQLite database is embedded into Android OS , which is very light weight and helps in creating and manipulate data in your application , The android SDK provides the  “android.database.sqlite”  package which provides all specific class to SQLite to work ,

Here In this Post we will see an example of adding member to a table and then performing update and delete operations on that existing member . Below is the screenshot for sample output of the example . 


Android SQLite Database

Logical Flow
Android SQLite Database Example

Database Setup

Table Structure

Table name : member

   Field     Type Key
_id    INTEGER       PRIMARY KEY AUTOINCREMENT  
name TEXT

Create DBhelper Class which must be subclassed to SQLiteOpenHelper , and create a constructor for this class and call the super() method which takes database name and database version as a parameter to it .

override the onCreate() and onUpgrade() for creating and upgrading the database respectively .

File : DBhelper.java

package com.pavan.sqlitedemoo;

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

public class DBhelper extends SQLiteOpenHelper {

 // TABLE INFORMATTION
 public static final String TABLE_MEMBER = “member”;
 public static final String MEMBER_ID = “_id”;
 public static final String MEMBER_NAME = “name”;

 // DATABASE INFORMATION
 static final String DB_NAME = “MEMBER.DB”;
 static final int DB_VERSION = 1;

 // TABLE CREATION STATEMENT
private static final String CREATE_TABLE = “create table “
  + TABLE_MEMBER + “(” + MEMBER_ID
  + ” INTEGER PRIMARY KEY AUTOINCREMENT, “
  + MEMBER_NAME + ” TEXT NOT NULL);”;

public DBhelper(Context context) {
 super(context, DB_NAME, null,DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
 db.execSQL(CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 db.execSQL(“DROP TABLE IF EXISTS ” + TABLE_MEMBER);
 onCreate(db);
}
}

Create SQLController Class where we define CRUD operations , you can find the full source code inside the download file

File : SQLController.java

package com.pavan.sqlitedemoo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class SQLController {

    private DBhelper dbhelper;
    private Context ourcontext;
    private SQLiteDatabase database;

    public SQLController(Context c) {
        ourcontext = c;
    }

    public SQLController open() throws SQLException {
        dbhelper = new DBhelper(ourcontext);
        database = dbhelper.getWritableDatabase();
        return this;

    }

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

   //Inserting Data into table
    public void insertData(String name) {
        ContentValues cv = new ContentValues();
        cv.put(DBhelper.MEMBER_NAME, name);
        database.insert(DBhelper.TABLE_MEMBER, null, cv);
    }

  //Getting Cursor to read data from table
    public Cursor readData() {
        String[] allColumns = new String[] { DBhelper.MEMBER_ID,
                DBhelper.MEMBER_NAME };
        Cursor c = database.query(DBhelper.TABLE_MEMBER, allColumns, null,
                null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

   //Updating record data into table by id
    public int updateData(long memberID, String memberName) {
        ContentValues cvUpdate = new ContentValues();
        cvUpdate.put(DBhelper.MEMBER_NAME, memberName);
        int i = database.update(DBhelper.TABLE_MEMBER, cvUpdate,
                DBhelper.MEMBER_ID + ” = ” + memberID, null);
        return i;
    }

  // Deleting record data from table by id
    public void deleteData(long memberID) {
        database.delete(DBhelper.TABLE_MEMBER, DBhelper.MEMBER_ID + “=”
                + memberID, null);
    }

}

After Setting up the database , to perform insert and modify (update/delete) i have create the following Activities .

SQL
        Operation       
Activity
Layout File Java File
Select activity_main.xml
view_member_entry.xml
MainActivity.java
Insert add_member.xml Add_member.java
Update and Delete modify_member.xml Modify_member.java

Main_Activity

Android Reading SQLite Data Into Listview with CursorAdapter

Design the above layout in xml , you can find it inside res/layout with file name activity_main.xml  , this layout contains button view and listview ,

Set the content of above defined layout inside the MainActivity.java file and add onclicklistener for addmember button to move to add_member activity

Set the data from database for each items inside listview using cursor object , here we are calling the readData() method of SQLController which returns cursor .

File: activity_main.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”
    android:padding=”10dp”>

    <Button
        android:id=”@+id/addmem_bt_id”
        android:layout_width=”fill_parent”
        android:layout_height=”wrap_content”
        android:text=”ADD MEMBER” />

    <ListView
        android:id=”@+id/memberList_id”
        android:layout_width=”match_parent”
        android:layout_height=”wrap_content”
        android:dividerHeight=”2dp” >
    </ListView>
  
</LinearLayout>    

File: view_member_entry.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/member_id”
        android:layout_width=”fill_parent”
        android:layout_height=”wrap_content”
        android:visibility=”gone” />

    <TextView
        android:id=”@+id/member_name”
        android:layout_width=”fill_parent”
        android:layout_height=”wrap_content”
        android:padding=”15dp”
        android:textSize=”17sp”
        android:textStyle=”bold”/>

</LinearLayout>

File : MainActivity.java

package com.pavan.sqlitedemoo;

import com.example.sqlitedemoo.R;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
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.Button;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;

public class MainActivity extends Activity {

    Button addmem_bt;
    ListView lv;
    SQLController dbcon;
    TextView memID_tv, memName_tv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbcon = new SQLController(this);
        dbcon.open();
        addmem_bt = (Button) findViewById(R.id.addmem_bt_id);
        lv = (ListView) findViewById(R.id.memberList_id);
        
      // onClickListiner for addmember Button
        addmem_bt.setOnClickListener(new OnClickListener() {
      
                   @Override
                   public void onClick(View v) {
                          Intent add_mem = new Intent(MainActivity.this, Add_member.class);
                          startActivity(add_mem);
                  }
        });

        // Attach The Data From DataBase Into ListView Using Crusor Adapter
        Cursor cursor = dbcon.readData();
        String[] from = new String[] { DBhelper.MEMBER_ID, DBhelper.MEMBER_NAME };
        int[] to = new int[] { R.id.member_id, R.id.member_name };

        SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                MainActivity.this, R.layout.view_member_entry, cursor, from, to);

        adapter.notifyDataSetChanged();
        lv.setAdapter(adapter);

        // OnCLickListiner For List Items
        lv.setOnItemClickListener(new OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view,
                    int position, long id) {
                memID_tv = (TextView) view.findViewById(R.id.member_id);
                memName_tv = (TextView) view.findViewById(R.id.member_name);

                String memberID_val = memID_tv.getText().toString();
                String memberName_val = memName_tv.getText().toString();

                Intent modify_intent = new Intent(getApplicationContext(),
                        Modify_member.class);
                modify_intent.putExtra(“memberName”, memberName_val);
                modify_intent.putExtra(“memberID”, memberID_val);
                startActivity(modify_intent);
            }
        });

    } // create method end

}// class end

Add_member

Android InsertData Into Sqlite Database

Design the above layout in xml , you can find it inside res/layout with file name add_member.xml,this layout contains Edit_text and Button.

Set the content of above defined layout inside the Add_member.java file

onclick of add button we are calling for insertData() method of SQLController to insert the data into the member table .

File: add_member.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”
    android:padding=”20dp” >

    <EditText
        android:id=”@+id/member_et_id”
        android:layout_width=”match_parent”
        android:layout_height=”wrap_content”
        android:ems=”10″
        android:layout_marginTop=”40dp”
        android:hint=”Enter Name …”>

        <requestFocus />
    </EditText>

    <Button
        android:id=”@+id/add_bt_id”
        android:layout_width=”wrap_content”
        android:layout_height=”wrap_content”
        android:layout_gravity=”center”
        android:text=”ADD” />

</LinearLayout>

File : Add_member.java

package com.pavan.sqlitedemoo;

import com.example.sqlitedemoo.R;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class Add_member extends Activity implements OnClickListener {
    EditText et;
    Button add_bt, read_bt;
    SQLController dbcon;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    

        super.onCreate(savedInstanceState);
        setContentView(R.layout.add_member);
        et = (EditText) findViewById(R.id.member_et_id);
        add_bt = (Button) findViewById(R.id.add_bt_id);

        dbcon = new SQLController(this);
        dbcon.open();
        add_bt.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
      
        switch (v.getId()) {
        case R.id.add_bt_id:
               String name = et.getText().toString();
              dbcon.insertData(name);
              Intent main = new Intent(Add_member.this, MainActivity.class)
                    .setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
             startActivity(main);
             break;

        default:
            break;
        }
    }

}

Modify_member

Android Update/Delete Data from Sqlite Database

Design the above layout in xml , you can find it inside res/layout with file name modify_member.xml , this layout contains Edit_text and two Button.

Set the content of above defined layout inside the Modify_member.java file .

Here we are calling the updateData() and deleteData() method of SQLController to update and delete data respectively .

File : modify_member.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”
    android:padding=”20dp”>

    <EditText
        android:id=”@+id/edit_mem_id”
        android:layout_width=”match_parent”
        android:layout_height=”wrap_content”
        android:layout_marginTop=”40dp”
        android:ems=”10″ />

    <LinearLayout
        android:layout_width=”match_parent”
        android:layout_height=”match_parent”
        android:orientation=”horizontal”
        android:gravity=”center_horizontal”>

        <Button
            android:id=”@+id/update_bt_id”
            android:layout_width=”wrap_content”
            android:layout_height=”wrap_content”
            android:text=”Update” />

        <Button
            android:id=”@+id/delete_bt_id”
            android:layout_width=”wrap_content”
            android:layout_height=”wrap_content”
            android:text=”Delete” />
    </LinearLayout>

</LinearLayout>

File : Modify_member.java

package com.pavan.sqlitedemoo;

import com.example.sqlitedemoo.R;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class Modify_member extends Activity implements OnClickListener {

    EditText et;
    Button edit_bt, delete_bt;

    long member_id;

    SQLController dbcon;

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

        dbcon = new SQLController(this);
        dbcon.open();

        et = (EditText) findViewById(R.id.edit_mem_id);
        edit_bt = (Button) findViewById(R.id.update_bt_id);
        delete_bt = (Button) findViewById(R.id.delete_bt_id);

        Intent i = getIntent();
        String memberID = i.getStringExtra(“memberID”);
        String memberName = i.getStringExtra(“memberName”);

        member_id = Long.parseLong(memberID);

        et.setText(memberName);

        edit_bt.setOnClickListener(this);
        delete_bt.setOnClickListener(this);

    }

    @Override
    public void onClick(View v) {
    
        switch (v.getId()) {
        case R.id.update_bt_id:
            String memName_upd = et.getText().toString();
            dbcon.updateData(member_id, memName_upd);
            this.returnHome();
            break;

        case R.id.delete_bt_id:
            dbcon.deleteData(member_id);
            this.returnHome();
            break;
        }
    }

    public void returnHome() {

        Intent home_intent = new Intent(getApplicationContext(),
                MainActivity.class).setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);

        startActivity(home_intent);
    }

}

ALSO READ :

  • can u pls help me to code for a database to enter name,password,and disignation,and on login with the username it show display all other columns of password and designation of dat person along with dat login time also

  • can u pls help me to code for a database to enter name,password,and disignation,and on login with the username it show display all other columns of password and designation of dat person along with dat login time also

  • update and delete toh ho he nahi raha

  • @rajat jain
    first you need to add members , the member are added to listview using cursor adapter, on click of member you move to new activity there you either delete or update
    the code is absolutely working fine just download and import into your eclipse and run

  • Tuyệt vời, tôi đang học lập trình Android và tôi rất thích những bài viết hướng dẫn của bạn.

    From Hanoi, Vietnam

  • @ Thanh Thắng Tô
    You Welcome 🙂

  • How can I display the ID number in front of the member in ListView?
    1 member
    2 ….
    3 ….

    Great work by the way!

  • It's working very well but its not deleting and updating items in listview/

  • @Anshul Tyagi
    I rechecked it after seeing your comment , it's working fine from my end ,
    download the full source from the above link and try
    and mean while i will try to make a video and update in the article

  • @Sascha Melcher
    Remove android:visibility="gone" from the xml file "view_member_entry.xml" to display ID

  • Great work! Best pedagogic tutorial (with nice layout) I have seen for a loooong time. And it is dated.

    Is it possible for you to make a similar tutorial using CursorLoader with 2 tables?

  • This constructor was deprecated in API level 11
    SimpleCursorAdapter adapter = new SimpleCursorAdapter(
    MainActivity.this, R.layout.viewmemberslist, cursor, from, to);

    In constructor add parameters 1 or 2 at the end
    http://developer.android.com/reference/android/widget/CursorAdapter.html#FLAG_AUTO_REQUERY
    http://developer.android.com/reference/android/widget/CursorAdapter.html#FLAG_REGISTER_CONTENT_OBSERVER

    Ex
    SimpleCursorAdapter adapter = new SimpleCursorAdapter(
    MainActivity.this, R.layout.viewmemberslist, cursor, from, to,2);

  • Good work Pavan..

  • @Revanayya
    Thank you

  • Hello, Pavan. Thank you very much for your informative post, did everything as you said, after the application on the emulator, the application crashes logsat displays http://sf.uploads.ru/WSXU6.jpg

  • @Александр
    I saw the logcat it's showing you have not declared activities in manifest.xml
    check manifest file and declare activities

  • H!! please help. i'd clicked the following
    link to activate my subscription, but i still get my email has pending verification from your site.
    my email : ram_al_666@yahoo.com

  • @X venuzz

    Database is updated you can download now

  • How can i get the custom alert when clicking on an item?

  • HI, I'm facing a problem at the readData() part, showing that I'm attempting to invoke a virtual method with a null object reference

  • please create this app by using openorcreatedatabase to reduce the code

  • Excelente post..