Display SQLite information via CUSTOM listview

Question

I have a working sqlite database which items can be added to and viewed via a listview. I now want to add a edit/delete button to each view dynamically.

Following the tutorial http://looksok.wordpress.com/2012/11/03/android-custom-listview-tutorial/ I have created a custom XML file with an edit/delete button. I am however unable to follow the tutorial as it does not show for retrieving and displaying sqlite information.

XML layout for custom list view:

<?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="horizontal" >

    <EditText
        android:id="@+id/itemName"
        android:layout_width="0sp"
        android:layout_height="fill_parent"
        android:layout_weight="2"
        android:gravity="center_vertical"
        android:hint="@string/title"
        android:textAppearance="?android:attr/textAppearanceSmall" />

    <EditText
        android:id="@+id/itemQuantity"
        android:layout_width="0sp"
        android:layout_height="fill_parent"
        android:layout_weight="1"
        android:gravity="center"
        android:inputType="numberDecimal"
        android:text="@string/quantity"
        android:textAppearance="?android:attr/textAppearanceSmall" />

    <ImageButton
        android:id="@+id/editItem"
        android:layout_width="@dimen/width_button"
        android:layout_height="fill_parent"
        android:contentDescription="@string/app_name"
        android:onClick="editOnClick"
        android:src="@android:drawable/ic_menu_edit" />
    
    <ImageButton
        android:id="@+id/deleteItem"
        android:layout_width="@dimen/width_button"
        android:layout_height="fill_parent"
        android:contentDescription="@string/app_name"
        android:onClick="removeOnClick"
        android:src="@android:drawable/ic_menu_delete" />
</LinearLayout>

Current Inventory XML (Where listview is shown):

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/currentInventory"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    xmlns:tools="http://schemas.android.com/tools" >

    <TextView android:layout_marginTop="5dip"
        android:id="@+id/selectCat"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/selectCategory"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <Spinner
        android:id="@+id/categoryChoose"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:entries="@array/catergory_arrays" />
    
    
    <ListView
        android:id="@+id/customDbListView"
        android:layout_width="match_parent"
        android:layout_height="350dip"
        android:layout_marginBottom="20dip"
        android:layout_marginTop="20dip"
        tools:listitem="@layout/list_view_custom" >

    </ListView>

    <Button
        android:id="@+id/scanCurrent"
        android:layout_width="match_parent"
        android:layout_height="50dp"
        android:layout_marginBottom="5dip"
        android:text="@string/scan" />

    <Button
        android:id="@+id/editItemCurrent"
        android:layout_width="match_parent"
        android:layout_height="50dp"
        android:layout_marginBottom="5dp"
        android:text="@string/editItem" />

</LinearLayout>

Creation of database:

package com.example.fooditemmonitor;

import java.util.ArrayList;

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 final class ItemDatabase {

    // the Activity or Application that is creating an object from this class.
    Context context;

    // a reference to the database used by this application/object
    private SQLiteDatabase db;

    // These constants are specific to the database.
    private final String DATABASE_NAME = "ItemDatabase.sqlite";
    private final int DATABASE_VERSION = 7;

    // These constants are specific to the database table.
    private final String TABLE_NAME = "foodItems";
    private final String COLUMN_NAME_ENTRY_ID = "entryid";
    private final String COLUMN_NAME_BARCODE = "barcode";
    private final String COLUMN_NAME_TITLE = "title";
    private final String COLUMN_NAME_QUANTITY = "quantity";
    private final String COLUMN_NAME_DATE = "date";
    private final String COLUMN_NAME_CATEGORY = "category";
    String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + TABLE_NAME;
    String SQL_CREATE_TABLE = "create table " + TABLE_NAME + " ("
            + COLUMN_NAME_ENTRY_ID
            + " integer primary key autoincrement not null," + COLUMN_NAME_DATE
            + " date," + COLUMN_NAME_BARCODE + " text," + COLUMN_NAME_TITLE
            + " text," + COLUMN_NAME_QUANTITY + " int," + COLUMN_NAME_CATEGORY
            + " text" + ");";

    public ItemDatabase(Context context) {
        this.context = context;

        // create or open the database
        ItemDatabaseHelper helper = new ItemDatabaseHelper(context);
        this.db = helper.getWritableDatabase();
    }

    public void addRow(String rowStringOne, String rowStringTwo,
            String rowStringThree, String rowStringFour, int rowIntFive) {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();
        values.put(COLUMN_NAME_DATE, rowStringOne);
        values.put(COLUMN_NAME_BARCODE, rowStringTwo);
        values.put(COLUMN_NAME_TITLE, rowStringThree);
        values.put(COLUMN_NAME_CATEGORY, rowStringFour);
        values.put(COLUMN_NAME_QUANTITY, rowIntFive);

        // ask the database object to insert the new data
        try {
            db.insert(TABLE_NAME, null, values);
        } catch (Exception e) {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }

    public void updateRow(long rowID, String rowStringOne, String rowStringTwo,
            String rowStringThree, int rowIntFour) {
        // this is a key value pair holder used by android's SQLite functions
        ContentValues values = new ContentValues();
        values.put(COLUMN_NAME_DATE, rowStringOne);
        values.put(COLUMN_NAME_BARCODE, rowStringTwo);
        values.put(COLUMN_NAME_TITLE, rowStringThree);
        values.put(COLUMN_NAME_QUANTITY, rowIntFour);

        // ask the database object to update the database row of given rowID
        try {
            db.update(TABLE_NAME, values, COLUMN_NAME_ENTRY_ID + "=" + rowID,
                    null);
        } catch (Exception e) {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
    }

    public void deleteRow(long rowID) {
        // ask the database manager to delete the row of given id
        try {
            db.delete(TABLE_NAME, COLUMN_NAME_ENTRY_ID + "=" + rowID, null);
            getAllRowsAsArrays();
        } catch (Exception e) {
            Log.e("DB ERROR", e.toString());
            e.printStackTrace();
        }
    }

    public ArrayList<ArrayList<Object>> getCategoryOfArrays(String category) {
        // create an ArrayList that will hold all of the data collected from
        // the database.
        ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
        // this is a database call that creates a "cursor" object.
        // the cursor object store the information collected from the
        // database and is used to iterate through the data.
        Cursor cursor;

        try {
            // ask the database object to create the cursor.

            cursor = db.query(TABLE_NAME, new String[] { COLUMN_NAME_DATE,
                    COLUMN_NAME_TITLE, COLUMN_NAME_QUANTITY,
                    COLUMN_NAME_CATEGORY }, COLUMN_NAME_CATEGORY + "='"
                    + category + "'", null, null, null, COLUMN_NAME_TITLE
                    + " ASC");

            // move the cursor's pointer to position zero.
            cursor.moveToFirst();

            // if there is data after the current cursor position, add it to the
            // ArrayList.
            if (!cursor.isAfterLast()) {
                do {

                    ArrayList<Object> dataList = new ArrayList<Object>();

                    dataList.add(cursor.getString(cursor
                            .getColumnIndex(COLUMN_NAME_DATE)));
                    dataList.add(cursor.getString(cursor
                            .getColumnIndex(COLUMN_NAME_TITLE)));
                    dataList.add(cursor.getInt(cursor
                            .getColumnIndex(COLUMN_NAME_QUANTITY)));

                    dataArrays.add(dataList);
                } while (cursor.moveToNext());
            }

            // let java know that you are through with the cursor.
            cursor.close();
        } catch (SQLException e) {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
        // return the ArrayList that holds the data collected from the database.
        return dataArrays;
    }

    public ArrayList<ArrayList<Object>> getAllRowsAsArrays() {
        // create an ArrayList that will hold all of the data collected from
        // the database.
        ArrayList<ArrayList<Object>> dataArrays = new ArrayList<ArrayList<Object>>();
        // this is a database call that creates a "cursor" object.
        // the cursor object store the information collected from the
        // database and is used to iterate through the data.
        Cursor cursor;

        try {
            // ask the database object to create the cursor.

            cursor = db.query(TABLE_NAME, new String[] { COLUMN_NAME_TITLE,
                    COLUMN_NAME_QUANTITY, COLUMN_NAME_CATEGORY }, null, null,
                    null, null, COLUMN_NAME_TITLE + " ASC");

            // move the cursor's pointer to position zero.
            cursor.moveToFirst();

            // if there is data after the current cursor position, add it to the
            // ArrayList.
            if (!cursor.isAfterLast()) {
                do {
                    ArrayList<Object> dataList = new ArrayList<Object>();

                    dataList.add(cursor.getString(cursor
                            .getColumnIndex(COLUMN_NAME_TITLE)));
                    dataList.add(cursor.getInt(cursor
                            .getColumnIndex(COLUMN_NAME_QUANTITY)));

                    dataArrays.add(dataList);
                } while (cursor.moveToNext());
            }

            // let java know that you are through with the cursor.
            cursor.close();

        } catch (SQLException e) {
            Log.e("DB Error", e.toString());
            e.printStackTrace();
        }
        // return the ArrayList that holds the data collected from the database.
        return dataArrays;
    }

    public class ItemDatabaseHelper extends SQLiteOpenHelper {
        public ItemDatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        public void onCreate(SQLiteDatabase db) {
            // execute the query string to the database.
            db.execSQL(SQL_CREATE_TABLE);
        }

        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // This database is only a cache for online data, so its upgrade
            // policy is to simply to discard the data and start over
            db.execSQL(SQL_DELETE_ENTRIES);
            onCreate(db);
        }
    }
}

Current code to display database data:

package com.example.fooditemmonitor;

import java.util.ArrayList;

import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Spinner;

public class CurrentItems extends Activity {

    ItemDatabase db;
    Context context;
    Button addButton, editButton;
    ListView listView;
    int spinnerID;

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

        db = new ItemDatabase(this);

        // create references and listeners for the GUI interface
        setupViews();

        // make the buttons clicks perform actions
        addButtonListeners();

        // display search results
        displaySearch();

    }

    private void setupViews() {
        // bring up current database items
        listView = (ListView) findViewById(R.id.customDbListView);

        // THE BUTTONS
        addButton = (Button) findViewById(R.id.scanCurrent);
        editButton = (Button) findViewById(R.id.editItemCurrent);
    }

    private void addButtonListeners() {

        Spinner selectCat = (Spinner) findViewById(R.id.categoryChoose);
        selectCat
                .setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                    public void onItemSelected(AdapterView<?> parent,
                            View view, int pos, long id) {
                        displaySearch();
                    }

                    public void onNothingSelected(AdapterView<?> parent) {
                        // Do nothing
                    }
                });

        addButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                startActivity(new Intent(CurrentItems.this, AddItem.class));

            }
        });

        editButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                startActivity(new Intent(CurrentItems.this, EditItems.class));

            }
        });
    }

    public int getSelectedItemPosition() {
        Spinner selectCat = (Spinner) findViewById(R.id.categoryChoose);
        spinnerID = selectCat.getSelectedItemPosition();
        return spinnerID;
    }

    private void displaySearch() {
        // TODO Auto-generated method stub
        Spinner selectCat = (Spinner) findViewById(R.id.categoryChoose);
        spinnerID = selectCat.getSelectedItemPosition();
        String catSelected;
        final ArrayList<String> items = new ArrayList<String>();
        final ArrayAdapter<String> itemArray;
        itemArray = new ArrayAdapter<String>(this,
                android.R.layout.simple_list_item_1, items);

        {
            if (getSelectedItemPosition() == 0) {
                ArrayList<ArrayList<Object>> data = db.getAllRowsAsArrays();
                for (int position = 0; position < data.size(); position++) {
                    ArrayList<Object> row = data.get(position);
                    items.add("\nTitle:  " + row.get(0).toString()
                            + "\nQuantity:  " + row.get(1).toString() + "\n");
                    itemArray.notifyDataSetChanged();
                }
                listView.setAdapter(itemArray);
            } else if (getSelectedItemPosition() == 1) {
                catSelected = "Fridge";
                ArrayList<ArrayList<Object>> data = db
                        .getCategoryOfArrays(catSelected);
                for (int position = 0; position < data.size(); position++) {
                    ArrayList<Object> row = data.get(position);
                    items.add("\nDate:  " + row.get(0).toString()
                            + "\nTitle:  " + row.get(1).toString()
                            + "\nQuantity:  "
                            + Integer.parseInt(row.get(2).toString()) + "\n");
                    itemArray.notifyDataSetChanged();
                }
                listView.setAdapter(itemArray);
            } else if (getSelectedItemPosition() == 2) {
                catSelected = "Can";
                ArrayList<ArrayList<Object>> data = db
                        .getCategoryOfArrays(catSelected);
                for (int position = 0; position < data.size(); position++) {
                    ArrayList<Object> row = data.get(position);
                    items.add("\nDate:  " + row.get(0).toString()
                            + "\nTitle:  " + row.get(1).toString()
                            + "\nQuantity:  "
                            + Integer.parseInt(row.get(2).toString()) + "\n");
                    itemArray.notifyDataSetChanged();
                }
                listView.setAdapter(itemArray);
            } else if (getSelectedItemPosition() == 3) {
                catSelected = "Fruit";
                ArrayList<ArrayList<Object>> data = db
                        .getCategoryOfArrays(catSelected);
                for (int position = 0; position < data.size(); position++) {
                    ArrayList<Object> row = data.get(position);
                    items.add("\nDate:  " + row.get(0).toString()
                            + "\nTitle:  " + row.get(1).toString()
                            + "\nQuantity:  "
                            + Integer.parseInt(row.get(2).toString()) + "\n");
                    itemArray.notifyDataSetChanged();
                }
                listView.setAdapter(itemArray);
            } else if (getSelectedItemPosition() == 4) {
                catSelected = "Vegetable";
                ArrayList<ArrayList<Object>> data = db
                        .getCategoryOfArrays(catSelected);
                for (int position = 0; position < data.size(); position++) {
                    ArrayList<Object> row = data.get(position);
                    items.add("\nDate:  " + row.get(0).toString()
                            + "\nTitle:  " + row.get(1).toString()
                            + "\nQuantity:  "
                            + Integer.parseInt(row.get(2).toString()) + "\n");
                    itemArray.notifyDataSetChanged();
                }
                listView.setAdapter(itemArray);
            } else {
                ArrayList<ArrayList<Object>> data = db.getAllRowsAsArrays();
                for (int position = 0; position < data.size(); position++) {
                    ArrayList<Object> row = data.get(position);
                    items.add("\nTitle:  " + row.get(0).toString()
                            + "\nQuantity:  " + row.get(1).toString() + "\n");
                    itemArray.notifyDataSetChanged();
                }

            }
        }
    }

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

The current code as it stands will only display the data as a regular listview. I need the data to show as:

Title Quantity (EditButton) (DeleteButton)

All help will be appreciated! Thanks

Answer 1

As i was a little bored i took the liberty to implement the adapter. You will have to edit the way you retrieve your elements from the db and how the Classes Information is Accessed since you didnt post the corresponding class representation.

You could however implement a CursorAdapter instead of a BaseAdapter.

public class QuantatiyAdapter extends BaseAdapter {

    private Context context;
    private List<Item> data;

    public QuantatiyAdapter(Context context) {
        this.context = context;
        //TODO actually put all your data into that list.
        this.data = Database.getAllData();
    }

    @Override
    public int getCount() {
        return this.data.size();
    }

    @Override
    public Object getItem(int arg0) {
        return this.data.get(arg0);
    }

    @Override
    public long getItemId(int arg0) {
        return 0;
    }

    @Override
    public View getView(int position, View converView, ViewGroup container) {

        ViewHolder holder = null;

        if (converView == null) {
            converView = LayoutInflater.from(context).inflate(R.layout.cutomLayout, null);
            holder = new ViewHolder();
            holder.title = (TextView) converView.findViewById(R.id.itemName);
            holder.quantity = (TextView) converView.findViewById(R.id.itemQuantity);
            holder.edit = (ImageButton) converView.findViewById(R.id.editItem);
            holder.delete = (ImageButton) converView.findViewById(R.id.deleteItem);
            converView.setTag(holder);
        } else {
            holder = (ViewHolder) converView.getTag();
        }

        // filling text
        holder.title.setText(getItem(position).getTitle());


        // adding action listeners to buttons
        holder.edit.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO fill methods for buttons

            }
        });


        return converView;
    }

    private static class ViewHolder {
        TextView title,quantity;
        ImageButton edit,delete;
    }

}