Android populate custom ListView from SQLite database

Question

I know how to manage listview with custom adapter. I would populate my listview fron SQLite Database. This is my CartHandler:

public class CartHandler extends SQLiteOpenHelper {
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "cartManager";

// Contacts table name
private static final String TABLE_PRODUCTS = "products";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_NO = "number";
private static final String KEY_PIECES = "pieces";
private static final String KEY_PRICE = "price";
private static final String KEY_TOT_PRICE = "totprice";


public CartHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_PRODUCTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_NO + " TEXT," + KEY_PIECES + " TEXT,"+ KEY_PRICE + " TEXT," + KEY_TOT_PRICE + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);

    // Create tables again
    onCreate(db);
}


// Adding new contact
void addProduct(CartRow product) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, product.getName()); // Contact Name
    values.put(KEY_NO, product.getNumber()); // Contact Phone
    values.put(KEY_PIECES, product.getPieces());
    values.put(KEY_PRICE, product.getPrice());
    values.put(KEY_TOT_PRICE, product.getTotPrice());
    // Inserting Row
    db.insert(TABLE_PRODUCTS, null, values);
    db.close(); // Closing database connection
}

// Getting single contact
CartRow getProduct(int id) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_PRODUCTS, new String[] { KEY_ID,
                    KEY_NAME, KEY_NO, KEY_PIECES, KEY_PRICE, KEY_TOT_PRICE }, KEY_ID + "=?",
            new String[] { String.valueOf(id) }, null, null, null, null);
    if (cursor != null)
        cursor.moveToFirst();

    CartRow product = new CartRow(Integer.parseInt(cursor.getString(0)),
            cursor.getString(1), Integer.parseInt(cursor.getString(2)), Integer.parseInt(cursor.getString(3)), Integer.parseInt(cursor.getString(4)), Integer.parseInt(cursor.getString(5)));
    // return contact
    return product;
}

// Getting All Contacts
public List<CartRow> getAllProducts() {
    List<CartRow> productList = new ArrayList<CartRow>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_PRODUCTS;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            CartRow product = new CartRow();
            product.setID(Integer.parseInt(cursor.getString(0)));
            product.setName(cursor.getString(1));
            product.setNumber(Integer.parseInt(cursor.getString(2)));
            product.setPieces(Integer.parseInt(cursor.getString(3)));
            product.setPrice(Integer.parseInt(cursor.getString(4)));
            product.setTotPrice(Integer.parseInt(cursor.getString(5)));
            // Adding contact to list
            productList.add(product);
        } while (cursor.moveToNext());
    }

    // return contact list
    return productList;
}

// Updating single contact
public int updateProduct(CartRow product) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_NAME, product.getName());
    values.put(KEY_NO, product.getNumber());
    values.put(KEY_PIECES, product.getPieces());
    values.put(KEY_PRICE, product.getPrice());
    values.put(KEY_TOT_PRICE, product.getTotPrice());

    // updating row
    return db.update(TABLE_PRODUCTS, values, KEY_ID + " = ?",
            new String[] { String.valueOf(product.getID()) });
}

// Deleting single contact
public void deleteProduct(CartRow product) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_PRODUCTS, KEY_ID + " = ?",
            new String[] { String.valueOf(product.getID()) });
    db.close();
}


// Getting contacts Count
public int getProductsCount() {
    String countQuery = "SELECT  * FROM " + TABLE_PRODUCTS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();

    // return count
    return cursor.getCount();
}}

This is my CartRow class:

public class CartRow {

//private variables
int _id;
String _name;
int _number;
int _pieces;
int _price;
int _totprice;

// Empty constructor
public CartRow(){

}
// constructor
public CartRow(int id, String name, int number, int pieces, int price, int totprice){
    this._id = id;
    this._name = name;
    this._number = number;
    this._pieces = pieces;
    this._price = price;
    this._totprice = totprice;
}

// constructor
public CartRow(String name, int number, int pieces, int price, int totprice){
    this._name = name;
    this._number = number;
    this._pieces = pieces;
    this._price = price;
    this._totprice = totprice;
}
// getting ID
public int getID(){
    return this._id;
}

// setting id
public void setID(int id){
    this._id = id;
}

public String getName(){
    return this._name;
}

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

public int getNumber(){
    return this._number;
}

public void setNumber(int number){
    this._number = number;
}

public int getPieces(){
    return this._pieces;
}

public void setPieces(int pieces){
    this._pieces = pieces;
}

public int getPrice(){
    return this._price;
}

public void setPrice(int price){
    this._price = price;
}

public int getTotPrice(){
    return this._totprice;
}

public void setTotPrice(int totprice){
    this._totprice = totprice;
}}

And this is the layout I want to use in my listview.

    <?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical" android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:paddingLeft="20dp"
    android:paddingTop="10dp"
    android:paddingRight="10dp"
    android:background="@android:color/white"
    android:paddingBottom="10dp">

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textAppearance="?android:attr/textAppearanceMedium"
        android:id="@+id/number"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:textColor="@android:color/black" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textAppearance="?android:attr/textAppearanceMedium"
        android:id="@+id/name"
        android:layout_alignBottom="@+id/number"
        android:layout_toRightOf="@+id/number"
        android:layout_toEndOf="@+id/number"
        android:layout_marginLeft="25dp"
        android:layout_marginStart="29dp"
        android:textColor="@android:color/black" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textAppearance="?android:attr/textAppearanceSmall"
        android:id="@+id/pieces"
        android:layout_below="@+id/name"
        android:layout_alignLeft="@+id/name"
        android:layout_alignStart="@+id/name"
        android:layout_marginLeft="1dp"
        android:layout_marginTop="5dp" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textAppearance="?android:attr/textAppearanceSmall"
        android:id="@+id/price"
        android:layout_below="@+id/pieces"
        android:layout_alignLeft="@+id/pieces"
        android:layout_alignStart="@+id/pieces" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textAppearance="?android:attr/textAppearanceMedium"
        android:id="@+id/totprice"
        android:layout_alignTop="@+id/name"
        android:layout_alignParentRight="true"
        android:layout_alignParentEnd="true"
        android:textColor="#ffe20c18" />
</RelativeLayout>

I correctly populate the db and read the content in the LogCat. Don't know how populate my listview. Tried to look at similar questions but found how to populate with a single information. I should already have most of code I need to let it work, I would not change the whole code to follow a sample tutorial. Can anyone help me? Any suggestion would be much appreciated. Thanks in advance.

Yes, show your Custom listView adapter. Then only we can help you :) — Ajeet
– Ajeet, Commented Mar 30, 2015 at 6:53
I don't know how to set my adapter in this case, this is my problem. Varun Madhvani answer should work but I miss the point in the middle. — Nonjoe
– Nonjoe, Commented Mar 30, 2015 at 8:59

score 2 · Accepted Answer · 2015-03-30 09:36:37Z

Create lists of Column names in your Cart Class class first,

public static ArrayList<String> itemIdList = new ArrayList<String>();
public static ArrayList<String> itemNameList = new ArrayList<String>();
public static ArrayList<String> itemQuantityList = new ArrayList<String>();
public static ArrayList<String> itemPriceList = new ArrayList<String>();

Call this class to fetch items from database and add into lists.

public void getItemsFromDatabase() {

        Cursor cursor = null;

            try{
                mdb=new DBCart(getApplicationContext(), DATABASE_NAME,null, DATABASE_VERSION);
                db=mdb.getReadableDatabase();

                cursor=db.rawQuery("select * from cart", null);

                cursor.moveToFirst();

                while (cursor.moveToNext()) {                       

                        Log.e("get", cursor.getString(1)+":"+cursor.getString(2)+":"+cursor.getString(3)+":"+cursor.getString(4));
                        //Here i added data directly to lists in Custom Cart Class
                        CartClass.itemIdList.add(cursor.getString(1));
                        CartClass.itemNameList.add(cursor.getString(2));
                        CartClass.itemQuantityList.add(cursor.getString(3));
                        CartClass.itemPriceList.add(cursor.getString(4));

                }
                cursor.close();
            }catch (SQLException e){
                Log.e("DB Error", e.toString());
                e.printStackTrace();
            }           
}

First Create custom Cart list using this code

class CustomCartList extends BaseAdapter {
    private LayoutInflater inflater;
    private SparseBooleanArray mSelectedItemsIds;

    public CustomCartList(Context context) {
        inflater = LayoutInflater.from(context);
        mSelectedItemsIds = new SparseBooleanArray();
    }

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

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

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

    @SuppressLint("InflateParams")
    public View getView(int position, View convertView, ViewGroup parent) {
        // TODO Auto-generated method stub
        ViewHolder holder;

        if(convertView == null){
            convertView = inflater.inflate(R.layout.cart_layout_row, null);
            holder = new ViewHolder();
            holder.itemName = (TextView) convertView.findViewById(R.id.itemName);
            holder.itemQuantity = (TextView) convertView.findViewById(R.id.itemQuantity);
            holder.itemPrice = (TextView) convertView.findViewById(R.id.itemPrice);

            convertView.setTag(holder);
        }else{
            holder = (ViewHolder) convertView.getTag();
        }


        holder.itemName.setText(Cart.itemNameList.get(position));
        holder.itemQuantity.setText(Cart.itemQuantityList.get(position));
        holder.itemPrice.setText(Cart.itemPriceList.get(position));

        return convertView;
    }

    static class ViewHolder {
        TextView itemName, itemQuantity, itemPrice;
    }

    public void toggleSelection(int position) {
        selectView(position, !mSelectedItemsIds.get(position));
    }

    public void selectView(int position, boolean value) {
        if (value)
            mSelectedItemsIds.put(position, value);
        else
            mSelectedItemsIds.delete(position);
        notifyDataSetChanged();
    }


    public int getSelectedCount() {
        return mSelectedItemsIds.size();
    }

    public SparseBooleanArray getSelectedIds() {
        return mSelectedItemsIds;
    }

    public void removeSelection() {
        mSelectedItemsIds = new SparseBooleanArray();
        notifyDataSetChanged();
    }
}

Create an adapter of CustomCartList Class using

CustomCartList adapter=new CustomCartList(YourActivity.this);

Than set list adapter using

list.setAdapter(adapter);

It should works but I miss the adapter, don't know how to set it and link to sqlite query result
It works great! Thank you for your time. Just need to setup small particulars, you solved a great issue for me.
To avoid another question: what if I would delete this list? db.deleteAll() works but delete my db, not this list and everytime I add a product it goes in the old list.

Collectives™ on Stack Overflow

Android populate custom ListView from SQLite database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related