Android SQLite: Retrieving a list of all data of a table

Question

Actually I can do a loop and log in console all rows of my table:

    db.getAllPoids();
    List<Poids> poids = db.getAllPoids();

    for (Poids val : poids) {
        String log = "Id: " + val.getId() + " ,Date: " + val.getDate_enr() + " ,Poids: " + val.getPoids() + " ,Evolution: " + val.getEvolution() ;
        // Writing Contacts to log
        Log.d("Name: ", log);
    }

DatabaseHandler:

public List<Poids> getAllPoids() {
    List<Poids> poidsList = new ArrayList<Poids>();
    // Select All Query
    String selectQuery = "SELECT * FROM " + TABLE_POIDS;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Poids poid = new Poids();
            poid.setId(Integer.parseInt(cursor.getString(0)));
            poid.setPoids(Integer.parseInt(cursor.getString(1)));
            poid.setDate_enr(cursor.getString(2));
            poid.setEvolution(cursor.getString(3));
            poid.setId_utilisateurs(Integer.parseInt(cursor.getString(4)));
            // Adding contact to list
            poidsList.add(poid);
        } while (cursor.moveToNext());
    }

But now Iwan't to do a better view, I need something like a table or liste, I know that they're listview exemple on google, but not with this method that I use.

And in my view I need to have3 rows: get the date in the first, the row "poids" in the second and n image view containing the id to delete the row on click. It is possible ? I don't know how to do.

PoidsAdapter:

public class PoidsAdapter extends ArrayAdapter<Poids> {
    private Context mContext;
    private List<Poids> mListPoids;

    public PoidsAdapter(Context context, int resource, List<Poids> objects) {
        super(context, resource, objects);
        this.mContext = context;
        this.mListPoids = objects;
    }


    @Override
    public int getCount() {
        return mListPoids.size();
    }

    @Override
    public Poids getItem(int position) {
        return mListPoids.get(position);
    }

    @Override
    public View getView(final int position, View view, final ViewGroup parent) {
        final holder holder;
            LayoutInflater inflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
            view = inflater.inflate(R.layout.listpoids, null);
            holder = new holder();
            holder.mTvTitle = (TextView) view.findViewById(R.id.textViewDate);
            holder.mTvMediaName = (TextView) view.findViewById(R.id.textViewPoids);
            holder.mImageUrl = (Button) view.findViewById(R.id.buttonSupprimer);
    return view;
    }

    public class holder {
        public Button mImageUrl;
        public TextView mTvTitle;
        public TextView mTvMediaName;
    }
}

UPDATE 2:

I put a text view hidden to keep the id:

@Override
public View getView(final int position, View view, final ViewGroup parent) {
    final ViewHolder holder;
        LayoutInflater inflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        view = inflater.inflate(R.layout.listpoids, null);
        holder = new ViewHolder();
        holder.mTvTitle = (TextView) view.findViewById(R.id.textViewDate);
    holder.mTvMediaName = (TextView) view.findViewById(R.id.textViewPoids);
    holder.poidsId = (TextView) view.findViewById(R.id.textViewPoidsId);
        holder.mImageUrl = (Button) view.findViewById(R.id.buttonSupprimer);
        Poids poids = mListPoids.get(position);
        holder.mTvTitle.setText(poids.getDate_enr().toString());
    holder.mTvMediaName.setText(String.valueOf(poids.getPoids()).toString() + "kg");
    holder.poidsId.setText(String.valueOf(poids.getId()).toString());


    return view;
}

Fragment:

public class MonPoidsFragment extends Fragment {

    DatabaseHandler db = new DatabaseHandler(getActivity());

    public MonPoidsFragment(){}

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        //View rootView = inflater.inflate(R.layout.accueil_activity, container, false);
        View view = inflater.inflate(R.layout.monpoids_activity, container, false);

        final ListView listView=(ListView) view.findViewById(R.id.listView);
        Button  buttonAjouter = (Button)view.findViewById(R.id.buttonAjouter);
        Button  buttonSupprimer = (Button)view.findViewById(R.id.buttonSupprimer);

        db = new DatabaseHandler(getActivity());

        db.getAllPoids();
        final List<Poids> poids = db.getAllPoids();


        PoidsAdapter  mAdapter = new PoidsAdapter(getActivity(), R.layout.listpoids, poids);
        listView.setAdapter(mAdapter);
        Log.d("getCount(): ", "" + mAdapter.getCount());

        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

            @Override
            public void onItemClick(final AdapterView<?> parent, View view, final int position, long id) {

                poids.remove(position);
                db.deletePoids(position);

            }
        });


        buttonSupprimer.setOnClickListener(
                listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                                                    @Override
                                                    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                                                        poids.remove(position);
                                                        db.deletePoids(position);
                                                    }
                                                })});

        buttonAjouter.setOnClickListener(
                new View.OnClickListener() {
                    @Override
                    public void onClick(View v) {
                        Fragment fragment = null;
                        fragment = new AjoutPoidsFragment();
                        FragmentManager fragmentManager = getFragmentManager();
                        fragmentManager.beginTransaction().replace(R.id.frame_container, fragment).commit();
                    }
                });


        return view;
    }
}

Database:

public void deletePoids(int rowID) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_POIDS, KEY_ID + " =? ", new String[]{String.valueOf(rowID)});
}

monpoids_activity:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent" android:layout_height="fill_parent"
    android:focusable="true"
    android:focusableInTouchMode="true"
    android:descendantFocusability="beforeDescendants"
    android:background="#ffffff"
    android:orientation="vertical">

    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="vertical"
        android:layout_width="fill_parent" android:layout_height="fill_parent"
        android:layout_alignParentLeft="true"
        android:layout_alignParentRight="true"
        android:padding="10dp">


        <Button
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Ajouter un poids"
            android:id="@+id/buttonAjouter"
            android:background="#70cbed"
            android:textColor="#ffffff"
            android:textAlignment="center"
            android:layout_marginTop="20dp" />

        <ListView

            android:layout_width="fill_parent"
            android:layout_height="fill_parent"
            android:id="@+id/listView" />

    </LinearLayout>

</RelativeLayout>

listpoids.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="horizontal"
    android:background="#bbd3dc">

    <TextView
        android:id="@+id/textViewDate"
        android:layout_gravity="center"
        android:layout_width="0.7in"
        android:layout_height="wrap_content"
        android:text="TextView"
        android:padding="10dp" />

    <TextView
        android:layout_gravity="center"
        android:layout_width="0.7in"
        android:layout_height="wrap_content"
        android:text="New Text"
        android:id="@+id/textViewPoids"
        android:padding="10dp" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/textViewPoidsId"
        android:text="hidden"
        android:visibility="gone"/>

    <Button
        android:layout_gravity="center"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Supprimer"
        android:id="@+id/buttonSupprimer" />

</LinearLayout>

You still can use `ListView`, build your custom row layout and your custom Adapter. — Jonathan Darryl, May 04 '16 at 03:22
@ccsnoopy can I use my method with getAllPoids(); for (Poids val : poids) {} ? I don't see exemples with this method — BJM, May 04 '16 at 03:24
You might want to check this out: http://stackoverflow.com/questions/16333754/how-to-customize-listview-using-baseadapter — Jonathan Darryl, May 04 '16 at 03:26
Have you ever used Realm, it is easier than SQLite Database: https://realm.io/docs/java/latest/ — Khang .NT, May 04 '16 at 03:29

Nguyễn Trung Hiếu · Accepted Answer · 2016-05-04T09:37:37.833

add method to DatabaseHandler

   public static void delete(int rowID) {
    SQLiteDatabase database = this.getWritableDatabase();
    database.delete(TABLE_POIDS, COL_ID + " =? ", new String[]{String.valueOf(rowID)});
   }

in listview.setOnItemClickListener

@Override
public void onItemClick(final AdapterView<?> parent, View view, final int position, long id) {
     Poids poids= mAdapter.getItem(position);
     int id = poids.getId();
     poids.remove(position);
     db.delete(id);
      mAdapter.notifyDataSetChanged();

}

Create PoidsAdapter.class

public class PoidsAdapter extends ArrayAdapter<Poids> {
private Context mContext;
private List<Poids> mListPoids;

public PoidsAdapter(Context context, int resource, List<Poids> objects) {
    super(context, resource, objects);
    this.mContext = context;
    this.mListPoids = objects;
}


@Override
public int getCount() {
    return mListPoids.size();
}

@Override
public Poids getItem(int position) {
    return mListPoids.get(position);
}

@Override
public View getView(final int position, View view, final ViewGroup parent) {
    final ViewHolder holder;
    if (view == null) {
        LayoutInflater inflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        view = inflater.inflate(R.layout.item_poids, null);
        holder = new ViewHolder();
        holder.mTvTitle = (TextView) view.findViewById(R.id.text_title);
        holder.mTvMediaName = (TextView) view.findViewById(R.id.text_mediaName);
        holder.mImageUrl = (ImageView) view.findViewById(R.id.image_url);
        view.setTag(holder);
    } else {
        holder = (ViewHolder) view.getTag();
    }
    final Poids poids = mListPoids.get(position);
    holder.mTvTitle.setText(poids.getTitle());
    holder.mTvMediaName.setText(poids.getMediaName());
    Picasso.with(mContext).load(poids.getImageUrl())
            //.fit().centerInside()
            .into(holder.mImageUrl);

    return view;
}

public class ViewHolder {
    public ImageView mImageUrl;
    public TextView mTvTitle;
    public TextView mTvMediaName;
}
}

in Oncreate

List<Poids> poids= db.getAllPoids();
PoidsAdapter  mAdapter = new PoidsAdapter(this, R.layout.item_poids, poids);
    listView.setAdapter(mAdapter);

Add picasso to build.gradle

dependencies {
compile fileTree(dir: 'libs', include: ['*.jar'])
compile 'com.android.support:appcompat-v7:22.2.1'
compile 'com.squareup.picasso:picasso:2.5.2'

}

in activity_main.xml add control ListView

create layout item_poids.xml have 3 control TextView, TextView, ImageView

But here it doasn't work, and look my getAllPoids it return more columns than I get — BJM, May 04 '16 at 04:11
Can you explein more for the delete ? I did a setOnClickListener but position doesn't found — BJM, May 04 '16 at 08:24
compile 'com.squareup.picasso:picasso:2.5.2' add to build.gradle — Nguyễn Trung Hiếu, May 04 '16 at 09:12
please help me, if i'm on a button now, all is working ut the button delete is done nothing — BJM, May 04 '16 at 09:15
if you want show ImageView from url. You must use Lib Piccaso or lib any load image — Nguyễn Trung Hiếu, May 04 '16 at 09:15
no I'm displaying a button, and you say listview.setOnItemClickListener, so it's on the click of a item, but it doesn't work too — BJM, May 04 '16 at 09:17
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/110971/discussion-between-bjm-and-nguyn-trung-hiu). — BJM, May 04 '16 at 09:36

MikeT · Answer 2 · 2016-05-04T04:23:54.847

Yes it is possible.

One way is to use a ListView.

This requires the ListView to be defined as part of the layout e.g. :-

    <ListView
        android:id="@+id/mylistview"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1">
    </ListView>

You then need a layout for a row/entry in the list View (ie it equates to a cursor row). A simple example that caters for two db columns (Name and Order) :-

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="@dimen/standard_listview_row_height"
    android:paddingBottom="@dimen/standard_listview_row_padding_vertical"
    android:paddingLeft="@dimen/standard_listview_row_padding_horizontal"
    android:paddingRight="@dimen/standard_listview_row_padding_horizontal"
    android:paddingTop="@dimen/standard_listview_row_padding_vertical">
    <!-- Aisle Name -->
    <TextView
        android:id="@+id/aisle_name_entry"
        android:layout_width="@dimen/standard_dummy_size"
        android:layout_height="match_parent"
        android:layout_weight="0.89"
        android:textSize="@dimen/standard_listview_text_size"
        android:textStyle="bold" />
    <!-- Aisle Order-->
    <TextView
        android:id="@+id/aisle_order_entry"
        android:layout_width="@dimen/standard_dummy_size"
        android:layout_height="match_parent"
        android:layout_weight="0.1"
        android:gravity="end"
        android:textSize="@dimen/standard_listview_text_size"
        android:visibility="visible"/>
</LinearLayout>

You need an Adapter.A Cursor Adapater basically places the data from the DB cursor into the appropriate views. Here's an Adapter for the above:-

class AislesCursorAdapter extends CursorAdapter {
    public AislesCursorAdapter(Context context, Cursor cursor, int flags) {
        super(context, cursor, 0);
    }
    @Override
    public View getView(int position, View convertview, ViewGroup parent) {
        View view = super.getView(position, convertview, parent);
        Context context = view.getContext();
        if (position % 2 == 0) {
            view.setBackgroundColor(ContextCompat.getColor(context, R.color.colorlistviewroweven));
        } else {
            view.setBackgroundColor(ContextCompat.getColor(context, R.color.colorlistviewrowodd));
        }
        return view;
    }

    @Override
    public void bindView(View view, Context context, Cursor cursor) {
        TextView textviewaislename = (TextView) view.findViewById(R.id.aisle_name_entry);
        TextView textviewaisleorder = (TextView) view.findViewById(R.id.aisle_order_entry);

        textviewaislename.setText(cursor.getString(ShopperDBHelper.AISLES_COLUMN_NAME_INDEX));
        textviewaisleorder.setText(cursor.getString(ShopperDBHelper.AISLES_COLUMN_ORDER_INDEX));

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

Note! getView method isn't required. Here it's used to alternate the background colour. Additionally ShopperDBHelper.??????_ORDER equates to the offset of the respective column in the DB cursor. The inflated layout (R.layout.activity_aisle_list_entry) is the 2nd layout (as above).

You tie this together in the respective activity by creating the DB cursor, creating an adapter instance using the DB cursor and then setting the ListView to use that adapter e.g. :-

        Cursor aislescsr = shopperdb.getAislesPerShopAsCursor(csr.getInt(ShopperDBHelper.SHOPS_COLUMNN_ID_INDEX));
        ListView lv = (ListView) findViewById(R.id.aislelist_listview);
        AislesCursorAdapter aisleadapter = new AislesCursorAdapter(lv.getContext(), aislescsr, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
        lv.setAdapter(aisleadapter);

As for a clickable image you can add this to the entry layout and set the onClick attribute to the name of a method in the activity (not the only wau, but perhaps the simplest). e.g. (this is for a TextView as opposed to an image) :-

        <TextView
            android:id="@+id/shoppinglist_deletebutton"
            android:layout_width="@dimen/standard_dummy_size"
            android:layout_height="@dimen/shoppinglist_listview_button_height"
            android:layout_weight="0.05"
            android:singleLine="true"
            android:text="@string/standarddeletetext"
            android:gravity="center"
            android:textSize="@dimen/standard_subsubsubheading_text_size"
            android:textStyle="bold"
            android:background="@color/colorRequiredLabel"
            android:textColor="@color/colorNormalButtonText"
            android:onClick="sledelete"/>

Here's the respective sledelete method :-

public void sledelete(View view) {
        Integer tag = (Integer)view.getTag();
        shoppinglistcsr.moveToPosition(tag);
        shopperdb.setShopListEntryAsComplete(shoppinglistcsr.getLong(0));
        shoppinglistcsr = shopperdb.getShoppingList();
        currentsla.swapCursor(shoppinglistcsr);
    }

Note! the method is only passed the view and importantly NOT the position. Hence the getTag (tag is set to position in the adpter, example to follow). Note that the last 2 lines refreshes the Listview ie, gets new cursor from DB, and then swaps to the new cursor (you can also use changeCursor and onNotifyDataSetChanged).

Here's the tag setting code in the adapter. This is in the bindView method (and sets the tags for 3 TextViews) :-

public void bindView(View view,Context context, Cursor cursor) {
    int pos = cursor.getPosition();
.........
    donebtntv.setTag(pos);
    deletebtntv.setTag(pos);
    replacebtntv.setTag(pos);

Alternately you could set the Tag(s) this in the getView method, which has the position passed to it (so you wouldn't need int pos = cursor.getPosition )

Android SQLite: Retrieving a list of all data of a table

2 Answers2