In the last article, we learned some basics about Android databases and a few useful SQLite commands, all directly from the command line.

preface

In the last article, we learned some basics about Android databases and a few useful SQLite commands, all directly from the command line. But as we all know, the database is to be used together with the application, it does not make sense to carry out deletion, change and check operation on a database alone, so today we will learn how to operate the SQLite database in the Android application. Haven’t read the previous article friends can first refer to the Android database master secret (1) – SQLite command.

Operation database, of course, the first step is to create the table, the traditional method of create table believe most people know, so today I will show in addition to the traditional building table method, will explain the basic usage of LitePal this framework and use it to complete the same table operation, let everybody experience using the framework to the charm of the database operation.

LitePal is an open source Android database framework, it uses the object relational mapping (ORM) model, and we usually develop some of the most commonly used database functions are encapsulated, Without writing a row of SQL statements, you can complete all kinds of table building, deletion, change and query operations. LitePal is also “light”, with jar packages of less than 100K and near zero configuration, which is quite different from frameworks like Hibernate. The LitePal source code is currently hosted on GitHub at github.com/LitePalFram… .

OK, with LitePal out of the way, let’s take a look at how tables are created in traditional Android development.

Traditional table building method

In fact, in order to facilitate the management of database tables, Android itself provides a helper class: SQLiteOpenHelper. This set of classes is a very useful tool for creating and upgrading databases, and for automatically managing database versions.

Let’s try SQLiteOpenHelper now. The first thing you need to know is that SQLiteOpenHelper is an abstract class, which means that if we want to use it, we need to create our own helper class to inherit from it. SQLiteOpenHelper has two abstract methods, onCreate() and onUpgrade(), which we must override in our helper classes and then implement the logic to create and upgrade the database in each method. This article will focus on creating the database, which we will discuss in the next article.

Create a new MySQLiteHelper class and let it inherit from SQLiteOpenHelper. A basic database helper class looks like this:

public class MySQLiteHelper extends SQLiteOpenHelper {

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

super(context, name, factory, version);

public void onCreate(SQLiteDatabase db) {

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

The onCreate() method is called when the database is created. For example, if we want to create a new news table with the title, content, publishDate, and CommentCount columns representing the news title, news content, publication time, and number of comments, the code would say:

public class MySQLiteHelper extends SQLiteOpenHelper {

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

			+ "id integer primary key autoincrement, "

			+ "commentcount integer)";

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

super(context, name, factory, version);

public void onCreate(SQLiteDatabase db) {
Copy the code

As you can see, we define the constructor as a constant and execute the constructor in the onCreate() method to create the news table. This sentence is simple, but it contains some small details. Let me explain. First, the database paradigm requires that every table should have a primary key, so here we add a self-growing ID column and set it to the primary key. And then the title column and the Content column are both strings, and the CommentCount column is an integer, which makes sense, but what about the publishDate column? Since the date data type is not supported in SQLite, we need to convert the date to the number of milliseconds in UTC time (zero since January 1, 1970) before storing it in the database, so the publishDate column should also be an integer.

Now, all we need to do is retrieve 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();
Copy the code

Feels so simple and convenient, doesn’t it? Let’s take a look at the basics of LitePal and see how the same functionality can be achieved using this framework.

Basic use of LitePal

LitePal claims to be near zero configuration, but it’s only “near.” It still requires a few simple configurations to use, so let’s take a quick look at how LitePal is configured.

Rapid configuration

1. Import the Jar package or source code

First we need to introduce the LitePal JAR package into the project, you can click here to view the latest version of LitePal, select the download you need. After downloading the JAR package, copy it to the project’s libs directory, as shown below:

If you don’t want to use the JAR package, you can download the LitePal source code, import it into Eclipse as a library, and have our project reference the library.

2. Configuration litepal. XML

Next, create a new litepal. XML file in the assets directory of the project and copy the following code into it:

<? The XML version = "1.0" encoding = "utf-8"? > <dbname value="demo" ></dbname> <version value="1" ></version>Copy the code

The configuration file is fairly simple and is used to set the name of the database, to set the version number of the database, and to set all the mapping models that we will use later.

3. The configuration LitePalApplication

Because we need to use Context to manipulate the database, and we obviously don’t want to pass this parameter in every interface, because it would be too tedious to manipulate the database. Therefore, LitePal uses a method to simplify the Context argument. Just configure LitePalApplication in androidmanifest.xml, and all database operations do not need to pass Context, as follows:

android:
Copy the code

Of course, some programs may have their own Application, which is configured here. Let’s say we have a MyApplication that looks like this:

android:
Copy the code

MyApplication inherits LitePalApplication instead of Application. MyApplication inherits LitePalApplication instead of Application.

public class MyApplication extends LitePalApplication {
Copy the code

However, some programs may encounter more extreme situations, such as MyApplication inheriting another AnotherApplication, which is still in a JAR package and cannot be modified. This may be a rare case, but don’t worry if you do, there are ways to explain it. You can download the LitePal source code and copy all the code from the SRC directory directly to the SRC directory of your project. Then open the LitePalApplication class and change its inheritance structure from AnotherApplication. Let MyApplication inherit from LitePalApplication so that all applications work together.

In just three steps, we got all the configuration work done, and it was a big deal, and from there, you can enjoy all the conveniences that LitePal has to offer. Let’s start with building tables.

Start building table

As mentioned earlier in the introduction, LitePal takes an object relational mapping (ORM) model, so what is an object relational mapping? To put it simply, the programming language we use is object-oriented, and the database we use is relational, so the mapping between the object-oriented language and relational database is called object-relational mapping.

But why do we use the object-relational mapping pattern? This is mainly because most programmers are good at object-oriented programming, but only a few of them are good at relational databases. And database SQL language is obscure, even if you are proficient in it, I am afraid you do not like to write it in the code often? The object-relational mapping model solves this problem by allowing us to manipulate databases in an object-oriented manner, freeing us from the complexities of the SQL language.

So let’s take a look at how tables are created in LitePal. According to the concept of the object-relational mapping schema, every table should correspond to a Model, that is, if we want to build a news table, we should have a corresponding news Model class. Create a new News class as follows:

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

Then, each column in the table actually corresponds to a field in the model class. For example, if there are id, title, Content, publishdate, and CommentCount columns in the news table, then there should also be these fields in the news class. The code is as follows:

private Date publishDate;

private int commentCount;
Copy the code

LitePal will automatically generate an ID column in the table. After all, every table must have a primary key.

In particular, LitePal’s mapping rules are very lightweight. Unlike some other database frameworks, which require a separate XML mapping for each model class, all of LitePal’s mapping is done automatically. Based on LitePal’s data type support, there are eight data types for object-relational mapping: int, short, long, float, double, Boolean, String, and Date. Any field declared as one of the eight data types is automatically mapped to the database table without any additional configuration.

Now that the model class is set up, the last step is to configure it to the map list. Edit the litepal. XML file in assets and add the News model class declaration to the tag:

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

Note that you must fill in the full class name of the News class.

OK, so all the work is done, now whenever you do anything to the database, the news table will be automatically created. For example, LitePal provides a convenient way to get an instance of SQLiteDatabase as follows:

SQLiteDatabase db = Connector.getDatabase();

Copy the code

Call the above code and the news table should have been created. Let’s use the SQLite command we learned in the last article to check it out. Open the demo.db database and enter the.table command. The result looks like this:

As you can see, the news table already exists. The other two tables, Android_METADATA and table_Schema, are automatically generated, so we’ll ignore them. Next, we can query the construction sentence of the news table again, as shown in the figure below:

This is what LitePal automatically generates for us based on the fields in the News class, which indicates that the table operation has completed successfully.

So far, you’ve gotten a little started on LitePal, so that’s it for this article. In the next article, we’ll learn how to upgrade tables using LitePal.