Using Listview and Sqlite

Question

I have downloaded an example of SQLite from the following link http://www.codegod.com/Android-ListView-with-dynamic-Images-AID590.aspx

SQLiteListActivity.java:

package com.codegod.android;

import java.util.ArrayList;

import android.app.ListActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ImageButton;
import android.widget.ListView;
import android.widget.AdapterView.OnItemClickListener;

public class SQLiteListActivity extends ListActivity implements View.OnClickListener {

    private StockAdapter tableRowAdapter;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        try {

            StocksManager stockMgr = new StocksManager(getApplicationContext());

            // clear the table
            stockMgr.clear();

            // Add values to database
            stockMgr.insert("APPL", "Apple Inc.");
            stockMgr.insert("GOOG", "Google Inc.");
            stockMgr.insert("LTSB", "Let's drink some beer");

            // get the values from database
            ArrayList<Stock> stocks = stockMgr.getStocks(); 

            tableRowAdapter = new StockAdapter(
                    getApplicationContext(), R.layout.table_row, stocks);

            setListAdapter(tableRowAdapter);


        } catch (Exception ex) {
            Log.e(ex.toString(), ex.toString());

        }

    }

    @Override
    public void onClick(View v) {
        ImageButton button = (ImageButton) v;
        Stock row = (Stock) button.getTag();

        tableRowAdapter.deleteRow(row);
        tableRowAdapter.notifyDataSetChanged();

    }
}

Stock.java

package com.codegod.android;

public class Stock {
    private String id;
    private String description;

    public Stock(String id, String description) {
        this.id = id;
        this.description = description;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getId() {
        return id;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public String getDescription() {
        return description;
    }

}

StockAdapter.java

package com.codegod.android;

import java.util.List;

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

/**
 * Adapter for Stock-objects 
 *
 */
public class StockAdapter extends BaseAdapter {

    private final List<Stock> rows;

    public StockAdapter(final Context context, final int itemResId,
            final List<Stock> items) {
        this.rows = items;
    }

    public int getCount() {
        return this.rows.size();
    }

    public Object getItem(int position) {
        return this.rows.get(position);
    }

    public long getItemId(int position) {
        return position;
    }

    /**
     * Set the content for a row here
     */
    public View getView(int position, View convertView, ViewGroup parent) {

        final Stock row = this.rows.get(position);
        View itemView = null;

        if (convertView == null) {
            LayoutInflater inflater = (LayoutInflater) parent.getContext()
                    .getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            itemView = inflater.inflate(R.layout.table_row, null);
        } else {
            itemView = convertView;
        }

        // Set the text of the row
        TextView txtId = (TextView) itemView.findViewById(R.id.rowId);
        txtId.setText(row.getId());

        TextView txtDesc = (TextView) itemView.findViewById(R.id.rowDesc);
        txtDesc.setText(row.getDescription());

        // Remember the row for each button so that we can refer to
        // it when the button is clicked
        ImageButton imgButton = (ImageButton) itemView.findViewById(R.id.icon);
        imgButton.setTag(row);

        return itemView;

    }

    /**
     * Delete a row from the list of rows
     * @param row row to be deleted
     */
    public void deleteRow(Stock row) {

        if(this.rows.contains(row)) {
            this.rows.remove(row);
        }
    }
}

StocksManager.java

package com.codegod.android;

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.util.Log;

import com.codegod.android.db.OpenDbHelper;

public class StocksManager {

    private OpenDbHelper dbHelper;

    private static final String STOCK_TABLE = "Stock";
    private static final String STOCK_ID = "stock_id";
    private static final String STOCK_DESC = "stock_desc";

    private static final String SELECT_STOCKS = "SELECT * FROM " + STOCK_TABLE;

    public StocksManager(Context context) {
        dbHelper = new OpenDbHelper(context, STOCK_TABLE, STOCK_ID + " TEXT,"
                + STOCK_DESC + " TEXT");
    }

    /**
     * Insert a Stock-value into database
     * @param stockId id of the stock
     * @param stockDesc description of the stock
     * @return success or fail
     */
    public boolean insert(String stockId, String stockDesc) {
        try {

            SQLiteDatabase sqlite = dbHelper.getWritableDatabase();

            ContentValues initialValues = new ContentValues();

            initialValues.put(STOCK_ID, stockId);
            initialValues.put(STOCK_DESC, stockDesc);

            sqlite.insert(STOCK_TABLE, null, initialValues);

        } catch (SQLException sqlerror) {

            Log.v("Insert into table error", sqlerror.getMessage());

            return false;
        }

        return true;

    }

    /**
     * Get all available stocks
     * @return List of stocks
     */
    public ArrayList<Stock> getStocks() {
        ArrayList<Stock> stocks = new ArrayList<Stock>();

        SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();

        Cursor crsr = sqliteDB.rawQuery(SELECT_STOCKS, null);

        crsr.moveToFirst();

        for (int i = 0; i < crsr.getCount(); i++)
        {
            stocks.add(new Stock(crsr.getString(0), crsr.getString(1)));

            crsr.moveToNext();
        }

        return stocks;
    }

    /**
     * Clear the table
     */
    public void clear() {

        try {

            SQLiteDatabase sqlite = dbHelper.getWritableDatabase();

            sqlite.delete(STOCK_TABLE, "", null);

        } catch (SQLException sqlerror) {

            Log.v("delete from table error", sqlerror.getMessage());

        }   

    }

}

OpenDBHelper.java

package com.codegod.android.db;

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

public class OpenDbHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 2;

    private String createStatement = "";

    /**
     * Creates the OpenDbHelper 
     * 
     * @param context app-context
     * @param tableName name of the table to open/create
     * @param fields fields of the table to create
     */
    public OpenDbHelper(Context context, String tableName, String fields) {

        super(context, tableName, null, DATABASE_VERSION);

        this.createStatement  = "CREATE TABLE ";
        this.createStatement += tableName + " (";
        this.createStatement += fields + ");";

    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int neVersion) {
        // TODO Auto-generated method stub
    }

}

Layout.xml:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent" android:layout_height="?android:attr/listPreferredItemHeight"
    android:padding="4dip">

    <TextView android:id="@+id/rowId" android:layout_width="fill_parent"
        android:layout_height="26dip" android:singleLine="true"
        android:textSize="20dip" android:textColor="#ffffff" android:text="Id..." />

    <TextView android:id="@+id/rowDesc" android:layout_width="fill_parent"
        android:text="Descr..." android:layout_height="20dip"
        android:textSize="12dip" android:layout_below="@+id/rowId"
        android:layout_alignParentBottom="true" android:textColor="#ffffff" />

    <ImageButton android:id="@+id/icon" android:layout_width="wrap_content"
        android:layout_height="wrap_content" android:src="@drawable/delete"
        android:gravity="right" android:layout_alignParentRight="true"
        android:onClick="onClick" />

</RelativeLayout>

which there is a listview connected to the database and also an image button to delete the rows, but I want to change it in order to save the delete results in my database, I mean the changes after deleting won't be saved now in this project and even when I pull it from the file explorer there are still three rows in my database, how should I change it? Please help me on this issue.

Answer 1

You need to write database method to remove row from sqlite database. Here is some code snippet

public void RemoveRow(String id){
    SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
    String query = "DELETE FROM Stock WHERE stock_id='"+id+"'";
    sqliteDB.execSQL(query);
}

And call this method at listview onClick method

@Override
public void onClick(View v) {
    StocksManager.RemoveRow();
}

You will need to initialize StockManager method;

Edit :

By doing so, StocksManager.java would be

package com.codegod.android;

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.util.Log;

import com.codegod.android.db.OpenDbHelper;

public class StocksManager {

    private OpenDbHelper dbHelper;

    private static final String STOCK_TABLE = "Stock";
    private static final String STOCK_ID = "stock_id";
    private static final String STOCK_DESC = "stock_desc";

    private static final String SELECT_STOCKS = "SELECT * FROM " + STOCK_TABLE;

    public StocksManager(Context context) {
        dbHelper = new OpenDbHelper(context, STOCK_TABLE, STOCK_ID + " TEXT,"
                + STOCK_DESC + " TEXT");
    }

    /**
     * Insert a Stock-value into database
     * @param stockId id of the stock
     * @param stockDesc description of the stock
     * @return success or fail
     */
    public boolean insert(String stockId, String stockDesc) {
        try {

            SQLiteDatabase sqlite = dbHelper.getWritableDatabase();

            ContentValues initialValues = new ContentValues();

            initialValues.put(STOCK_ID, stockId);
            initialValues.put(STOCK_DESC, stockDesc);

            sqlite.insert(STOCK_TABLE, null, initialValues);

        } catch (SQLException sqlerror) {

            Log.v("Insert into table error", sqlerror.getMessage());

            return false;
        }

        return true;

    }

    /**
     * Get all available stocks
     * @return List of stocks
     */
    public ArrayList<Stock> getStocks() {
        ArrayList<Stock> stocks = new ArrayList<Stock>();

        SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();

        Cursor crsr = sqliteDB.rawQuery(SELECT_STOCKS, null);

        crsr.moveToFirst();

        for (int i = 0; i < crsr.getCount(); i++)
        {
            stocks.add(new Stock(crsr.getString(0), crsr.getString(1)));

            crsr.moveToNext();
        }

        return stocks;
    }

    /**
     * Remove selected stock
     * @return Void
     */
    public void RemoveRow(String id){
        SQLiteDatabase sqliteDB = dbHelper.getReadableDatabase();
        String query = "DELETE FROM Stock WHERE stock_id='"+id+"'";
        sqliteDB.execSQL(query);
    }

    /**
     * Clear the table
     */
    public void clear() {

        try {

            SQLiteDatabase sqlite = dbHelper.getWritableDatabase();

            sqlite.delete(STOCK_TABLE, "", null);

        } catch (SQLException sqlerror) {

            Log.v("delete from table error", sqlerror.getMessage());

        }   

    }

}

And SQLiteListActivity.java:

package com.codegod.android;

import java.util.ArrayList;

import android.app.ListActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ImageButton;
import android.widget.ListView;
import android.widget.AdapterView.OnItemClickListener;

public class SQLiteListActivity extends ListActivity implements View.OnClickListener {

    private StockAdapter tableRowAdapter;
    private StocksManager stockMgr;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        try {

            //Initialize StockManager Database class
            stockMgr = new StocksManager(getApplicationContext());

            // clear the table
            stockMgr.clear();

            // Add values to database
            stockMgr.insert("APPL", "Apple Inc.");
            stockMgr.insert("GOOG", "Google Inc.");
            stockMgr.insert("LTSB", "Let's drink some beer");

            // get the values from database
            ArrayList<Stock> stocks = stockMgr.getStocks(); 

            tableRowAdapter = new StockAdapter(
                    getApplicationContext(), R.layout.table_row, stocks);

            setListAdapter(tableRowAdapter);


        } catch (Exception ex) {
            Log.e(ex.toString(), ex.toString());

        }

    }

    @Override
    public void onClick(View v) {
        ImageButton button = (ImageButton) v;
        Stock row = (Stock) button.getTag();

        tableRowAdapter.deleteRow(row);
        tableRowAdapter.notifyDataSetChanged();

        //Call DB Method to remove stock from database
        stockMgr.RemoveRow(row.getId());
    }
}

I haven't got a chance to test it out but I'm sure it will work fine.