Getting Started with Android (12) SQLite transaction, upgrade database

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

First, the business

SQLite supports transactions, look at how Android uses transactions: for example, the data in the Book table is already very old, and now ready to discard all the new data, you can first use the Delete () method to delete the data in the Book table, and then use the Insert () method to add new data to the table. We want to make sure that deleting old data and adding new data must be done together, or you will continue to keep the old data.

                 Button replaceData =  (Button)  findviewbyid (r.id.replace_data); Replacedata.setonclicklistener (New view.onclicklistener ()  {@Overridepublic  void onclick ( VIEW V)  {sqlitedatabase db = dbhelper.getwritabledatabase ();d b.begintransaction (); Db.delete ("book",  null, null);try {if  (true)  {//  manually throw an exception to make the transaction fail throw new  exception ();} Contentvalues values = new contentvalues (); Values.put ("Name",  "Book new"); Values.put ("Author",  "Orlion"), Values.put ("pages",  200), Values.put ("Price",  100);d B.insert (" Book ",  null, values);d b.settransactionsuccessful (); //  transaction has been executed successfully} catch  ( exception e)  {e.printstacktrace ();}  finally {db.endtransaction (); //  End Transaction}}); 

The above code is the standard use of transactions in Android, first call Sqlitedatabase's BeginTransaction () method to open a transaction, and then in an exception-caught code block to perform specific database operations, when all the operations are completed, call Settransactionsuccessful () indicates that the transaction has executed successfully, and finally calls Endtransaction () in the finally code block to end the transaction. Note that we have manually thrown a nullpointerexception after the operation to delete the old data, so that the code to add the new data is not executed. However, due to the existence of a transaction, an exception in the middle will cause the failure of the transaction, when the old data should be deleted.

Ii. best Practices for upgrading databases

Copy the first line of the original code directly here

Getting Started with Android (10) SQLite the way to upgrade a database in the article on creating an upgrade database is very rude, in order to ensure that the tables in the database are up to date, we simply remove all of the current tables in the Onupgrade () method and force the re-execution again onCreate () method. This method can be used in the development phase of the product, but when the product is actually online, it will definitely not work.

Each database version corresponds to a version number that is entered into Onupgrade () when the specified database version number is greater than the current database version number.

method to perform the update operation. It is necessary to give each version number its own changed content, and then in the Onupgrade () method to determine the current database version number, and then perform the corresponding changes.

Then let's simulate a case of a database upgrade, or the Mydatabasehelper class to manage the database. The first version of the program requirements is very simple, just to create a book table, the code in Mydatabasehelper is as follows:

public class mydatabasehelper extends sqliteopenhelper {     public static final string create_book =  "create table book  ("         +  "id integer primary key  autoincrement,  "        + " author text,  "         +  "price real, "          +  "pages integer, "         +  "name"  text) ";     public mydatabasehelper (Context context, string name,  cursorfactory        factory, int version)  {         super (context, name, factory, version);     }    @Override    public void oncreate (SQLITEDATABASE DB)  {         db.execsql (Create_book);     }    @ Override    public void onupgrade (sqlitedatabase db, int  oldversion, int newversion)  {    }}

     However, a few weeks later there was a new need to add another category table to the database. Then, modify the code in the Mydatabasehelper as follows:

public class mydatabasehelper extends sqliteopenhelper {     public static final string create_book =  "create table book  ("         +  "id integer primary key  autoincrement,  "        + " author text,  "         +  "price real, "          +  "pages integer, "         +  "name"  text) ";     public static final string create_category =   "create table category  ("         +  "id  integer primary key autoincrement,  "        +   "category_name text, "  &Nbsp;      +  "Category_code integer)";     public  mydatabasehelper (context context, string name,         cursorfactory factory, int version)  {         super (context, name, factory, version);     }    @ Override    public void oncreate (SQLITEDATABASE DB)  {         db.execsql (Create_book);         Db.execsql (create_category);    }     @Override      Public void onupgrade (sqlitedatabase db, int oldversion, int newversion)  {        switch  (oldversion)  {         case&Nbsp;1:            db.execsql (CREATE_CATEGORY);             default:         }    }}

     can see that in the OnCreate () method We have added a new build statement, 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. This creates the two tables together when the user is the second version of the program that is installed directly. When the user is using the second version of the program to overwrite the installation of the first version of the program, will go into the upgrade database operation, at this time because the book table already exists, so only need to create a category table. But it wasn't long before the new demand came, and this time, to relate the Book table to the category table, you need to add a category_id field to the Book table. Modify the code in Mydatabasehelper again, as follows:

public class mydatabasehelper extends sqliteopenhelper {     public static final string create_book =  "create table book  ("         +  "id integer primary key  autoincrement,  "        + " author text,  "         +  "price real, "          +  "pages integer, "         +  "name"  text,  "        + " Category_id integer) ";     public static final String CREATE_CATEGORY =  "create  table category  ("        + " id integer  primary key autoincrement,  "        +  "category_name text, "          +  "Category_code integer");     public mydatabasehelper ( Context context, string name,        cursorfactory  factory, int version)  {        super (context,  name, factory, version);    }     @Override      public void oncreate (SQLITEDATABASE DB)  {         db.execsql (Create_book);         db.execsql (CREATE_CATEGORY );    }     @Override     public void  Onupgrade (sqlitedatabase db, int oldversion, int newversion)  {         switch  (oldversion)  {        case 1:             db.execsql (create_category);         case 2:             db.execsql ("Alter table book add column category_id integer");         default:        }     }}

As you can see, first we add a category_id column to the Book table's build statement, so that when the user installs the third version of the program directly, the new column is automatically added. However, if the user has previously installed a version of the program, and now needs to overwrite the installation, it will go to the upgrade database operation. In the Onupgrade () method, we added a new case, and if the current database version number is 2, an alter command will be executed to add a category_id column to the Book table.

Please note that there is a very important detail here, and the end of each case in switch does not use break, why do you do this? This is to ensure that every database modification can be performed at the time of the cross-version upgrade. For example, the user is currently upgrading from the second version of the program to the third version of the program, then the logic in case 2 will be executed. If the user is directly from the first version of the program to upgrade to the third version of the program, then case 1 and the logic in case 2 will be executed. Using this approach to maintain a database upgrade, regardless of how the version is updated, ensures that the database's table structure is up-to-date and that the data in the table is not lost at all.

Getting Started with Android (12) SQLite transaction, upgrade database