How to fetch one by one record from sqlite?

Question

I have two table "TABLE_EXAM" and "TABLE_QUESTION". I fetch record using following code
but it show only one record. I need to show all record but one by one after clicking on Next button.Please give me some reference or hint.
I don't understand how to fetch record one by one by clicking on Next Button.
Thanks in Advance.

AppearingExamActivity.java

db=new MySQLiteHelper(getBaseContext());
        db.getWritableDatabase();
        examId=db.getExamId(profile);

        final List<ObjectiveWiseQuestion> QuestionWiseProfile= db.getOneQuestion(examId);       

        for (final ObjectiveWiseQuestion cn : QuestionWiseProfile)
        {   
            db=new MySQLiteHelper(getBaseContext());
            db.getWritableDatabase();

            //db.close();
            txtQuestion.setText(cn.getQuestion());
            optionA.setText(cn.getOptionA());
            optionB.setText(cn.getOptionB());
            optionC.setText(cn.getOptionC());
            optionD.setText(cn.getOptionD());   
            correctOption=cn.getCorrectOption();

        }

    }
    btnNext.setOnClickListener(new View.OnClickListener()
    {           
        @Override
        public void onClick(View v)
        {
            try
            {
                db=new MySQLiteHelper(getBaseContext());
                db.getWritableDatabase();
                owq.getCorrectAnswer();
                owq.setExamId(examId);
                //owq.getExamId();
                owq.getQuestionId();
                db.addResultDetails(owq);
                db.close();

            }
            catch(Exception e)
            {
                e.printStackTrace();
            }

        }
    });

MySQLiteHelper.java

public List<ObjectiveWiseQuestion> getOneQuestion(int examId)
{
   // long index = 0;
    List<ObjectiveWiseQuestion>LocwiseProfileList=new ArrayList<ObjectiveWiseQuestion>();
     db = getReadableDatabase();


     String selectQuery=("select * from question where exam_id ='"+ examId +"'");
        Cursor cursor = db.rawQuery(selectQuery, null);


    if (cursor.moveToFirst())
    {
        do {
            ObjectiveWiseQuestion owq= new ObjectiveWiseQuestion();

            owq.setQuestionId(cursor.getInt(0));
            owq.setExamId(cursor.getInt(1));
            owq.setQuestion(cursor.getString(2));
            owq.setOptionA(cursor.getString(3));
            owq.setOptionB(cursor.getString(4));
            owq.setOptionC(cursor.getString(5));
            owq.setOptionD(cursor.getString(6));
            owq.setCorrectOption(cursor.getString(7));

            LocwiseProfileList.add(owq);
        } while(cursor.moveToNext());
        db.close();

    }


    return LocwiseProfileList;
}

how to do that?

Answer 1

Finally I got answer... I count number of record and only decrement it by 1. If i have 10 record then i set counter=10 and simply decrement it by 1 on each next click.
Here is simple function which help me.

public List<ObjectiveWiseQuestion> getOneByOneQuestion(int examId, int questionId)
{      
    List<ObjectiveWiseQuestion>LocwiseProfileList=new ArrayList<ObjectiveWiseQuestion>();
     db = getWritableDatabase(); 

     String selectQuery=("select * from question where exam_id ='" + examId + "' and question_id ='" + questionId +"'" );
        Cursor cursor = db.rawQuery(selectQuery, null);


    if (cursor.moveToFirst())
    {
        do {
            ObjectiveWiseQuestion owq= new ObjectiveWiseQuestion();

            owq.setQuestionId(cursor.getInt(0));
            owq.setExamId(cursor.getInt(1));
            owq.setQuestion(cursor.getString(2));
            owq.setOptionA(cursor.getString(3));
            owq.setOptionB(cursor.getString(4));
            owq.setOptionC(cursor.getString(5));
            owq.setOptionD(cursor.getString(6));
            owq.setCorrectOption(cursor.getString(7));

            LocwiseProfileList.add(owq);
        } while(cursor.moveToNext());
        cursor.moveToFirst();
        db.close();

    }


    return LocwiseProfileList;
}

Answer 2

first fire query like

public Cursor Value_Of_Data(String string) throws SQLException {
        // TODO Auto-generated method stub


         Cursor mCursor = db.query(Normal_Scoring,
                  new String[] {
Columns1,Columns2,Columns3,Columns4,Columns5,Columns1
},
                  Game_Date +"<='" + string + "'",
                  null,null,null,null,null);





        if (mCursor != null)
        {
        mCursor.moveToFirst();
        }
        return mCursor;
    }

And now receiving end receive like

  ArrayList<String> row1 = new ArrayList<String>();

          ArrayList<String> row2 = new ArrayList<String>();

          ArrayList<String> row3 = new ArrayList<String>();

          ArrayList<String> row4 = new ArrayList<String>();

          ArrayList<String> row5 = new ArrayList<String>();

          ArrayList<String> row6 = new ArrayList<String>();




    try{
            cursor1 = db.Nomal_Score_Data(dateFormat.format(dateis));
            cursor1.moveToFirst();   
            startManagingCursor(cursor1);

            for(int i=0;i<cursor1.getCount();i++){


            String rec1 = cursor1.getString(cursor1.getColumnIndex
                                ("Columns1"));



            String rec2 = cursor1.getString(cursor1.getColumnIndex
                ("Columns2"));



            String rec3 = cursor1.getString(cursor1.getColumnIndex
                ("Columns3"));



            String rec4 = cursor1.getString(cursor1.getColumnIndex
                ("Columns4"));


            String rec5 = cursor1.getString(cursor1.getColumnIndex
                    ("Columns5"));




            String rec6 = cursor1.getString(cursor1.getColumnIndex
                       ("Columns6"));


          row1.add(rec1);  

         row12.add(rec2);

          row3.add(rec3);

         row4.add(rec4);

         row5.add(rec5);

          row6.add(rec6);

}
        catch(Exception e)
        {
            Log.e("ERROR OF FETCHING ", e.toString());
            e.printStackTrace();

        }

initialize a counter like

int count = 0 ;

and on button click display like

@Override
    public void onClick(View v) {
        // TODO Auto-generated method stub



        switch(v.getId())
        {
        case R.id.goNext :



            // display


       text1.setText(row1.get(count));
       text2.setText(row2.get(count));
       text3.setText(row3.get(count));
       text4.setText(row4.get(count));
       text5.setText(row5.get(count));
       text6.setText(row6.get(count));

   count++;


             }

                     break; 

case R.id.goPrev :

            Toast.makeText(this, "Button Clicked goPrev",
                    Toast.LENGTH_LONG).show();



       text1.setText(row1.get(count));
       text2.setText(row2.get(count));
       text3.setText(row3.get(count));
       text4.setText(row4.get(count));
       text5.setText(row5.get(count));
       text6.setText(row6.get(count));


         count--;



                     break; 

}
}

Answer 3

On Button click just handle cursor position thats it.

cursor inbuilt has capability to move its position. you have moveNext(), movePrevious(), moveToPosition(), moveToFirst(), moveToLast(). Besides this its also has getCount() which gives total number of record.

http://developer.android.com/reference/android/database/Cursor.html

String selectQuery=("select * from question where exam_id ='"+ examId +"'");
        Cursor cursor = db.rawQuery(selectQuery, null);

btnNext.setOnClickListener(new View.OnClickListener()
    {           
        @Override
        public void onClick(View v)
        {
            try
            {
               cursor.moveNext();
               //show on UI with cursor value
           }

        catch(Exception e)
        {
            e.printStackTrace();
        }

    }
});

Answer 4

This is the Code which you have to write in your java file.

Cursor mCursor=null;
mCursor = DatabaseObject.getDefault(); // 
if (mCursor.getCount() >= 1) {
                mCursor.moveToFirst();
                try {
                    for (int i = 0; i < mCursor.getCount(); i++) {
                        profileName.setText(mCursor.getString(0).toString()); // You can Do your work Here 
                        firstName.setText(mCursor.getString(1).toString());
                        lastName.setText(mCursor.getString(2).toString());
                        email.setText(mCursor.getString(3).toString());

                     }
            }
}

This is the Function of Database

public Cursor getDefault() throws SQLException {

    String getRT = "SELECT * from "+ PROFILE_TABLE+";";
    Cursor mCur = sqldb.rawQuery(getRT, null);
    return mCur;
}