Multiple rows from sqlite to custom adapter android

Question

The app: I have an app that creates multiple machines with:

id, name and location

each of these machines I have to let the user input the income

id, note, date, money, machines_id

The Problem: I need to populate all the notes from the matching machine_id to a ListView.

What I have tried: Creating DBHelper Method to get the info i need (Notes and Date).

db.rawQuery("SELECT note, date FROM income WHERE machines_id = "+machinesId+"", null);

This currently works on a DB Browser for SQLite but i do not know how to add it to the ListView Adapter and set it to the corresponding TextView

My Question: What is the best approach to accomplish the union between the DBHelper Method and the Custom Adapter to get only the information required in the TextViews? (Date and Note from matching machines_id)

If you need any other class or have any feedback, please dont hesitate to indulge me!

DBHelper

    public class DBHelpter extends SQLiteOpenHelper {

    private static final String DB_NAME = "machines.db";
    private static final int DB_VERSION = 1;

    public static final String TABLE_MACHINES = "machines";
    public static final String MACHINES_COLUMN_NAME = "name";
    public static final String MACHINES_COLUMN_LOCATION = "location";
    public static final String MACHINES_ID = "id";

    public static final String TABLE_INCOME = "income";
    public static final String INCOME_COLUMN_MONEY = "money";
    public static final String INCOME_COLUMN_DATE = "date";
    public static final String INCOME_COLUMN_NOTE = "note";
    public static final String INCOME_ID = "id";
    public static final String INCOME_COLUMN_MACHINES_ID = "machines_id";

    private Context mContext;

public DBHelpter(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

@Override
public void onCreate(SQLiteDatabase db) {
    String query1 = String.format("CREATE TABLE " + TABLE_MACHINES + "("
        + MACHINES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + MACHINES_COLUMN_NAME + " TEXT NOT NULL, "
        + MACHINES_COLUMN_LOCATION + " TEXT NOT NULL)",
            TABLE_MACHINES, MACHINES_COLUMN_NAME, MACHINES_COLUMN_LOCATION, MACHINES_ID);

    String query2 = String.format("CREATE TABLE " + TABLE_INCOME + "("
        + INCOME_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + INCOME_COLUMN_MONEY + " REAL NOT NULL, "
        + INCOME_COLUMN_DATE + " DATE NOT NULL, "
        + INCOME_COLUMN_NOTE + " TEXT NOT NULL, "
        + INCOME_COLUMN_MACHINES_ID + " INTEGER NOT NULL)",
            TABLE_INCOME, INCOME_ID, INCOME_COLUMN_MONEY, INCOME_COLUMN_DATE, INCOME_COLUMN_NOTE, INCOME_COLUMN_MACHINES_ID);
    db.execSQL(query1);
    db.execSQL(query2);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String query1 = String.format("DROP TABLE IF EXISTS " + TABLE_MACHINES);
    String query2 = String.format("DROP TABLE IF EXISTS " + TABLE_INCOME);
    db.execSQL(query1);
    db.execSQL(query2);
    onCreate(db);

}
public void insertNewMachine(String name, String location){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(MACHINES_COLUMN_NAME, name);
        values.put(MACHINES_COLUMN_LOCATION, location);
        db.insertWithOnConflict(TABLE_MACHINES, null, values, SQLiteDatabase.CONFLICT_REPLACE);
        db.close();
    }
public void insertNewIncome(Double money, String date, String note, long machines_id){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(INCOME_COLUMN_MONEY, money);
        values.put(INCOME_COLUMN_DATE, date);
        values.put(INCOME_COLUMN_NOTE, note);
        values.put(INCOME_COLUMN_MACHINES_ID, machines_id);
        db.insertWithOnConflict(TABLE_INCOME, null, values, SQLiteDatabase.CONFLICT_REPLACE);
        db.close();
    }
public void getIncomeOfMachine(long machinesId){
        SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT machines_id, SUM(money) AS total FROM income WHERE machines_id = "+machinesId+"", null);
    cursor.moveToFirst();
    double total_amount = cursor.getDouble(cursor.getColumnIndex("total"));
    return total_amount;
    }
public ArrayList<MachinesClass> getAllMachines(){
        ArrayList<MachinesClass> machinesList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM "+ TABLE_MACHINES, null);
        while (cursor.moveToNext()){
            final long id = cursor.getLong(cursor.getColumnIndex(MACHINES_ID));
            final String name = cursor.getString(cursor.getColumnIndex(MACHINES_COLUMN_NAME));
            final String location = cursor.getString(cursor.getColumnIndex(MACHINES_COLUMN_LOCATION));
            machinesList.add(new MachinesClass(id, name, location));
        }
        cursor.close();
        db.close();
        return machinesList;
    }

public ArrayList<String> getInfoFromMachine(long machinesId){
        ArrayList<String> all_notes = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT note, date FROM income WHERE machines_id = "+machinesId+"",null);
        cursor.moveToFirst();
        while (cursor.moveToNext()){
            String note = cursor.getString(cursor.getColumnIndex("note"));
            String date = cursor.getString(cursor.getColumnIndex("date"));
            all_notes.add(date);
            all_notes.add(note);
        }
        db.close();
        cursor.close();
        return all_notes;
    }
}

ListAdapter

    public class ListAdapter extends BaseAdapter {

    private Context mContext;
    private DBHelpter mDBHelpter;
    private ArrayList<IncomeClass> mList;

    public ListAdapter(Context mContext, DBHelpter mDBHelper, ArrayList<IncomeClass> mList){
        this.mContext = mContext;
        this.mDBHelpter = mDBHelper;
        this.mList = mList;
    }

    @Override
    public int getCount() {
        return mList != null ? mList.size(): 0;
    }

    @Override
    public Object getItem(int position) {
        return mList.get(position);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {

        mDBHelpter = new DBHelpter(mContext);
        SQLiteDatabase db = mDBHelpter.getReadableDatabase();
        ViewHolder viewHolder;

        if (convertView == null){
            convertView = LayoutInflater.from(mContext).inflate(R.layout.notes_list, parent, false);
            viewHolder = new ViewHolder(convertView);
            convertView.setTag(viewHolder);
        } else {
            viewHolder = (ViewHolder) convertView.getTag();
        }



//        Cursor info = mDBHelpter.getInfoFromMachine(currentNote.getId());
//        viewHolder.mNote.setText(mDBHelpter.getInfoFromMachine(currentNote.getId()).toString());
//        viewHolder.mNoteDate.setText(mDBHelpter.getInfoFromMachine(currentNote.getId()).toString());

        return convertView;
    }

    private class ViewHolder {
        private TextView mNote, mNoteDate;

        public ViewHolder(View view){
            mNote = (TextView) view.findViewById(R.id.tvNote);
            mNoteDate = (TextView) view.findViewById(R.id.tvNoteDate);
        }
    }
}

DustyMan · Accepted Answer · 2017-02-02 04:38:35Z

1

Basically your database will return a Cursor object when you do a query. This Cursor will contains all the returned rows from your database. You can use a CursorAdapter or simply pass the Cursor to your custom Adapter's constructor.

Here is a very simple example of how you would achieve that using a RecyclerView Adapter.

public class RecyclerViewCursorAdapter extends RecyclerView.Adapter<RecyclerView.ViewHolder> {

    private Cursor mCursor;
    private int mRowIdColumn;

    // Pass the cursor to your adapter.
    public void RecyclerViewCursorAdaper(Cursor cursor) {
        mCursor = cursor;
        mRowIdColumn = mCursor.getColumnIndex("_id");
    }

    @Override
    public RecyclerView.ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        // Inflate your view here.
        return null;
    }

    @Override
    public long getItemId(int position) {
        if (mCursor != null && mCursor.moveToPosition(position)) {
            return mCursor.getLong(mRowIdColumn);
        }
        return 0;
    }

    @Override
    public void onBindViewHolder(RecyclerView.ViewHolder holder, int position) {
        mCursor.moveToPosition(position);
        // Todo: Bind your custom view holder.
    }

    @Override
    public int getItemCount() {
        if (mCursor != null) {
            return mCursor.getCount();
        }
        return 0;
    }

    public Cursor getCursor() {
        return mCursor;
    }
}

answered Feb 2, 2017 at 4:38

DustyMan

3663 silver badges9 bronze badges

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

2 Comments

Ispam Over a year ago

Hi, thank you for ur feedback. How do i set the adapter in the corresponding activity? when using mRecyclerView.setAdapter(new RecyclerViewCursorAdape(mCursor)); Im getting "cannot be applied to"

DustyMan Over a year ago

RecyclerView requires a LayoutManager in order to work. Choose what kind of LayoutManager you want between, LinearLayoutManager, StaggeredGridLayoutManager or GridLayoutManager, then pass the layout manager as the second argument in .setAdapter()

Collectives™ on Stack Overflow

Multiple rows from sqlite to custom adapter android

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related