Getting started with Android (XII) SQLite transaction and database upgrade

Original link: http://www.orlion.ga/610/

1、 Business

SQLite supports transactions. Let's take a look at how Android uses transactions: for example, the data in the book table is very old and ready to be discarded and replaced with new data. You can first delete the data in the book table using the delete () method, and then add the new data to the table using the insert () method. What we want to ensure is that the operations of deleting old data and adding new data must be completed together, otherwise the original old data will continue to be retained.

The above code is the standard usage of transactions in Android. First, call the beginTransaction () method of sqliteDatabase to open a transaction, then perform the specific database operation in an exceptionally captured code block. When all the operations are completed, calling setTransactionSuccessful () indicates that the transaction has been executed successfully. Finally, call endTransaction () in the finally code block to end the transaction. Note that after the operation of deleting old data is completed, we manually throw a NullPointerException, so the code to add new data cannot be executed. However, due to the existence of the transaction, an exception in the middle will lead to the failure of the transaction. At this time, the old data should not be deleted.

2、 Best way to upgrade database

//Here, copy the original text of the first line of code directly

Getting started with Android (10) the method of upgrading the database in the article "creating and upgrading the database by SQLite" is very rough. In order to ensure that the tables in the database are up-to-date, we simply delete all the current tables in the onupgrade () method, and then force the oncreate () method to be re executed. This method can be used in the product development stage, but it will never work when the product is really online.

Each database version corresponds to a version number. When the specified database version number is greater than the current database version number, it will enter onupgrade ()

Method to perform the update operation. Here, you need to give each version number its own changes, then judge the version number of the current database in the onupgrade () method, and then perform the corresponding changes.

Then let's simulate a database upgrade case, or the mydatabasehelper class manages the database. The program requirements of the first version are very simple. You only need to create a book table. The code in mydatabasehelper is as follows:

However, a few weeks later, there is a new demand. This time, you need to add another category table to the database. Then, modify the code in mydatabasehelper as follows:

As you can see, in the oncreate () method, we added a statement to create a table, and then added a switch judgment in the onupgrade () method. If the user's current database version number is 1, only one category table will be created. In this way, when the user installs the second version of the program directly, the two tables will be created together. When you use the second version of the program to overwrite the first version of the program, you will enter the operation of upgrading the database. At this time, because the book table already exists, you only need to create a category table. But before long, new requirements came again. This time, to establish an association between the book table and the category table, you need to add a category to the book table_ Field of ID. Modify the code in mydatabasehelper again as follows:

As you can see, first, we added a category to the table creation statement of the book table_ ID column, so that when the user directly installs the third version of the program, the new column has been automatically added successfully. However, if the user has installed a certain version of the program before and needs to overwrite the installation, he will enter the operation of upgrading the database. In the onupgrade () method, we add a new case. If the version number of the current database is 2, we will execute the alter command to add a category for the book table_ ID column.

Please pay attention to a very important detail here. Break is not used at the end of each case in switch. Why do you do this? This is to ensure that every database modification can be executed during cross version upgrade. For example, if the user is currently upgrading from version 2 to version 3, the logic in case 2 will be executed. If the user upgrades directly from the first version to the third version, the logic in case 1 and case 2 will be executed. Using this method to maintain the database upgrade, no matter how the version is updated, it can ensure that the database table structure is up-to-date, and the data in the table will not be lost at all.