preface

Now that we know how to use LitePal and how to use LitePal to create and upgrade tables, let’s take it a step further and explore how to use LitePal to establish relationships between tables. For those who haven’t read the previous article, it’s recommended to refer to Android Database Master Tip # 3 — Upgrade tables with LitePal.

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

The basics of association

A programmer who likes to write all of his code in one class must be a novice. Yes, no decent program can have only one class, and no decent database can have only one table. As we all know, in object-oriented programming languages, multiple classes can refer to each other to accomplish certain functions together. Can multiple tables be associated with each other in a database? Of course! But the relationship between tables is more complicated than the relationship between objects, and it is more difficult to understand, but as the basic skills of the database, or should understand clearly, so we will first learn the basic knowledge of database table association.

There are three types of relationship between tables: one-to-one, many-to-one, and many-to-many. The following three types are discussed separately.

One to one

Indicates that the data in the two tables must be in a one-to-one correspondence. This is not a very common scenario, but let’s try to visualize it through an example, which is still based on the previous article.

Now we have created the table news, which mainly records the title and content of the news. In addition to the title and content, some news may also have some introduction and summary. We put these two fields in an introduction table as the introduction of the news. So obviously, the relationship between news table and introduction table is one-to-one, because a news can only correspond to a profile, and a profile can only belong to a news. The corresponding relationship between them is roughly as described in the following figure:

As you can see, News1 corresponded to Introduction2, News2 corresponded to Introduction3, News3 corresponded to Introduction1, but in any case, they all had a one-to-one relationship.

So how does this one-to-one relationship work in a programming language? If you are familiar with object-oriented design, you can easily figure it out. All you need to do is hold a reference to the Introduction class in the News class, and then hold a reference to the News class in the Introduction class, so there is a one-to-one relationship between them.

Yes, the one-to-one relationship between objects is straightforward, but the challenge is to establish such a one-to-one relationship in a database table. Since databases do not support mutual reference as object-oriented languages do, a one-to-one relationship between two tables is usually achieved through a foreign key. Therefore, the table structure for one-to-one relationships can be designed like this:

Please note that the introduction table has a news_ID column, which is a foreign key column. It should store a specific news ID, so that one introduction can correspond to one news, and a one-to-one relationship can be achieved, as shown in the figure below:

From this, we can see that the introduction with ID 1 corresponds to the news with ID 2, and the introduction with ID 2 corresponds to the news with ID 3. The introduction with ID 3 corresponds to the news with ID 1. Note that the one-to-one relationship does not impose a foreign key on a table. You can use a news_id in the introduction table, or a news_id in the news table, whichever one you use. You can show that they’re related one to one.

For one more

Indicates that data in one table can correspond to multiple data in another table. This scenario is much more common than one-to-one relationships, and we see many one-to-one relationships everywhere in our development work. For example, there is a news table and a comment table in our database, which is a typical many-to-one relationship. A news can have many comments, but one comment can only belong to one news. Their relationship is shown in the following figure:

This many-to-one relationship is very easy to show in programming languages. For example, Java has special collection classes, such as List, Set, etc., which make it easy to establish many-to-one relationships between objects, as we’ll see later. The difficulty here, then, remains how to establish such a many-to-one relationship in a database table. Now, the hard part is not that hard, because we’ve already learned how to build one-to-one relationships, and many-to-one relationships are similar. Yes, many-to-one relationships in database tables are still established using foreign keys, but in many-to-one cases the foreign keys can be added to any table, but in many-to-one cases the key must be added to the multi-party table. Therefore, a many-to-one table structure can be designed like this:

In the comment table, there is a news_ID column, which is a foreign key column. It should store a specific news ID and allow multiple comments to store the same news ID. In this way, a comment can only correspond to one news, but a news can have multiple comments, thus achieving the many-to-one relationship. As shown in the figure below:

From this we can see that the three comments with ID 1, 2 and 3 belong to the first story, while the two comments with ID 4 and 5 belong to the second story.

Many to many

Indicates that data in two associated tables can correspond to multiple data in the other table. This scenario is not very common, but it is slightly more common than a one-to-one relationship. For example, we all know that news sites will classify news into categories, so that users can choose the kind of news they like to browse. For example, netease news will have headlines, technology, entertainment, mobile phone and other categories. Of course, there are many pieces of news under each category, and a news item can fall into multiple categories. For example, the announcement of the iPhone6 can fall into both the phone category and the technology category, and it can even make headlines. Thus, there is a many-to-many relationship between news and categories, as shown in the figure below:

As you can see, News1 is Category1, while News2 and News3 are both Category1 and Category2. How can this complex relationship be represented? In an object-oriented programming language, it’s quite simple to use a collection class in the News class to declare multiple categories, and then use a collection class in the Category class to declare multiple News, as we’ll see later. But the difficulty remains in the database, how to establish a many-to-many relationship between two tables, or using foreign keys? It’s not going to work. Many-to-many is going to have to be done with an intermediate table. In other words, we need to create one more table, which has no other function except to store the association relationship between the news table and the category table, as shown in the figure below:

Note that we have created an intermediate table called category_news. There are no restrictions on the naming of the intermediate table, but a good naming convention will make it clear at a glance what the table is for. There are only two columns in the middle table, one is the foreign key of the news table, the other is the foreign key of the category table, and the other is the foreign key of the category table, and the other is the foreign key of the news table, and the other is the foreign key of the category table.

From this we can see that the first news belongs to the first category, while the second and third news belong to both the first and second categories. The other way to think about it is that there are three stories under the first category, and only two stories under the second category. No matter how you look at it, the many-to-many relationship holds.

All right, so that’s all three associations, so let’s just summarize. Although the above introduction spent a lot of space to explain the database table correlation knowledge, but in fact, the final conclusion is very simple, you can remember it as a formula. The one-to-one association is implemented with foreign keys, the many-to-one association is implemented with foreign keys, and the many-to-many association is implemented with intermediate tables. Memorize this formula, in many database design, you can play more easily.

Use LitePal to establish table associations

This is what the formula looks like, but when it comes to table associations, it makes it more difficult to build the table, it makes the sentence more complex, and you need to be extra careful to avoid mistakes. Therefore, LitePal is a very good choice for automatic table association. We don’t need to worry about the implementation details of foreign keys, intermediate tables, etc. We just need to declare references to each other in the objects, and LitePal will automatically establish the corresponding relationship between the database tables. Let’s try it out.

First of all, we need to determine which entity classes are involved, News and Comment, which have been established in the previous two articles. Then we need Introduction and Category. Create Introduction, and the code is as follows:

public class Introduction {
Copy the code

Next, create a new Category class that looks like this:

Now that all four classes are built, they are independent and have no connection to each other, so let’s start to relate them in a very simple way. First, News and Introduction have a one-to-one relationship, so you can add the following reference to the News class:

private Introduction introduction;
Copy the code

It’s as simple as that. You can get an instance of the corresponding Introduction in the News class, so it’s a one-to-one relationship between them.

Then Comment and News have a many-to-one relationship, so News should contain multiple comments, while Comment should contain only one News, so it can be written as follows:

private Introduction introduction;

private List<Comment> commentList = new ArrayList<Comment>();
Copy the code

Use a List set with the generic type Comment to indicate that News contains multiple comments, then modify the Comment class code as follows:

Declaring an instance of News in the Comment class makes it clear that a News can contain multiple comments, and a Comment can only contain one News, a many-to-one relationship.

Finally, News and Category are many-to-many, so you’re smart enough to know how to write them. News can contain more than one Category, so it should still be represented by the List collection:

private Introduction introduction;

private List<Comment> commentList = new ArrayList<Comment>();

private List<Category> categoryList = new ArrayList<Category>();
Copy the code

A Category can also contain more than one News, so the Category class should be written in the same way, as follows:

private List<News> newsList = new ArrayList<News>();
Copy the code

This makes it clear that there is a many-to-many relationship.

Once the relationship is declared, all we need to do is add all the entity classes to the mapping list and add the database version number by one. Modify the litepal.xml code to look like this:

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

Basically, it’s easy to say that you’re done here. Now you just need to do anything to the database, such as calling the connector-getDatabase () method, and the relationships between the tables will be created automatically.

To verify this, enter the.table command to view the tables in the current database, as follows:

OK, we have all the news, comment, category, and introduction tables, plus a category_news middle table. Let’s look at the structure of the introduction table, as follows:

As you can see, the addition of the news_id column indicates that the one-to-one relationship between the introduction table and the news table is established.

Then check the structure of the comment table again, as follows:

OK, the comment table also has a news_id column, so the many-to-one relationship between the comment table and the news table has been established.

Finally, check the structure of the category_news middle table, which looks like this:

There are only two columns in total, one is news_id and the other is category_id, and they correspond to the foreign keys of the two tables, respectively. In this way, the many-to-many relationship between the news table and category table is established.

With LitePal’s help, even if you are not familiar with database table association design, as long as you have object-oriented programming, you can easily establish table to table association. Create table, upgrade table, table association, this is LitePal in the database table management to bring us great convenience, I believe everyone can understand its charm. So that’s all you need to know about table management using LitePal. Starting with the next article, I’ll show you how to use LitePal for CRUD operations. If you are interested, please read on.