Storing quiz questions in SQLite

Question

I am creating my first android app. It is going to be a quiz.

There are 40 different question categories and each category has its own database table.

I am writing all questions into a sqlite database like this:

private void addQuestions() {
    // Category 1 (ACE) Questions
    // First Question
    String aceQuestion1 = MainActivity.getContext().getResources().getString(R.string.ace_Question1);
    String aceQuestion1a = MainActivity.getContext().getResources().getString(R.string.ace_Question1a);
    String aceQuestion1b = MainActivity.getContext().getResources().getString(R.string.ace_Question1b);
    String aceQuestion1c = MainActivity.getContext().getResources().getString(R.string.ace_Question1c);
    String aceQuestion1d = MainActivity.getContext().getResources().getString(R.string.ace_Question1d);
    Question ace1 = new Question(aceQuestion1, aceQuestion1a, aceQuestion1b, aceQuestion1c, aceQuestion1d, aceQuestion1b);
    this.addACEQuestion(ace1);

    // Second Question
    String aceQuestion2 = MainActivity.getContext().getResources().getString(R.string.ace_Question2);
    String aceQuestion2a = MainActivity.getContext().getResources().getString(R.string.ace_Question2a);
    String aceQuestion2b = MainActivity.getContext().getResources().getString(R.string.ace_Question2b);
    String aceQuestion2c = MainActivity.getContext().getResources().getString(R.string.ace_Question2c);
    String aceQuestion2d = MainActivity.getContext().getResources().getString(R.string.ace_Question2d);
    Question ace2 = new Question(aceQuestion2, aceQuestion2a, aceQuestion2b, aceQuestion2c, aceQuestion2d, aceQuestion2d);
    this.addACEQuestion(ace2);

    // Category 2 (Androgen) Questions
    // First Question
    String androgensQuestion1 = MainActivity.getContext().getResources().getString(R.string.androgens_Question1);
    String androgensQuestion1a = MainActivity.getContext().getResources().getString(R.string.androgens_Question1a);
    String androgensQuestion1b = MainActivity.getContext().getResources().getString(R.string.androgens_Question1b);
    String androgensQuestion1c = MainActivity.getContext().getResources().getString(R.string.androgens_Question1c);
    String androgensQuestion1d = MainActivity.getContext().getResources().getString(R.string.androgens_Question1d);
    Question androgens1 = new Question(androgensQuestion1, androgensQuestion1a, androgensQuestion1b, androgensQuestion1c, androgensQuestion1d, androgensQuestion1c);
    this.addAndrogensQuestion(androgens1);

    // Second Question
    String androgensQuestion2 = MainActivity.getContext().getResources().getString(R.string.androgens_Question2);
    String androgensQuestion2a = MainActivity.getContext().getResources().getString(R.string.androgens_Question2a);
    String androgensQuestion2b = MainActivity.getContext().getResources().getString(R.string.androgens_Question2b);
    String androgensQuestion2c = MainActivity.getContext().getResources().getString(R.string.androgens_Question2c);
    String androgensQuestion2d = MainActivity.getContext().getResources().getString(R.string.androgens_Question2d);
    Question androgens2 = new Question(androgensQuestion2, androgensQuestion2a, androgensQuestion2b, androgensQuestion2c, androgensQuestion2d, androgensQuestion2b);
    this.addAndrogensQuestion(androgens2);
}

// Add Category 1 (ACE) Questions
public void addACEQuestion(Question quest) {
    addQuestion(quest, TABLE_ACE);
}

// Add Category 2 (Androgens) Questions
public void addAndrogensQuestion(Question quest) {
    addQuestion(quest, TABLE_ANDROGENS);
}

public void addQuestion(Question quest, String table) {
    //SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(QUES, quest.getQUESTION());
    values.put(OPTA, quest.getOPTA());
    values.put(OPTB, quest.getOPTB());
    values.put(OPTC, quest.getOPTC());
    values.put(OPTD, quest.getOPTD());
    values.put(ANSWER, quest.getANSWER());
    // Inserting Rows
    database.insert(table, null, values);
}

This works fine but I will add about 30 questions for each category, so there will be more than 1000 questions and the code will become unmanagable, if I implement it like this.

I tried to implement a for loop for each category but unfortunately did not manage.

How can I simplify this code and make it less repetitive?

---

The Question Object

public class Question {
private int ID;
private String QUESTION;
private String OPTA;
private String OPTB;
private String OPTC;
private String OPTD;
private String ANSWER;

public Question() {
    ID=0;
    QUESTION="";
    OPTA="";
    OPTB="";
    OPTC="";
    OPTD="";
    ANSWER="";
}

public Question(String question, String opta, String optb, String optc, String optd, String answer) {
    QUESTION = question;
    OPTA = opta;
    OPTB = optb;
    OPTC = optc;
    OPTD = optd;
    ANSWER = answer;
}

public int getID() { return ID; }
public String getQUESTION() { return QUESTION; }
public String getOPTA() { return OPTA; }
public String getOPTB() { return OPTB; }
public String getOPTC() {
    return OPTC;
}
public String getOPTD() {
    return OPTD;
}
public String getANSWER() { return ANSWER; }

public void setID(int id)
{
    ID=id;
}
public void setQUESTION(String question) {
    QUESTION = question;
}
public void setOPTA(String opta) {
    OPTA = opta;
}
public void setOPTB(String optb) {
    OPTB = optb;
}
public void setOPTC(String optc) { OPTC = optc; }
public void setOPTD(String optd) { OPTD = optd; }
public void setANSWER(String answer) { ANSWER = answer; }
}

---

My unsuccessful approach

private void addQuestions() {
    for (int i = 0; i <= 30; i++) {
        // ACE Questions
        String aceQuestioni = MainActivity.getContext().getResources().getString(R.string."ace_Question" + i);
        String aceQuestionia = MainActivity.getContext().getResources().getString(R.string."ace_Question" + i + "a");
        String aceQuestionib = MainActivity.getContext().getResources().getString(R.string."ace_Question" + i + "b");
        String aceQuestionic = MainActivity.getContext().getResources().getString(R.string."ace_Question" + i + "c");
        String aceQuestionid = MainActivity.getContext().getResources().getString(R.string."ace_Question" + i + "d");
        Question ace1 = new Question(aceQuestion1, aceQuestion1a, aceQuestion1b, aceQuestion1c, aceQuestion1d, aceQuestion1b);
        this.addACEQuestion(ace1);
        Question ace2 = new Question(aceQuestion2, aceQuestion2a, aceQuestion2b, aceQuestion2c, aceQuestion2d, aceQuestion2d);
        this.addACEQuestion(ace2);
    }
}

Answer 1

The problem is that R.string.ace_Question2 are Java identifiers (most likely for strings) and that there are separate identifiers for the question and the answers (I guess).

Splitt the constants in R.string into two enums

class R {
  enum QandAkey {
   KEY_QUESTION, KEY_ANSWER_A, KEY_ANSWER_B /*, ...*/;
  }
  enum QuestionKey { // string is not a good name!!
    ace_Question1,ace_Question1,androgens_Question1,androgens_Question2 /*,...*/;
  }
}

Then you could simplify your code to a loop:

for(QuestionKey qk : R.QuestionKey.values()) {
  String[] questionAndAnswers = new String[R.QandA.values().length];
  for(QandAkey qak : R.QandAkey.values()){
     questionAndAnswers[qak.ordinal()]
       = MainActivity.getContext().getResources()
               .getString(qk.name()+"."+qak.name());
  }
  // let the Question-constructor deal with the strings array itself 
  addAndrogensQuestion( new Question(questionAndAnswers)); 
}

offcause you have to change the keys of the entries in the Resource property to match the new pattern:

ace_Question1.KEY_QUESTION = dflafDSTDJWEfd
ace_Question1.KEY_ANSWER_A = dflafDSTDJWEfd
ace_Question1.KEY_ANSWER_B = dflafDSTDJWEfd

If you want to add more question you only need to add for each question a single new constant in QuestionKey and as many entries in the resource file as you have QandAkey entires.

Answer 2

Another problem in your approach is that you select the correct answer at runtime.
The answer should be saved as reference to the correct answer in the resources, not determined during runtime:

ace_Question1.KEY_QUESTION = dflafDSTDJWEfd
ace_Question1.KEY_CORRECT = KEY_ANSWER_A 
ace_Question1.KEY_ANSWER_A = dflafDSTDJWEfd
ace_Question1.KEY_ANSWER_B = dflafDSTDJWEfd

ace_Question2.KEY_QUESTION = dflafDSTDJWEfd
ace_Question2.KEY_CORRECT = KEY_ANSWER_B 
ace_Question2.KEY_ANSWER_A = dflafDSTDJWEfd
ace_Question2.KEY_ANSWER_B = dflafDSTDJWEfd

This requires a second default entry in the first enum:

  enum QandAkey {
   KEY_QUESTION, KEY_CORRECT, KEY_ANSWER_A, KEY_ANSWER_B /*, ...*/;
   // the order of entries here does not need to reflect
   // the order of entries in the resource file
   // as long as we can rely on String type keys...
  }

And then we have to change the Question constructor to cope with th new approach from my first answer (resolving Error:(422, 28) error: no suitable constructor found for Question(String[])) and the changes in this one:

public Question(String... questionAndAnswers) {
    QUESTION = questionAndAnswers[QandAkey.KEY_QUESTION.ordinal()];
    OPTA =questionAndAnswers[QandAkey.KEY_ANSWER_A.ordinal();
    OPTB = questionAndAnswers[QandAkey.KEY_ANSWER_B.ordinal();
    OPTC = questionAndAnswers[QandAkey.KEY_ANSWER_C.ordinal();
    OPTD = questionAndAnswers[QandAkey.KEY_ANSWER_A.ordinal();
    ANSWER = questionAndAnswers[QandAkey.valueOf(
                  questionAndAnswers[QandAkey.KEY_CORRECT.ordinal()]
                ).ordinal()
             ];
}

This is the lease destructive change to the Question class.
Of cause the much better solution was to change ANSWER to int and store the index, but this would require more changes in the class.

Answer 3

Since your questions and answers are stored in an XML you could use JaxB to create the collection of Questions. https://docs.oracle.com/javase/tutorial/jaxb/intro/examples.html

You first have to enhance your Question class with JaxB annotations like This:

@XmlElement
public class Question {
    private int ID;
    @XmlElement(name="TheQuestion") // because we need a different Name in XML
    private String QUESTION;
    @XmlElement
    private String OPTA;
    @XmlElement
    private String OPTB;
    @XmlElement
    private String OPTC;
    @XmlElement
    private String OPTD;
    @XmlAttribute(name="answer") // because of case change
    private int ANSWER;

    // could be removed completely...
    public Question() {
    }

Then you have to create the mapping code with the jaxb compiler as mentioned in the tutorial.

and Your XML must change from what ever it looks now to this (or similar)

<Questions> <!-- the root element -->
  <Question answer="2">
    <TheQuestion>flaiufdgl</TheQuestion>
    <OPTA>flaiufdgl</OPTA>
    <OPTB>flaiufdgl</OPTB>
    <OPTC>flaiufdgl</OPTC>
    <OPTD>flaiufdgl</OPTD>
  </Question>
</Questions>