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

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 +
                '}';
    }
}
 
 

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();
    }
 
}

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();
}

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 +
                '}';
    }
}

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;
    }
}

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();
}

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

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;
    }
 
}