MybatisPlus provides us with a powerful method of adding, deleting, modifying and querying a single table, and adds a paging plug-in to automatically realize physical paging, which is very easy to use However, in practice, the addition, deletion, modification and query of a single table is obviously not enough. If multi table operation is added, you also want to use the provided condition constructor and paging plug-in. How can you do it?

1.mapper interface

public interface ServeMapper extends BaseMapper<Serve> {
    IPage<Serve> getServes( IPage<Serve> page, @Param(Constants.WRAPPER) Wrapper<Serve> queryWrapper );
}

1). Set the return parameter to iPage < Object >

Where Object is the self-defined entity class or the corresponding vo class. Mybatis plus will automatically bind and assign values according to the parameter name of the query column and the vo class;  

The IPage table name returns the class of the paging plug-in of type mybatisplus, including paging information such as records, current and limit

2). Parameters pass the paging object IPage, and the conditional constructor Wrapper

It should be noted that paging parameters should be placed in front of condition constructor parameters, otherwise inexplicable errors will occur@ Param(Constant.WRAPPER) annotation is required

I don't know whether generics in Wrapper are needed for the time being. I hope someone can explain it

2. Write sql from XML file

<select id="getServes" resultType="com.kjfw.shannxi.entity.Serve">
     select s.id , s.name , s.content, s.score , i.name from
        serve s left join institution i on s.institutionId = i.id
     ${ew.customSqlSegment}
</select>

1). The ID should correspond to the interface

2). Add ${ew.customSqlSegment} at the end

3). If you use a paging plug-in, don't add a limit statement

4). Aliasing a table allows you to conditionally restrict another table in the condition constructor

3. Test

@Test
    public void testPage(){
        QueryWrapper<Serve> serveQueryWrapper = new QueryWrapper<>();
        serveQueryWrapper.like("s.name" , "testing");
        serveQueryWrapper.ge("s.score",4);
        serveQueryWrapper.eq("i.id","2");
        Page<Serve> servePage = new Page<>(1,5);
        IPage<Serve> serves = serveMapper.getServes( servePage , serveQueryWrapper);
        // Get total
        serves.getTotal();
        // Get pages
        serves.getPages();
        // Get paged records
        List<Serve> records = serves.getRecords();
        System.out.println(records);
    }

1). new is a conditional constructor

2). Add a condition for the condition constructor, (you can add conditions for other tables)

3). new is a paging object. The generic type is a paging record object

4). Set the parameters of paging object (current page, paging size)

5). Execute the mapper method and pass in the paging object and the conditional constructor respectively (again, the paging object should be before the conditional constructor object)

4. Result analysis

==>  Preparing: SELECT COUNT(1) FROM serve s LEFT JOIN institution i ON s.institutionId = i.id WHERE (s.name LIKE ? AND s.score >= ? AND i.id = ?) 
==> Parameters: %testing%(String), 4(Integer), 2(String)
<==    Columns: COUNT(1)
<==        Row: 1
==>  Preparing: select s.id , s.name , s.content, s.score , i.name from serve s left join institution i on s.institutionId = i.id WHERE (s.name LIKE ? AND s.score >= ? AND i.id = ?) LIMIT ?,? 
==> Parameters: %testing%(String), 4(Integer), 2(String), 0(Long), 5(Long)
<==    Columns: id, name, content, score, name
<==        Row: 4238048, Ultra high temperature engine material inspection, <<BLOB>>, 5, Shaanxi Key Laboratory of new energy development, application and automobile energy saving
<==      Total: 1

1). The first Preparing is a query statement that queries the total number of records that meet the conditions by paging conditions

It can be seen that the wrapper condition constructor, eq(), ge() and other methods are interpreted as specific sql statements and spliced at the end of the sql statement

2). The first preparation is to query the specific records that meet the conditions, and put the paging conditions at the end to realize paging

3). Finally, mybatisplus will bind the returned result to the generic type of Page object and return it in records