1天搞定SpringBoot+Vue全栈开发 (4)多表查询及分页查询

1.多表查询

在多表查询中，mybatis和mybatis plus无区别

User:

package com.example.mpdemo.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("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 + '\'' +
                ", orders=" + orders +
                '}';
    }
}

UserController:

package com.example.mpdemo.controller;

import com.alibaba.druid.pool.ha.selector.StickyDataSourceHolder;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.mpdemo.entity.User;
import com.example.mpdemo.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 UsrController {

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

}

UserMapper:

package com.example.mpdemo.mapper;



import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mpdemo.entity.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

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

    //查询用户及其所有订单
    @Select("select * from 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=@Many(select = "com.example.mpdemo.mapper.OrderMapper.selectByUid" )
                 )
            }
    )
    List<User> selectAllUserAndOrders();
}

Order:

package com.example.mpdemo.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("orderss")
public class Order {
    @TableId(type= IdType.AUTO)
    private int id;
    private String orderTime;
    private int total;
    private int uid;

    //描述用户的所有订单 select id,username,password,orderss from user
    @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 getTotal() {
        return total;
    }

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

    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 "Order{" +
                "id=" + id +
                ", orderTime='" + orderTime + '\'' +
                ", total='" + total + '\'' +
                ", uid=" + uid +
                ", user=" + user +
                '}';
    }
}

OrderController:

package com.example.mpdemo.controller;

import com.example.mpdemo.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.apache.ibatis.annotations.Mapper;

import javax.annotation.Resource;
import java.util.List;

@RestController
public class OrderController {

    @Autowired
    private OrderMapper orderMapper;

    @GetMapping("/order/findAll")
    public List findAll(){
        List orders=orderMapper.selectAllUserAndOrders();
        return orders;
    }
}

OrderMapper:

package com.example.mpdemo.mapper;


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

import java.util.List;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {

    @Select("select * from orderss where uid=#{uid}")
    List<Order> selectByUid(int uid);

    //查询所有订单，同时查询订单的用户
    @Select("select * from orderss")
    @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.mpdemo.mapper.UserMapper.selectById" )
                    )
            }
    )
    List<User> selectAllUserAndOrders();
}

注意！！！！一直查不出order可以把表名字改成orderss。（因为order是sql的关键字）

测试：

2.条件查询

UserConrtroller中加入：

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

测试：

3.分页查询 （通过拦截器实现）

 MyBatisPlusConfig：

package com.example.mpdemo.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;
    }

}

测试：（从0开始，查两个记录）

mp-demo:https://download.csdn.net/download/l141930402/89274949?spm=1001.2014.3001.5501