MybatisPlus多表查询及分页查询

1.新建两个表，t_user和t_order

t_user如下

t_order如下

2.有两个实例类，用户(User)和订单(Order)

User如下

package com.example.mdbdemo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import java.util.List;



@TableName("t_user")
public class User {
    @TableId(type = IdType.AUTO)
    private int id;
    private String username;
    private String password;
    private String birthday;

    @TableField(exist = false)
    private List<Order> orders;



    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", birthday='" + birthday + '\'' +
                '}';
    }
}

Order如下

package com.example.mdbdemo.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName("t_order")
public class Order {
    private int id;
    private String ordertime;
    private double total;
    @TableField(exist = false)
    private User user;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getOrdertime() {
        return ordertime;
    }

    public void setOrdertime(String ordertime) {
        this.ordertime = ordertime;
    }

    public double getTotal() {
        return total;
    }

    public void setTotal(double total) {
        this.total = total;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }


}

 3.两个接口，UserMapper和OrderMapper

UserMapper如下

package com.example.mdbdemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mdbdemo.entity.User;
import org.apache.ibatis.annotations.*;

import java.util.List;


@Mapper
public interface UserMapper extends BaseMapper<User> {
//    查询用户，根据用户id查询信息select * from user where id=
    @Select("select * from t_user where id = #{id}")
    User selectById(int id);



    @Select("select * from t_user")
    @Results(
            {
                    @Result(column = "id",property ="id"),
                    @Result(column = "username",property="username"),
                    @Result(column = "password",property="password"),
                    @Result(column = "birthday",property ="birthday"),
                    @Result(column = "id",property = "orders",javaType = List.class,
                            many=@Many(select ="com.example.mdbdemo.mapper.OrderMapper.selectByUid")
                    )

            }
    )
    List<User> selectAllUserAndOrders();

}

OrderMapper如下

 

package com.example.mdbdemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mdbdemo.entity.User;
import org.apache.ibatis.annotations.*;
import com.example.mdbdemo.entity.Order;

import java.util.List;
@Mapper
public interface OrderMapper extends BaseMapper {
    @Select("select * from t_order where uid = #{uid}")
    List<Order> selectByUid(int uid);

    @Select("select * from t_order")
    @Results(
            {
                    @Result(column = "id",property ="id"),
                    @Result(column = "ordertime",property="ordertime"),
                    @Result(column = "total",property="total"),
                    @Result(column = "uid",property = "user",javaType = User.class,
                            one=@One(select ="com.example.mdbdemo.mapper.UserMapper.selectById")
                    )

            }
    )
    List<Order> selectAllOrdersAndUser();
}

4.两个控制器UserController和OrderController

UserController如下

package com.example.mdbdemo.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.mdbdemo.entity.User;
import com.example.mdbdemo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("/user/findAll")
    public List<User> find(){ return userMapper.selectAllUserAndOrders();}


    }

 OrderController如下

package com.example.mdbdemo.controller;

import com.example.mdbdemo.entity.Order;
import com.example.mdbdemo.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class OrderController {

    @Autowired
    private OrderMapper orderMapper;

    @GetMapping("/order/findAll")
    public List findAll(){

        List orders = orderMapper.selectAllOrdersAndUser();
        return orders;

    }
}

5. 运行，看结果

输入localhost:8080/user/findAll，查看每个用户的订单

 输入localhost:8080/order/findAll，查看每个订单的用户

6.条件查询

把下面代码写入UserController

//    条件查询
    @GetMapping("/user/find")
    public List<User> findByCond() {
        QueryWrapper<User> queryWrapper = new QueryWrapper();
        queryWrapper.eq("username","zhangsan");
        return userMapper.selectList(queryWrapper);
    }

运行，输入localhost:8080/user/find，查找到用户名为zhangsan的用户

 7.分页查询

先做一个配置类MyBatisPlusConfig

package com.example.mdbdemo.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
        interceptor.addInnerInterceptor(paginationInterceptor);
        return interceptor;
    }
}

把下面代码写入UserController

//    分表查询
    @GetMapping("/user/findByPage")
    public IPage findByPage() {
        Page<User> page = new Page<>(0, 2);
        IPage iPage = userMapper.selectPage(page, null);
        return iPage;
    }

运行 http://localhost:8080/user/findByPage，得到分页查询结果

第一页的两个结果