Thursday, June 2, 2011

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.

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

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();

    
  }
}


download filesDownload the files.

next:
- Add data to SQLite database, with SimpleCursorAdapter updated dynamically

16 comments:

Leyo said...

An excellent example. Thanks!! Say, what would be the best way of display a multi-column table on a listview?

Ravi Kiran said...

Please expailn what exactly this part of the code doing

String[] from = new String[]{DBAdapter.ContactName};
int[] to = new int[]{R.id.text};

SimpleCursorAdapter cursorAdapter =
new SimpleCursorAdapter(this, R.layout.rows, c, from, to);

Erik said...

Please refer to the (deprecated) constructor SimpleCursorAdapter (Context context, int layout, Cursor c, String[] from, int[] to).

The main point is we have to provide String[] from and int[] to.

Erik said...

String[] from: A list of column names representing the data to bind to the UI. Can be null if the cursor is not available yet.

int[] to: The views that should display column in the "from" parameter. These should all be TextViews. The first N views in this list are given the values of the first N columns in the from parameter. Can be null if the cursor is not available yet.

Anonymous said...

I want to know how onclicklistener can be added with this code without changing much.. please reply soon as possible..

Erik said...

hello Anonymous,

In the post Retrieve individual items in cursor, I have implemented ListView.OnItemClickListener() to handle user touch on list item.

THE PROTAGONIST said...

Thank you it helps a lot for me

Zegarki said...

Your blog is great help for me. I`m student and write project for pass my lesson. Thank you!

Unknown said...

I love you Man <3

Anonymous said...

Hello, I could make it work on my app, but I found some issues I wasn't able to solve.

In my table I have this columns:
_id, name (text), lastcontact (text), city (text), age (integer), rating (real) and thumb_url (text).

First issue:
I want to add some text on each field after the data on the table.
Example: Age is 21. I want to write on the field age "21 years old". How can I include the " years old" text when filling the list? This is for multilanguage purposes.

Second issue:
The Cursor tries to assign the value of rating (Real) to my RatingBar but it fails.
How can I assign this?

The last one (similar to second issue):
The last field "thumb_url" (text) is a string with the URL of the Thumb Image of the contact, and I need to get it and process it with my ImageLoader class.
Any idea?

Thanks a lot, your blog is saving too much time to me.
Herni_Hdez

virus said...

How i fetch record id from sqlite database in androi....???? i am using listview for displaying data from the database

Erik said...

hello tapi multani,

please read nex exercise Add data to SQLite database, with SimpleCursorAdapter updated dynamically, it have ID displayed in ListView.

hiphopman said...

from ...

JSONObject jsonem = new JSONObject(json_etc);
JSONArray employee = jsonem.getJSONArray("employees");
myarrayemployees=new String[employee.length()][1];
for(int ctr=0;ctr<myarrayemployees.length;ctr++)
{
JSONObject data = employee.getJSONObject(ctr);
myarrayemployees[ctr][0] = data.getString("nickname");

}

I'm want get 2 dimensional array
Outside the function .

Unknown said...

how to retrieve the particular data from database using your example

Erik said...

hello Binu Paul,

Please follow the next link, then next link, here Retrieve individual items in cursor.

orange_18 said...

how to make it like if i clicked one of the list it will go to another activity containing the description of the item i clicked