How to upgrade the Android database

Source: Internet
Author: User

Add the upgrade operation to the table in the base class:

Public abstract class Dbbasetable {private static final String TAG = "dbbasetable";/** * @return the DB table name */abstr Act String getName ();/** * creates the DB table according to the DB scheme * * @param db */abstract void OnCreate (sqlited Atabase db), void Onupgrade (sqlitedatabase db, int oldversion, int newversion, String tempname) {//rename O  LD table to temporary name<strong>dbutils.renametable (db, GetName (), tempname);</strong>//create Clear  Table According to the new scheme <strong> onCreate (db);</strong>//copy content of the matching                Columns from the old table to the new one <strong> joincolumns (db, Tempname, GetName ());</strong> Delete Old table <strong> dbutils.droptable (db, Tempname);</strong>//This is a few updates    The contents of the table inittablecontent (db); } void Inittablecontent (Sqlitedatabase db) {} void Joincolumns (Sqlitedatabase db, String temPName, String tableName) {<strong>dbutils.joincolumns (db, Tempname, TableName);</strong>}} 

Final class Dbutils {private static final String TAG = "Dbutils";        Private static Final Boolean DEBUG = false; private static final String sqlite_stmt_list_tables = "SELECT name from Sqlite_master WHERE type= ' table ' and name N    OT like ' sqlite_% ' and ' name ' is not a like ' android% ';    private static final String Sqlite_table_name_column = "NAME";    private static final String sqlite_stmt_template_list_columns = "SELECT * from%s LIMIT 1";    private static final String sqlite_stmt_template_drop_table = "DROP TABLE IF EXISTS%s";    private static final String sqlite_stmt_template_rename_table = "ALTER TABLE%s RENAME to%s";        private static final String Sqlite_stmt_template_copy_columns = "INSERT into%s (%s) select%s from%s"; /** * @param db * @return Collection object containing table names in the database */static COLLECTION&LT ;  String> listtables (Sqlitedatabase db) {cursor cursor = db.rawquery (sqlite_stmt_list_tables, NULL);      if (cursor = = NULL | |!cursor.movetofirst ()) {if (cursor! = NULL) {cursor.close ();        } return null;        } int table_name_column = Cursor.getcolumnindex (Sqlite_table_name_column);        hashset<string> tables = new hashset<string> (Cursor.getcount ());        do {Tables.add (cursor.getstring (table_name_column));        } while (Cursor.movetonext ());                Cursor.close ();    return tables;  }/** * @param db * @param table * @return List of column names in the DB table */public static List<string> ListColumns (sqlitedatabase db, String table) {cursor cursor = Db.rawquery (string.forma        T (sqlite_stmt_template_list_columns, table), NULL);        if (cursor = = NULL) {return null;        } list<string> columns = Arrays.aslist (Cursor.getcolumnnames ());        Cursor.close ();    return columns;       }     /** * @param db * @param table */static void droptable (Sqlitedatabase db, String table) {DB.E    Xecsql (String.Format (sqlite_stmt_template_drop_table, TABLE)); } static void Renametable (Sqlitedatabase db, String oldname, String newName) {Db.execsql (String.Format (SQLI    Te_stmt_template_rename_table, Oldname, NewName)); } static void Joincolumns (Sqlitedatabase db, String oldtable, String newtable) {//delete all re                Cords in the new table before copying from the old table db.delete (newtable, NULL, NULL); Find columns which exist in both tables arraylist<string> old_columns = new Arraylist<string> (listcol        Umns (DB, oldtable));        list<string> new_columns = listcolumns (db, newtable);        Old_columns.retainall (New_columns);                String common_columns = Textutils.join (",", old_columns); Copy records from old table to new table Example:<span style= "FOnt-family: Microsoft Jacob Black; ><strong><span style= "font-size:10px;" >insert to Mytest1 (_ID,ACCOUNT_ID,TEST1,TEST3) SELECT _id,account_id,test1,test3 from Mytest1_temp_</span ></strong></span> Db.execsql (String.Format (Sqlite_stmt_template_copy_columns, newtable, Common_    Columns, Common_columns, oldtable)); }}

Then overload the Onupgrade method in DBHelper:

 @Override public void Onupgrade (sqlitedatabase db, int oldversion, int. newversion) {//get table names in the O        LD db collection<string> old_tables = dbutils.listtables (db);            if (Old_tables = = NULL | | old_tables.size () = = 0) {onCreate (db);        Return }//get table names in the new DB set<string> new_tables = DataBaseClass.sRCMDbTables.keySet ()                ;            try {db.begintransaction (); Remove old tables which is not in the new DB scheme hashset<string> obsolete_tables = new Hashset<s            Tring> (); for (String table:old_tables) {if (!new_tables.contains (table)) {System.out.println ("= = =                    =dbhelp onupgrade droptable table= "+table);                    Dbutils.droptable (db, table);                Obsolete_tables.add (table);                }} old_tables.removeall (Obsolete_tables);Create and upgrade new tables dbbasetable table_descriptor;                                for (String table:new_tables) {table_descriptor = DataBaseClass.sRCMDbTables.get (table);                    Check If the new table exists in the old DB if (old_tables.contains (table)) {                    String temp_name = gettemptablename (table, Old_tables, new_tables);                    System.out.println ("====dbhelp onupgrade temp_name =" +temp_name);                Table_descriptor.onupgrade (db, Oldversion, NewVersion, temp_name);                } else {table_descriptor.oncreate (db);        }} db.settransactionsuccessful ();        } catch (Throwable e) {throw new RuntimeException ("DB Upgrade failed:" + e.getmessage ());        } finally {db.endtransaction (); }    }

The central idea is:

Compare the old database structure, if there is a table in the old table in the new database, then delete the

If there is a table update in the new database, you need to update the structure of the table and then copy the data from the old table.

(Rename the old table to a name and copy the data from the named table to the new table)

Code can be downloaded in

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.