Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have an offline Question application for android here I used SQLite database to store all my Questions. it works fine for the first run let's say I added 1000 questions with 5 categories for the first time and published it. if after some time I want to add more questions and category I am not able to update it until my user uninstall the application or clear its data after updating the application otherwise it shows old data only (eg 1000 questions only)

here is my code for the database


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import com.offline.quizigator.Constant;
import com.offline.quizigator.model.Category;
import com.offline.quizigator.model.Quizplay;
import com.offline.quizigator.model.SubCategory;
import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class DBHelper extends SQLiteOpenHelper {
    private String packageName;
    private SQLiteDatabase db;
    private static final String db_name = "quiz_main_sub_cat.db";
    private static final String db_name_single_cat = "quiz_single_cat.db";
    //table names
    public static final String TBL_CATEGORY = "tbl_category";
    public static final String TBL_SUB_CATEGORY = "tbl_subCategory";
    public static final String TBL_QUESTION = "questions_list";
    //table name
    public static String TBL_LEVEL = "tbl_level";
    //column names
    public static String LEVEL_NO = "level_no";
    public static final String ID = "id";
    public static final String CATE_ID = "cate_id";
    public static final String SUB_CATE_ID = "sub_cate_id";
    public static final String CATEGORY_NAME = "category";
    public static final String SUB_CATEGORY_NAME = "sub_category";
    public static final String QUESTION_SOLUTION = "que_solution";
    public static final String QUESTION = "question";
    public static final String OPTION_A = "option_a";
    public static final String OPTION_B = "option_b";
    public static final String OPTION_C = "option_c";
    public static final String OPTION_D = "option_d";
    public static final String RIGHT_ANSWER = "right_answer";
    public static final String LEVEL = "level";
    private String db_path;
    private static int db_version = 1;
    Context con;
    public DBHelper(Context con) {
        super(con, db_name, null, db_version);
        // TODO Auto-generated constructor stub
        db_path = con.getDatabasePath(db_name).getAbsolutePath();
        //db_path = con.getDatabasePath(db_name).toString().replace(db_name, "");
        this.con = con;
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
    }
    public void createDB() throws IOException {
        if (checkDB()) {
        } else if (!checkDB()) {
            this.getReadableDatabase();
            close();
            copyDB();
        }
    }
    private boolean checkDB() {
        SQLiteDatabase cDB = null;
        try {
            cDB = SQLiteDatabase.openDatabase(db_path, null,
                    SQLiteDatabase.OPEN_READWRITE);
        } catch (SQLiteException e) {
            e.printStackTrace();
        }
        if (cDB != null) {
            cDB.close();
        }
        return cDB != null ? true : false;
    }
    private void copyDB() throws IOException {
        InputStream inputFile = con.getAssets().open(db_name);
        //  String outFileName = db_path + db_name;
        OutputStream outFile = new FileOutputStream(db_path);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = inputFile.read(buffer)) > 0) {
            outFile.write(buffer, 0, length);
        }
        outFile.flush();
        outFile.close();
        inputFile.close();      
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
    }
    private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) throws IOException {
        String line;
        StringBuilder statement = new StringBuilder();
        while ((line = reader.readLine()) != null) {
            statement.append(line);
            statement.append("\n");
            if (line.endsWith(";")) {
                db.execSQL(statement.toString());
                statement = new StringBuilder();
            }
        }
    }
    /*
     *get All categories from table
     */
    public ArrayList<Category> getAllCategories() {
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<Category> categoryArrayList = new ArrayList<>();
        Cursor cur = db.rawQuery("SELECT * FROM  " + TBL_CATEGORY, null);
        if (cur.moveToFirst()) {
            do {
                Category category = new Category();
                category.setId(cur.getInt(cur.getColumnIndex(ID)));
                category.setName(cur.getString(cur.getColumnIndex(CATEGORY_NAME)));
                categoryArrayList.add(category);
            } while (cur.moveToNext());
        }
        //}
        return categoryArrayList;
    }
         public int GetMaxLevelSingleCat(int cat_id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("select max(" + LEVEL + ") from " + TBL_QUESTION + " where (" + CATE_ID + "=" + cat_id + ")", null);
        if (cur.moveToFirst()) {
            do {
                Constant.totalLevel = cur.getInt(cur.getColumnIndex("max(level)"));
            } while (cur.moveToNext());
        }
        //}
        return Constant.totalLevel;
    }
    public int GetMaxLevel(int cat_id, int sub_cate_id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("select max(" + LEVEL + ") from " + TBL_QUESTION + " where (" + CATE_ID + "=" + cat_id + " and " + SUB_CATE_ID + "=" + sub_cate_id + ")", null);
        if (cur.moveToFirst()) {
            do {
                Constant.totalLevel = cur.getInt(cur.getColumnIndex("max(level)"));
            } while (cur.moveToNext());
        }
        //}
        return Constant.totalLevel;
    }
    public ArrayList<SubCategory> getSubCategoryById(int cate_id) {
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<SubCategory> subCategories = new ArrayList<>();
        Cursor cur = db.rawQuery("SELECT * FROM  " + TBL_SUB_CATEGORY + " where (" + CATE_ID + " = " + cate_id + ")", null);
        if (cur.moveToFirst()) {
            do {
                SubCategory subCategory = new SubCategory();
                subCategory.setId(cur.getInt(cur.getColumnIndex(ID)));
                subCategory.setCategoryId(cur.getString(cur.getColumnIndex(CATE_ID)));
                subCategory.setName(cur.getString(cur.getColumnIndex(SUB_CATEGORY_NAME)));
                subCategories.add(subCategory);
            } while (cur.moveToNext());
        }
        //}
        return subCategories;
    }
    public List<Quizplay> getQuestionGujSingleCat(int cate_id, int noOfQuestion, int level) {
        List<Quizplay> quizplay = new ArrayList<Quizplay>();
        int total = noOfQuestion;
        String sql = "select *  FROM " + TBL_QUESTION + " where (" + CATE_ID + "=" + cate_id + " and "
                + LEVEL + "=" + level + ") ORDER BY RANDOM() LIMIT " + total;
        SQLiteDatabase db = this.getReadableDatabase();
        //SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/" + packageName + "/databases/" + DATABASE_NAME, null, 0);
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            do {
                Quizplay question = new Quizplay();
                question.setId(cursor.getInt(cursor.getColumnIndex("id")));
                question.setQuestion(cursor.getString(cursor.getColumnIndex("question")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_a")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_b")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_c")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_d")));
                String rightAns = cursor.getString(cursor.getColumnIndex("right_answer"));
                if (rightAns.equalsIgnoreCase("A")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_a")));
                } else if (rightAns.equalsIgnoreCase("B")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_b")));
                } else if (rightAns.equalsIgnoreCase("C")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_c")));
                } else {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_d")));
                }
                if (question.getOptions().size() == 4) {
                    quizplay.add(question);
                }
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        Collections.shuffle(quizplay);
        quizplay = quizplay.subList(0, noOfQuestion);
        return quizplay;
    }
    public List<Quizplay> getQuestionGuj(int cate_id, int sub_cate_id, int noOfQuestion, int level) {
        List<Quizplay> quizplay = new ArrayList<Quizplay>();
        int total = noOfQuestion;
        String sql = "select *  FROM " + TBL_QUESTION + " where (" + CATE_ID + "=" + cate_id + " and "
                + SUB_CATE_ID + " =" + sub_cate_id + " and "
                + LEVEL + "=" + level + ") ORDER BY RANDOM() LIMIT " + total;
        SQLiteDatabase db = this.getReadableDatabase();
        //SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/" + packageName + "/databases/" + DATABASE_NAME, null, 0);
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            do {
                Quizplay question = new Quizplay();
                question.setId(cursor.getInt(cursor.getColumnIndex("id")));
                question.setQuestion(cursor.getString(cursor.getColumnIndex("question")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_a")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_b")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_c")));
                question.addOption(cursor.getString(cursor.getColumnIndex("option_d")));
                String rightAns = cursor.getString(cursor.getColumnIndex("right_answer"));
                if (rightAns.equalsIgnoreCase("A")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_a")));
                } else if (rightAns.equalsIgnoreCase("B")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_b")));
                } else if (rightAns.equalsIgnoreCase("C")) {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_c")));
                } else {
                    question.setTrueAns(cursor.getString(cursor.getColumnIndex("option_d")));
                }
                if (question.getOptions().size() == 4) {
                    quizplay.add(question);
                }
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        Collections.shuffle(quizplay);
        quizplay = quizplay.subList(0, noOfQuestion);
        return quizplay;
    }
    /*
     * insert level no
     */
    public void insertIntoDBSingleCat(int cat_id, int level_no) {
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "INSERT INTO " + TBL_LEVEL + " (" + CATE_ID + "," + LEVEL_NO + ") VALUES('" + cat_id + "', '" + level_no + "');";
        db.execSQL(query);
    }
    public void insertIntoDB(int cat_id, int sub_cat_id, int level_no) {
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "INSERT INTO " + TBL_LEVEL + " (" + CATE_ID + "," + SUB_CATE_ID + "," + LEVEL_NO + ") VALUES('" + cat_id + "', '" + sub_cat_id + "', '" + level_no + "');";
        db.execSQL(query);
    }
    /*
     *with this method we check if categoryId & subCategoryId is already exist or not in our database
     */
    public boolean isExistSingleCat(int cat_id) {
        db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("SELECT * FROM " + TBL_LEVEL + " WHERE ( " + CATE_ID + " = " + cat_id + ")", null);
        boolean exist = (cur.getCount() > 0);
        cur.close();
        return exist;
    }
    public boolean isExist(int cat_id, int sub_cat_id) {
        db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("SELECT * FROM " + TBL_LEVEL + " WHERE ( " + CATE_ID + " = " + cat_id + " AND " + SUB_CATE_ID + " = " + sub_cat_id + ")", null);
        boolean exist = (cur.getCount() > 0);
        cur.close();
        return exist;
    }
    /*
     * get level
     */
    public int GetLevelByIdUsingSingleCat(int cat_id) {
        int level = 1;
        String selectQuery = "SELECT  * FROM " + TBL_LEVEL + " WHERE  (" + CATE_ID + "=" + cat_id + ")";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                level = c.getInt(c.getColumnIndex(LEVEL_NO));
            } while (c.moveToNext());
        }
        return level;
    }
    public int GetLevelById(int cat_id, int sub_cat_id) {
        int level = 1;
        String selectQuery = "SELECT  * FROM " + TBL_LEVEL + " WHERE  (" + CATE_ID + "=" + cat_id + " AND " + SUB_CATE_ID + "=" + sub_cat_id + ")";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                level = c.getInt(c.getColumnIndex(LEVEL_NO));
            } while (c.moveToNext());
        }
        return level;
    }
    public String getQuestionSolution(int queId) {
        String level = "";
        String selectQuery = "SELECT  * FROM " + TBL_QUESTION + " WHERE  (" + ID + "=" + queId + ")";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                level = c.getString(c.getColumnIndex(QUESTION_SOLUTION));
            } while (c.moveToNext());
        }
        return level;
    }
    /*
     * Update level
     */
    public void UpdateLevelSingleCat(int cat_id, int level_no) {
        db = this.getReadableDatabase();
        db.execSQL("update " + TBL_LEVEL + " set level_no=" + level_no + " where (" + CATE_ID + "=" + cat_id + ")");
    }
    public void UpdateLevel(int cat_id, int sub_cat_id, int level_no) {
        db = this.getReadableDatabase();
        db.execSQL("update " + TBL_LEVEL + " set level_no=" + level_no + " where (" + CATE_ID + "=" + cat_id + "  and  " + SUB_CATE_ID + " = " + sub_cat_id + ")");
    }
}


What I have tried:

I used the DB Browser for SQLite to edit my database. how to update my data without losing user progress?
Posted
Updated 6-Jul-22 18:40pm
Comments
David Crow 7-Jul-22 8:15am    
If your questions were in an accompanying resource file (e.g., txt, json), you could check for the existence of that file, and, if present, add its contents to the database during app startup.

1 solution

Use an SQL UPDATE Statement[^] - it allows you to change the content of rows that match a WHERE clause only.

But much more importantly, don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
It's less of a problem with SqLite that SQLServer or MySql, but ... it's a very good idea to get into good habits rather than dangerous ones!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900