Android Database Master cheats (iv)--use Litepal to establish table Association

Reprint Please specify source: http://blog.csdn.net/guolin_blog/article/details/39207945

Now that we have a certain understanding of the usage of Litepal and learned how to use Litepal to create tables and upgrade tables, let's move on to the advanced level today and explore how to use Litepal to establish the association between tables. Friends who have not read the previous article suggested first refer to the Android database Master cheats (iii)--Using the Litepal upgrade table .

Litepal's project address is: Https://github.com/LitePalFramework/LitePal

The basic knowledge of association relationships

Programmers who like to write all the code in a class must be a novice. Yes, no decent program can be just a single class, and similarly, no decent database can have just one table. As we all know, in an object-oriented programming language, multiple classes can relate references to each other to accomplish a function. So in a database, can multiple tables be interconnected? Of course! But the relationship between table and table is more complicated and more difficult to understand than the relation between objects, but as the basic of database, we should understand clearly, then we should learn the basic knowledge of database table Association first.

There are three types of association between table and table, one-to-one, many pairs, and gears, we discuss these three types respectively.

One

Indicates that the data in two tables must be one by one corresponding to the relationship. This kind of scenario is not very common, and we use the example to intuitively understand that the example is still based on the previous article.

Now that we've created the news table, which mainly records the headlines and the content, some of the news may have some lead and summaries in addition to the title and content, and we put the two fields in a introduction table as a brief introduction to the news. Then obviously, news and introduction tables are a one-to-two relationship, because a news can only correspond to a profile, a profile can only belong to a news. The correspondence between them is probably the same as described:

As you can see, the News1 corresponds to the introduction2,news2 corresponding to the introduction3,news3 corresponding to the Introduction1, but anyway, they are all one-to-one relationships.

So how does this one-to-one relationship show up in programming languages? Believe that you are familiar with object-oriented design, it must be easy to think about it, you just have to 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 that they are naturally one-to-one relationships.

Yes, the one-to-one relationship between objects is very simple and easy to understand, so the difficulty is how to establish such a one-to-one relationship in a database table. Because databases do not support mutual references like object-oriented languages, if you want to create a one-to-two relationship between tables, you can generally only do so by means of a foreign key. Therefore, the table structure of a one-to-one relationship can be designed like this:

Note that there is a news_id column in the introduction table, which is a foreign key column, which should contain a specific news ID, so that a introduction can correspond to one news and one-to-two relationships, as shown in:

From this we can see that the ID of 1 introduction corresponds to the ID 2 news,id 2 of the introduction corresponding to the ID 3 of the News,id 3 introduction corresponding to the ID 1 of the news. It is important to note that one-to-one relationships do not require a foreign key to be added to the table, you can add a news_id as a foreign key in the introduction table, or you can add a introduction_id as a foreign key in the news table, regardless of which one is used, Can indicate that they are related to one to the other.

Many-to-one

Indicates that the data in one table can correspond to more than one data in another table. This scenario is much more common than one-to-ones relationships, and it is true that many pairs of relationships are ubiquitous in our usual development work. For example, now we have a news list in our database, and a comment table, which is a typical many-to-one relationship between the two, a piece of news can have a lot of comments, but a comment can only belong to a piece of news. Their relationship is as follows:

And this many-to-one relationship in the programming language is very easy to reflect, such as Java has a special collection class, such as list, set, and so on, using them can easily create a multi-to-one relationship between objects, we will see later. The difficulty here, then, is still how to establish such a many-to-one relationship in a database table. Now it is not difficult to say the difficulty, because we have learned the first-to-one relationship of the establishment of methods, and many of the same is similar. Yes, many-to-one relationships in database tables are still created by foreign keys, but it is possible to add a foreign key to a table on a one-to-one list, but the key must be added to the table in many ways. Therefore, the table structure of a many-to-one relationship can be designed like this:

In the comment table there is a news_id column, which is a foreign key column, which should contain a specific news ID, and allow multiple comment to store the same news ID, so that a comment can only correspond to one news, but a news can have a number of comments, It also achieves a many-to-one relationship, as shown in:

Thus we can see that the ID 1, 2, 3 of the three comments belong to the first news, and the ID 4, 5 of the two comments belong to the second piece of news.

Many-to-many

Indicates that data from two associated tables can correspond to more than one data in another table. This is not a very common scenario, but it's a little more common than a one-to-one relationship. For example, we all know that news sites will classify the news, so that users can choose their favorite type of news to browse, such as NetEase News will have headlines, technology, entertainment, mobile phones and so on. There will certainly be a lot of news under each category, and a piece of news may belong to a variety of categories, such as the IPhone6 release of the news can belong to the mobile phone type, but also belong to the science and technology category, and even can make headlines. Therefore, there is a many-to-many relationship between news and categories, as shown in:

As you can see, News1 belongs to Category1, and News2 and NEWS3 are both Category1 and Category2, so how do you express such a complex relationship? Everything is so simple in object-oriented programming languages that you need to have multiple categories using the collection class declaration in the news class, and then use the collection class declaration in the category class to have multiple news, as we'll see later. And the difficulty is still left in the database, how to establish a multi-to-many relationship between the two tables, or use foreign keys? Certainly not, many-to-many cases can only be done with the help of the intermediate table. In other words, we need to create a table that has nothing else to do with the relationship between the news table and the category table, as shown in:

Note that here we create an intermediate table named Category_news, where the name of the intermediate table is not mandatory, but a good naming convention can give you a glimpse of what this table is for. There are only two columns in the middle table, and there are only two columns, the foreign key of the news table and the outer key of the category table, where the corresponding IDs of the news and categories are stored, so that they can establish an association between them, as shown in:

From this we can see that the first piece of news belongs to the first category, and the second and third news, which belong to the first category and the second category. In turn, it can be seen that the first category under the first, second, third of the three news, and the second category below only the second and third of the two news. In any case, many-to-many relationships are established.

Well, three relationships are over, so let's summarize them briefly. Although the above introduced a lot of space to explain the database table related knowledge, but in fact, the final conclusion is very simple, we can be as a formula as a back down. that is, the implementation of one-to-many correlation is the use of foreign keys, many of the implementation of an association is also using foreign keys, many-to-multi-association implementation is using the intermediate table . Write down this formula, in a lot of database design, you can play more easily.

Using Litepal to establish table associations

Although the formula is this way, but it is related to the table associated with the increase in the difficulty of building the table, the statement will be more complex, you also need to be extra careful to prevent errors. Therefore, using Litepal to create a table association is a very good choice, we do not need to care about what foreign keys, intermediate tables and other implementation of the details, only need to declare in the object of their reference relationship between each other, Litepal will automatically set up the corresponding relationship between the database tables, Let's try it out here.

First determine what entity classes, news and comment are involved, which we have already built in the first two articles, and then need to have introduction and category two classes, the new introduction class, as shown in the code below:

public class Introduction {private int id;private string Guide;private string digest;//automatically generate get, set method}

Then create a new category class with the code as follows:

public class Category {private int id;private String name;//automatically generate get, set method}

Now that four classes have been built, but now they are all independent and not connected to each other, we are now starting to relate them in a very straightforward way. First of all, news and introduction are a one-to-one relationship, so you can add the following references to the news class:

public class News {... private Introduction introduction;//auto-Generate GET, set method}

It is that simple, in the news class can get a corresponding instance of introduction, then they are a one-on relationship.

Then comment and news are many-to-one relationships, so news should contain multiple comment, and comment should have only one news, so you can write:

public class News {... private Introduction introduction;private list<comment> commentlist = new arraylist< Comment> ();//auto-Generate GET, set method}

Use a generic Comment list collection to represent the news with multiple comment, and then modify the code for the comment class as follows:

public class Comment {... private News news;//automatically generate get, set method}

An instance of news is declared in the comment class, which clearly shows that news can contain more than one comment, while comment can have only one news, which is a many-to-one relationship.

The last news and category are many-to-many relationships, and I believe you must already know how to write the smart. News can contain multiple category, so you should still use the list collection to represent:

public class News {... private Introduction introduction;private list<comment> commentlist = new arraylist< Comment> ();p rivate list<category> categorylist = new arraylist<category> ();//auto-Generate GET, set method}

The category can also contain multiple news, so the category class should also use the same notation, as follows:

public class Category {... private list<news> newslist = new arraylist<news> ();//auto-Generate GET, set method}

This clearly expresses the many-to-many correlations between them.
Once the correlation has been declared, we just need to add all the entity classes to the mapping list and the database version number to 1. Modify the code for the Litepal.xml as follows:

<?xml version= "1.0" encoding= "Utf-8"?><litepal> <dbname    value= "Demo" ></dbname>    <version value= "4" ></version>    <list>        <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>    </list></litepal>

Basically here it is easy to say the end, now only need to arbitrarily manipulate the database, the relationship between the tables will be automatically established, for example, call the Connector.getdatabase () method.

Let's verify this, enter the. Table command to see the tables in the current database as follows:

Ok,news, Comment, category, introduction These tables all have, in addition there is a category_news intermediate table. Let's check the structure of the introduction table first, as shown below:

As you can see, there is a news_id column that shows that the one-to-many relationship between the introduction table and the news table has been established.

Then look at the structure of the comment table as follows:

There is also a news_id column in the Ok,comment table, 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 table, as follows:

There are only two columns, one is news_id, one is category_id, and the other is the foreign key for two tables, so that the many-to-many relationships between the news table and the category table are established.

With the help of Litepal, even if you are not familiar with the database's table association design, you can easily establish the association between tables and tables as long as you can object-oriented programming. CREATE TABLE, upgrade table, table Association, this is litepal in the database table management has brought us great convenience, I believe everyone can appreciate its charm. So far we've learned all about using litepal for table management, and from the next article I'll explain how to use Litepal for CRUD operations.

Android Database Master cheats (iv)--use Litepal to establish table Association