Multi table join query and query result paging are implemented in mybatis framework of Java

Realize multi table joint query

Still on David mybatis. Create a new website class under the model package to persist data. Rewrite the corresponding toString () method to facilitate the test program.

In David mybatis. Under demo, create corresponding operation interfaces respectively:

Create a new websitemapper under the mapper folder XML Mapping file, respectively refer to the single table operation configuration of addition, deletion, modification and query mentioned in the previous one, so that you can build some test data. as follows

Today, we mainly talk about the query. Now we want to query the website and take out the corresponding visitor information together. How to do it? You can refer to the query in the configuration and write the SQL of the joint table query,

The main thing to note here is that the ID and name attributes of the website entity and the visit entity are the same. Therefore, in order to avoid mapping errors, list the corresponding query results with different aliases, so that binding can be avoided.

What would I get if I configured it like this?

You have noticed that the ID of the visitor has also changed to 2. In fact, it maps the ID of the website by default, because the two IDS in the query result of the SQL statement have changed to 2. Some people will ask why it is not 4, because it matches the first one by default. If you put the website ID and visit If you change the position of ID, you will find that the result has magically changed again

So you need to use an alias to avoid this situation, so you will find that there is only one truth, which is the following:

You can see that in fact, the processing method of multi table resultmap is the same as that of single table. It is nothing more than listing the correspondence with the JavaBean attribute name. You can see another resultmap in the foreground in the < resultmap > node of website, which represents the entity that needs to be mapped by the visit entity. You can associate it in the following ways

The visitor is the name of the visit field in the website entity. The name must be consistent, or there is no getter for property named 'xxx' in 'class David will be thrown mybatis. model. The exception of 'website' has been described in the previous chapters. Of course, if you think it is OK not to nest resultmap, and nesting is also because this configuration can be used in other places, it is a refined process and an abstract idea. You can find the corresponding differences from the official website by using the ID and result in < resultmap >: http://mybatis.github.io/mybatis-3/sqlmap-xml.html#Result_Maps

In this way, a simple multi table joint query will come out ~. If there are more complex queries, the business cost will be modified on this basis.

Paging effect logic the following is about the paging problem we often encounter in a business problem. When developing web projects, we often use list display. Generally, we use some common list controls, such as DataTables (I feel very good), and the encapsulated table controls under easy UI.

Idea: to achieve the paging effect in these controls, two parameters are generally passed. The first is the index of the current page (generally starting from 0), the second is the number of business records displayed on the current page, and then the corresponding parameters are passed to the list < T > GetList (pagenateags args) method, When we finally realize paging in the database, we can use the limit keyword (for MySQL) for paging. If it is Oracle or SQL server, they all have their own rownum function to use.

For the above ideas, first of all, we need to continue in the demo mybatis. Under model, create a paging parameter entity class named pagenateags and an enumeration class named sortdirectionenum, which contains the current page index PageIndex, the current page shows the number of business records, PageSize, and the pagestart property indicates the starting point, (pagestart = PageIndex * PageSize) because the keyword limit is used to indicate [limit start number (excluding), how many to take], orderfieldstr sorting field and orderdirectionstr sorting direction, it is created as follows: package david.mybatis.model;

After completing the above steps, we continue to add a method public list < visitor > getlistbypagenate (pagenateags args) to the ivistoroperation interface class. In the previous chapters, we actually have a GetList method. This paging is actually a little changed on this basis. After the ivistoroperation interface class is changed, it is as follows:

Next, we will start to change our visitormapper XML configuration file. Add a < Select > node ID and parameter type. Refer to the previous chapters for configuration. The new ID here is getlistbypagenate. After configuration, it is as follows

You will find that there is a similar configuration in the figure below. The field properties in this configuration are consistent with the property names in the pagenateags parameter class.

Create a test method in the demorun class:

After running, the test results are arranged in reverse order by ID. there are 14 records in the visitor table,

Suppose we take 5 items on page 2 and execute the following 6-10 items of data. In this way, we can transfer parameters

The results are as follows:

In this way, I have realized a paging logic ~ ^ 0 ^. What I need to pay attention to here is that I haven't made any judgment on the orderfieldstr field. In theory, I need to deal with it to prevent the wrong column name from being passed in. However, there should be ready-made packaged things on the Internet. You can also go to Google. Here is just an idea to demonstrate how to use mybatis paging.

After completing this, because it is mysql, it does not bring its own rownum sequence ID in the query results, so it may not be obvious when viewing the test data. It is not Zao urgent. We can transform the above method by ourselves, As like as two peas, I'll create a new VisitorWithRn entity in model package, which will bring a rownum parameter to RownumID.

Create a new method named public list < visitorwithrn > getlistbypagenatewithrn (pagenateags args) in ivistoroperation. Similarly, we need to configure the corresponding < Select > node and script in visitormapper. The only difference here is to change the SQL script, as follows:

The rest is to add the test method under demorun just now. There is no mapping here. After completion, you can see that the just 6-10 data will become as follows