Android: Populating ListView from SQLite database (multiple columns)

Question

I am developing an app in Android and have a problem with passing data from databse to listview and viewing it.

Here is my database class with all methods:

package com.example.admin.planesproject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.admin.planesproject.TodoTask;

public class PlanesDb {

private static final int DB_VERSION = 1;
private static final String DB_NAME = "database.db";
private static final String DB_TODO_TABLE = "PlanesDb";

//columns

public static final String KEY_ID = "_id";
public static final String ID_OPTIONS = "INTEGER PRIMARY KEY AUTOINCREMENT";
public static final int ID_COLUMN = 0;

public static final String KEY_DATE = "date";
public static final String DATE_OPTIONS = "TEXT NOT NULL";
public static final int DATE_COLUMN = 1;

public static final String KEY_DIRECTION = "direction";
public static final String DIRECTION_OPTIONS = "TEXT NOT NULL";
public static final int DIRECTION_COLUMN = 2;

public static final String KEY_MODEL = "model";
public static final String MODEL_OPTIONS = "TEXT";
public static final int MODEL_COLUMN = 3;

public static final String KEY_AIRLINES = "airlines";
public static final String AIRLINES_OPTIONS = "TEXT";
public static final int AIRLINES_COLUMN = 4;

public static final String KEY_FROM = "from";
public static final String FROM_OPTIONS = "TEXT";
public static final int FROM_COLUMN = 5;

public static final String KEY_TO = "to";
public static final String TO_OPTIONS = "TEXT";
public static final int TO_COLUMN = 6;

private static final String DB_CREATE_TODO_TABLE =
        "CREATE TABLE " + DB_TODO_TABLE + "( " +
                KEY_ID + " " + ID_OPTIONS + ", " +
                KEY_DATE + " " + DATE_OPTIONS + ", " +
                KEY_DIRECTION + " " + DIRECTION_OPTIONS + ", " +
                KEY_MODEL + " " + MODEL_OPTIONS + ", " +
                KEY_AIRLINES + " " + AIRLINES_OPTIONS + ", " +
                KEY_FROM + " " + FROM_OPTIONS + ", " +
                KEY_TO + " " + TO_OPTIONS + ");";

private static final String DROP_TODO_TABLE =
        "DROP TABLE IF EXISTS " + DB_TODO_TABLE;

private SQLiteDatabase db;
private Context context;
private DatabaseHelper dbHelper;

private static class DatabaseHelper extends SQLiteOpenHelper {
    public DatabaseHelper(Context context, String name,
                          SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_TODO_TABLE);
        onCreate(db);
    }
}

public PlanesDb(Context context) {
    this.context = context;
}

public PlanesDb open(){
    dbHelper = new DatabaseHelper(context, DB_NAME, null, DB_VERSION);
    db = dbHelper.getWritableDatabase();
    return this;
}

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

public long insertTodo(String date, String direction, String model, String airlines, String from, String to) {
    ContentValues newTodoValues = new ContentValues();
    newTodoValues.put(KEY_DATE, date);
    newTodoValues.put(KEY_DIRECTION, direction);
    newTodoValues.put(KEY_MODEL, model);
    newTodoValues.put(KEY_AIRLINES, airlines);
    newTodoValues.put(KEY_FROM, from);
    newTodoValues.put(KEY_TO, to);
    return db.insert(DB_TODO_TABLE, null, newTodoValues);
}

public boolean updateTodo(TodoTask task) {
    long id = task.getId();
    String date = task.getDate();
    String direction = task.getDirection();
    String model = task.getModel();
    String airlines = task.getAirlines();
    String from = task.getFrom();
    String to = task.getTo();
    return updateTodo(id, date, direction, model, airlines, from, to);
}

public boolean updateTodo(long id, String date, String direction, String model, String airlines, String from, String to) {
    String where = KEY_ID + "=" + id;
    ContentValues updateTodoValues = new ContentValues();
    updateTodoValues.put(KEY_DATE, date);
    updateTodoValues.put(KEY_DIRECTION, direction);
    updateTodoValues.put(KEY_MODEL, model);
    updateTodoValues.put(KEY_AIRLINES, airlines);
    updateTodoValues.put(KEY_FROM, from);
    updateTodoValues.put(KEY_TO, to);
    return db.update(DB_TODO_TABLE, updateTodoValues, where, null) > 0;
}

public boolean deleteTodo(long id){
    String where = KEY_ID + "=" + id;
    return db.delete(DB_TODO_TABLE, where, null) > 0;
}

public Cursor getAllTodos() {
    String[] columns = {KEY_ID, KEY_DATE, KEY_DIRECTION, KEY_MODEL, KEY_AIRLINES, KEY_FROM, KEY_TO};
    return db.query(DB_TODO_TABLE, columns, null, null, null, null, null);
}
}

I'd like to have a listview showing everything from my table, but I cannot grasp how to do it. Tutorials don't help much as most of them only shows how to work with one row database.

My ToDo Task with all getters and setters:

public class TodoTask {
private long id;
private String date;
private String direction;
private String model;
private String airlines;
private String from;
private String to;

public TodoTask(long id, String date, String direction, String model, String airlines, String from, String to) {
    this.id = id;
    this.date = date;
    this.direction = direction;
    this.model = model;
    this.airlines = airlines;
    this.from = from;
    this.to = to;
}

public long getId() {
    return id;
}

public void setId(long id) {
    this.id = id;
}

public String getDate(){
    return date;
}

public void setDate(String date){
    this.date = date;
}

public String getDirection(){
    return direction;
}

public void setDirection(String direction){
    this.direction = direction;
}

public String getModel(){
    return model;
}

public void setModel(String model){
    this.model = model;
}

public String getAirlines(){
    return airlines;
}

public void setAirlines(String airlines){
    this.airlines = airlines;
}

public String getFrom(){
    return from;
}

public void setFrom(String from){
    this.from = from;
}

public String getTo(){
    return to;
}

public void setTo(String to){
    this.to = to;
}

}

And finally my activity. This is how I populate listview from the array, I'd like something similar for my database.

import android.app.Activity;
import android.content.res.Resources;
import android.os.Bundle;
import android.widget.ArrayAdapter;
import android.widget.ListView;

public class DatabaseCheck extends Activity {

    private ListView lvPlanes;
    private String[] planes;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_database_check);
        lvPlanes = (ListView) findViewById(R.id.list_all);
        initResources();
        initListView();
    }

    private void initResources() {
        Resources res = getResources();
        planes = res.getStringArray(R.array.listofplanes);
    }

    private void initListView() {
        lvPlanes.setAdapter(new ArrayAdapter<String>(
                getBaseContext(),
                android.R.layout.simple_list_item_1,
                planes));
    }
}

I know I should open DB, populate the list and close DB. I understand I should build a custom adapter, but I have no idea how to do what I want. I tried doing something, but every attempt ended in an app crash. Can anyone help me? Any suggestion will be appreciated, thank you in advance.

Answer 1

Try this, define a CursorAdapter:

public class yourCursorAdapter extends CursorAdapter {
  public yourCursorAdapter(Context context, Cursor cursor) {
      super(context, cursor, 0);
  }

  @Override
  public View newView(Context context, Cursor cursor, ViewGroup parent) {
      return LayoutInflater.from(context).inflate(R.layout.item_todo, parent, false);
  }

  @Override
  public void bindView(View view, Context context, Cursor cursor) {

      // Find all fields to populate in inflated template
      // Example
      TextView tvDate = (TextView) view.findViewById(R.id.tvDate);

      // Extract all needed properties from cursor
      // Example
      String date = cursor.getString(cursor.getColumnIndexOrThrow("date"));

      // Populate all the required fields with extracted properties
      // Example
      tvDate.setText(date);

  }
}

Then, in your initListView set the adapter to the listview:

private void initListView() {
     DatabaseHelper handler = new DatabaseHelper(this);
     SQLiteDatabase db = handler.getWritableDatabase(); 
     Cursor cursor = db.rawQuery(YOUR_SQL_QUERY, null);  
     yourCursorAdapter adapter = new yourCursorAdapter(this, cursor);
     lvPlanes.setAdapter(adapter);
}

Hope it helps