Android Database Master cheats (iii)--Use Litepal upgrade table

Reprint Please specify source: http://blog.csdn.net/guolin_blog/article/details/39151617

In the previous article, we learned the basic usage of litepal and experienced the convenience of using frames to create table operations. However, as you all know, creating a table is just one of the most basic steps in database operations, and the table structure we created at the outset is most likely to need to be modified in the late stages as requirements change. Therefore, the operation of the upgrade table is also critical for any project, so today we will learn how to upgrade tables in Android Legacy development and use Litepal to perform upgrade table operations. If you have not read the previous article, it is recommended to first refer to the Android database Master cheats (ii)--Create tables and Litepal basic usage .

Traditional way to upgrade tables

In the previous article we created the News table with Mysqlitehelper, which is also the first version of the DEMO.DB database. However, now that the demand has changed, our software should allow users to comment in addition to the news, so we need to upgrade the database and add a comment table.

What should I do? Add a Comment table statement, and then execute it in the OnCreate () method? Yes, so the two tables are created at the same time, and the code looks like this:

public class Mysqlitehelper extends Sqliteopenhelper {public static final String create_news = "CREATE table NEWS (" + "ID Integer PRIMARY key AutoIncrement, "+" title text, "+" content text, "+" Publishdate Integer, "+" Commentcount integer) ";p u Blic static final String create_comment = "CREATE table COMMENT (" + "ID integer primary key autoincrement," + "content Tex T) ";p ublic mysqlitehelper (context context, String name, cursorfactory factory,int version) {Super (context, name, factory , version);} @Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (create_news);d b.execsql (create_comment);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {}}

This is for the first time the user to install our software is fully functional, but if some users have installed the previous version of the software, then unfortunately, the comment table is not created, because the database has been created, the OnCreate () method will not be re-executed.

For this situation we need to upgrade the way to solve, see the Mysqlitehelper constructor method of the fourth parameter, this is the database version number of the identity, each time the version number increases will call the Onupgrade () method, All we have to do is handle the upgrade table here. The simple and brutal way is to delete all existing tables in the database and recreate them, as shown in the following code:

public class Mysqlitehelper extends Sqliteopenhelper {... @Overridepublic void onCreate (Sqlitedatabase db) { Db.execsql (create_news);d b.execsql (create_comment);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {db.execsql ("drop table if exists news") ; onCreate (db);}}

As you can see, when the database is upgraded, we first remove the news list and then re-execute the OnCreate () method, which ensures that the tables in the database are up to date.

However, if you already have data in the news table, upgrading in this way will result in the loss of all the data in the table, so this is not a recommended upgrade method. So what is the better way to upgrade? This is a little bit more complicated, you need to add the specific upgrade logic in the Onupgrade () method based on the version number, let's try it out. For example, the previous database version number is 1, so you can write it in the Onupgrade () method:

public class Mysqlitehelper extends Sqliteopenhelper {... @Overridepublic void onCreate (Sqlitedatabase db) { Db.execsql (create_news);d b.execsql (create_comment);} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {switch (oldversion) {case 1:db.execsql (create_comment);d Efault:}}}

As you can see, a switch is added to the Onupgrade () method, and if Oldversion equals 1, a comment table is created. Now just call the following code, and the table can be created or upgraded:

Sqliteopenhelper dbhelper = new Mysqlitehelper (This, "demo.db", NULL, 2); Sqlitedatabase db = Dbhelper.getwritabledatabase ();

Here we add the version number 1, if the user is upgraded from the old version, will add a comment table, and if the user is a direct installation of the new version, will be in the OnCreate () method of the two tables are created together.

OK, now the second version of the software has been released, but shortly after the release, it was suddenly discovered that a field was missing from the comment table, and we did not record the time the comment was posted. No way, had to fix this problem in the third edition, then how do we add this field? The main need to modify the Comment table statement, as well as the logic in the Onupgrade () method, the code is as follows:

public class Mysqlitehelper extends Sqliteopenhelper {... public static final String create_comment = "CREATE Table comm ENT ("+" ID integer primary key autoincrement, "+" content text, "+" publishdate integer) "; @Overridepublic void on Upgrade (sqlitedatabase db, int oldversion, int newversion) {switch (oldversion) {case 1:db.execsql (create_comment); case 2:db.execsql ("ALTER TABLE comment add column publishdate integer");d Efault:}}}

As you can see, in the table statements we have added the Publishdate column so that when the OnCreate () method is executed to create the table, the comment table will have this column. So what if it was upgraded from the old version? No problem, we have already handled the upgrade logic in the Onupgrade () method, and when Oldversion equals 2, an ALTER statement is executed to add the Publishdate column. Now call the following code to create or upgrade the database:

Sqliteopenhelper dbhelper = new Mysqlitehelper (This, "demo.db", NULL, 3); Sqlitedatabase db = Dbhelper.getwritabledatabase ();

Set the database version number to 3 so that the tables in the database are up to date.

Here we have to pay attention to a detail, switch in each case is not the end of the use of break, why do you want to do so? 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.

Now that we've learned the two ways to upgrade new and new columns, if it's a column in a table that's no longer working, I want to delete that column. Unfortunately, SQLite does not support the ability to delete columns, in this case, most of the software is to ignore it, anyway, will not be able to use it, there is no space to stay, so in response to this demand, there is really no simple solution.

This is probably the way to upgrade database tables in traditional development, although it is possible to write code that shows that you have a clearer understanding of the database upgrade operation, but as the version becomes more and more, the logic in the Onupgrade () method becomes more complex, and a bit of inattention may result in errors. Therefore, if you can let the code automatically control the upgrade logic, rather than manually to manage, that is better, then we will learn how to use Litepal to upgrade the table operation.

Using the Litepal upgrade table

Through the study of the previous article, we already know that Litepal is the framework of an ORM mode, you are familiar with the process of creating tables, I believe that the upgrade table will also be pro. So in order to imitate the requirements in the traditional upgrade table, now we also need to create a comment table. What about the first step? I believe you may have guessed it earlier, of course, first create a comment class, as follows:

Package Com.example.databasetest.model;public class Comment {private int id;private String content;//automatically generate get, set method ...}

The Ok,comment class has both the ID and Content fields, which means that there are two columns of ID and content in the comment table.

Then modify the configuration in Litepal.xml, add the Cooment class to the map list, and add the version number to 1, as follows:

<?xml version= "1.0" encoding= "Utf-8"?><litepal> <dbname    value= "Demo" ></dbname>    <version value= "2" ></version>    <list>        <mapping class= " Com.example.databasetest.model.News "></mapping>        <mapping class=" Com.example.databasetest.model.Comment "></mapping>    </list></litepal>

Yes, it is that simple, just two steps, the upgrade operation has been completed, now we just need to manipulate the database, the comment table will be automatically generated, as follows:

Sqlitedatabase db = Connector.getdatabase ();

So let's go through the. Table command to see the results, as shown in:

Ok,comment table has come out, then through the pragma command to see its table structure it:

No problem, the comment table currently has two columns of ID and content, and the fields in the comment model class are consistent.

So now there's a new requirement, and you need to add a publishdate column to the comment table, what do we do? No doubt, follow your instincts, believe you have guessed you should add such a field to the comment class, as shown below:

public class Comment {private int id;private String content;private Date publishdate;//automatically generate get, set method ...}

And then what? The rest of the operation is very simple, just add 1 to the version number in Litepal.xml, as shown below:

<litepal>    <dbname value= "Demo" ></dbname>    <version value= "3" ></version>    ...</litepal>

So when we next operate the database, the Publishdate column should be automatically added to the comment table. Call the Connector.getdatabase () method, and then requery the comment table structure as follows:

As you can see, the Publishdate column has indeed been successfully added to the comment table.

By comparing these two upgrade modes, I believe you have fully realized the convenience of using Litepal to perform upgrade table operation. We don't need to write any upgrade-related logic or care about which version of the program was upgraded, the only thing to do is to determine what the latest model structure is, and then add 1 to the version number in Litepal.xml, and all the upgrade logic will be done automatically. Litepal does make it extremely easy to upgrade database tables, freeing many programmers from the trouble of maintaining database table upgrades.

Litepal, however, has clearly done better. Before we mentioned the problem of deleting columns, the final conclusion is that it cannot be resolved because SQLite does not support the command to delete columns. But if the use of Litepal, this problem can be easily solved, such as publishdate this column we do not want, then only need to delete it in the comment class, and then the version number 1, the next time the database operation of the column will be gone.

Then some friends may ask, not to say that SQLite does not support the Delete column command? How did the Litepal do it? In fact, Litepal did not delete any column, it just first renamed the comment table to a temporary table, and then based on the structure of the latest comment class to generate a new comment table, and then the temporary table in addition to the publishdate of data copied into the new table, Finally, delete the temporary table. As a result, the effect seems to be the ability to delete columns.

This is also the advantage of using the framework, without the help of the framework, we obviously do not want to delete a column and a lot of waste to write so much code, and the use of the framework, the concrete implementation of the logic we have no longer care, just need to control the data structure of the model class can be.

In addition, if you want to delete a table, the operation is also very simple, in the map list in the Litepal.xml the corresponding class is deleted, the table naturally does not exist. Some of the other upgrade operations are similar, I believe you have been able to extrapolate, here will not repeat.

Well, today's introduction to Litepal is here, and in the next article we'll learn to use Litepal for table-related operations.

Android Database Master cheats (iii)--Use Litepal upgrade table