Edit: I changed my approach.
I am trying to add categorieswant to my existing SQLiteshow you the SQLiteOpenHelper and Contract class for a little quiz app I made for practicing purposes. This is myI have 3 tables for 3 different categories. The approach works, what do you think about it? Dobut I wonder if I should remove redundancy. As you think the foreign key restrictioncan see, there is necessary? I am a total SQLite nooblot of similar code for creating and it's the first timefilling those tables, but I use a foreign keyfeel 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 class QuizDbHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "MyAwesomeQuiz.db";
private static final int DATABASE_VERSIONclass =QuizContract 1;{
private SQLiteDatabaseQuizContract() db;{
}
public QuizDbHelper(Contextstatic context)class Category1Table implements BaseColumns {
super(context, DATABASE_NAME, null, DATABASE_VERSION); 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;
@Override
public void onCreate(SQLiteDatabase db) {
this.dbprivate =SQLiteDatabase db;
final String SQL_CREATE_CATEGORIES_TABLE = "CREATE TABLE " +
CategoriesTable.TABLE_NAME +public "QuizDbHelper(Context "context) +{
CategoriesTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENTsuper(context, " +
CategoriesTable.COLUMN_NAME + " TEXTDATABASE_NAME, "null, +DATABASE_VERSION);
")";}
final String SQL_CREATE_QUESTIONS_TABLE = "CREATE TABLE " +
QuestionsTable.TABLE_NAME + " ( " +
QuestionsTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
QuestionsTable.COLUMN_QUESTION + " TEXT, " +
QuestionsTable.COLUMN_OPTION1 + " TEXT, " +
QuestionsTable.COLUMN_OPTION2 + " TEXT, " +
QuestionsTable.COLUMN_OPTION3 + " TEXT, " +
QuestionsTable.COLUMN_ANSWER_NR + " INTEGER, " +
QuestionsTable.COLUMN_DIFFICULTY + " TEXT, " +
QuestionsTable.COLUMN_CATEGORY + " INTEGER, " +@Override
public "FOREIGNvoid KEYonCreate(" + QuestionsTable.COLUMN_CATEGORY +SQLiteDatabase "db) REFERENCES " +{
CategoriesTable.TABLE_NAME + "(" + QuestionsTablethis._ID + ")" +
db = ")";db;
db final String SQL_CREATE_CAT1_TABLE = "CREATE TABLE " +
Category1Table.execSQLTABLE_NAME + " (SQL_CREATE_CATEGORIES_TABLE); " +
db Category1Table.execSQL(SQL_CREATE_QUESTIONS_TABLE);_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
fillCategoriesTable(); Category1Table.COLUMN_QUESTION + " TEXT, " +
fillQuestionsTable(); Category1Table.COLUMN_OPTION1 + " TEXT, " +
} Category1Table.COLUMN_OPTION2 + " TEXT, " +
Category1Table.COLUMN_OPTION3 + " TEXT, " +
Category1Table.COLUMN_ANSWER_NR + " INTEGER" +
")";
@Override final String SQL_CREATE_CAT2_TABLE = "CREATE TABLE " +
public void onUpgrade Category2Table.TABLE_NAME + " (SQLiteDatabase db" +
Category2Table._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, int" oldVersion+
Category2Table.COLUMN_QUESTION + " TEXT, int" newVersion)+
{ Category2Table.COLUMN_OPTION1 + " TEXT, " +
db Category2Table.execSQL("DROPCOLUMN_OPTION2 TABLE+ IF" EXISTSTEXT, " +
QuestionsTable Category2Table.TABLE_NAME);COLUMN_OPTION3 + " TEXT, " +
onCreate(db); Category2Table.COLUMN_ANSWER_NR + " INTEGER" +
} ")";
@Override final String SQL_CREATE_CAT3_TABLE = "CREATE TABLE " +
public void onConfigure Category3Table.TABLE_NAME + " (SQLiteDatabase db)" {+
super Category3Table.onConfigure(db);_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
db Category3Table.setForeignKeyConstraintsEnabled(true);COLUMN_QUESTION + " TEXT, " +
} Category3Table.COLUMN_OPTION1 + " TEXT, " +
Category3Table.COLUMN_OPTION2 + " TEXT, " +
Category3Table.COLUMN_OPTION3 + " TEXT, " +
Category3Table.COLUMN_ANSWER_NR + " INTEGER" +
")";
private void fillQuestionsTable() {
Question q1 = new Question("Easy: A is correct",
"A", "B", "C", 1, Questiondb.DIFFICULTY_EASY, 1);
addQuestionexecSQL(q1SQL_CREATE_CAT1_TABLE);
Question q2 = new Question("Medium: B is correct",
"A", "B", "C", 2, Questiondb.DIFFICULTY_MEDIUM, 2);
addQuestionexecSQL(q2SQL_CREATE_CAT2_TABLE);
Question q3 = new Question("Medium: C is correct",
"A", "B", "C", 3, Questiondb.DIFFICULTY_MEDIUM, 3);
addQuestionexecSQL(q3SQL_CREATE_CAT3_TABLE);
Question q4 = new Question("Hard: A is correct",
"A", "B", "C", 1, Question.DIFFICULTY_HARD, 4);
addQuestionfillCategory1Table(q4);
Question q5 = new Question("Hard: B is correct",
"A", "B", "C", 2, Question.DIFFICULTY_HARD, 5);
addQuestionfillCategory2Table(q5);
Question q6 = new QuestionfillCategory3Table("Hard: C is correct",
"A", "B", "C", 3, Question.DIFFICULTY_HARD, 6);
addQuestion(q6);
}
private void addQuestion(Question question) {@Override
ContentValues cv =public newvoid ContentValuesonUpgrade();
SQLiteDatabase db, int cv.put(QuestionsTable.COLUMN_QUESTIONoldVersion, question.getQuestion()int newVersion); {
cv.put(QuestionsTable.COLUMN_OPTION1, question db.getOption1execSQL());
"DROP TABLE IF EXISTS cv.put(QuestionsTable.COLUMN_OPTION2," question+ Category1Table.getOption2()TABLE_NAME);
cv.put(QuestionsTable.COLUMN_OPTION3, question db.getOption3execSQL());
"DROP TABLE IF EXISTS cv.put(QuestionsTable.COLUMN_ANSWER_NR," question+ Category2Table.getAnswerNr()TABLE_NAME);
cv.put(QuestionsTable.COLUMN_DIFFICULTY, question db.getDifficultyexecSQL());
"DROP TABLE IF EXISTS cv.put(QuestionsTable.COLUMN_CATEGORY," question+ Category3Table.getCategory()TABLE_NAME);
db.insert(QuestionsTable.TABLE_NAME, null, cv onCreate(db);
}
private void fillCategoriesTablefillCategory1Table() {
Category c1 Question q1 = new CategoryQuestion("Cat"(C1) 1"A is correct", "A", "B", "C", 1);
addCategory addQuestionToCategory1(c1q1);
Category c2 Question q2 = new CategoryQuestion("Cat"(C1) 2"B is correct", "A", "B", "C", 2);
addCategory addQuestionToCategory1(c2q2);
Category c3 Question q3 = new CategoryQuestion("Cat"(C1) 3"C is correct", "A", "B", "C", 3);
addCategory addQuestionToCategory1(c3q3);
}
private void addCategoryfillCategory2Table(Category category) {
ContentValues cv Question q1 = new ContentValuesQuestion("(C2) A is correct", "A", "B", "C", 1);
cv.put addQuestionToCategory2(CategoriesTable.COLUMN_NAMEq1);
Question q2 = new Question("(C2) B is correct", category.getName"A", "B", "C", 2);
addQuestionToCategory2(q2);
Question q3 = new Question("(C2) C is correct", "A", "B", "C", 3);
db.insert addQuestionToCategory2(CategoriesTable.TABLE_NAMEq3);
Question q4 = new Question("(C2) A is correct again", null"A", cv"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);
}
public List<Category> getAllCategories private void addQuestionToCategory1(Question question) {
ArrayList<Category> allCategories ContentValues cv = new ArrayList<>ContentValues();
db = getReadableDatabase cv.put(Category1Table.COLUMN_QUESTION, question.getQuestion());
Cursor c = db cv.rawQueryput("SELECTCategory1Table.COLUMN_OPTION1, *question.getOption1());
FROM " + CategoriesTable 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);
}
ifprivate (c.moveToFirstvoid addQuestionToCategory2()Question question) {
doContentValues {cv = new ContentValues();
cv.put(Category2Table.COLUMN_QUESTION, question.getQuestion());
Category category = new Category cv.put(Category2Table.COLUMN_OPTION1, question.getOption1());
categorycv.setIdput(cCategory2Table.getInt((cCOLUMN_OPTION2, question.getColumnIndexgetOption2(CategoriesTable._ID))));
categorycv.setNameput(cCategory2Table.getString(cCOLUMN_OPTION3, question.getColumnIndexgetOption3(CategoriesTable.COLUMN_NAME)));
cv.put(Category2Table.COLUMN_ANSWER_NR, allCategoriesquestion.addgetAnswerNr(category));
} while (cdb.moveToNextinsert()Category2Table.TABLE_NAME, null, cv);
}
cprivate void addQuestionToCategory3(Question question) {
ContentValues cv = new ContentValues();
cv.closeput(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);
}
returnpublic allCategories;ArrayList<Question> getAllCategory1Questions() {
} ArrayList<Question> questionList = new ArrayList<>();
db = getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM " + Category1Table.TABLE_NAME, null);
public ArrayList<Question> getAllQuestions if (c.moveToFirst()) {
ArrayList<Question> questionList do {
Question question = new ArrayList<>Question();
db = getReadableDatabase question.setQuestion(c.getString(c.getColumnIndex(Category1Table.COLUMN_QUESTION)));
Cursor question.setOption1(c.getString(c.getColumnIndex(Category1Table.COLUMN_OPTION1)));
= db question.rawQuerysetOption2("SELECTc.getString(c.getColumnIndex(Category1Table.COLUMN_OPTION2)));
* FROM " + QuestionsTable question.TABLE_NAME,setOption3(c.getString(c.getColumnIndex(Category1Table.COLUMN_OPTION3)));
null question.setAnswerNr(c.getInt(c.getColumnIndex(Category1Table.COLUMN_ANSWER_NR)));
questionList.add(question);
} while (c.moveToNext());
}
if (c.moveToFirst()) {
do {
Question question = new Question();
question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
questionList.addclose(question);
} whilereturn (c.moveToNext());questionList;
}
c.close();
return questionList;
}
public ArrayList<Question> getQuestionsgetAllCategory2Questions(String difficulty) {
ArrayList<Question> questionList = new ArrayList<>();
db = getReadableDatabase();
Cursor c = db.rawQuery("SELECT * FROM " + Category2Table.TABLE_NAME, null);
String[] selectionArgs if (c.moveToFirst()) {
do {
Question question = new String[]{difficulty}Question();
Cursor question.setQuestion(c.getString(c.getColumnIndex(Category2Table.COLUMN_QUESTION)));
= db question.rawQuerysetOption1("SELECTc.getString(c.getColumnIndex(Category2Table.COLUMN_OPTION1)));
* FROM " + QuestionsTable question.TABLE_NAMEsetOption2(c.getString(c.getColumnIndex(Category2Table.COLUMN_OPTION2)));
+ question.setOption3(c.getString(c.getColumnIndex(Category2Table.COLUMN_OPTION3)));
" WHERE " + QuestionsTablequestion.COLUMN_DIFFICULTYsetAnswerNr(c.getInt(c.getColumnIndex(Category2Table.COLUMN_ANSWER_NR)));
+ " = ?", selectionArgs questionList.add(question);
} while (c.moveToNext());
}
if (c.moveToFirst()) {
do {
Question question = new Question();
question.setQuestion(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_QUESTION)));
question.setOption1(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION1)));
question.setOption2(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION2)));
question.setOption3(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_OPTION3)));
question.setAnswerNr(c.getInt(c.getColumnIndex(QuestionsTable.COLUMN_ANSWER_NR)));
question.setDifficulty(c.getString(c.getColumnIndex(QuestionsTable.COLUMN_DIFFICULTY)));
questionList.addclose(question);
} whilereturn (c.moveToNext());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;
}
}
