preface

In the last article, we learned the basics of LitePal and experienced the convenience of using the framework to create tables. However, as we all know, table creation is only the most basic step in database operation. The table structure we create at the beginning will most likely need to be modified later as requirements change. Therefore, the operation of upgrading tables is also very important for any project, so today we will learn how to upgrade tables in the traditional Android development, and how to use LitePal to upgrade tables. If you haven’t read the previous article, I suggest you first look at Android Database Master Tips (2) — Basic use of Creating tables and LitePal.

LitePal’s project address is: github.com/LitePalFram…

Traditional upgrade table mode

In the previous article, we created the news table using MySQLiteHelper, which is the first version of the demo.db database. However, now that the requirements have changed, our software should allow users to comment as well as watch news, we need to update the database to add a comment table.

How do you do that? Add a comment to the table and execute it in the onCreate() method. Yes, in this case, both tables will be created at the same time, as shown in the following code:

public class MySQLiteHelper extends SQLiteOpenHelper {

public static final String CREATE_NEWS = "create table news ("

			+ "id integer primary key autoincrement, "

			+ "commentcount integer)";

public static final String CREATE_COMMENT = "create table comment ("

			+ "id integer primary key autoincrement, "

public MySQLiteHelper(Context context, String name, CursorFactory factory,

super(context, name, factory, version);

public void onCreate(SQLiteDatabase db) {

		db.execSQL(CREATE_COMMENT);

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Copy the code

This is perfectly fine for a user who has installed our software for the first time, but if someone has already installed a previous version of the software, the comment table will not be created because the database has already been created and the onCreate() method will not be re-executed.

The fourth parameter in the MySQLiteHelper constructor indicates the database version number. The onUpgrade() method is called whenever the database version number is increased. We only need to upgrade the table here. The simplest way is to delete all existing tables in the database and create them again.

public class MySQLiteHelper extends SQLiteOpenHelper {

public void onCreate(SQLiteDatabase db) {

		db.execSQL(CREATE_COMMENT);

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("drop table if exists news");
Copy the code

As you can see, when the database was upgraded, we first deleted the news table and then re-executed the onCreate() method to ensure that the tables in the database were up to date.

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

public class MySQLiteHelper extends SQLiteOpenHelper {

public void onCreate(SQLiteDatabase db) {

		db.execSQL(CREATE_COMMENT);

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			db.execSQL(CREATE_COMMENT);
Copy the code

As you can see, a switch judgment has been added to the onUpgrade() method to create a comment table if oldVersion is equal to 1. The table can now be created or upgraded by simply calling the following code:

SQLiteOpenHelper dbHelper = new MySQLiteHelper(this, "demo.db", null, 2);

SQLiteDatabase db = dbHelper.getWritableDatabase();
Copy the code

Here we increment the version number by one, adding a comment table if the user is upgrading from an older version, and creating the two tables together in the onCreate() method if the user is directly installing the new version.

OK, now the second version of the software has been released, but shortly after the release, we suddenly found that a field in the comment table was missing, we didn’t record when the comment was published. We have no choice but to fix this problem in the third version, so how do we add this field? We need to modify the construction sentence of the comment table and the logic in the onUpgrade() method. The code is as follows:

public class MySQLiteHelper extends SQLiteOpenHelper { public static final String CREATE_COMMENT = "create table comment  (" + "id integer primary key autoincrement, " + "publishdate integer)"; public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(CREATE_COMMENT); db.execSQL("alter table comment add column publishdate integer");Copy the code

As you can see, we have added a publishDate column to the table statement, so that when the onCreate() method is executed to create the table, the comment table will have this column. What if you upgrade from an older version? If oldVersion is equal to 2, an ALTER statement will be 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();
Copy the code

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

Now that you’ve learned how to add a new table and add a new column, what if a column in a table is no longer useful and I want to delete it? Unfortunately, SQLite doesn’t support column deletion, and most software simply ignores it because it won’t take up much space since it won’t be used in the future, so there really isn’t an easy solution to this need.

This is probably the traditional way to upgrade a database table. Writing code like this means that you have a good understanding of how to upgrade a database, but the logic in the onUpgrade() method becomes more complex as the number of versions increases, and errors can occur if you are not careful. Therefore, it would be nice to have the code automatically control the upgrade logic instead of having it managed by a human, so let’s learn how to use LitePal to upgrade tables.

Upgrade the table using LitePal

LitePal is an ORM framework. If you are familiar with creating tables, you will be familiar with upgrading tables as well. So now we need to create a comment table as well to mimic the requirements in the traditional update table approach. What’s the first step? As you’ve probably already guessed, the first step is to create a Comment class like this:

package com.example.databasetest.model;
Copy the code

OK, the Comment class has two fields, ID and Content, which means the Comment table will have two columns, ID and content.

Next, modify the configuration in litepal.xml to add Cooment to the mapping list and add the version number by 1, as shown below:

<? The XML version = "1.0" encoding = "utf-8"? > <dbname value="demo" ></dbname> <version value="2" ></version> <mapping class="com.example.databasetest.model.News"></mapping> <mapping class="com.example.databasetest.model.Comment"></mapping>Copy the code

Yes, it is as simple as that, only two steps, the upgrade operation has been completed, now we only need to operate the database, the comment table will be automatically generated, as shown below:

SQLiteDatabase db = Connector.getDatabase();

Copy the code

So let’s use the.table command to check the results, as shown in the figure below:

OK, the comment table is there, so let’s see the pragma structure again:

There is no problem. There are two columns in the comment table, id and Content, which are consistent with the fields in the comment model class.

So what about the new requirement to add a publishDate column to the comment table? No doubt, following your intuition, you’ve already guessed that you should add a field like this to the Comment class, as follows:

private Date publishDate;
Copy the code

And then what? The rest of the operation is as simple as adding one to the version number in litepal.xml, as shown below:

<dbname value="demo" ></dbname>

<version value="3" ></version>
Copy the code

The next time we operate on the database, the publishDate column should be automatically added to the comment table. Call the Connector. GetDatabase () method and re-query the comment table structure as follows:

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

By comparing the two upgrade methods, you can see the convenience of using LitePal to upgrade tables. We don’t need to write any upgrade logic, and we don’t need to care about which version the program is being upgraded from. All we need to do is figure out what the latest Model structure looks like, add one to the version number in litepal.xml, and all the upgrade logic is done automatically. LitePal does make it extremely easy to upgrade database tables, freeing many programmers from the hassle of maintaining database table upgrades.

LitePal, however, is clearly doing better. We mentioned earlier about the issue of deleting columns, and the final conclusion is that it cannot be resolved because SQLite does not support the command of deleting columns. If we use LitePal, this problem can be easily solved. For example, if we don’t want the publishDate column, we just delete it from the Comment class and increment the version number by 1. The next time we operate the database, the column will be gone.

SQLite does not support the delete column command. So how does LitePal do it? LitePal renatures the comment table into a temporary table, generates a new comment table based on the structure of the latest comment class, and copies all the data from the temporary table except for publishdate into the new table. Finally, delete the temporary table. Therefore, it looks as if the ability to delete columns has been achieved.

This is also the benefit of using a framework. Without the framework, we obviously wouldn’t have to write so much code to remove a column. With a framework, we don’t have to worry about the implementation logic, but only control the data structure of the model class.

In addition, if you want to delete a table, it is easy to delete the corresponding class from the mapping list in litepal.xml, and the table will not exist. Some of the other upgrades are similar, so I’m not going to tell you about them here.

In the next article, we will learn how to use LitePal to perform table association operations. If you are interested, please read on.