Creation of Android database tables and data upgrade operations

As mentioned in the previous article, you can configure database information in an XML file: http://www.cnblogs.com/wenjiang/p/4492303.html, now how to build a database with these information classes.

The XML file might look like this:

<?XML version= "1.0" encoding= "Utf-8"?><Database>    <!--Database name -    <dbnamevalue= "Zwb.db"></dbname>    <!--Database Version -    <versionvalue= "1"></version>    <!--Database Tables -    <List>        <Mappingclass= "Com.zwb.args.dbpratice.model.Status"></Mapping>        <Mappingclass= "Com.zwb.args.dbpratice.model.User"></Mapping>    </List></Database>

The information read is actually simple: database name, database version and database table.

Android originally provided Sqliteopenhelper to help us complete some basic operations of the database, so there is still a need to implement Sqliteopenhelper.

Define a sqliteopenhelper that passes the database name and database version at initialization time:

 Public int version) {    supernull, version);}

The next step is to implement the CREATE table action in the OnCreate method:

Taking into account the problem of data upgrade, I put the operation of the data upgrade in a method, and then call OnCreate in the Onupgrade method is OK.

This is only a matter of data upgrade, of course, the downgrade is also possible, but generally this situation is relatively rare on the mobile side.

Before the list containing the table name of the database is stored in the Sharedpreferencesmanager, of course, the general Sharedpreferencesmanager is not able to store the container class, but here I have extended, specific implementation needs to be taken out later.

The database is the information that configures the database tables in the XML file, so it must be judged whether the database information read from the XML file is consistent with the previous one, and if the original configuration information is not, create the table (even if you just modify the table name to recreate the table, which is no doubt, Since modifying the old table is also expected to be able to migrate the data to the new table, it was a little hasty to start the table ... ), if the original configuration has, not now, the description is to delete the old table.

list<string> oldtablelist = Sharedpreferencesmanager.getinstance (). getliststring ("Tables"); if (Oldtablelist.size ()! = 0)     {for  (String table:oldtablelist)          {if (!  DatabaseCache.tableSet.contains (table)) {                droptable (db, table);          }     }       for (String table:DatabaseCache.tableSet) {           if (!  Oldtablelist.contains (table)) {                createtable (db, table);       }}}

The implementation of Droptable is very simple:

String deletesql = "drop table if exists" + table; Db.execsql (deletesql);

The implementation of the createtable is also not complex, it is also splicing SQL statements:

    Private voidcreatetable (sqlitedatabase db, String table) {Try{StringBuilder SQL=NewStringBuilder ("CREATE table if not exists"); BaseTable Entity=( basetable) (Class.forName (table). newinstance ()); String TableName=Gettablename (entity);            Sql.append (TableName); Sql.append ("(ID integer primary key autoincrement,"); List<String> columnlist =GetColumns (entity);  for(inti = 0, length = columnlist.size (); i < length; i++) {sql.append (Columnlist.get (i)+ " "); if(i = = Length-1) {                     Break; } sql.append (", "); } sql.append (");");        Db.execsql (Sql.tostring ()); } Catch(instantiationexception e) {LOGUTIL.E (e.tostring ()); } Catch(illegalaccessexception e) {LOGUTIL.E (e.tostring ()); } Catch(ClassNotFoundException e) {LOGUTIL.E (e.tostring ()); } Catch(nosuchtableexception e) {LOGUTIL.E (e.tostring ()); }    }

Android itself also provides encapsulation of SQL statements, but in fact, using the Android API is not as good as writing SQL yourself ...

If the application is installed for the first time, the creation of the database is ended, and the new configuration information is stored in the Sharedpreferencesmanager.

But if it is a database upgrade, in the execution of the above operation, the table is the deleted, the construction of the building, but if you want to do data migration, for example, a table added some fields (here does not do delete field processing, even if there are extra fields of data retention is not a problem, compared to the severity of the loss of data, A certain amount of redundant fields is still acceptable.)

The implementation is also relatively simple, is a few steps: Rename the old table to a temporary table to save data, create old tables, migrate temporary table data to the old table, delete temporary tables, each step is the concatenation of SQL statements:

        for(String tableEntity:DatabaseCache.tableSet) {Try{basetable entity=( basetable) (Class.forName (tableentity). newinstance ());                Db.begintransaction (); String Table=Gettablename (entity); String Selectsql= "SELECT * from" +table; Cursor Cursor= Db.rawquery (Selectsql,NULL); List<String> Oldcolumns =NewArraylist<>();  for(String column:cursor.getColumnNames ()) {oldcolumns.add (column); } String Altersql= "ALTER TABLE" + table + "Rename to" + table + "_temp";                Db.execsql (Altersql);                CreateTable (DB, tableentity); List<String> Newcolumns =Getcolumnfrom (tableentity); StringBuilder Upgradesqlbuilder=NewStringBuilder ("INSERT into" + table + "SELECT ID,"); inti = 0;  for(String column:newcolumns) {if(oldcolumns.contains (column)) {upgradesqlbuilder.append (column+ ", "); I++; }                }                if((I! = 0) && (i <newcolumns.size ())) {                     for(intj = 0, length = newcolumns.size ()-I; J < length; J + +) {upgradesqlbuilder.append ("‘‘, "); }} String Upgradestr=upgradesqlbuilder.tostring (); String Upgradesql= upgradestr.substring (0, Upgradestr.length ()-2) + "from" + table + "_temp";                Db.execsql (Upgradesql); Droptable (db, Table+ "_temp");                Db.settransactionsuccessful ();            Db.endtransaction (); } Catch(instantiationexception e) {LOGUTIL.E (e.tostring ()); } Catch(illegalaccessexception e) {LOGUTIL.E (e.tostring ()); } Catch(ClassNotFoundException e) {LOGUTIL.E (e.tostring ()); } Catch(nosuchtableexception e) {LOGUTIL.E (e.tostring ()); }        }

Because of the number of database operations involved, I put these actions in the transaction execution.

Here, based on the XML configuration information read, the application database information is created.

Creation of Android database tables and data upgrade operations