How can I reduce code duplication for multiple tables?

I am making an app with questions and answers, a knowledge game if you will. I created a database schema and a Question object.

There is an enormous amount of duplicate code. It was fine when I had 2 tables, but I added 4 more and now it's almost 400 lines of code. Aside from the final strings which are SQLite code, how can I reduce the amount of duplicate code?

This is where I add questions (I have 5 more for the rest of 5 games):

private void addQuestionToGame1(Question question) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(QuestionsTable.COLUMN_QUESTION, question.getQuestion());
        contentValues.put(QuestionsTable.COLUMN_ANSWER1, question.getAnswer1());
        contentValues.put(QuestionsTable.COLUMN_ANSWER2, question.getAnswer2());
        contentValues.put(QuestionsTable.COLUMN_ANSWER3, question.getAnswer3());
        contentValues.put(QuestionsTable.COLUMN_ANSWER4, question.getAnswer4());
        contentValues.put(QuestionsTable.COLUMN_CORRECT_ANSWER_ID, question.getCorrectAnswerID());
        db.insert(QuestionsTable.TABLE_NAME1, null, contentValues);
    }

This is where I get them as ArrayList (again, I have 5 of these):

@SuppressLint("Range")
    public ArrayList<Question> getAllBeginnerGame1() {
        ArrayList<Question> questionsArrayList = new ArrayList<>();
        db = getReadableDatabase();

        Cursor c = db.rawQuery("SELECT * FROM " + QuestionsTable.TABLE_NAME1, null);
        if (c.moveToFirst()) {
            do {
                Question question = new Question();
                question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
                question.setAnswer1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER1)));
                question.setAnswer2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER2)));
                question.setAnswer3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER3)));
                question.setAnswer4(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER4)));
                question.setCorrectAnswerID(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_CORRECT_ANSWER_ID)));
                questionsArrayList.add(question);
            } while (c.moveToNext());
        }

        c.close();
        return questionsArrayList;
    }

Just because a different TABLE_NAME, I am forced to duplicate code for the other tables. Is there a way around this?

Solution

Just because a different TABLE_NAME, I am forced to duplicate code for the other tables. Is there a way around this?

Flexible Table Naming

Instead of addQuestionToGame1, addQuestionToGame2 ....

You could, if the table names all end with the Game number have something like:-

private void addQuestionToGame1(Question question, Int gameNumber) {
    String table = QuestionsTable.BASE_TABLE_NAME + String/valueOf(gameNumber);
    ContentValues contentValues = new ContentValues();
    contentValues.put(QuestionsTable.COLUMN_QUESTION, question.getQuestion());
    contentValues.put(QuestionsTable.COLUMN_ANSWER1, question.getAnswer1());
    contentValues.put(QuestionsTable.COLUMN_ANSWER2, question.getAnswer2());
    contentValues.put(QuestionsTable.COLUMN_ANSWER3, question.getAnswer3());
    contentValues.put(QuestionsTable.COLUMN_ANSWER4, question.getAnswer4());
    contentValues.put(QuestionsTable.COLUMN_CORRECT_ANSWER_ID, question.getCorrectAnswerID());
    db.insert(table, null, contentValues);
}

Obviously BASE_TABLE_NAME would be the table name less the game number suffix.
likewise for the method used to extract the data.

A Single Table

An alternative approach would be to have a single table that has an additional column for the game number, rather than having separate tables.

So again you would pass the game number to the methods but in the case of inserting into a table based upon the game number you would insert into the same table but including the game number.

When extracting, again you pass the game number but you include the passed game number as part of a WHERE clause.

So for extract an ArrayList you could have something like:-

@SuppressLint("Range")
public ArrayList<Question> getAllQuestionsByGameNumber(Int gameNumber) {
    ArrayList<Question> questionsArrayList = new ArrayList<>();
    db = getReadableDatabase();

    Cursor c = db.rawQuery("SELECT * FROM " + QuestionsTable.BASE_TABLE_NAME + " WHERE " + QuestionsTable.COLUMN_GAMENUMBER + "=?", new String[]{String.valueOf(gameNumber)});
    if (c.moveToFirst()) {
        do {
            Question question = new Question();
            question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
            question.setAnswer1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER1)));
            question.setAnswer2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER2)));
            question.setAnswer3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER3)));
            question.setAnswer4(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER4)));
            question.setCorrectAnswerID(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_CORRECT_ANSWER_ID)));
            questionsArrayList.add(question);
        } while (c.moveToNext());
    }

    c.close();
    return questionsArrayList;
}

You save a little code but using the convenience query method rather than raw query. This builds the SQL based upon parameters

So you could have :-

Cursor c = db.query(QuestionsTable.BASE_TABLE_NAME,null,QuestionsTable.COLUMN_GAMENUMBER + "=?",new String[]{String.valueOf(gameNumber)},null,null,null);

instead of Cursor c = db.rawQuery(....

The latter single table solution would be considered the better option of the 2. Each table takes up a minimum of 4k (assuming the default page size) as each table will have at least 1 page even if there is no data. The SQLite routines only have to consider the single table, there is more likelihood that cached data will be the required data so there may well be fewer disk accesses.

The only real benefit of the multiple table scenario is for human readability aspect, for some.