DatabaseHelper with multiple tables

Question

I am programming my first android app. It is going to be a quiz. It will have 40 different categories of questions and for each category I am going to have a SQLite Table, which holds the questions for that category.

---

I have a working DatabaseHelper for the first 2 tables. If I keep going like this for the next 38 tables the code is going to be very very repetitive. I am just not experienced enough to figure out a way to make the code less repetitive.

Could somebody with more experience help me to clean this code up and make it less repetitive?

DatabaseHelper

public class DatabaseHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 13; // Database version
private static final String DATABASE_NAME = "PharmaQuestions"; // database name

// Table Names
private static final String TABLE_ACE = "ace";
private static final String TABLE_ANDROGENS = "androgens";

// General question columns
private static final String ID = "id"; // question id
private static final String QUES = "question"; // the question
private static final String OPTA = "opta"; // option a
private static final String OPTB = "optb"; // option b
private static final String OPTC = "optc"; // option c
private static final String OPTD = "optd"; // option d
private static final String ANSWER = "answer"; // correct option

private SQLiteDatabase database;

// Create Table ace
private static final String CREATE_TABLE_ACE = "CREATE TABLE IF NOT EXISTS " + TABLE_ACE + "( "
        + ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + QUES
        + " TEXT, "+OPTA +" TEXT, " +OPTB +" TEXT, "+OPTC+" TEXT, "+OPTD
        + " TEXT, " + ANSWER+ " TEXT)";

// Create Table androgens
private static final String CREATE_TABLE_ANDROGENS = "CREATE TABLE IF NOT EXISTS " + TABLE_ANDROGENS + "( "
        + ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + QUES
        + " TEXT, "+OPTA +" TEXT, " +OPTB +" TEXT, "+OPTC+" TEXT, "+OPTD
        + " TEXT, " + ANSWER+ " TEXT)";

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

@Override
public void onCreate(SQLiteDatabase db) {
    // creating required tables
    database = db;
    db.execSQL(CREATE_TABLE_ACE);
    db.execSQL(CREATE_TABLE_ANDROGENS);
    addQuestions();
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACE);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_ANDROGENS);
    // Create tables again
    onCreate(db);
}

private void addQuestions() {
    // ACE Questions
    Question ace1 = new Question("ACE Frage Nr. 1?","Antwort1", "Antwort2", "Antwort3", "Antwort4", "Antwort1");
    this.addACEQuestion(ace1);
    Question ace2 = new Question("ACE Frage Nr. 2?", "Antwort1", "Antwort2", "Antwort3", "Antwort4", "Antwort2");
    this.addACEQuestion(ace2);
    // Androgen Questions
    Question androgen1 = new Question("Androgen Frage Nr. 1?", "Antwort1", "Antwort2", "Antwort3", "Antwort4", "Antwort2");
    this.addAndrogensQuestion(androgen1);
    Question androgen2 = new Question("Androgen Frage Nr. 2?", "Antwort1", "Antwort2", "Antwort3", "Antwort4", "Antwort2");
    this.addAndrogensQuestion(androgen2);
}

// Adding ace question
public void addACEQuestion(Question quest) {
    //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_ACE, null, values);
}

// Adding androgen question
public void addAndrogensQuestion(Question quest) {
    //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_ANDROGENS, null, values);
}

public List<Question> getAllACEQuestions() {
    List<Question> quesList = new ArrayList<Question>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_ACE;
    database = this.getReadableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Question quest = new Question();
            quest.setID(cursor.getInt(0));
            quest.setQUESTION(cursor.getString(1));
            quest.setOPTA(cursor.getString(2));
            quest.setOPTB(cursor.getString(3));
            quest.setOPTC(cursor.getString(4));
            quest.setOPTD(cursor.getString(5));
            quest.setANSWER(cursor.getString(6));
            quesList.add(quest);
        } while (cursor.moveToNext());
    }
    // return quest list
    return quesList;
}

public List<Question> getAllAndrogensQuestions() {
    List<Question> quesList = new ArrayList<Question>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_ANDROGENS;
    database = this.getReadableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Question quest = new Question();
            quest.setID(cursor.getInt(0));
            quest.setQUESTION(cursor.getString(1));
            quest.setOPTA(cursor.getString(2));
            quest.setOPTB(cursor.getString(3));
            quest.setOPTC(cursor.getString(4));
            quest.setOPTD(cursor.getString(5));
            quest.setANSWER(cursor.getString(6));
            quesList.add(quest);
        } while (cursor.moveToNext());
    }
    // return quest list
    return quesList;
}

public int acerowcount() {
    int row=0;
    String selectQuery = "SELECT  * FROM " + TABLE_ACE;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    row=cursor.getCount();
    return row;
}

public int androgenrowcount() {
    int row=0;
    String selectQuery = "SELECT  * FROM " + TABLE_ANDROGENS;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    row=cursor.getCount();
    return row;
   }
}

---

I am really new to this and would be very thankful for every line of code I can get rid of.

Answer 1

Your functions addACEQuestion and addAndrogensQuestion seem nearly identical. The only difference is a constant. Instead of manually copy and pasting these functions and changing that constant, you can try making a new function and passing the constant. Take:

public void addACEQuestion(Question quest) {
    addQuestion(quest, TABLE_ACE);
}

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

It also seems like you can do this for getAllACEQuestions and getAllAndrogensQuestions, and acerowcount and androgenrowcount. You also seem to be duplicating logic for CREATE_TABLE_ACE and CREATE_TABLE_ANDROGENS, and so I'd recommend you make another string(s) that you can add TABLE_ACE or TABLE_ANDROGENS into.