Simple display of data from SQlite database

Question

I am trying to find a simple example of displaying data from an SQLite database in a layout. I tried to look at the "Notebook" example but it only has one output column. Is there an example out there with multiple output columns and, if possible, integer data in some of the columns?

Answer 1

theblitz,

You may want to consider creating your own customer Adapter. For my project I created a custom CursorAdapter. The result was such:

.

Here is my code for the custom CursorAdapter if you would like to use it as an example:

public class ItemAdapter extends CursorAdapter {
    private LayoutInflater mLayoutInflater;
    private Context mContext;
    public ItemAdapter(Context context, Cursor c) {
        super(context, c);
        mContext = context;
        mLayoutInflater = LayoutInflater.from(context); 
    }

    @Override
    public View newView(Context context, Cursor cursor, ViewGroup parent) {
        View v = mLayoutInflater.inflate(R.layout.items_row, parent, false);
        return v;
    }

    /**
     * @author will
     * 
     * @param   v
     *          The view in which the elements we set up here will be displayed.
     * 
     * @param   context
     *          The running context where this ListView adapter will be active.
     * 
     * @param   c
     *          The Cursor containing the query results we will display.
     */

    @Override
    public void bindView(View v, Context context, Cursor c) {
        String title = c.getString(c.getColumnIndexOrThrow(ItemDbAdapter.KEY_TITLE));
        String date = c.getString(c.getColumnIndexOrThrow(ItemDbAdapter.KEY_DATE));
        String imagePath = c.getString(c.getColumnIndexOrThrow(ItemDbAdapter.KEY_IMG));
        String reminder = c.getString(c.getColumnIndexOrThrow(ItemDbAdapter.KEY_REMINDER));
        int deletion = c.getInt(c.getColumnIndexOrThrow(ItemDbAdapter.KEY_DELETION));
        int priority = c.getInt(c.getColumnIndexOrThrow(ItemDbAdapter.KEY_PRIORITY));

        /**
         * Next set the title of the entry.
         */

        TextView title_text = (TextView) v.findViewById(R.id.item_text);
        if (title_text != null) {
            title_text.setText(title);
        }

        setPriorityColor(title_text, priority);

        /**
         * Set Date
         */

        TextView date_text = (TextView) v.findViewById(R.id.item_date);
        if (date_text != null) {
            date_text.setText(date);
        }       

        /**
         * Decide if we should display the paper clip icon denoting image attachment
         */

        ImageView item_image = (ImageView) v.findViewById(R.id.item_attachment);
        item_image.setVisibility(ImageView.INVISIBLE);
        if (imagePath != null && imagePath.length() != 0 && item_image != null) {
            item_image.setVisibility(ImageView.VISIBLE);
        }

        /**
         * Decide if we should display the deletion indicator
         */
        ImageView del_image = (ImageView) v.findViewById(R.id.item_deletion);
        del_image.setVisibility(ImageView.INVISIBLE);
        if (deletion == 1) {
            del_image.setVisibility(ImageView.VISIBLE);
        }

        /**
         * Decide if we should display the reminder indicator
         */
        ImageView rem_image = (ImageView) v.findViewById(R.id.item_reminder);
        rem_image.setVisibility(ImageView.INVISIBLE);
        if(reminder != null && reminder.length() != 0 && rem_image != null) {
            rem_image.setVisibility(ImageView.VISIBLE);
        }
    }

    /**
     * Set the priority colors based on the SharedPreferences
     * 
     * @author will
     * 
     * @param   title 
     *          The particular TextView item we are handling
     * @param   priority 
     *          The current TextView item's associated priority level
     */

    private void setPriorityColor(TextView title, int priority) {
        SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(mContext);
        Resources res = mContext.getResources();

        switch(priority) {
        case ItemDbAdapter.PRIORITY_HIGH:
            title.setTextColor(prefs.getInt("highColor", res.getColor(R.color.high_priority)));
            break;
        case ItemDbAdapter.PRIORITY_NORMAL:
            title.setTextColor(prefs.getInt("normColor", res.getColor(R.color.norm_priority)));
            break;
        case ItemDbAdapter.PRIORITY_LOW:
            title.setTextColor(prefs.getInt("lowColor", res.getColor(R.color.low_priority)));
            break;
        }
    }
}

and the xml for the individual items:

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal"
    android:background="@drawable/list_bg">

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="vertical">

        <TextView android:id="@+id/item_text"
            android:layout_width="200dp"
            android:layout_height="wrap_content"
            android:lines="1"
            android:scrollHorizontally="true"
            android:ellipsize="end"
            android:paddingLeft="2sp"
            android:paddingTop="2sp"
            android:textSize="18sp"
            android:textStyle="bold"
            android:shadowColor="#90909090"
            android:shadowDx="1.0"
            android:shadowDy="1.0"
            android:shadowRadius="1.0"/>

        <TextView android:id="@+id/item_date"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textSize="12sp"
            android:textColor="#FF808080"
            android:paddingLeft="2sp"
            android:paddingTop="2sp"/>
    </LinearLayout>

    <ImageView android:id="@+id/item_deletion"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:src="@drawable/deletion"
        android:visibility="invisible"
        android:layout_centerVertical="true"
        android:layout_alignParentRight="true"
        android:paddingRight="5sp"/>

    <ImageView android:id="@+id/item_attachment"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:src="@drawable/attachment"
        android:visibility="invisible"
        android:layout_centerVertical="true"
        android:layout_toLeftOf="@id/item_deletion"/>

    <ImageView android:id="@+id/item_reminder"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:src="@drawable/alarm"
        android:visibility="invisible"
        android:layout_centerVertical="true"
        android:layout_toLeftOf="@id/item_attachment"/>

</RelativeLayout>

Here is the code from the Main Activity: I call this function to display the cursor using my custom adapter:

private void fillData(String sortOrder) {
    Cursor itemsCursor = mDbHelper.fetchAllItems(sortOrder);
    startManagingCursor(itemsCursor);

    ItemAdapter itemAdapter = new ItemAdapter(this, itemsCursor);
    setListAdapter(itemAdapter);
    itemAdapter = null;
}

The XML for the main Activity: the top relative layout is for the action bar

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_height="fill_parent"
    android:layout_width="fill_parent"
    android:orientation="vertical">

    <RelativeLayout
        android:layout_width="fill_parent"
        android:layout_height="40dip"
        android:background="@drawable/action_bar">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/app_name"
            android:textColor="#FFFFFF"
            android:textSize="20sp"
            android:textStyle="bold"
            android:shadowColor="#B0B0B0B0"
            android:shadowDx="2.0"
            android:shadowDy="2.0"
            android:shadowRadius="2.0"
            android:layout_centerVertical="true"
            android:paddingLeft="10dip"/>

        <ImageView
            android:drawable="@drawable/action_bar_add"
            android:id="@+id/action_bar_add"
            android:layout_height="fill_parent"
            android:layout_width="wrap_content"
            android:layout_alignParentRight="true"
            android:layout_centerVertical="true"
            android:paddingRight="5dip"
            android:paddingLeft="5dip"
            android:src="@drawable/action_bar_add_anim"/>

        <View
            android:layout_height="fill_parent"
            android:layout_width="2px"
            android:layout_toLeftOf="@id/action_bar_add"
            android:background="#90909090"/>

</RelativeLayout>

<LinearLayout
    android:orientation="horizontal"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">

    <ListView android:id="@id/android:list"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"/>

    <TextView android:id="@+id/android:empty"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:gravity="center"
        android:text="@string/no_items"
        android:textSize="20sp"
        android:textStyle="bold"
        android:shadowColor="#90909090"
        android:shadowDx="1.0"
        android:shadowDy="1.0"
        android:shadowRadius="1.0"/>

</LinearLayout>
</LinearLayout>