Android Database Expert secret (2)-basic usage of creating tables and LitePal

Android Database Expert secret (2)-basic usage of creating tables and LitePal

We learned some basic knowledge about the Android database and some useful SQLite commands, which are directly operated on the command line. However, we all know that the database should be used together with the program. It does not make any sense to perform the delete, modify, and query operations on a single database, so today we will learn how to operate the SQLite database in the Android program. If you have not read the previous article, please refer to it first.Secret of Android database experts (1) -- SQLite command.

The first step in database operations is to create a table. I believe most people know the traditional method of creating a table. Today, apart from the traditional method of table creation, we will also explain the basic usage of the LitePal framework and use it to perform the same table creation operations, so that you can appreciate the charm of using the framework to operate databases.

Here is a brief introduction. LitePal is an open-source Android database framework that adopts the object relational ing (ORM) mode, we also encapsulate some of the database functions most commonly used during development so that you do not need to write a single SQL statement to complete the operations of creating tables and deleting and modifying tables. In addition, LitePal is very "light", and the jar package is less than kb, and almost zero configuration, which is very different from the frameworks such as Hibernate. LitePal source code has been hosted on GitHub, address is https://github.com/LitePalFramework/LitePal.

OK. After briefly introducing LitePal, let's take a look at how to create a table in traditional Android development.

Traditional table creation methods

In fact, to facilitate the management of database tables, Android itself provides a help class: SQLiteOpenHelper. This class combines database creation and upgrade, and automatically manages the database version. It is a very useful tool.

Now let's try SQLiteOpenHelper. First, you need to know that SQLiteOpenHelper is an abstract class, which means that if we want to use it, we need to create a help class to inherit it. SQLiteOpenHelper has two Abstract METHODS: onCreate () and onUpgrade (). We must rewrite these two methods in our help class, then the logic for creating and upgrading databases is implemented in the two methods respectively. This article only needs to focus on creating databases. We will discuss upgrading databases in the next article.

Create a new MySQLiteHelper class and let it inherit SQLiteOpenHelper. The code for such a basic database help class is as follows:

public class MySQLiteHelper extends SQLiteOpenHelper {public MySQLiteHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}@Overridepublic void onCreate(SQLiteDatabase db) {}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}

The onCreate () method is called when the database is created. You can create a table here. For example, we want to create a new news table with the title, content, publishdate, and commentcount columns representing the news title, news content, release time, and comment count, respectively, the code can be written as follows:

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);public MySQLiteHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_NEWS);}    ...}

We can see that the table creation statement is defined as a constant, and then this table creation statement is executed in the onCreate () method, and the news table is created successfully. Although this table creation statement is simple, it still contains some small details. Let me explain it. First, according to the database paradigm, any table should have a primary key, so here we add an auto-increment id column and set it as the primary key. Then, the title column and content column are both string-type and the commentcount column is integer. This is well understood. But how should I design the publishdate column? Since SQLite does not support data types such as date storage, we need to convert the date to the number of milliseconds in UTC time (from on January 1, January 1, 1970) before storing it in the database, therefore, the publishdate column should also be an integer type.

 

Now, we only need to get the SQLiteDatabase instance, and the database table will be automatically created, as shown below:

SQLiteOpenHelper dbHelper = new MySQLiteHelper(this, demo.db, null, 1);SQLiteDatabase db = dbHelper.getWritableDatabase();

It feels very simple and convenient, right? Then you are too satisfied. Let's take a look at the basic usage of LitePal and see how this framework achieves the same function.

Basic LitePal usage

Although LitePal is declared to be almost zero-configuration, it is only "almost". It still requires some simple configuration to be used, the first step is to quickly learn how to configure LitePal.

Quick Configuration

1. Introduce the Jar package or source code

First, we need to introduce the LitePal jar package into the project.Click hereView the latest LitePal version and select the desired download. After downloading the jar package, you can copy it to the libs directory of the project, as shown in:

If you do not want to use the jar package, you can also download the LitePal source code and import it to Eclipse as a library. Then let our project reference this library.

2. Configure litepal. xml

Create a litepal. xml file under the assets Directory of the project and copy the following code:

The configuration file is quite simple, Used to set the Database Name, Used to set the database version number, Used to set all the ing models, which will be used later.

 

3. Configure LitePalApplication

Because Context is required for database operations, and we obviously do not want to pass this parameter in every interface, it is too cumbersome to operate the database. Therefore, LitePal uses a method to simplify the Context parameter. You only need to configure the LitePalApplication in AndroidManifest. xml. All database operations do not need to pass Context, as shown below:

    
             ...    
    

Of course, some programs may have their own applications and have been configured here. For example, there is a MyApplication, as shown below:

    
             ...    
    

It does not matter. In this case, you only need to modify the inheritance structure of MyApplication so that it does not inherit the Application class directly, but inherits the LitePalApplication class, and everything can work normally. The Code is as follows:

public class MyApplication extends LitePalApplication {    ...}

However, some programs may encounter more extreme situations. For example, MyApplication needs to inherit another AnotherApplication, and this AnotherApplication is still in the jar package and cannot be modified. This is a rare case, but if you encounter it, you don't have to worry about it. There is still a solution to explain it. You can download the LitePal source code, copy all the code under the src directory to the src directory of your project, and then open the LitePalApplication class, change its inheritance structure to inherit from AnotherApplication, and then let MyApplication inherit from LitePalApplication, so that all applications can work together normally.

In just three steps, we have completed all the configuration work, and this is a very beneficial thing. Since then, You can happily experience the various conveniences provided by LitePal, let's start with creating a table.

Start table Creation

As mentioned earlier, LitePal adopts the object relationship ing (ORM) mode. What is object relationship ing? To put it simply, the programming language we use is an object-oriented language, while the database we use is a relational database, then, a ing relationship is established between the object-oriented language and the relational database, which is the object link ing.

But why do we need to use the object link ing mode? This is mainly because most programmers are very good at object-oriented programming, but only a few of them are proficient in relational databases. In addition, the SQL language of the database is obscure. Even if you are proficient in it, I am afraid you do not like to write it frequently in code? The object link ing mode solves this problem well. It allows us to operate databases in an object-oriented way, so that we can be freed from obscure SQL languages.

Next, let's take a look at how to create a table in LitePal. According to the concept of object relationship ing mode, each table should correspond to a Model. That is to say, if we want to create a news table, we should have a corresponding News Model class. Create a News class as follows:

package com.example.databasetest.model;public class News {}

Then, each column in the table corresponds to a field in the model class. For example, the news table contains the id, title, content, publishdate, and commentcount columns, the following code shows the fields in the News class:

Public class News {private int id; private String title; private String content; private Date publishDate; private int commentCount; // The get and set methods are automatically generated ...}

The id field can be written or not written, because even if this field is not written, LitePal will automatically generate an id column in the Table. After all, each table must have a primary key.

 

Here I want to explain in particular that the LitePal ing rules are very lightweight. Unlike some other database frameworks, You need to configure a single ing XML for each model class, all Mappings of LitePal are automatically completed. According to the data types supported by LitePal, there are eight types of data that can be mapped to Object Relations: int, short, long, float, double, boolean, String, and Date. Fields declared as these eight data types will be automatically mapped to the database table without any additional configuration.

Some may ask, what if there is a string field in the News class that I don't want to map to a database table? LitePal also uses an extremely lightweight solution. Only fields declared as private modifiers are mapped to the database table. If you do not want to map a field, you only need to change it to the public, protected, or default modifier.

Now that the model class has been created, the last step is to configure it in the ing list. Edit the litepal. xml file in the assets Directory. Declaration of adding the News model class to the label:

Note that you must enter the full name of the News class.

 

OK, so that all the work has been completed. Now, as long as you have any operations on the database, the news table will be automatically created. For example, LitePal provides a convenient way to obtain the SQLiteDatabase instance, as shown below:

SQLiteDatabase db = Connector.getDatabase();

Call the above code to create the news table. We can use the SQLite Command learned in the previous article to view it. Open the demo. db database and enter the. table command. The result is as follows:

 

We can see that the news table already exists. The other two android_metadata and table_schema tables are automatically generated. Next, we can query the table creation statement of the news table, as shown in:

This is the table creation statement automatically generated by LitePal Based on the fields in the News class. It also indicates that the table creation operation has been completed successfully.

So far, you have gotten started with the use of LitePal. Here is the content of this article. In the next article, we will learn how to use LitePal to upgrade tables.