Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to upgrade the database version separately and migrate the original data. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

In the application we develop, it generally involves the database, and when using the data, it will involve the upgrade of the database, the migration of the data, the fields of adding rows, and so on. For example, the preservation of user-customized data and the preservation of end-point continuation information of the file will be related to the database.

The first version we applied was V1.0, and in the iterative version v1.1, we added a field to the database. Therefore, the database of V1.0 needs to be upgraded in v1.1, and the data in the original database cannot be lost when V1.0 is upgraded to v1.1.

Then there must be a place in V1.1 to detect version differences and upgrade the database of the V1.0 software to a database that can be used by the V1.1 software. That is, add that field to the table in the database of the V1.0 software and give the field a default value. How to detect that the database needs to be upgraded in the application? There is a method in the SQLiteOpenHelper class:

Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {}

When we create an object, if the version number passed in is greater than the previous version number, this method will be called, and we can decide how to upgrade the database by judging oldVersion and newVersion. In this function, add fields to the corresponding table in the old version of the database, and add default values to each record. Both the new version number and the old version number will be passed in as arguments to the onUpgrade function, making it easier for developers to know which version the database should be upgraded from. After the upgrade is complete, the database automatically stores the version number of the current database version.

Database upgrade

SQLite provides ALTER TABLE commands that allow users to rename or add new fields to an existing table, but cannot delete fields from the table. And you can only add a field to the end of the table, for example, add a field to the Orders table: "ALTER TABLE Order ADDCOLUMN Country"

The code is as follows:

Public class OrderDBHelper extends SQLiteOpenHelper {private static final int DB_VERSION = 1; private static final String DB_NAME = "Test.db"; public static final String TABLE_NAME = "Orders"; public OrderDBHelper (Context context, int version) {super (context, DB_NAME, null, version);} @ Override public void onCreate (SQLiteDatabase db) {String sql = "create table if not exists" + TABLE_NAME + "(Id integer primary key," + "CustomName text, OrderPrice integer)"; db.execSQL (sql) @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {Log.e ("owen", "DB onUpgrade"); if (newVersion = = 2) {db.execSQL ("ALTER TABLE" + TABLE_NAME + "ADD COLUMN Country"); Cursor cr = db.rawQuery ("select * from" + TABLE_NAME, null); while (cr.moveToNext ()) {String name = cr.getString (cr.getColumnIndex ("CustomName")); ContentValues values = new ContentValues () Values.put ("CustomName", name); values.put ("Country", "China"); db.update (TABLE_NAME, values, "CustomName=?", new String [] {name});} cr.close ();} OrderDBHelper orderDBHelper = new OrderDBHelper (this, 2); SQLiteDatabase db = orderDBHelper.getWritableDatabase (); ContentValues contentValues = new ContentValues (); contentValues.put ("OrderPrice", 100); contentValues.put ("CustomName", "OwenChan") Db.insert (OrderDBHelper.TABLE_NAME, null, contentValues); Log.e ("owen", "create finish"); Cursor cr = db.rawQuery ("select * from" + OrderDBHelper.TABLE_NAME, null); while (cr.moveToNext ()) {String name = cr.getString (cr.getColumnIndex ("CustomName")); Log.e ("owen", "name:" + name); String country = cr.getString (cr.getColumnIndex ("Country")) Log.e ("owen", "country:" + country);} cr.close (); db.close ()

Migration of database

You can migrate the database in several steps.

1. Change the table name to a temporary table

ALTER TABLE Order RENAME TO _ Order

2. Create a new table

CREATETABLE Test (Id VARCHAR (32) PRIMARY KEY, CustomName VARCHAR (32) NOTNULL, Country VARCHAR (16) NOTNULL)

3. Import data

INSERTINTO Order SELECT id, "", Age FROM _ Order

4. Delete temporary table

DROPTABLE _ Order

Through the above four steps, we can complete the migration from the old database structure to the new database structure, and ensure that the data will not be lost because of the upgrade. Of course, if you encounter a reduction in fields, you can also do so by creating a temporary table.

The implementation code is as follows:

@ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {if (newVersion = = 2) {char str ='"; db.beginTransaction (); db.execSQL (" ALTER TABLE Order RENAME TO _ Order "); db.execSQL (" CREATE TABLE Order (Id integer primary key autoincrement, CustomName VARCHAR (20) NOT NULL, "+" Country VARCHAR (32) NOT NULL, OrderPrice VARCHAR (16) NOT NULL ")) Db.execSQL ("INSERT INTO Order SELECT Id," + str + str + ", CustomName, OrderPrice FROM _ Order"); db.setTransactionSuccessful (); db.endTransaction ();}}

Upgrade of multiple database versions

If the program we developed has been released in two versions: V1.0 and V2.0, we are developing V3.0. The version numbers are 1, 2, 2, 3 respectively. In this case, how should we upgrade? The user's choices are:

V1.0-> V3.0 DB 1-> 2

V2.0-> V3.0 DB 2-> 3

The database represented by each version of the database must be defined. For example, the database of V1.0 may have only two tables, TableA and TableB. If V2.0 wants to add a table TableC, if V3.0 wants to modify TableC, the database structure is as follows:

V1.0-> TableA, TableB V1.2-> TableA, TableB, TableC V1.3-> TableA, TableB, TableC (Modify)

The code is as follows:

@ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {if (1 = = oldVersion) {String sql = "Create table C...."; db.execSQL (sql); oldVersion = 2;} if (2 = = oldVersion) {/ / modify C oldVersion = 3;}}

Import an existing database:

/ * * Created by Owen Chan * On 2017-09-26. * / public class DbManager {public static final String PACKAGE_NAME = "com.example.sql"; public static final String DB_NAME = "table.db"; public static final String DB_PATH = "/ data/data/" + PACKAGE_NAME; private Context mContext; public DbManager (Context mContext) {this.mContext = mContext;} public SQLiteDatabase openDataBase () {return SQLiteDatabase.openOrCreateDatabase (DB_PATH + "/" + DB_NAME, null) } public void importDB () {File file = new File (DB_PATH + "/" + DB_NAME); if (! file.exists ()) {try {FileOutputStream out = new FileOutputStream (file); int buffer = 1024; InputStream in = mContext.getResources (). OpenRawResource (R.raw.xxxx); byte [] bts = new byte [buffer]; int lenght; while (lenght = in.read (bts) > 0) {out.write (bts, 0, bts.length) } out.close (); in.close ();} catch (Exception e) {e.printStackTrace ();}}

Call method:

@ Override protected void onResume () {super.onResume (); DbManager dbManager = new DbManager (this); dbManager.importDB (); SQLiteDatabase db = dbManager.openDataBase (); db.execSQL ("do what you want"); above is how to upgrade the database version separately and migrate the original data. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.