Springboot+MybatisPlus+Mysql 多表查询

基本配置在上一篇博客 CSDN，此文章为小白自学记录。

功能实现：我有两张数据表，分别为 t_user 和t_order，t_user中的用户id是t_order中的外键，表示这个订单属于哪个用户。我想要查询每个用户信息及属于该用户的订单号。

1.项目结构

                                            

2.数据库

t_user表

 t_order表

    

注：不要把表命名为order，否则会疯狂提示语法有误。。。因为order是排序的关键字。这个错误找了很久。。

                              

3.配置文件

application.properties 和pox.xml 同上一篇博客。

4.实体类

1. User

@TableName("t_user")
public class User {
    @TableId(type = IdType.AUTO)
    private int id;
    private String username;
    private String password;
    //描述用户订单
    @TableField(exist = false)    //表名订单属性在user数据表中不存在。
    private List<Order> orders;

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

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

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

    public int getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    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 + '\'' +
                ", orders=" + orders +
                '}';
    }
}

 2.Order

package com.example.mybatisplus.entity;

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

@TableName("t_order")
public class Order {
    private int id;
    @TableField("order_time")       //标记1
    private String ordertime;
    @TableField("total_price")
    private int totalprice;
    private int uid;
    //订单属于的用户
    @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 int getTotalprice() {
        return totalprice;
    }

    public void setTotalprice(int totalprice) {
        this.totalprice = totalprice;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }


    public User getUser() {
        return user;
    }

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

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", ordertime='" + ordertime + '\'' +
                ", total=" + totalprice +
                ", uid=" + uid +
                ", user=" + user +
                '}';
    }
}

5.Mapper

1.UserMapper

@Mapper
public interface UserMapper extends BaseMapper<User> {

    //查询用户及该用户订单 标记2
    @Select("select * from t_user")
    @Results({
            //给每个字段赋值.column代表表的字段，property代表类的字段
           @Result(column="id",property="id"),
           @Result(column="username",property="username"),
           @Result(column="password",property="password"),
            //把id传递给selectByUid就能找到订单号了
            //这里的property=orders，与User类的成员变量orders保持一致
           @Result(column="id",property="orders",javaType = List.class,
                   many = @Many(select = "com.example.mybatisplus.mapper.OrderMapper.selectByUid"))
    })
    List<User> selectAllUserAndOrders();


    //查询用户信息 标记3
    @Select("select * from t_user")
    List<User> find();
}

2.OrderMapper

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    //根据用户id查询该用户的订单
    @Select("select * from t_order where uid = #{uid}")
    List<Order> selectByUid(int uid);   
}

6.UserController

@RestController
public class UserController {

    @Autowired
    UserMapper userMapper;

    @GetMapping("/user/findAll")          //函数一
    public List find() {
        System.out.println(userMapper.selectAllUserAndOrders());
        return userMapper.selectAllUserAndOrders();
    }

    @GetMapping("/user/findUser")        //函数二
    public List findUser() {
        List<User> user = userMapper.find();
        System.out.println(user);
        return user;
    }
}

我走过的弯路：

1.类的成员变量不能有下划线

2.如果表的字段名和类的成员变量相同，可以不做处理。如果不同，需要在类中使用@TableField标注（如标记1），或在Mapper中进行映射（如标记2）。