java - How to Read Data from SQLite Database and show it on a list view

Question

How to pull Data from SQLite Database and show it on a list view.

I am doing this in my code, but I am getting an error:

public class ShoewDataListActivity extends ListActivity  {

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.grupadd);
        Button btnAdd = (Button) findViewById(R.id.btnAdd);
        Button view=(Button) findViewById(R.id.btnShowData);
        view.setOnClickListener(new OnClickListener() {

            public void onClick(View v) {
                displayResultList();
            }
        });
        btnAdd.setOnClickListener(new OnClickListener() {

            public void onClick(View v) {

                boolean didItWork = true;

                try {
                    EditText edtAddName=(EditText) findViewById(R.id.editNameAdd);
                    String name = edtAddName.getText().toString();
                    AddData entry = new AddData(ShoewDataListActivity.this);
                    entry.open();
                    entry.createEntry(name);
                    entry.close();
                } catch (Exception e) {
                    // e.printStackTrace();
                    didItWork = false;
                    String error = e.toString();
                    Dialog d = new Dialog(ShoewDataListActivity.this);
                    d.setTitle("What happend...!!");
                    TextView tv = new TextView(ShoewDataListActivity.this);
                    tv.setText("...." + error + "....");
                    d.setContentView(tv);
                    d.show();

                } finally {
                    if (didItWork) {
                        // Dialog d = new Dialog(GroupActivity.this);
                        // d.setTitle("This is ok..........!!");
                        // TextView tv= new TextView(GroupActivity.this);
                        // tv.setText("....SuccessFull....");
                        // d.setContentView(tv);
                        // d.show();
                        Toast.makeText(getBaseContext(), "Project Saved",
                                Toast.LENGTH_LONG).show();



                    }
                }
            }
        });

    }

    private void displayResultList() {
        TextView tView = new TextView(this);
        AddData info = new AddData(this);
        ArrayList<String> results = new ArrayList<String>();
        results = info.fatchData();
        tView.setText("This data is retrieved from the database and only 4 "
                + "of the results are displayed");
        getListView().addHeaderView(tView);
        setListAdapter(new ArrayAdapter<String>(this,
                android.R.layout.simple_list_item_1, results));
        getListView().setTextFilterEnabled(true);

    }


}

And my Dbhelper class is:

public class AddData {

    public static final String TAG = DbHelper.class.getSimpleName();
    public static final String DB_NAME = "Grup.db";
    public static final int DB_VERSION = 1;
    public static final String TABLE = "Grups";
    public static final String C_ID = BaseColumns._ID;
    public static final String C_CREATED_AT = "easy_ass_created_At";
    public static final String C_NAME = "name";
    private DbHelper ourHelper;
    private final Context ourContext;
    private SQLiteDatabase ourDatabase;

    public class DbHelper extends SQLiteOpenHelper {

        // public static final String TAG = DbHelper.class.getSimpleName();
        // public static final String DB_NAME = "Grup.db";
        // public static final int DB_VERSION = 1;
        // public static final String TABLE = "Grups";
        // public static final String C_ID = BaseColumns._ID;
        // public static final String C_CREATED_AT = "easy_ass_created_At";
        // public static final String C_NAME = "name";

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

        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            String sql = ("create table " + TABLE + " ( " + C_ID
                    + " integer primary key autoincrement, " + C_NAME
                    + " text not null" + ");");
            db.execSQL(sql);
            Log.d(TAG, "OnCreate sql" + sql);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("drop table if exists " + TABLE);
            this.onCreate(db);
            Log.d("TAG", "********On Upgrate Drop Table*****");

        }

    }

    public AddData(Context context) {
        ourContext = context;

    }

    public AddData open() throws SQLException {

        ourHelper = new DbHelper(ourContext);
        ourDatabase = ourHelper.getReadableDatabase();
        return this;
    }

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

    public long createEntry(String name) {
        ContentValues cv = new ContentValues();
        cv.put(C_NAME, name);
        return ourDatabase.insert(TABLE, null, cv);
    }

    public String getData() {

        String[] columns = new String[] { C_ID, C_NAME };
        Cursor c = ourDatabase.query(TABLE, columns, null, null, null, null,
                null);
        String result = "";
        // int iRow=c.getColumnIndex(C_ID);
        int iName = c.getColumnIndex(C_NAME);

        // for(c.moveToFirst();!c.isAfterLast();c.moveToLast()){
        for (boolean hasItem = c.moveToFirst(); hasItem; hasItem = c
                .moveToNext()) {
            result = result + "   " + c.getString(iName) + "
";
            c.moveToNext();
        }
        c.close();
        return result;

    }

    public ArrayList<String> fatchData(){

        String[] columns = new String[] { C_ID, C_NAME };
        Cursor c = ourDatabase.query(TABLE, columns, null, null, null, null,
                null);
        String result="";
        ArrayList<String> result1=new ArrayList<String>();
        // int iRow=c.getColumnIndex(C_ID);
        int iName = c.getColumnIndex(C_NAME);

        // for(c.moveToFirst();!c.isAfterLast();c.moveToLast()){
        for (boolean hasItem = c.moveToFirst(); hasItem; hasItem = c
                .moveToNext()) {

            result = "   " + c.getString(iName) + "
";
            result1.add(result);
            c.moveToNext();
        }
        c.close();
        return result1;


    }
}

Answer 1

Try this,

AndroidManifest.xml

 <?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
      package="com.collabera.labs.sai.db"
      android:versionCode="1"
      android:versionName="1.0">
    <application android:icon="@drawable/icon" android:label="@string/app_name">
        <activity android:name=".CRUDonDB"
                  android:label="@string/app_name">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />
                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>
    <uses-sdk android:minSdkVersion="3" />
</manifest> 

Activity File

    import java.util.ArrayList;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Bundle;
import android.util.Log;
import android.widget.ArrayAdapter;

public class CRUDonDB extends ListActivity {

    private final String SAMPLE_DB_NAME = "myFriendsDb";
    private final String SAMPLE_TABLE_NAME = "friends";

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


        ArrayList<String> results = new ArrayList<String>();
        SQLiteDatabase sampleDB = null;

        try {
            sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);

            sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                    SAMPLE_TABLE_NAME +
                    " (LastName VARCHAR, FirstName VARCHAR," +
                    " Country VARCHAR, Age INT(3));");

            sampleDB.execSQL("INSERT INTO " +
                    SAMPLE_TABLE_NAME +
                    " Values ('Makam','Sai Geetha','India',25);");
            sampleDB.execSQL("INSERT INTO " +
                    SAMPLE_TABLE_NAME +
                    " Values ('Chittur','Raman','India',25);");
            sampleDB.execSQL("INSERT INTO " +
                    SAMPLE_TABLE_NAME +
                    " Values ('Solutions','Collabera','India',20);");

            Cursor c = sampleDB.rawQuery("SELECT FirstName, Age FROM " +
                    SAMPLE_TABLE_NAME +
                    " where Age > 10 LIMIT 5", null);

            if (c != null ) {
                if  (c.moveToFirst()) {
                    do {
                        String firstName = c.getString(c.getColumnIndex("FirstName"));
                        int age = c.getInt(c.getColumnIndex("Age"));
                        results.add("" + firstName + ",Age: " + age);
                    }while (c.moveToNext());
                } 
            }

            this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));

        } catch (SQLiteException se ) {
            Log.e(getClass().getSimpleName(), "Could not create or Open the database");
        } finally {
            if (sampleDB != null) 
                sampleDB.execSQL("DELETE FROM " + SAMPLE_TABLE_NAME);
                sampleDB.close();
        }
    }
}