Management implementation of Android SQLite database version upgrade

Management implementation of Android SQLite database version upgrade

We know the constructor method in SQLiteOpenHelper:

super(Context context, String name,  factory, int version)

The last parameter in the database indicates the version number. The method will be called when the new version number is greater than the current version:

onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

So our focus is to implement the management of SQLite database version upgrade in this method

When our project started, the first version of SQLiteOpenHelper was written like this:

package ; 
 
import ; 
import ; 
import ; 
import ; 
/**
  * Demo description:
  * Management implementation of SQLite database version upgrade
  *
  * References:
  * /guolin_blog
  * Thank you very much
  */ 
public class DataBaseOpenHelper extends SQLiteOpenHelper { 
  private final static String DATABASE_NAME=""; 
  private static DataBaseOpenHelper mDataBaseOpenHelper; 
   
  public static final String CREATE_PERSON= 
  "create table person(personid integer primary key autoincrement,name varchar(20),phone VARCHAR(12))"; 
   
  public DataBaseOpenHelper(Context context,String name,CursorFactory factory,int version) { 
    super(context, name, factory, version); 
  } 
 
   
  //Notice:  //Write DataBaseOpenHelper as a singleton.  // Otherwise, when () is called frequently in a for loop  //An error will be reported, prompting that the database has not performed a shutdown operation  static synchronized DataBaseOpenHelper getDBInstance(Context context) { 
    if (mDataBaseOpenHelper == null) { 
      mDataBaseOpenHelper = new DataBaseOpenHelper(context,DATABASE_NAME,null,1); 
    } 
    return mDataBaseOpenHelper; 
  }  
   
  @Override 
  public void onCreate(SQLiteDatabase db) { 
    (CREATE_PERSON); 
  } 
 
  @Override 
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
     
  } 
 
}

A few days later, according to the project needs, a student table needs to be added, so the second version of DataBaseOpenHelper appears:

package ; 
 
import ; 
import ; 
import ; 
import ; 
 
public class DataBaseOpenHelper extends SQLiteOpenHelper { 
  private final static String DATABASE_NAME=""; 
  private static DataBaseOpenHelper mDataBaseOpenHelper; 
   
  public static final String CREATE_PERSON= 
  "create table person(personid integer primary key autoincrement,name varchar(20),phone VARCHAR(12))"; 
   
  public static final String CREATE_STUDENT= 
  "create table student(studentid integer primary key autoincrement,name varchar(20),phone VARCHAR(12))"; 
   
  public DataBaseOpenHelper(Context context,String name,CursorFactory factory,int version) { 
    super(context, name, factory, version); 
  } 
 
   
  //Notice:  //Write DataBaseOpenHelper as a singleton.  // Otherwise, when () is called frequently in a for loop  //An error will be reported, prompting that the database has not performed a shutdown operation  static synchronized DataBaseOpenHelper getDBInstance(Context context) { 
    if (mDataBaseOpenHelper == null) { 
      //Change 1      mDataBaseOpenHelper = new DataBaseOpenHelper(context,DATABASE_NAME,null,2); 
    } 
    return mDataBaseOpenHelper; 
  }  
   
  @Override 
  public void onCreate(SQLiteDatabase db) { 
    (CREATE_PERSON); 
    //Change 2    (CREATE_STUDENT); 
  } 
 
  @Override 
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
    //Change 3    switch (oldVersion) { 
     
    case 1: 
    (CREATE_STUDENT); 
 
    default: 
    } 
  } 
 
}

There are three modifications in version 2:

1 Version number becomes 2

2 Added code (CREATE_STUDENT) in the onCreate() method; create student table

Because some users don’t have the first version of the APP at all, they directly download the second version of the APP from the market. So of course onCreate() will be executed without onUpgrade()

3. OnUpgrade() is processed: call when oldVersion is 1 (CREATE_STUDENT); create student table
Because some users already have the first version of the APP on their mobile phones, onUpgrade() will be executed when the App is upgraded to the second version, and onCreate() will not be executed.

Through such processing, student tables will be generated when using the second version of APP under different circumstances

Another month later, according to the project change, a field genderid needs to be added to the person table, so the third version of DataBaseOpenHelper appears:

package ; 
 
import ; 
import ; 
import ; 
import ; 
 
public class DataBaseOpenHelper extends SQLiteOpenHelper { 
  private final static String DATABASE_NAME=""; 
  private static DataBaseOpenHelper mDataBaseOpenHelper; 
   
  //Change 1  public static final String CREATE_PERSON= 
  "create table person(personid integer primary key autoincrement,name varchar(20),phone VARCHAR(12)),genderid integer)"; 
   
  public static final String ALTER_PERSON="alter table person add column genderid integer"; 
   
  public static final String CREATE_STUDENT= 
  "create table student(studentid integer primary key autoincrement,name varchar(20),phone VARCHAR(12))"; 
   
  public DataBaseOpenHelper(Context context,String name,CursorFactory factory,int version) { 
    super(context, name, factory, version); 
  } 
 
   
  //Notice:  //Write DataBaseOpenHelper as a singleton.  // Otherwise, when () is called frequently in a for loop  //An error will be reported, prompting that the database has not performed a shutdown operation  static synchronized DataBaseOpenHelper getDBInstance(Context context) { 
    if (mDataBaseOpenHelper == null) { 
      //Change 2      mDataBaseOpenHelper = new DataBaseOpenHelper(context,DATABASE_NAME,null,3); 
    } 
    return mDataBaseOpenHelper; 
  }  
   
  @Override 
  public void onCreate(SQLiteDatabase db) { 
    (CREATE_PERSON); 
    (CREATE_STUDENT); 
  } 
 
  @Override 
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
    switch (oldVersion) { 
     
    case 1: 
    (CREATE_STUDENT); 
     
    //Change 3    case 2: 
    (ALTER_PERSON); 
 
    default: 
    } 
  } 
 
}

There are three modifications in version 2 in version 3:

1. The CREATE_PERSON statement has been changed, and a field genderid is added to the change statement.

Similar to the previous description, some users directly downloaded the third version when they first installed the APP.

2 Modify the version number to 3

Coping with the situation where users upgrade from the first or second version to the third version (see analysis below)

3. In the onUpgrade() method) handled: call (ALTER_PERSON); modify the person table and add the genderid field

Coping with the situation where users upgrade from the second version to the third version (see analysis below)

Note one problem:Why does the switch statement here have no break in each case? ? ?

This is to ensure that every database upgrade will be executed during cross-version upgrades.

For example, if you upgrade from the second version to the third version, then case 2 will be executed.

For example, if you directly upgrade from the first version to the third version, then case 1 will definitely be called. Since there is no break, the switch statement will be penetrated and the case 2 statement will be executed to continue the upgrade, thus ensuring that the upgrades in all versions of the data will be executed.

If you have any questions, please leave a message or go to the community of this site to exchange and discuss. Thank you for reading. I hope it can help you. Thank you for your support for this site!