app/src/main/java/com/example/map_quiz/DbHelper.java - master - Kulechek/Lab_10

app/src/main/java/com/example/map_quiz/DbHelper.java
130 строк5 KB
Temnikov Konstantin
lab11
25 май 2025, 15:05
25 май 2025, 15:05a3a54b1
package com.example.map_quiz;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "quiz.db";
    private static final int DATABASE_VERSION = 1;

    // имена таблиц и столбцов
    public static final String TABLE_QUESTIONS = "questions";
    public static final String Q_ID = "_id";
    public static final String Q_TEXT = "text";

    public static final String TABLE_ANSWERS = "answers";
    public static final String A_ID = "_id";
    public static final String A_TEXT = "text";
    public static final String A_QUESTION_ID = "question_id";
    public static final String A_IS_CORRECT = "is_correct"; // 0 или 1

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        // создаём таблицу вопросов
        db.execSQL(
                "CREATE TABLE " + TABLE_QUESTIONS + " (" +
                        Q_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        Q_TEXT + " TEXT NOT NULL" +
                        ");"
        );
        // создаём таблицу ответов
        db.execSQL(
                "CREATE TABLE " + TABLE_ANSWERS + " (" +
                        A_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        A_TEXT + " TEXT NOT NULL, " +
                        A_QUESTION_ID + " INTEGER NOT NULL, " +
                        A_IS_CORRECT + " INTEGER NOT NULL, " +
                        "FOREIGN KEY(" + A_QUESTION_ID + ") REFERENCES " + TABLE_QUESTIONS + "(" + Q_ID + ")" +
                        ");"
        );

        // заполняем начальными данными
        insertSampleData(db);
    }

    private void insertSampleData(SQLiteDatabase db) {
        // Вопрос 1
        long q1 = insertQuestion(db, "Столица России — это?");
        insertAnswer(db, "Москва",          q1, true);
        insertAnswer(db, "Петербург",       q1, false);
        insertAnswer(db, "Казань",          q1, false);
        insertAnswer(db, "Новосибирск",     q1, false);

        // Вопрос 2
        long q2 = insertQuestion(db, "Река Сена протекает через?");
        insertAnswer(db, "Лион",            q2, false);
        insertAnswer(db, "Париж",           q2, true);
        insertAnswer(db, "Марсель",         q2, false);
        insertAnswer(db, "Тулуза",          q2, false);

        // Вопрос 3
        long q3 = insertQuestion(db, "В каком континенте Рио-де-Жанейро?");
        insertAnswer(db, "Африка",          q3, false);
        insertAnswer(db, "Азия",            q3, false);
        insertAnswer(db, "Южная Америка",   q3, true);
        insertAnswer(db, "Европа",          q3, false);
    }

    private long insertQuestion(SQLiteDatabase db, String text) {
        ContentValues cv = new ContentValues();
        cv.put(Q_TEXT, text);
        return db.insert(TABLE_QUESTIONS, null, cv);
    }

    private long insertAnswer(SQLiteDatabase db, String text, long questionId, boolean isCorrect) {
        ContentValues cv = new ContentValues();
        cv.put(A_TEXT, text);
        cv.put(A_QUESTION_ID, questionId);
        cv.put(A_IS_CORRECT, isCorrect ? 1 : 0);
        return db.insert(TABLE_ANSWERS, null, cv);
    }

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

    public Question getQuestionWithAnswers(int questionId) {
        SQLiteDatabase db = getReadableDatabase();

        Question q = null;
        Cursor qc = db.query(TABLE_QUESTIONS,
                new String[]{Q_ID, Q_TEXT},
                Q_ID + " = ?",
                new String[]{String.valueOf(questionId)},
                null, null, null);
        if (qc.moveToFirst()) {
            q = new Question();
            q.id = qc.getInt(qc.getColumnIndexOrThrow(Q_ID));
            q.text = qc.getString(qc.getColumnIndexOrThrow(Q_TEXT));
        }
        qc.close();

        if (q == null) return null;

        Cursor ac = db.query(TABLE_ANSWERS,
                new String[]{A_ID, A_TEXT, A_IS_CORRECT},
                A_QUESTION_ID + " = ?",
                new String[]{String.valueOf(questionId)},
                null, null, null);
        while (ac.moveToNext()) {
            Answer a = new Answer();
            a.id = ac.getInt(ac.getColumnIndexOrThrow(A_ID));
            a.text = ac.getString(ac.getColumnIndexOrThrow(A_TEXT));
            a.isCorrect = ac.getInt(ac.getColumnIndexOrThrow(A_IS_CORRECT)) == 1;
            q.answers.add(a);
        }
        ac.close();
        return q;
    }

}