How to retrieve all the rows in my DB?

Question

Good day sorry for asking these question. Hope somebody help me solve my problem. Also hoping that my question is clear enough so that somebody will not down vote it. I'm just new to this android thing. I only watch and followed some tutorials to get these code done and asking question here in stackoverflow when things gets hard for me to comprehend.

this my current code and it only fetches the 1st row but it didn't display it in my CardView?

How could I fetch all the rows then display all rows column data?

this is the log cat response for inserting the data in SQLite DB.

D/SQLiteHandler: Members's SL Details was successfully inserted: 1
D/SQLiteHandler: Successfully inserted SL Desc:             Test Account 1
D/SQLiteHandler: Successfully inserted Transaction Date:    2015-08-17
D/SQLiteHandler: Successfully inserted Actual Balance:      483.67
D/SQLiteHandler: Successfully inserted Available Balance:   483.67
D/SQLiteHandler: -------------------------------------------------------------------
D/SQLiteHandler: Members's SL Details was successfully inserted: 2
D/SQLiteHandler: Successfully inserted SL Desc:             Test Account 2
D/SQLiteHandler: Successfully inserted Transaction Date:    2015-08-28
D/SQLiteHandler: Successfully inserted Actual Balance:      10129.43
D/SQLiteHandler: Successfully inserted Available Balance:   10129.43
D/SQLiteHandler: --------------------------------------------------------------------

this is the log cat response for fetching the SL Details

D/SQLiteHandler: List<Datas>) Getting members' SL Description:        Test Account 1
D/SQLiteHandler: List<Datas>) Getting members' SL TR Date:            2015-08-17
D/SQLiteHandler: List<Datas>) Getting members' SL Actual Balance:     483.67
D/SQLiteHandler: List<Datas>) Getting members' SL Available Balance:  483.67
D/SQLiteHandler: -------------------------------------------------------------------------
D/SQLiteHandler: List<Datas>) Members's SL Details data:              gsacmobileportal.activity.Datas@5357143c

It only returns the 1st row. How could I fetch all the rows then display all rows column data?

This SQLiteHandler.java

/**
 * Getting user data from List
 * */
public List<Datas> getUserSLDetails() {

    String selectQuery = "SELECT * FROM " + TABLE_MEMBERS_SLDTL;
    List<Datas>  mMemberDetails =  new ArrayList<>();

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    // Move to first row
    cursor.moveToFirst();
    if (cursor.getCount() > 0) {

        Datas pMemebr = new Datas();

        pMemebr.setSL_DESC(cursor.getString(0));
        pMemebr.setTR_DATE(cursor.getString(1));
        pMemebr.setACTUAL_BALANCE(cursor.getString(2));
        pMemebr.setAVAILABLE_BALANCE(cursor.getString(3));

        mMemberDetails.add(pMemebr);

        Log.d(TAG, "List<Datas>) Getting members' SL Description:        " + cursor.getString(0));
        Log.d(TAG, "List<Datas>) Getting members' SL TR Date:            " + cursor.getString(1));
        Log.d(TAG, "List<Datas>) Getting members' SL Actual Balance:     " + cursor.getString(2));
        Log.d(TAG, "List<Datas>) Getting members' SL Available Balance:  " + cursor.getString(3));
        Log.d(TAG, "-------------------------------------------------------------------------");

        Log.d(TAG, "List<Datas>) Members's SL Details data:              " + pMemebr.toString());

    }
    else{
        Log.d("getUserSLDetails()", "member's SL Details data is empty");
    }
    cursor.close();
    db.close();

    return mMemberDetails;

}

Datas.java

/**
 * Created by shifoodew on 7/28/2017.
 */

public class Datas {

    private String SL_DESC;
    private String TR_DATE;
    private String ACTUAL_BALANCE;
    private String AVAILABLE_BALANCE;

    public String getSL_DESC() {
        return SL_DESC;
    }

    public void setSL_DESC(String SL_DESC) {
        this.SL_DESC = SL_DESC;
    }

    public String getTR_DATE() {
        return TR_DATE;
    }

    public void setTR_DATE(String TR_DATE) {
        this.TR_DATE = TR_DATE;
    }

    public String getACTUAL_BALANCE() {
        return ACTUAL_BALANCE;
    }

    public void setACTUAL_BALANCE(String ACTUAL_BALANCE) {
        this.ACTUAL_BALANCE = ACTUAL_BALANCE;
    }

    public String getAVAILABLE_BALANCE() {
        return AVAILABLE_BALANCE;
    }

    public void setAVAILABLE_BALANCE(String AVAILABLE_BALANCE) {
        this.AVAILABLE_BALANCE = AVAILABLE_BALANCE;
    }
}

MyAdapter.java

public class MyAdapter extends RecyclerView.Adapter<MyAdapter.MyViewHolder> {

    private List<Datas> mDataset;

    // Provide a reference to the views for each data item
    // Complex data items may need more than one view per item, and
    // you provide access to all the views for a data item in a view holder
    public static class MyViewHolder extends RecyclerView.ViewHolder {

        public CardView mCardView;
        public TextView account_type;
        public TextView accnt_description;
        public TextView balance_label;
        public TextView account_balance;

        public MyViewHolder(View v) {
            super(v);

            mCardView = (CardView) v.findViewById(R.id.card_view);

            account_type = (TextView) v.findViewById(R.id.lblShareCapital);
            balance_label = (TextView) v.findViewById(R.id.lblAvailableBalance);

            accnt_description = (TextView) v.findViewById(R.id.sl_desc);
            account_balance = (TextView) v.findViewById(R.id.actual_balance);
        }
    }
    // Provide a suitable constructor (depends on the kind of dataset)
    public MyAdapter(List<Datas> myDataset) {
        mDataset = myDataset;
    }

    // Create new views (invoked by the layout manager)
    @Override
    public MyAdapter.MyViewHolder onCreateViewHolder(ViewGroup parent,
                                                     int viewType) {
        // create a new view
        View v = LayoutInflater.from(parent.getContext())
                .inflate(R.layout.card_item, parent, false);
        // set the view's size, margins, paddings and layout parameters
        MyViewHolder vh = new MyViewHolder(v);
        return vh;
    }
    @Override
    public void onBindViewHolder(MyViewHolder holder, final int position) {
        //holder.account_type.setText(mDataset[position]);
        Datas datas = mDataset.get(position);
        holder.account_balance.setText(datas.getSL_DESC());
        holder.mCardView.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                String sl_desc = mDataset.get(position).getSL_DESC();
                String actual_balance = mDataset.get(position).getACTUAL_BALANCE();

                Log.d("CardView", "CardView Clicked: " + sl_desc);
                Log.d("CardView", "CardView Clicked: " + actual_balance);
            }
        });
    }
    @Override
    public int getItemCount() {
        //return mDataset.length;
        return 0;
    }
}

AccountsFragment.java

public class AccountsFragment extends Fragment {
private SQLiteHandler db;
    public AccountsFragment() {
        // Required empty public constructor
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
    }

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        // Inflate the layout for this fragment
        View rootView = inflater.inflate(R.layout.fragment_accounts, container, false);
        RecyclerView rv = (RecyclerView) rootView.findViewById(R.id.rv_recycler_view);
        rv.setHasFixedSize(true);

        SQLiteHandler db = new SQLiteHandler(getActivity());
        MyAdapter adapter = new MyAdapter(db.getUserSLDetails());
        rv.setAdapter(adapter);

        LinearLayoutManager llm = new LinearLayoutManager(getActivity());
        rv.setLayoutManager(llm);

        return rootView;
    }
}

Check my answer below. Has changes in 2 of your classes. – Kapil G Aug 01 '17 at 07:14 — Kapil G, Aug 01 '17 at 07:14

score 0 · Answer 1 · answered Aug 01 '17 at 07:04

use do while loop

 if (cursor.moveToFirst()) {
                do {
                    Datas data = new DiaryData(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3)
                            , cursor.getString(4));
                    // Adding data to list
                    mMemberDetails.add(data);
                } while (cursor.moveToNext());
            }
     cursor.close();
    return mMemberDetails;

score 0 · Answer 2 · answered Aug 01 '17 at 07:05

0

you are passing 0 therefore there is no item available so use

@Override
public int getItemCount() {
    //return mDataset.length;
    return mdataset.size();
}

answered Aug 01 '17 at 07:05

jigar savaliya

474
1
8
21

score 0 · Answer 3 · answered Aug 01 '17 at 07:05

Right now you're only recovering the first result of your cursor. Check this piece of code you wrote:

if (cursor.getCount() > 0) {
    ...
    mMemberDetails.add(pMemebr);
    ...
}

You're only getting the first and only first item. You need to iterate through the cursor. Instead of doing that if (cursor.getCount() > 0) consider changint to while (cursor.moveToNext())

You can find more info about cursors in this question

score 0 · Accepted Answer · answered Aug 01 '17 at 07:10

First you need to iterate in your getUserSLDetails method in your SQLiteHandler class

if (cursor.moveToFirst()) {
    do {
          Datas pMemebr = new Datas();

          pMemebr.setSL_DESC(cursor.getString(0));
          pMemebr.setTR_DATE(cursor.getString(1));
          pMemebr.setACTUAL_BALANCE(cursor.getString(2));
          pMemebr.setAVAILABLE_BALANCE(cursor.getString(3));

          mMemberDetails.add(pMemebr);

          Log.d(TAG, "List<Datas>) Getting members' SL Description:        " + cursor.getString(0));
          Log.d(TAG, "List<Datas>) Getting members' SL TR Date:            " + cursor.getString(1));
          Log.d(TAG, "List<Datas>) Getting members' SL Actual Balance:     " + cursor.getString(2));
          Log.d(TAG, "List<Datas>) Getting members' SL Available Balance:  " + cursor.getString(3));
          Log.d(TAG, "-------------------------------------------------------------------------");

          Log.d(TAG, "List<Datas>) Members's SL Details data:              " + pMemebr.toString());
            } while (cursor.moveToNext());
        }
 cursor.close();
 return mMemberDetails;
}

Then in your MyAdapter you need to change the below method -

@Override
public int getItemCount() {
    //return mDataset.length;
    return mdataset.size();
}

Please do remove the logs once you have verified that your data is coming correctly.

How to retrieve all the rows in my DB?

4 Answers4

Linked