iterate through nested json object and save data in android sqlite

Question

i have a nested JSON Object as follows :

[
   {
  "question_id":"1",
  "description":"What is your gender ?",
  "widget_id":"1",
  "answers":[
     {
        "answer_text":"Male",
        "answer_id":"1"
     },
     {
        "answer_text":"Female",
        "answer_id":"2"
     }
  ]
   },
   {
  "question_id":"2",
  "description":"Which animal best describes your personality ?",
  "widget_id":"2",
  "answers":[
     {
        "answer_text":"Cat",
        "answer_id":"3"
     },
     {
        "answer_text":"Horse",
        "answer_id":"4"
     },
     {
        "answer_text":"Dove",
        "answer_id":"5"
     },
     {
        "answer_text":"Lion",
    "answer_id":"6"
    },
   {
            "answer_text":"Chameleon",
            "answer_id":"7"
     }
  ]
   },
   {
      "question_id":"3",
  "description":"Do you like meeting other people ?",
  "widget_id":"3",
  "answers":[

  ]
   },
   {
  "question_id":"4",
  "description":"On a scale of 1-10, how would you rate your sense of humour ?",
  "widget_id":"4",
  "answers":[

  ]
   },
   {
  "question_id":"5",
  "description":"Are you afraid of the dark ?",
  "widget_id":"1",
  "answers":[
     {
        "answer_text":"No",
        "answer_id":"8"
     },
     {
        "answer_text":"Yes",
        "answer_id":"9"
     }
  ]
   },
   {
  "question_id":"6",
  "description":"Is it true that cannibals do not eat clowns because they taste kind of funny ?",
  "widget_id":"3",
  "answers":[

  ]
   },
   {
  "question_id":"7",
  "description":"What is your email address ? (Optional)",
  "widget_id":"3",
  "answers":[

  ]
   }
]

After retrieving from mysql server, i'm trying to insert into sqlite android as shown below, and it works.The only problem is i seem to lose the relationships between each question and all of it's answers and even widget_id.Because some questions have more than one answer option.

JSONArray aJson = new JSONArray(sJson);
ArrayList<Question> Question_Id_array = new ArrayList<Question>();

            for (int i = 0; i < aJson.length(); i++) {
                JSONObject json = aJson.getJSONObject(i);

                Question que = new Question();

                Question id = new Question();

                que.setDescription(json.getString("description"));

                id.setQuestionId(Integer.parseInt(json
                        .getString("question_id")));
                que.setWidgetId((Integer.parseInt(json
                        .getString("widget_id"))));
JSONArray cJson = json.getJSONArray("answers");
                ArrayList<Answer> ans = que.getAnswers();

                for (int k = 0; k < cJson.length(); k++) {
                    JSONObject Objectjson = cJson.getJSONObject(k);
                    Answer answer = new Answer();

                    answer.setAnswer_Text(Objectjson
                            .getString("answer_text"));
                    answer.setAnswer_Id(Integer.parseInt(Objectjson
                            .getString("answer_id")));
ans.add(answer);

String answer_value = answer.getAnswer_Text()
                            .toString();

                    int answer_id = answer.getAnswer_Id();

                    String question_title = que.getDescription().toString();

                    int question_id = que.getQuestionId();

                    int widget_id = que.getWidgetId();

                    ContentValues cv = new ContentValues();
                    cv.put(ResponseDetails.KEY_QUESTION_ID,question_id);
                    cv.put(ResponseDetails.KEY_QUESTION_DESCRIPTION,question_title);
                    cv.put(ResponseDetails.ANSWER_ID, answer_id);
                    cv.put(ResponseDetails.KEY_ANSWER_VALUE,answer_value);
                    cv.put(ResponseDetails.WIDGET_ID, widget_id);


                    getApplicationContext().getContentResolver()

                    .insert(ResponseContentProvider.CONTENT_URI2, cv);

                }

i currently have a single table with all the columns as seen in the code :

question_id,question_title,answer_id,answer_value and widget_id.

How can i maintain the relationship present in the json object between each question,all of it's answers and widget ID both while INSERTING and RETRIEVING from sqlite android.

EDIT

So this is now the exception i get:

02-11 15:44:33.487: E/AndroidRuntime(1336): FATAL EXCEPTION: main
02-11 15:44:33.487: E/AndroidRuntime(1336): java.lang.NullPointerException
02-11 15:44:33.487: E/AndroidRuntime(1336):     at         com.mabongar.survey.TableAnswers.insert(TableAnswers.java:53)
02-11 15:44:33.487: E/AndroidRuntime(1336):     at     com.mabongar.survey.FragmentStatePagerActivity$FetchQuestions.onPostExecute(FragmentStatePagerActivity.java:    177)
02-11 15:44:33.487: E/AndroidRuntime(1336):     at    com.mabongar.survey.FragmentStatePagerActivity$FetchQuestions.onPostExecute(FragmentStatePagerActivity.java:    1)
02-11 15:44:33.487: E/AndroidRuntime(1336):     at android.os.AsyncTask.finish(AsyncTask.java:631)
02-11 15:44:33.487: E/AndroidRuntime(1336):     at     android.os.AsyncTask.access$600(AsyncTask.java:177)
02-11 15:44:33.487: E/AndroidRuntime(1336):     at   android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:644)
02-11 15:44:33.487: E/AndroidRuntime(1336):     at   android.os.Handler.dispatchMessage(Handler.java:99)

and another one

02-11 15:44:39.867: E/SQLiteLog(1357): (14) cannot open file at line 30191 of [00bb9c9ce4]
02-11 15:44:39.867: E/SQLiteLog(1357): (14) os_unix.c:30191: (2)   open(/data/data/com.mabongar.survey/databases/responsetable.db) - 
02-11 15:44:40.017: E/SQLiteDatabase(1357): Failed to open database    '/data/data/com.mabongar.survey/databases/responsetable.db'.
02-11 15:44:40.017: E/SQLiteDatabase(1357): android.database.sqlite.SQLiteCantOpenDatabaseException:   unknown error (code 14): Could not open database
02-11 15:44:40.017: E/SQLiteDatabase(1357):     at   android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)

EDIT *FragmentStatePagerActivity which downloads from mysql server,passes values to PagerAdapter which then loads the fragments*

public class FragmentStatePagerActivity extends ActionBarActivity {

public SQLiteDatabase db;
private final String DB_PATH = "/data/data/com.mabongar.survey/databases/";

private static final String DATABASE_NAME = "responsetable.db";
// AsyncTask Class

private class FetchQuestions extends AsyncTask<String, Void, String> {

    @SuppressWarnings("static-access")
    @Override
    protected String doInBackground(String... params) {

        if (params == null)

            return null;

        try{

            String mPath = DB_PATH + DATABASE_NAME;

            db = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CONFLICT_NONE);

        }catch(SQLException e){

            Log.e("Error ", "while opening database");
            e.printStackTrace();
        }

//          // get url from params

        String url = params[0];

        try {
            // create http connection
            HttpClient client = new DefaultHttpClient();
            HttpGet httpget = new HttpGet(url);

            // connect
            HttpResponse response = client.execute(httpget);

            // get response
            HttpEntity entity = response.getEntity();

            if (entity == null) {
                return null;
            }

            // we get response content and convert it to json string
            InputStream is = entity.getContent();
            return streamToString(is);
        } catch (IOException e) {
            Log.e("Log message", "No network connection");
        }

        return null;
    }

}

As you can see,that is how i open it in the doInBackground() method Then i also open it in the pagerAdapter class,because it has the public ArrayList SelectAll() method which you just showed me in youe second answer.And finally i open it in the TableAnswers class and TableQuestions class as well because we're inserting data into the database.

Answer 1

you have to create two table 1)question parent 2)answer

1)question table field:

    auto_Id (primary Key) auto increment
    question_id
    description
    widget_id

2)answer Table field:

   auto_Id (primary Key) auto increment
   answer_text
   answer_id
   question_id

public void table_question{
//this functin is used for insert data .......when pass data from json
public void insert(Arraylist<Model_question> modelArrlist){

     for (Model_question model : modelArrlist) {
    ContentValues values = new ContentValues();
        values.put(auto_Id, model.auto_Id);
        values.put(question_id, model.question_id);
        values.put(description, model.description);
        values.put(widget_id, model.widget_id);
        sqldb.insert(TableName, null, values);

        for(Model_answer model_answer :model.arrAnswerList)
        {
           model_answer.question_id=model.question_id
          Tbl_answer.insert(model_master);
        }
     }

}
}

//this is tbl_answer insert method

public class tbl_answer{

public void insert(Model_answer model_answer){

    ContentValues values = new ContentValues();
        values.put(auto_Id, model.auto_Id);
        values.put(question_id, model.question_id);
        values.put(answer_text, model.answer_text);
      values.put(answer_id, model.answer_id);

      }
}


public void Model_question {

    public String question_id,
        description,
        widget_id;
   public List<Model_answer> arrAnswerList=new ArrayList<Model_answer>;
}

public void Model_answer{

    public String answer_text,
       answer_id,
       question_id;

 }

please check this code this code will be useful to insert data into both table..sucessfully..

Answer 2

You can make Objects of a model class depending on the QuestionId. Where your model class would include an ArrayList of Answers, which would be another model class. Based on question id, you have one object which would have all answers for that question.

That can be determined by a for each loop.

class QuestionModel {
  String questionId;
  String description;
  String widgetId;
  ArrayList<Answers> answers;
  //getter setters here
}  

class AnswersModel{
 String answerText;
 String answerId;
//getter setter
}  

While inserting, use a for each loop -> for each object & answers list in QuestionModel ->match with question id and insert accordingly.
While retrieving you have the list sorted out.
http://docs.oracle.com/javase/1.5.0/docs/guide/language/foreach.html
How does the Java 'for each' loop work?

Also, instead of json parsing like in your code, you can use Gson and map the response directly with your class, something like:

Gson gson = new Gson();
QuestionModel questionModel= new QuestionModel();
questionModel= gson.fromJson(responseContent,QuestionModel.class); 
//where responseContent is your jsonString  

Then you don't have to handle parsing or checking Answer array separately.
Check: https://code.google.com/p/google-gson/

For Naming discrepancies(according to the variables in webservice), can use annotations like @SerializedName. (So no need to use Serializable)

Answer 3

public static ArrayList<Model_question> SelectAll() 
    ArrayList<Model_question> arrModelList = null;
        Cursor cursor = null;
        String Query = "Select * from " + TableName;
        cursor = sqldb.rawQuery(Query, null);
        if (cursor != null && cursor.moveToFirst()) {
            arrModelList = new ArrayList<Model_question>();
            do {
                Model_question model = new Model_question();
                        model.auto_Id= (cursor.getString(cursor.getColumnIndex("auto_Id")));

                model.question_id= (cursor.getString(cursor.getColumnIndex("question_id")));
                model.description= (cursor.getString(cursor
                        .getColumnIndex("description")));
                model.widget_id= (cursor.getString(cursor.getColumnIndex("widget_id")));
                        model.arrAnswerList= Tbl_answer
                        .selectIdWiseData(model.question_id);
                arrModelList.add(model);
            } while (cursor.moveToNext());
            cursor.close();
        }// end if(cursor!=null)
        return arrModelList;
    }
}

public void selectIdWiseData(String question_id){

ArrayList<Model_answer> arrayList = null;
        Log.d("tag", "Model_answerId" + inId);
        String Query = "Select * from " + TableName
                + " where question_id='" + question_id+ "'";
        Log.d("tag", "Model_answer Query" + Query);
        Cursor cursor = sqldb.rawQuery(Query, null);
        if (cursor != null && cursor.moveToFirst()) {
            arrayList = new ArrayList<Model_answer>();
            do {
                Model_answer model = new Model_answer();
                model.autoId = (cursor.getString(cursor.getColumnIndex(AUOTID)));
                model.question_id= (cursor.getString(cursor
                        .getColumnIndex("question_id")));
                model.answer_text= (cursor.getString(cursor
                        .getColumnIndex("answer_text")));
model.answer_id= (cursor.getString(cursor
                        .getColumnIndex("answer_id")));


                arrayList.add(model);
            } while (cursor.moveToNext());
            cursor.close();
        }// end if(cursor!=null)
        return arrayList;
}