java - 如何使用新的 Android 应用程序更新来更新现有的 SQLite 数据库表数据？

Question

我最近在 Android 应用商店中推出了一个包含 SQLite 数据库的应用。

我现在正在尝试发布应用程序的更新，并希望将更多数据添加到现有数据库中，但是有点困难。我已经阅读了关于 SO 的答案，概述了对数据库本身进行更改，但是我希望我的表和列保持不变，只添加新数据。

我想要添加到数据库的数据是从 Raw 文件中的 CSV 文件中提取的，最初是在用户注册应用程序时加载到数据库中的。

我感觉我需要实现 onUpgrade 方法，但是此时我是否也应该添加 CSV 文件中的新数据？是简单地更新数据库版本并使用onUpgrade加载新数据的问题吗？

我对 SQLite DB 相当陌生，因此我们将非常感谢任何帮助。

CourseDBHelper代码

public class CourseDBHelper extends SQLiteOpenHelper {

// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "CourseDB";

// Create two table names
private static final String TABLE_COURSES = "courses";

// Universities Table Columns names
private static final String COURSE_NAME = "Course_name";
private static final String UNI_NAME = "Uni_name";
private static final String COURSE_DURATION = "Duration";
private static final String COURSE_STUDY_MODE = "Study_mode";
private static final String COURSE_QUALIFICATION = "Qualification";
private static final String COURSE_ENTRY_STANDARDS = "Entry_standards";
private static final String COURSE_GRADUATE_PROSPECTS = "Graduate_prospects";
private static final String COURSE_STUDENT_SATISFACTION = "Student_satisfaction";


private String CREATE_COURSES_TABLE = "CREATE TABLE courses" +
        "(" +
        "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
        "Course_name TEXT NOT NULL," +
        "Uni_name TEXT NOT NULL," +
        "Duration TEXT NOT NULL," +
        "Study_mode TEXT NOT NULL," +
        "Qualification TEXT NOT NULL," +
        "Entry_standards TEXT NOT NULL," +
        "Graduate_prospects TEXT NOT NULL," +
        "Student_satisfaction TEXT NOT NULL" +
        ");";

private static final String[] COLUMNS = {
        COURSE_NAME,
        UNI_NAME,
        COURSE_DURATION,
        COURSE_STUDY_MODE,
        COURSE_QUALIFICATION,
        COURSE_ENTRY_STANDARDS,
        COURSE_GRADUATE_PROSPECTS,
        COURSE_STUDENT_SATISFACTION
};

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

// TODO: REMOVED NOT NULL FROM EVERY COLUMN FOR TEST PURPOSES, WILL NEED TO BE READDED
@Override
public void onCreate(SQLiteDatabase db)
{
    db.execSQL(CREATE_COURSES_TABLE);
}

public void deleteAll()
{
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete("courses", null, null);
    db.execSQL("delete from " + "courses");
    db.close();
}

// Getting one course by course name and uni name
public Course getCourse(String courseName, String uniName) {
    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.query(TABLE_COURSES, COLUMNS, " Course_name = ? AND Uni_name = ?",
            new String[]{courseName, uniName},
            null,
            null,
            null,
            null);

    if (cursor != null)
        cursor.moveToFirst();


    Course course = new Course();

    /*

    System.out.println(cursor.getString(0));
    System.out.println(cursor.getString(1));
    System.out.println(cursor.getString(2));
    System.out.println(cursor.getString(3));
    System.out.println(cursor.getString(4));
    System.out.println(cursor.getString(5));
    System.out.println(cursor.getString(6));
    */

    course.setCourseName(cursor.getString(0));
    course.setUniversity(cursor.getString(1));
    course.setCourseDuration(cursor.getString(2));
    course.setStudyMode(cursor.getString(3));
    course.setQualification(cursor.getString(4));
    course.setEntryStandards(cursor.getString(5));
    course.setGradProspects(cursor.getString(6));
    course.setStudentSatisfaction(cursor.getString(7));


    return course;

}

public void addCourse(Course course)
{
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(COURSE_NAME, course.getCourseName());
    values.put(UNI_NAME, course.getUniversity());
    values.put(COURSE_DURATION, course.getCourseDuration());
    values.put(COURSE_STUDY_MODE, course.getStudyMode());
    values.put(COURSE_QUALIFICATION, course.getQualification());
    values.put(COURSE_ENTRY_STANDARDS, course.getEntryStandards());
    values.put(COURSE_GRADUATE_PROSPECTS, course.getGradProspects());
    values.put(COURSE_STUDENT_SATISFACTION, course.getStudentSatisfaction());

    db.insert(TABLE_COURSES,
            null, //nullColumnHack
            values);

    db.close();
}


public ArrayList<Course> getAllCourses()
{
    ArrayList<Course> courses = new ArrayList<>();
    // 1. build the query
    String query = "SELECT  * FROM " + TABLE_COURSES;
    // 2. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

    // 3. go over each row, build course and add it to list
    Course course;
    if(cursor.moveToFirst()){
        cursor.moveToNext();
        do{
            course = new Course();
            course.setCourseName(cursor.getString(1));
            course.setUniversity(cursor.getString(2));
            course.setCourseDuration(cursor.getString(3));
            course.setStudyMode(cursor.getString(4));
            course.setQualification(cursor.getString(5));
            course.setEntryStandards(cursor.getString(6));
            course.setGradProspects(cursor.getString(7));
            course.setStudentSatisfaction(cursor.getString(8));

            // Add course to courses list
            courses.add(course);
        } while(cursor.moveToNext());
    }

    // return courses
    return courses;
}

public int getDBCount()
{
    SQLiteDatabase db = this.getWritableDatabase();
    String count = "SELECT count(*) FROM courses";
    Cursor mcursor = db.rawQuery(count, null);
    mcursor.moveToFirst();
    int icount = mcursor.getInt(0);
    return icount;
}

public void deleteCourse(Course course) {

    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    // 2. delete
    db.delete("courses", //table name
            "Course_name = ? AND Uni_name = ?",  // selections
            new String[] { course.getCourseName(), course.getUniversity() }); //selections args

    // 3. close
    db.close();
}



@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {


}
}

将数据从 CSV 文件加载到 SQlite DB 的方法

public void populateCourseDatabase(int id) {

        // NOW POPULATE THE COURSE DATABASE FILE
inputStream = getResources().openRawResource(R.raw.coursesone);


        BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
        String word;
        String cvsSplitBy = ",";

        try{
            while((word = reader.readLine()) != null){

                Log.d(TAG, "constructing Course object from: " + word);

                String[] segment = word.split(cvsSplitBy);

                Course course = new Course();
                course.setCourseName(segment[0]);
                course.setUniversity(segment[1]);
                course.setCourseDuration(segment[2]);
                course.setStudyMode(segment[3]);
                course.setQualification(segment[4]);
                course.setEntryStandards(segment[5]);
                course.setGradProspects(segment[6]);
                course.setStudentSatisfaction(segment[7]);


                myCourseDBHelper.addCourse(course);

                progressBar.setProgress(count);

                count = count + 1;

                System.out.println("Sucessfully added: " + course.toString());
            }
        }
        catch(IOException e1){
            e1.printStackTrace();
            System.out.println("SOMETHING WENT WRONG");
        }

    }

Answer 1

SQLiteOpenHelper onCreate() 和 onUpgrade() 回调在数据库实际打开时调用，例如通过调用 getWritableDatabase()。onCreate() 仅在数据库文件不存在且刚刚创建时运行。仅当数据库文件存在但存储的版本号低于构造函数中请求的版本号时，才会调用 onUpgrade()。增加数据库版本，以便调用 onUpgrade()。下面的示例伪代码

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch(oldVersion) {

    case 1:
        //upgrade logic from version 1 to 2
    case 2:
        //upgrade logic from version 2 to 3
    case 3:
        //upgrade logic from version 3 to 4
        break;
    default:
        throw new IllegalStateException(
        "onUpgrade() with unknown oldVersion " + oldVersion);
    }
}