Базы данных SQLite Android с 3 таблицами


Я хочу показать вам SQLiteOpenHelper и Contract класса для маленькой викторины приложение, которое я сделал для практических целей. У меня 3 стола для 3-х различных категориях. Подход работает, но я интересно, если я удалить избыточность. Как вы можете видеть, есть много подобных код для создания и заполнения этих таблиц, но я чувствую, что было бы странно, если бы я объединить эти методы в 1.

Я должен сохранить создание и выполнение запросов к таблицам, как это, или я должен передать имя таблицы в качестве аргумента в метод, а затем объединить 3 похожие методы 1?

Что вы думаете?

Контракт:

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


185
0
задан 10 марта 2018 в 08:03 Источник Поделиться
Комментарии