A simple example using Android's SQLite database, exposes data from Cursor to a ListView.

A simple example using Android's SQLite database, exposes data from Cursor to a ListView.

In previous exercise "A simple example using Android's SQLite database", the result of queue was presented as string. It's going to be modified to exposes data from Cursor to a ListView widget.



Modify SQLiteAdapter.java to include a column of "_id" in our database, also include it in the queue. Modify the return of queueAll() to Cursor. Refer to the article "column '_id' does not exist".

package com.exercise.AndroidSQLite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class SQLiteAdapter {

public static final String MYDATABASE_NAME = "MY_DATABASE";
public static final String MYDATABASE_TABLE = "MY_TABLE";
public static final int MYDATABASE_VERSION = 1;
public static final String KEY_ID = "_id";
public static final String KEY_CONTENT = "Content";

//create table MY_DATABASE (ID integer primary key, Content text not null);
private static final String SCRIPT_CREATE_DATABASE =
"create table " + MYDATABASE_TABLE + " ("
+ KEY_ID + " integer primary key autoincrement, "
+ KEY_CONTENT + " text not null);";

private SQLiteHelper sqLiteHelper;
private SQLiteDatabase sqLiteDatabase;

private Context context;

public SQLiteAdapter(Context c){
context = c;
}

public SQLiteAdapter openToRead() throws android.database.SQLException {
sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
sqLiteDatabase = sqLiteHelper.getReadableDatabase();
return this;
}

public SQLiteAdapter openToWrite() throws android.database.SQLException {
sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
sqLiteDatabase = sqLiteHelper.getWritableDatabase();
return this;
}

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

public long insert(String content){

ContentValues contentValues = new ContentValues();
contentValues.put(KEY_CONTENT, content);
return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);
}

public int deleteAll(){
return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null);
}

public Cursor queueAll(){
String[] columns = new String[]{KEY_ID, KEY_CONTENT};
Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns,
  null, null, null, null, null);

return cursor;
}

public class SQLiteHelper extends SQLiteOpenHelper {

public SQLiteHelper(Context context, String name,
  CursorFactory factory, int version) {
 super(context, name, factory, version);
}

@Override
public void onCreate(SQLiteDatabase db) {
 // TODO Auto-generated method stub
 db.execSQL(SCRIPT_CREATE_DATABASE);
}

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

}

}

}

Create /res/layout/row.xml for our ListView

<?xml version="1.0" encoding="utf-8"?>
<TextView xmlns:android="http://schemas.android.com/apk/res/android"
android:id="@+id/text"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:padding="10dip"/>

Modify main.xml to change the result(contentlist) from TextView to ListView.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  android:orientation="vertical"
  android:layout_width="fill_parent"
  android:layout_height="fill_parent"
  >
<TextView
  android:layout_width="fill_parent"
  android:layout_height="wrap_content"
  android:text="@string/hello"
  />
<ListView
android:id="@+id/contentlist"
android:layout_width="fill_parent"
android:layout_height="fill_parent"/>
</LinearLayout>

Finally, modify AndroidSQLite.java

package com.exercise.AndroidSQLite;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class AndroidSQLite extends Activity {

private SQLiteAdapter mySQLiteAdapter;

  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.main);
      ListView listContent = (ListView)findViewById(R.id.contentlist);
    
      /*
       *  Create/Open a SQLite database
       *  and fill with dummy content
       *  and close it
       */
      mySQLiteAdapter = new SQLiteAdapter(this);
      mySQLiteAdapter.openToWrite();
      mySQLiteAdapter.deleteAll();

      mySQLiteAdapter.insert("A for Apply");
      mySQLiteAdapter.insert("B for Boy");
      mySQLiteAdapter.insert("C for Cat");
      mySQLiteAdapter.insert("D for Dog");
      mySQLiteAdapter.insert("E for Egg");
      mySQLiteAdapter.insert("F for Fish");
      mySQLiteAdapter.insert("G for Girl");
      mySQLiteAdapter.insert("H for Hand");
      mySQLiteAdapter.insert("I for Ice-scream");
      mySQLiteAdapter.insert("J for Jet");
      mySQLiteAdapter.insert("K for Kite");
      mySQLiteAdapter.insert("L for Lamp");
      mySQLiteAdapter.insert("M for Man");
      mySQLiteAdapter.insert("N for Nose");
      mySQLiteAdapter.insert("O for Orange");
      mySQLiteAdapter.insert("P for Pen");
      mySQLiteAdapter.insert("Q for Queen");
      mySQLiteAdapter.insert("R for Rain");
      mySQLiteAdapter.insert("S for Sugar");
      mySQLiteAdapter.insert("T for Tree");
      mySQLiteAdapter.insert("U for Umbrella");
      mySQLiteAdapter.insert("V for Van");
      mySQLiteAdapter.insert("W for Water");
      mySQLiteAdapter.insert("X for X'mas");
      mySQLiteAdapter.insert("Y for Yellow");
      mySQLiteAdapter.insert("Z for Zoo");
    
      mySQLiteAdapter.close();

      /*
       *  Open the same SQLite database
       *  and read all it's content.
       */
      mySQLiteAdapter = new SQLiteAdapter(this);
      mySQLiteAdapter.openToRead();

      Cursor cursor = mySQLiteAdapter.queueAll();
      startManagingCursor(cursor);

      String[] from = new String[]{SQLiteAdapter.KEY_CONTENT};
      int[] to = new int[]{R.id.text};

      SimpleCursorAdapter cursorAdapter =
       new SimpleCursorAdapter(this, R.layout.row, cursor, from, to);

      listContent.setAdapter(cursorAdapter);
    
      mySQLiteAdapter.close();

    
  }
}