How to use ListView in order to view SQLite Database in Android Studio?

Question

My aim is to get the database values in a ListView. I've never used ListView and don't exactly know how to do it.

So far I have been using an AlertDialog to view the entries of database. But it creates problem as when one entry is added in database and is viewed then it is stored in stack memory and when you add another entry in Database table and then view it, it shows the new entry but after pressing Back button it also shows the previous entry as such.

How can I show entries from database in the ListView ?

Database.java

package com.example.user.phonebook;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.ListView;

public class Database extends SQLiteOpenHelper {

    public static final String DATABASE_NAME="PhoneBook.db";
    public static final String TABLE_NAME="Information";
    public static final String ID="ID";
    public static final String NAME="Name";
    public static final String EMAIL="Email";
    public static final String PHONE="Phone";


    public Database(Context context) {
        super(context, DATABASE_NAME, null, 2);
    }



    @Override
    public void onCreate(SQLiteDatabase db) {

        String create_table= "CREATE TABLE " + TABLE_NAME + "(" + ID + " TEXT,"
                + NAME + " TEXT," + EMAIL + " TEXT," + PHONE + " 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);
    }

    public boolean AddData(String id,String name, String email, String phone)
    {
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues cv= new ContentValues();
        cv.put(ID,id);
        cv.put(NAME,name);
        cv.put(EMAIL,email);
        cv.put(PHONE,phone);

        long result=db.insert(TABLE_NAME,null,cv);

        if (result==-1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    public Cursor getData()
    {

        SQLiteDatabase db=this.getWritableDatabase();
        Cursor cv=db.rawQuery("SELECT * FROM " + TABLE_NAME,null);
        return cv;

    }

    public boolean UpdateDate(String id, String name, String email, String phone)
    {
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues cv=new ContentValues();
        cv.put(ID,id);
        cv.put(NAME,name);
        cv.put(EMAIL,email);
        cv.put(PHONE,phone);
        db.update(TABLE_NAME,cv,"ID="+id,null);
        return true;

    }
    }

MainActivity.java

package com.example.user.phonebook;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

    EditText ed1,ed2,ed3,ed4;
    TextView tv;
    Button bn1,bn2,bn3;
    Database mydb;
    ListView lv;



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

        ed1=(EditText)findViewById(R.id.editText);
        ed2=(EditText)findViewById(R.id.editText2);
        ed3=(EditText)findViewById(R.id.editText4);
        ed4=(EditText)findViewById(R.id.editText3);
        tv=(TextView) findViewById(R.id.textView2);
        bn1=(Button) findViewById(R.id.button);
        bn2=(Button) findViewById(R.id.button2);
        bn3=(Button) findViewById(R.id.button3);
        lv=(ListView)findViewById(R.id.listview) ;


        mydb=new Database(this);

        bn1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                boolean value=mydb.AddData(ed4.getText().toString(),ed1.getText().toString(),ed2.getText().toString(),ed3.getText().toString());

                if (value==true)
                {
                    Toast.makeText(getApplicationContext(),"Data Inserted",Toast.LENGTH_SHORT).show();
                }
                else
                {
                    Toast.makeText(getApplicationContext(),"Error",Toast.LENGTH_SHORT).show();
                }
            }
        });


        bn2.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Cursor cv=mydb.getData();
                if (cv.getCount()==0)
                {
                    Toast.makeText(getApplicationContext(),"No data found",Toast.LENGTH_SHORT).show();
                    return;
                }

                while (cv.moveToNext())
                {
                    StringBuffer stringBuffer=new StringBuffer();


                    stringBuffer.append("ID : "+cv.getString(0)+"\n");
                    stringBuffer.append("Name : "+cv.getString(1)+"\n");
                    stringBuffer.append("Email ID : "+cv.getString(2)+"\n");
                    stringBuffer.append("Phone No. : "+cv.getString(3)+"\n\n");


                    message("Details", stringBuffer.toString());
                }



            }
        });


        bn3.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                boolean isupdated=mydb.UpdateDate(ed4.getText().toString(),ed1.getText().toString(),ed2.getText().toString(),ed3.getText().toString());
                if (isupdated==true)
                {
                    Toast.makeText(getApplicationContext(),"Data Updated",Toast.LENGTH_SHORT).show();
                }
                else
                {
                    Toast.makeText(getApplicationContext(),"Updation failed",Toast.LENGTH_SHORT).show();
                }

            }
        });





    }

    public void message(String title, String messageis)
    {
        AlertDialog.Builder builder=new AlertDialog.Builder(this);
        builder.setCancelable(true);
        builder.setTitle(title);
        builder.setMessage(messageis);
        AlertDialog dialog=builder.create();
        dialog.show();
    }




}

XML Layout:

<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <EditText
        android:id="@+id/editText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="8dp"
        android:layout_marginTop="8dp"
        android:ems="10"
        android:hint="Name"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toTopOf="@+id/editText2"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.502"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="1.0" />

    <EditText
        android:id="@+id/editText2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="8dp"
        android:ems="10"
        android:hint="Email ID"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toTopOf="@+id/editText4"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.502"
        app:layout_constraintStart_toStartOf="parent" />

    <EditText
        android:id="@+id/editText4"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="76dp"
        android:ems="10"
        android:hint="Phone Number"
        android:inputType="phone"
        app:layout_constraintBottom_toTopOf="@+id/button2"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.502"
        app:layout_constraintStart_toStartOf="parent" />

    <Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:text="Save"
        app:layout_constraintEnd_toStartOf="@+id/button2"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="@+id/button2" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="256dp"
        android:text="View"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <Button
        android:id="@+id/button3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:text="Update"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toEndOf="@+id/button2"
        app:layout_constraintTop_toTopOf="@+id/button2" />

    <EditText
        android:id="@+id/editText3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="8dp"
        android:layout_marginTop="8dp"
        android:ems="10"
        android:hint="ID"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toTopOf="@+id/button2"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.502"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editText4"
        app:layout_constraintVertical_bias="0.076" />

    <ListView
        android:id="@+id/listview"
        android:layout_width="347dp"
        android:layout_height="185dp"
        android:layout_marginBottom="8dp"
        android:layout_marginEnd="16dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="8dp"
        android:divider="#f00"
        android:dividerHeight="1sp"
        android:listSelector="#faa"
        android:orientation="vertical"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintHorizontal_bias="0.846"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/button2"
        app:layout_constraintVertical_bias="0.028" />

</android.support.constraint.ConstraintLayout>

Thanks in advance.

This might help you https://stackoverflow.com/questions/8656901/populating-a-listview-from-a-sqlite-database — Arshad, May 15 '18 at 11:05
Possible duplicate of [Populating a listview from a SQLite database](https://stackoverflow.com/questions/8656901/populating-a-listview-from-a-sqlite-database) — Koustuv Ganguly, May 15 '18 at 11:15

score 1 · Answer 1 · answered May 16 '18 at 05:23

Make a model class with three params

String id,String name, String email, String phone

In your code in Database.java class make a method "getData" that will return list of Table: TABLE_NAME Like this

public List<ModelName modelname> getData()
    {
        SQLiteDatabase db=this.getReadableDatabase();
        List<ModelName > modelList=new ArrayList<>();
        Cursor res=db.rawQuery("SELECT * FROM "+TABLE_NAME,null);
        if(res!=null && res.moveToFirst())
        {
            do {

                ModelName model=new ModelName ();

                model.setId(res.getInt(res.getColumnIndex(ID)));
                model.setName(res.getString(res.getColumnIndex(NAME)));
                model.setEmail(res.getDouble(res.getColumnIndex(EMAIL)));
                model.setPhone(res.getString(res.getColumnIndex(PHONE)));

            }while (res.moveToNext());
        }
        res.close();
        db.close();
        return modelList;
    } // end of getdata method

This method will return list of data that will be needed to populate in listview code like this:

This will return the list

List listName= db.getData;

Add this list in code of listview

// Find ListView to populate
ListView lvItems = (ListView) findViewById(R.id.lvItems);
// Setup cursor adapter using cursor from last step
YourAdapter adapter = new YourAdapter(this, listName);
// Attach cursor adapter to the ListView 
lvItems.setAdapter(todoAdapter);

Best of luck

score 0 · Answer 2 · answered May 15 '18 at 11:16

You CursorAdapter, The CursorAdapter fits in between a Cursor (data source from SQLite query) and the ListView (visual representation) and configures two aspects:

Which layout template to inflate for an item Which fields of the cursor to bind to which views in the template

simply create an XML layout template in res/layout/item_todo.xml, representing a particular cursor row:

   <?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="horizontal" >
    <TextView
        android:id="@+id/tvBody"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Study cursors"
        android:textAppearance="?android:attr/textAppearanceLarge" />
    <TextView
        android:id="@+id/tvPriority"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:text="3"
        android:textAppearance="?android:attr/textAppearanceMedium" />
</LinearLayout>

Next, we need to define the adapter to describe the process of projecting the Cursor's data into a View. To do this we need to override the newView method and the bindView method. The naive approach to this (without any view caching) looks like the following:

public class TodoCursorAdapter extends CursorAdapter {
  public TodoCursorAdapter(Context context, Cursor cursor) {
      super(context, cursor, 0);
  }

  // The newView method is used to inflate a new view and return it, 
  // you don't bind any data to the view at this point. 
  @Override
  public View newView(Context context, Cursor cursor, ViewGroup parent) {
      return LayoutInflater.from(context).inflate(R.layout.item_todo, parent, false);
  }

  // The bindView method is used to bind all data to a given view
  // such as setting the text on a TextView. 
  @Override
  public void bindView(View view, Context context, Cursor cursor) {
      // Find fields to populate in inflated template
      TextView tvBody = (TextView) view.findViewById(R.id.tvBody);
      TextView tvPriority = (TextView) view.findViewById(R.id.tvPriority);
      // Extract properties from cursor
      String body = cursor.getString(cursor.getColumnIndexOrThrow("body"));
      int priority = cursor.getInt(cursor.getColumnIndexOrThrow("priority"));
      // Populate fields with extracted properties
      tvBody.setText(body);
      tvPriority.setText(String.valueOf(priority));
  }
}

First, we define a constructor that passes the cursor and context to the superclass. Next, we override the newView method, which is used to inflate a new view template. Finally, we override the bindView method, which is used to bind all data to a given view to populate the template content for the item.

Retrieving the Cursor In order to use a CursorAdapter, we need to query a SQLite database and get back a Cursor representing the result set. This requires us to use a SQLiteOpenHelper for persistence as described here or an ORM that provides access to the underlying database.

Once you have a database and tables defined, then we can get access to a Cursor by querying the database with rawQuery:

// TodoDatabaseHandler is a SQLiteOpenHelper class connecting to SQLite
TodoDatabaseHandler handler = new TodoDatabaseHandler(this);
// Get access to the underlying writeable database
SQLiteDatabase db = handler.getWritableDatabase();
// Query for items from the database and get a cursor back
Cursor todoCursor = db.rawQuery("SELECT  * FROM todo_items", null);

Now, we can use the CursorAdapter in the Activity to display an array of items into the ListView:

// Find ListView to populate
ListView lvItems = (ListView) findViewById(R.id.lvItems);
// Setup cursor adapter using cursor from last step
TodoCursorAdapter todoAdapter = new TodoCursorAdapter(this, todoCursor);
// Attach cursor adapter to the ListView 
lvItems.setAdapter(todoAdapter);

This will then trigger the CursorAdapter iterating through the result set and populating the list. We can change the cursor to update the adapter at any time with:

// Switch to new cursor and update contents of ListView
todoAdapter.changeCursor(todoCursor);

Reference Link

I'm confused where do i need to place the same in my above code? Can you please guide me with the same. The basic theoretical concept has been understood but I'm confused of how and where to place it in my existing code??? @M. Adil — Vibhor Gupta, May 16 '18 at 03:52

How to use ListView in order to view SQLite Database in Android Studio?

2 Answers2