How do I load a SQlite Database into Recyclerview?

Question

I have a recyclerview inside a fragment activity that uses a button to bring up an alert dialog box to get input for 2 string variables. I have successfully created the database and can add items to the database from the alertdialog input, but I want to populate the recyclerview with the database when the app initially starts up. The database is used to store the input and I want it to populate the recyclerview so the user can see the items they entered previously.

This is my fragment:

public class tab1Expenses extends Fragment {

SqlDatabase dbEntry;

ArrayList<ExRow> expenseList = new ArrayList<>();
RecyclerView recyclerView;
ExpensesAdapter mAdapter;
Button btnEx;
String Na;
String Am;
String message = "Name must be longer than 2 characters";
String message2 = "Please enter valid amount";
Double value;
String am;

public void expenseData() {
    ExRow exs = new ExRow(Na, Am);
    expenseList.add(exs);
    mAdapter.notifyDataSetChanged();
}

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
                         Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    final View rootView = inflater.inflate(R.layout.tab1expense, container, false);
    btnEx = (Button) rootView.findViewById(R.id.btnEx);
    recyclerView = (RecyclerView) rootView.findViewById(R.id.recycler_view);
    recyclerView.addItemDecoration(new DividerItemDecoration(tab1Expenses.this.getActivity(), LinearLayoutManager.VERTICAL));

    mAdapter = new ExpensesAdapter(expenseList);
    final RecyclerView.LayoutManager mLayoutManager = new LinearLayoutManager(this.getActivity());

    recyclerView.setHasFixedSize(true);
    recyclerView.setClickable(true);
    recyclerView.isFocusable();
    recyclerView.setFocusableInTouchMode(true);
    recyclerView.setLayoutManager(mLayoutManager);
    recyclerView.setItemAnimator(new DefaultItemAnimator());
    recyclerView.setAdapter(mAdapter);

    btnEx.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            //Brings up Alert Dialog when Add income button is pressed
            add();
        }
    });
    return rootView;
}


public int add() {
    View view = LayoutInflater.from(tab1Expenses.this.getActivity())
            .inflate(R.layout.add_ex, null);
    final EditText txtExName = (EditText) view.findViewById(R.id.exName);
    final EditText txtExAmount = (EditText) view.findViewById(R.id.exAmount);
    //Creates Alert Dialog
    AlertDialog.Builder add = new AlertDialog.Builder(tab1Expenses.this.getActivity());
    add.setCancelable(true)
            .setTitle("Enter Expense:")
            .setView(view)
            .setPositiveButton("Add",
                    //AlertDialog positive button ClickListener
                    new DialogInterface.OnClickListener() {
                        @Override
                        public void onClick(DialogInterface dialog, int which) {
                            //Checks for invalid input
                            //If criteria met, expenseData() is called
                            if (txtExAmount.getText().length() > 0 && txtExName.getText().length() > 2 && txtExAmount.getText().length() < 6 && txtExName.getText().length() < 25) {
                                Na = txtExName.getText().toString();
                                am = txtExAmount.getText().toString();
                                value = Double.parseDouble(am);
                                Am = NumberFormat.getCurrencyInstance().format(value);
                                expenseData();

                            }
                            //Toast for invalid input
                            if (txtExName.getText().length() <= 2) {
                                Toast.makeText(tab1Expenses.this.getActivity(), message, Toast.LENGTH_SHORT).show();
                            } else if (txtExAmount.getText().length() == 0) {
                                Toast.makeText(tab1Expenses.this.getActivity(), message2, Toast.LENGTH_SHORT).show();
                            } else if (txtExAmount.getText().length() >= 6) {
                                Toast.makeText(tab1Expenses.this.getActivity(), "You don't make that much", Toast.LENGTH_SHORT).show();
                            } else if (txtExName.getText().length() >= 25) {
                                Toast.makeText(tab1Expenses.this.getActivity(), "Name must be less than 25 characters", Toast.LENGTH_SHORT).show();
                            }
                        }
                    });
    Dialog dialog = add.create();
    dialog.show();

    return 0;
}

public void viewAll() {

        ExRow exrow = new ExRow();
        Cursor res = dbEntry.getAllData();
        exrow.setTitle(res.getString(1));
        exrow.setAmount(res.getString(2));

  }
}

This is my database:

public class SqlDatabase extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "Expenses.db";
public static final String TABLE_NAME = "Expense_Table";
public static final String ID = "id";
public static final String Col_NAME = "Na";
public static final String Col_AMOUNT = "Am";


    public SqlDatabase(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    //Set up database here
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("Create table " + TABLE_NAME +
                "(ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT, AMOUNT TEXT)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
    public boolean insertData(String Na, String Am) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(Col_NAME, Na);
        contentValues.put(Col_AMOUNT, Am);
        long result = db.insert(TABLE_NAME, null, contentValues);
        if (result == -1) {
            return false;
        } else {
            return true;
        }

    }
public static Cursor getAllData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from " + TABLE_NAME, null);
    return res;

}
}

And this is my RecyclerView Adapter + View holder:

public class ExpensesAdapter extends RecyclerView.Adapter<ExpensesAdapter.MyViewHolder> {
public ImageButton mRemoveButton;
private ArrayList<ExRow> expenseList;

public class MyViewHolder extends RecyclerView.ViewHolder {
    public TextView title, amount;

    public MyViewHolder(View view) {
        super(view);
        title = (TextView) view.findViewById(R.id.name);
        amount = (TextView) view.findViewById(R.id.amount);
        mRemoveButton = (ImageButton) view.findViewById(R.id.ib_remove);
    }
}

public ExpensesAdapter(ArrayList<ExRow> expenseList) {
    this.expenseList = expenseList;
}

@Override
public MyViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
    View itemView = LayoutInflater.from(parent.getContext())
            .inflate(R.layout.expense_list, parent, false);

    return new MyViewHolder(itemView);
}

@Override
public void onBindViewHolder(final MyViewHolder holder, int position
) {

    ExRow expense = expenseList.get(holder.getAdapterPosition());
    holder.title.setText(expense.getTitle());
    holder.amount.setText(expense.getAmount());
    mRemoveButton.setOnClickListener(new View.OnClickListener() {
        public void onClick(View view) {
            // Remove the item on remove/button click
            int adapterPosition = holder.getAdapterPosition();

            expenseList.remove(adapterPosition);
            notifyItemRemoved(adapterPosition);
            notifyDataSetChanged();
        }
    });
}
@Override
public int getItemCount() {
    return expenseList.size();

use this adapter

pskink
– pskink

2017-02-11 14:15:40 +00:00
Commented Feb 11, 2017 at 14:15 — pskink
– pskink, Commented Feb 11, 2017 at 14:15

ישו אוהב אותך · Accepted Answer · 2017-02-12 03:51:52Z

2

You need to get List of your ExRow instead of cursor in your SQLiteOpenHelper. So instead of:

public static Cursor getAllData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from " + TABLE_NAME, null);
    return res;

}

You need to return List with something like this:

// Your column 
private String[] allColumns = { "ID", "COLUMN_1", "COLUMN_2" };

public List<ExRow> getAllData() {
    List<ExRow> exrows = new ArrayList<>();

    Cursor cursor = database.query("YOUR_TABLE", allColumns, null, null, 
                                   null, null, null);

    if (cursor != null && cursor.moveToFirst()) {
      while (!cursor.isAfterLast()) {

        ExRow exrow = populateModel(cursor);
        exrows.add(exrow);
        cursor.moveToNext();
      }
      cursor.close();
    }
    return exrows;
  }


 private ExRow populateModel(Cursor c) {
    ExRow model = new ExRow();
    model.setId(c.getLong("ID"));
    model.setColumn1(c.getString(c.getColumnIndex("COLUMN_1")));
    model.setColumn2(c.getString(c.getColumnIndex("COLUMN_2")));

    return model;
 }

Then you can use the value returned from getAllData() and pass it to your Adapter.

edited Feb 12, 2017 at 3:51

answered Feb 11, 2017 at 14:32

ישו אוהב אותך

29.9k11 gold badges83 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

pskink Over a year ago

no, he needs a Cursor aware adapter, like the one in my comment above

ישו אוהב אותך Over a year ago

If we look at the op SQLite helper, then you're correct. But if we look at op Adapter, we can conclude that List is the op answer. I don't think it's a good advice to recreate op adapter.

pskink Over a year ago

It is not a good idea to duplicate your data, what do you need exrows for if your data is already stored in the Cursor?

Corpse Over a year ago

This is my first time using recyclerviews and sqlite databases in an app so I don't know my way around Cursors. Any help is appreciated :)

pskink Over a year ago

@Corpse then use Cursor aware adapter,like CursorRecyclerAdapter, you will save a lot of time then

|

Collectives™ on Stack Overflow

How do I load a SQlite Database into Recyclerview?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related