0
\$\begingroup\$

I want to show you the SQLiteOpenHelper and Contract class for a little quiz app I made for practicing purposes. I have 3 tables for 3 different categories. The approach works, but I wonder if I should remove redundancy. As you can see, there is a lot of similar code for creating and filling those tables, but I feel like it would be confusing if I would combine these methods into 1.

Should I keep creating and querying the tables like this, or should I pass the table name as an argument to the method and then combine 3 similar methods to 1?

What do you think?

Contract:

public final class QuizContract {

    private QuizContract() {
    }

    public static class Category1Table implements BaseColumns {
        public static final String TABLE_NAME = "category1_questions";
        public static final String COLUMN_QUESTION = "question";
        public static final String COLUMN_OPTION1 = "option1";
        public static final String COLUMN_OPTION2 = "option2";
        public static final String COLUMN_OPTION3 = "option3";
        public static final String COLUMN_ANSWER_NR = "answer_nr";
    }

    public static class Category2Table implements BaseColumns {
        public static final String TABLE_NAME = "category2_questions";
        public static final String COLUMN_QUESTION = "question";
        public static final String COLUMN_OPTION1 = "option1";
        public static final String COLUMN_OPTION2 = "option2";
        public static final String COLUMN_OPTION3 = "option3";
        public static final String COLUMN_ANSWER_NR = "answer_nr";
    }

    public static class Category3Table implements BaseColumns {
        public static final String TABLE_NAME = "category3_questions";
        public static final String COLUMN_QUESTION = "question";
        public static final String COLUMN_OPTION1 = "option1";
        public static final String COLUMN_OPTION2 = "option2";
        public static final String COLUMN_OPTION3 = "option3";
        public static final String COLUMN_ANSWER_NR = "answer_nr";
    }
}

SQLiteOpenHelper:

public class QuizDbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "MyAwesomeQuiz.db";
    private static final int DATABASE_VERSION = 3;

    private SQLiteDatabase db;

    public QuizDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;

        final String SQL_CREATE_CAT1_TABLE = "CREATE TABLE " +
                Category1Table.TABLE_NAME + " ( " +
                Category1Table._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                Category1Table.COLUMN_QUESTION + " TEXT, " +
                Category1Table.COLUMN_OPTION1 + " TEXT, " +
                Category1Table.COLUMN_OPTION2 + " TEXT, " +
                Category1Table.COLUMN_OPTION3 + " TEXT, " +
                Category1Table.COLUMN_ANSWER_NR + " INTEGER" +
                ")";

        final String SQL_CREATE_CAT2_TABLE = "CREATE TABLE " +
                Category2Table.TABLE_NAME + " ( " +
                Category2Table._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                Category2Table.COLUMN_QUESTION + " TEXT, " +
                Category2Table.COLUMN_OPTION1 + " TEXT, " +
                Category2Table.COLUMN_OPTION2 + " TEXT, " +
                Category2Table.COLUMN_OPTION3 + " TEXT, " +
                Category2Table.COLUMN_ANSWER_NR + " INTEGER" +
                ")";

        final String SQL_CREATE_CAT3_TABLE = "CREATE TABLE " +
                Category3Table.TABLE_NAME + " ( " +
                Category3Table._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                Category3Table.COLUMN_QUESTION + " TEXT, " +
                Category3Table.COLUMN_OPTION1 + " TEXT, " +
                Category3Table.COLUMN_OPTION2 + " TEXT, " +
                Category3Table.COLUMN_OPTION3 + " TEXT, " +
                Category3Table.COLUMN_ANSWER_NR + " INTEGER" +
                ")";

        db.execSQL(SQL_CREATE_CAT1_TABLE);
        db.execSQL(SQL_CREATE_CAT2_TABLE);
        db.execSQL(SQL_CREATE_CAT3_TABLE);
        fillCategory1Table();
        fillCategory2Table();
        fillCategory3Table();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + Category1Table.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Category2Table.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Category3Table.TABLE_NAME);
        onCreate(db);
    }

    private void fillCategory1Table() {
        Question q1 = new Question("(C1) A is correct", "A", "B", "C", 1);
        addQuestionToCategory1(q1);
        Question q2 = new Question("(C1) B is correct", "A", "B", "C", 2);
        addQuestionToCategory1(q2);
        Question q3 = new Question("(C1) C is correct", "A", "B", "C", 3);
        addQuestionToCategory1(q3);
    }

    private void fillCategory2Table() {
        Question q1 = new Question("(C2) A is correct", "A", "B", "C", 1);
        addQuestionToCategory2(q1);
        Question q2 = new Question("(C2) B is correct", "A", "B", "C", 2);
        addQuestionToCategory2(q2);
        Question q3 = new Question("(C2) C is correct", "A", "B", "C", 3);
        addQuestionToCategory2(q3);
        Question q4 = new Question("(C2) A is correct again", "A", "B", "C", 1);
        addQuestionToCategory2(q4);
    }

    private void fillCategory3Table() {
        Question q1 = new Question("(C3) A is correct", "A", "B", "C", 1);
        addQuestionToCategory3(q1);
        Question q2 = new Question("(C3) B is correct", "A", "B", "C", 2);
        addQuestionToCategory3(q2);
        Question q3 = new Question("(C3) C is correct", "A", "B", "C", 3);
        addQuestionToCategory3(q3);
        Question q4 = new Question("(C3) A is correct again", "A", "B", "C", 1);
        addQuestionToCategory3(q4);
        Question q5 = new Question("(C3) B is correct again", "A", "B", "C", 2);
        addQuestionToCategory3(q5);
    }

    private void addQuestionToCategory1(Question question) {
        ContentValues cv = new ContentValues();
        cv.put(Category1Table.COLUMN_QUESTION, question.getQuestion());
        cv.put(Category1Table.COLUMN_OPTION1, question.getOption1());
        cv.put(Category1Table.COLUMN_OPTION2, question.getOption2());
        cv.put(Category1Table.COLUMN_OPTION3, question.getOption3());
        cv.put(Category1Table.COLUMN_ANSWER_NR, question.getAnswerNr());
        db.insert(Category1Table.TABLE_NAME, null, cv);
    }

    private void addQuestionToCategory2(Question question) {
        ContentValues cv = new ContentValues();
        cv.put(Category2Table.COLUMN_QUESTION, question.getQuestion());
        cv.put(Category2Table.COLUMN_OPTION1, question.getOption1());
        cv.put(Category2Table.COLUMN_OPTION2, question.getOption2());
        cv.put(Category2Table.COLUMN_OPTION3, question.getOption3());
        cv.put(Category2Table.COLUMN_ANSWER_NR, question.getAnswerNr());
        db.insert(Category2Table.TABLE_NAME, null, cv);
    }

    private void addQuestionToCategory3(Question question) {
        ContentValues cv = new ContentValues();
        cv.put(Category3Table.COLUMN_QUESTION, question.getQuestion());
        cv.put(Category3Table.COLUMN_OPTION1, question.getOption1());
        cv.put(Category3Table.COLUMN_OPTION2, question.getOption2());
        cv.put(Category3Table.COLUMN_OPTION3, question.getOption3());
        cv.put(Category3Table.COLUMN_ANSWER_NR, question.getAnswerNr());
        db.insert(Category3Table.TABLE_NAME, null, cv);
    }

    public ArrayList<Question> getAllCategory1Questions() {
        ArrayList<Question> questionList = new ArrayList<>();
        db = getReadableDatabase();
        Cursor c = db.rawQuery("SELECT * FROM " + Category1Table.TABLE_NAME, null);

        if (c.moveToFirst()) {
            do {
                Question question = new Question();
                question.setQuestion(c.getString(c.getColumnIndex(Category1Table.COLUMN_QUESTION)));
                question.setOption1(c.getString(c.getColumnIndex(Category1Table.COLUMN_OPTION1)));
                question.setOption2(c.getString(c.getColumnIndex(Category1Table.COLUMN_OPTION2)));
                question.setOption3(c.getString(c.getColumnIndex(Category1Table.COLUMN_OPTION3)));
                question.setAnswerNr(c.getInt(c.getColumnIndex(Category1Table.COLUMN_ANSWER_NR)));
                questionList.add(question);
            } while (c.moveToNext());
        }

        c.close();
        return questionList;
    }

    public ArrayList<Question> getAllCategory2Questions() {
        ArrayList<Question> questionList = new ArrayList<>();
        db = getReadableDatabase();
        Cursor c = db.rawQuery("SELECT * FROM " + Category2Table.TABLE_NAME, null);

        if (c.moveToFirst()) {
            do {
                Question question = new Question();
                question.setQuestion(c.getString(c.getColumnIndex(Category2Table.COLUMN_QUESTION)));
                question.setOption1(c.getString(c.getColumnIndex(Category2Table.COLUMN_OPTION1)));
                question.setOption2(c.getString(c.getColumnIndex(Category2Table.COLUMN_OPTION2)));
                question.setOption3(c.getString(c.getColumnIndex(Category2Table.COLUMN_OPTION3)));
                question.setAnswerNr(c.getInt(c.getColumnIndex(Category2Table.COLUMN_ANSWER_NR)));
                questionList.add(question);
            } while (c.moveToNext());
        }

        c.close();
        return questionList;
    }

    public ArrayList<Question> getAllCategory3Questions() {
        ArrayList<Question> questionList = new ArrayList<>();
        db = getReadableDatabase();
        Cursor c = db.rawQuery("SELECT * FROM " + Category3Table.TABLE_NAME, null);

        if (c.moveToFirst()) {
            do {
                Question question = new Question();
                question.setQuestion(c.getString(c.getColumnIndex(Category3Table.COLUMN_QUESTION)));
                question.setOption1(c.getString(c.getColumnIndex(Category3Table.COLUMN_OPTION1)));
                question.setOption2(c.getString(c.getColumnIndex(Category3Table.COLUMN_OPTION2)));
                question.setOption3(c.getString(c.getColumnIndex(Category3Table.COLUMN_OPTION3)));
                question.setAnswerNr(c.getInt(c.getColumnIndex(Category3Table.COLUMN_ANSWER_NR)));
                questionList.add(question);
            } while (c.moveToNext());
        }

        c.close();
        return questionList;
    }
}
\$\endgroup\$
1
\$\begingroup\$

Why do you have 3 different tables? They look to me the same. If there are logic difference between them, you could have a column/attribute like an enum to make the difference clear.

If you want to keep three different tables, you could use repository pattern to solve simple CRUD access.

\$\endgroup\$
  • \$\begingroup\$ Thanks for your answer! I am trying to figure out how to properly add categories. Do you think I should just add another column? I later want to give the user the option to add his own questions together with his own categories. \$\endgroup\$ – Florian Walther Apr 8 '18 at 17:05
  • \$\begingroup\$ If users can add categories, then each category must be a row in that Category table. For the initial categories, you could added their corresponding rows when you create the database. Drawing the system will help you to understand what you really want to accomplish \$\endgroup\$ – A Bravo Dev Apr 8 '18 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.