In the last article we learned some Android database-related basics 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 program, it is not meaningful to separate a database to conduct the deletion operation, so today we will learn how to operate SQLite database in the Android program. Haven’t read the previous article friends can first go to the Android database master secret (a) – 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 that uses the object Relational Mapping (ORM) model and encapsulates some of the most commonly used database functions. Make do not need to write a line of SQL statements can be completed all kinds of table building, deletion, change, check operations. LitePal is also “light”, with jar packages of less than 100K and near zero configuration, which is quite different from frameworks like Hibernate. LitePal 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.

The traditional way to build a table

In order to facilitate the management of database tables, Android provides a helper class: SQLiteOpenHelper. This class creates and upgrades the database all in one, and automatically manages the database version, is a very useful tool.

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 it. SQLiteOpenHelper has two abstract methods, onCreate() and onUpgrade(), which we must override in our help class and implement the logic to create and upgrade the database in these methods. This article will just focus on creating the database, which will be discussed in the next article.

Create a new MySQLiteHelper class and let it inherit SQLiteOpenHelper. The code for a basic database helper class looks like this:

public class MySQLiteHelper extends SQLiteOpenHelper {
 
	public MySQLiteHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
	}
 
	@Override
	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 wanted to create a new news table with the title, Content, PublishDate, commentCount columns representing the news title, news content, publication date, and number of comments, the code would look 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)"; public MySQLiteHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_NEWS); }... }Copy the code

As you can see, we define the table builder as a constant, and then execute the table builder in the onCreate() method to create the news table. This construction sentence is simple, but it contains some small details. Let me explain. First, the database paradigm dictates that any table should have a primary key, so here we add a self-growing ID column and make it the primary key. Then the title and Content columns are strings, and the commentcount column is an integer, which makes sense, but what about the publishdate column? Since storing dates is not supported in SQLite, we need to convert the date to milliseconds of UTC time (since zero on 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 get an instance of SQLiteDatabase and the database table will be created automatically, 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? It’s too easy to be satisfied, so let’s take a look at the basic uses of LitePal and see how the same functionality can be achieved using the framework.

The basic usage of LitePal

Although LitePal claims to be near zero configuration, it is only “near”, but it still needs some simple configuration to work, so let’s start with a quick study of LitePal configuration methods.

Rapid configuration

1. Import Jar packages 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. Once you have downloaded the JAR package, copy it to your project’s liBS directory to count it as a successful import, as shown below:

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

2. Configuration litepal. XML

Then create a new litepal. XML file under assets of your project and copy the following code into it:

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

The configuration file is pretty simple, for setting the name of the database, for setting the version number of the database, for setting all the mapping models, which we’ll use later.

3. The configuration LitePalApplication

Since we need to use the Context to operate on the database, we obviously don’t want to pass this parameter through every interface, which would be too tedious to operate on the database. Therefore, LitePal uses a method to simplify the Context argument by simply configuring LitePalApplication in androidmanifest.xml, and all database operations do not need to pass the Context, as shown below:

<manifest> <application android:name="org.litepal.LitePalApplication" ... >... </application> </manifest>Copy the code

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

<manifest> <application android:name="com.example.MyApplication" ... >... </application> </manifest>Copy the code

It doesn’t matter, just change the MyApplication inheritance structure so that it doesn’t inherit directly from The Application class, but from the LitePalApplication class, and everything will work fine, as shown below:

public class MyApplication extends LitePalApplication {
    ...
}
Copy the code

However, some programs may encounter more extreme cases, such as MyApplication needing to inherit another AnotherApplication that is still in a JAR and cannot modify its code. It’s probably rare, but don’t worry if you do, there’s still a solution. You can download the LitePal source code, copy all the SRC code directly into your project’s SRC directory, and then open the LitePalApplication class and change its inheritance structure to inherit from AnotherApplication. Let MyApplication inherit from LitePalApplication so that all applications work together.

In just three steps, we did all the configuration work, and it was a very profitable thing to do. From there, you can have fun with all the conveniences LitePal has to offer, starting with building a table.

Start building table

LitePal uses the object relational mapping (ORM) model. What is object relational mapping? To put it simply, the programming language we use is object-oriented language, and the database we use is relational database, so the establishment of a mapping between object-oriented language and relational database, this is 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 the DATABASE SQL language is difficult to understand, even if you are familiar with it, I am afraid that you do not like to write it in code often? Object-relational mapping (ORM) solves this problem by allowing us to manipulate databases in an object-oriented way, freeing us from the arcane SQL language.

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

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

However, each column in the table corresponds to a field in the model class. For example, the ID, title, Content, PublishDate, and CommentCount columns in the News table should also have these columns in the news class, as follows:

public class News { private int id; private String title; private String content; private Date publishDate; private int commentCount; // automatically generate get, set methods... }Copy the code

LitePal will automatically create 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, LitePal does all of its mapping automatically. According to LitePal’s data type support, there are eight data types that can be used for object relational mapping: int, Short, Long, float, double, Boolean, String, and Date. Fields declared as these eight data types are automatically mapped to database tables without any additional configuration.

Now that the model class is set up, one final step is to configure it into the mapping list. Edit the litepal. XML file in assets and add the declaration of the News model class to the tag:

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

Be sure to fill in the full class name of the News class.

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

SQLiteDatabase db = Connector.getDatabase();
Copy the code

By calling the above code, the NEWS table should have been created successfully. Let’s use the SQLite command we learned in the last article to take a look. Open the demo.db database and enter the.table command. The result looks like the following:

As you can see, the NEWS table already exists. The other two tables, Android_metadata and table_SCHEMA, are automatically generated. Next, we can also query the construction clause of the news table, as shown in the following figure:

This is the table building clause that LitePal automatically generates for us based on the fields in the News class, and thus indicates that the table building operation has completed successfully.

Now that you’ve got a bit of a primer on LitePal, that’s it for this article. In the next article, we’ll learn how to upgrade tables using LitePal.

Pay attention to my technical public account “Guo Lin”, there are high-quality technical articles pushed every day.