Simplecursor adapter for sqlite database with four columns including a date

Question

I'm trying to get a data from my database created via SQLite into a listview, I have an SQL query that gets data from different tables. Here's my database's diagram:

Diagram DB

My activity historique contains only a listView and my adapter contains 4 textviews. Here's the code:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:weightSum="100">


        <LinearLayout
            android:layout_width="202dp"
            android:layout_height="wrap_content"
            android:layout_weight="33.3"
            android:orientation="vertical">

            <TextView
                android:id="@+id/txtnoom"
                android:layout_width="214dp"
                android:layout_height="30dp"
                android:gravity="center"
                android:text="nom" />

            <TextView
                android:id="@+id/hdate"
                android:layout_width="213dp"
                android:layout_height="30dp"
                android:gravity="center"
                android:text="date" />
        </LinearLayout>

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

        <TextView
            android:gravity="center"
            android:text="question"
            android:layout_width="match_parent"
            android:layout_height="30dp"
            android:id="@+id/txthistq"/>

        <TextView
            android:gravity="center"
            android:text="réponse"
            android:layout_width="match_parent"
            android:layout_height="30dp"
            android:id="@+id/txthistr"
            android:layout_below="@+id/textView2"/>
    </LinearLayout>
    </LinearLayout>

In my modelhelper, I wrote this function:

public Cursor getTableHistoAsCursor() { SQLiteDatabase db = this.getReadableDatabase(); return db.rawQuery(" SELECT Q." + KEY_QUESTION + " , U." + KEY_NOM + " , U." + KEY_PRENOM + " , A." + KEY_DATE + " , " + KEY_REPONSE + " from " + TABLE_QUESTION + " Q, " + TABLE_USER + " U, " + TABLE_ANSWER + " A WHERE Q." + KEY_ID_QUESTION + " = A." + KEY_ID_QUESTION + "AND A." + KEY_MATRICULE + " = U." + KEY_MATRICULE, null); }

It returns a cursor using the sqlQuery.

The code of my class is:

 ModelHelper openhelper; 
    SimpleCursorAdapter mSCA;
    SQLiteDatabase db;
    Cursor mCsr;
    ListView mListView;
    ArrayList<Historique> histList;
    Historique histo;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_historique);
        mListView = (ListView) findViewById(R.id.lsthisto); //<<<< Note 3
        openhelper =  new ModelHelper(this);
        db = openhelper.getReadableDatabase();
        histList = new ArrayList<>();


        mCsr = openhelper.getTableHistoAsCursor();
        int rows = mCsr.getCount();
        if (rows == 0 ){
            Toast.makeText(Historique.this, "No histo", Toast.LENGTH_SHORT).show();
        } else {
            while (mCsr.moveToNext()){
                histo = new Historique (mCsr.getInt(0),mCsr.getString(1), mCsr.getString(2),mCsr.getString(3))
            }
        }

        mSCA = new SimpleCursorAdapter(this,
                R.layout.activity_historique, //<<<< Note 2
                new String[]{openhelper.KEY_NOM, openhelper.KEY_QUESTION, openhelper.KEY_REPONSE, openhelper.KEY_DATE}, //<<<< Note 4
                new int[]{R.id.txtnoom, R.id.txthistq, R.id.txthistr, R.id.hnate}, //<<<< Note 5
                0
        );
        mListView.setAdapter(mSCA);
    }

The problem is that my code doesn't get the data from one table, so the table answer has only the IDs that it goes to search in the other tables, the constructor historique() can't be used: here's the error:

Then when I add the date's textview, all goes wrong: before:

After:

Is there any idea that can be helpful please? I searched a lot of tutorials but they are working on one table and it wasn't really useful. Thank you!

Answer 1

If I understand correctly, change :-

    mCsr = openhelper.getTableHistoAsCursor();
    int rows = mCsr.getCount();
    if (rows == 0 ){
        Toast.makeText(Historique.this, "No histo", Toast.LENGTH_SHORT).show();
    } else {
        while (mCsr.moveToNext()){
            histo = new Historique (mCsr.getInt(0),mCsr.getString(1), mCsr.getString(2),mCsr.getString(3))
        }
    }

    mSCA = new SimpleCursorAdapter(this,
            R.layout.activity_historique, //<<<< Note 2
            new String[]{openhelper.KEY_NOM, openhelper.KEY_QUESTION, openhelper.KEY_REPONSE, openhelper.KEY_DATE}, //<<<< Note 4
            new int[]{R.id.txtnoom, R.id.txthistq, R.id.txthistr, R.id.hnate}, //<<<< Note 5
            0
    );
    mListView.setAdapter(mSCA);

to :-

    mCsr = openhelper.getTableHistoAsCursor();
    mSCA = new SimpleCursorAdapter(this,
            R.layout.activity_historique, //<<<< Note 2
            mCsr, //<<<<<<<<<<<<<<<<<<< ADDED
            new String[]{openhelper.KEY_NOM, openhelper.KEY_QUESTION, openhelper.KEY_REPONSE, openhelper.KEY_DATE}, //<<<< Note 4
            new int[]{R.id.txtnoom, R.id.txthistq, R.id.txthistr, R.id.hnate}, //<<<< Note 5
            0
    );
    mListView.setAdapter(mSCA);

• The Historique class has no Constructor that takes 4 parameters, and from what I see is superfluous to populating the ListView, hence the deletion of the what appears to be unnecessary code.

• The second issue is that a SimpleCursorAdapter utilises a Cursor and expects to be passed a cursor as per SimpleCursorAdapter(Context context, int layout, Cursor c, String[] from, int[] to, int flags) SimpleCursorAdapter. Hence the addition of mCsr as the 3rd parameter.

Note! this is in-principle code and has not been tested.