0x06 MyBatis-Plus多表查询及分页查询-526互联

MyBatis-Plus是针对于MyBatis单表查询进行功能增强的框架，在多表查询上并未修改

在实现复杂关系映射时，可以使用@Results、@Result、@One、@Many注解组合完成复杂关系的配置

多表查询小demo

按照教程写的时候踩了个坑, 弄了好久才弄出来

总的思路是:

要在UserMapper中做好结果集的映射

    @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,
                // 根据用户的id查找订单,可能有多个订单。所以是一对多
                many = @Many(select = "com.example.icfh_springboot1.mapper.OrderMapper.selectByUid")
            )
    })
    List<User> selectAllUserAndOrders();

先写下大致过程:

先启sql服务,在本地3306端口处的mydb数据库中放两张表

注意应该加上前缀啥的以区分

t_user的数据如下:

t_order的数据如下:

由于要在查t_user表的时候查相应的orders, 所以需要在User类中声明orders变量

@TableField注解表示不在t_user表中

声明Order类, 写好getter和setter方法

注意写好@TableName注解

@TableName("t_order")
public class Order {

    @TableId(type= IdType.AUTO)
    private int id;
    private String ordertime;
    private int pay;
    private int uid;

    public int getId() {
        return id;
    }

    public String getOrdertime() {
        return ordertime;
    }

    public int getPay() {
        return pay;
    }

    public int getUid() {
        return uid;
    }

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

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

    public void setPay(int pay) {
        this.pay = pay;
    }

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

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", ordertime='" + ordertime + '\'' +
                ", pay=" + pay +
                ", uid=" + uid +
                '}';
    }
}

在UserMapper接口中写好结果集的映射

@Mapper
public interface UserMapper extends BaseMapper<User> {
    // Mybatis-Plus

    // 直接继承类完成上述的定义操作
    // 可以ctrl进入到BaseMapper


    @Select("select * from t_user where id = #{id}")
    User findByID(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,
                // 根据用户的id查找订单,可能有多个订单。所以是一对多
                many = @Many(select = "com.example.icfh_springboot1.mapper.OrderMapper.selectByUid")
            )
    })
    List<User> selectAllUserAndOrders();

}

controller的处理

@RestController
public class UserDBController {

    @Autowired
    private UserMapper userMapper;

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

    @GetMapping("/user/find/{id}")
    public User findByID(@PathVariable int id){
        System.out.println(id);
        return userMapper.findByID(id);
    }


	// QueryWrapper 条件类
    @GetMapping("/user/findTest")
    public List<User> findByCond(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id","1");
        return userMapper.selectList(queryWrapper);

    }
}