Loading data from an SQLite database into a ListView

Question

I'm trying to get some data from my SQLite database into my ListView. Right now, the data from my database is loading into a Toast when the app is started so I believe I'm on the right track. I'm just unable to put the data into the ListView. I've already looked at some tutorials but I'm still unsure. Here is my OverviewActivity.java class:

public class OverviewActivity extends ListActivity implements View.OnClickListener {

private DBAdapter dbAdapter;

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


    dbAdapter = new DBAdapter(this);
    dbAdapter.open();

    Cursor c = dbAdapter.getAllValues();


    if (c.moveToFirst()) {
        do {
            DisplayTitle(c);
        } while (c.moveToNext());
    }

}

public void DisplayTitle(Cursor c) {

    Toast.makeText(this,
                    "id: " + c.getString(0) + "\n" +
                    "Number: " + c.getString(1) + "\n" +
                    "Name: " + c.getString(2) + "\n" +
                    "Version:  " + c.getString(3) + "\n" +
                    "Method: " + c.getString(4) + "\n" +
                    "Chain Number: " + c.getString(5) + "\n" +
                    "Caught: " + c.getString(6) + "\n",
            Toast.LENGTH_LONG).show();
}

@Override
public void onClick(View view) {
    //TODO: AUTOGENERATED METHOD
}

@Override
public void onDestroy(){
    super.onDestroy();
    dbAdapter.close();
}

@Override
public boolean onCreateOptionsMenu(Menu menu){
    MenuInflater inflater = getMenuInflater();

    inflater.inflate(R.menu.menu, menu);
    return super.onCreateOptionsMenu(menu);
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
    switch (item.getItemId()) {
        case R.id.menu_add:
            Intent intent = new Intent(OverviewActivity.this, VersionMethodActivity.class);
            startActivity(intent);
            break;
    }
    return false;
}
}

Here is my DBAdapter.java class:

public static final String KEY_ID = "_id";
public static final String KEY_NUMBER = "number";
public static final String KEY_NAME = "name";
public static final String KEY_VERSION = "version";
public static final String KEY_METHOD = "method";
public static final String KEY_CHAIN_NUMBER = "chainNumber";
public static final String KEY_CAUGHT = "caught";
private static final String TAG = "DBAdapter";

private static final String DATABASE_NAME = "Chaining";
private static final String DATABASE_TABLE = "ValuesTable";
private static final int DATABASE_VERSION = 1;

private static final String DATABASE_CREATE =
        "create table " + DATABASE_TABLE + "( " +
                KEY_ID + " integer primary key autoincrement, " +
                KEY_NUMBER + " integer, " +
                KEY_NAME + " text, " +
                KEY_VERSION + " text, " +
                KEY_METHOD + " text, " +
                KEY_CHAIN_NUMBER + " integer, " +
                KEY_CAUGHT + " boolean)";

private final Context context;

private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DBAdapter (Context ctx){
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper
{
    DatabaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion,
                          int newVersion)
    {
        Log.w(TAG, "Upgrading database from version " + oldVersion
                + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS titles");
        onCreate(db);
    }
}


public DBAdapter open() throws SQLException{
    db = DBHelper.getWritableDatabase();
    return this;
}

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

public long insertValues(int number, String name, String version, String method, int chainNumber, boolean caught){
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NUMBER, number);
    initialValues.put(KEY_NAME, name);
    initialValues.put(KEY_VERSION, version);
    initialValues.put(KEY_METHOD, method);
    initialValues.put(KEY_CHAIN_NUMBER, chainNumber);
    initialValues.put(KEY_CAUGHT, caught);

    return db.insert(DATABASE_TABLE, null, initialValues);
}

public boolean deleteValues(long rowId){
    return db.delete(DATABASE_TABLE, KEY_ID + " = " + rowId, null) > 0;
}

public Cursor getAllValues(){
    return db.query(DATABASE_TABLE, new String[]{
            KEY_ID,
            KEY_NUMBER,
            KEY_NAME,
            KEY_VERSION,
            KEY_METHOD,
            KEY_CHAIN_NUMBER,
            KEY_CAUGHT},
            null, null, null, null, null);
    }

public Cursor getValues(long rowId) throws SQLException {
    Cursor mCursor =
            db.query(true, DATABASE_TABLE, new String[]{
                            KEY_ID,
                            KEY_NUMBER,
                            KEY_NAME,
                            KEY_VERSION,
                            KEY_METHOD,
                            KEY_CHAIN_NUMBER,
                            KEY_CAUGHT},
                    KEY_ID + " = " + rowId,
                    null, null, null, null, null);

    if (mCursor != null){
        mCursor.moveToFirst();
    }
    return mCursor;
}

public boolean updateValues(long rowId, int number, String name, String version, String method, int chainNumber, boolean caught){
    ContentValues args = new ContentValues();
    args.put(KEY_NUMBER, number);
    args.put(KEY_NAME, name);
    args.put(KEY_VERSION, version);
    args.put(KEY_METHOD, method);
    args.put(KEY_CHAIN_NUMBER, chainNumber);
    args.put(KEY_CAUGHT, caught);

    return db.update(DATABASE_TABLE , args, KEY_ID + " = " + rowId, null) > 0;
}
}

Any suggestions on how to achieve this?

Answer 1

Assuming that you are using TextViews for each of your columns in your database, you can use the following code within the activity that loads up the ListView:

DBAdapter db = new DBAdapter(this);
Cursor cursor = db.getAllValues();

// The columns that you want to bind
String[] columns = new String[] {
    db.KEY_NUMBER,
    db.KEY_NAME,
    db.KEY_VERSION,
    db.KEY_METHOD,
    db.KEY_CHAIN_NUMBER,
    db.KEY_CAUGHT
};

// The IDs of the TextViews you want to bind the columns with
int[] to = new int[] { 
    R.id.number,
    R.id.name,
    R.id.version,
    R.id.method,
    R.id.chain_number,
    R.id.caught
};

// Attach the layout file and the columns to the ListView
SimpleCursorAdapter adapter = new SimpleCursorAdapter(
    this, 
    R.layout.your_listview_layout, 
    cursor, 
    columns, 
    to,
    0);

ListView listView = (ListView) findViewById(R.id.your_listview);
// Assign the adapter to the ListView
listView.setAdapter(dataAdapter);

Makes sure you have a layout file for your ListView. You may need to change this code to suit your needs. If you have any queries, please comment below.