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;
}
}
