Mybatis多表查询&分页查询

多表查询

Mybatis只是对单表查询进行增强，对多表查询没有增强。

在查询用户时，顺便把所有订单查询出来。

1. 在User中添加一个不存在的字段，用来进行数据库的映射。并添加对应的toString方法

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

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

1. 创建OrderMapper

@Mapper
public interface OrderMapper extends BaseMapper<Order> {}

1. 手动书写SQL，通过User.id查询对应的Order

@Mapper
public interface UserMapper extends BaseMapper<User> {
    @Select("select * from t_user")
    @Results( {
            @Result(column = "id", property = "id"),
            @Result(column = "username", property = "username"),
            @Result(column = "password", property = "password"),
            @Result(column = "id", property = "orders", javaType = List.class,
                    many = @Many(select = "com.example.quick_demo.mapper.OrderMapper.selectById")
            ),
    })
    List<User> SelectAllUserAndOrders();
}

1. 最后调用方法即可。

代码：

package com.example.mpdemo.entity;

import com.baomidou.mybatisplus.annotation.TableName;

/**
 * @Author Fxdll
 * @Date 2024/5/7 23:25
 * @PackageName:com.example.mpdemo.entity
 * @ClassName: Order
 * @Description: TODO
 * @Version 1.0
 */
@TableName("T_order")
public class Order {
    private int id;
    private String order_time;
    private Double total;
    private int uid;

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

    public int getId() {
        return id;
    }

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

    public String getOrder_time() {
        return order_time;
    }

    public void setOrder_time(String order_time) {
        this.order_time = order_time;
    }

    public Double getTotal() {
        return total;
    }

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

    public int getUid() {
        return uid;
    }

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

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;

/**
 * @Author Fxdll
 * @Date 2024/5/7 22:53
 * @PackageName:com.example.mpdemo.mapper
 * @ClassName: OrderMapper
 * @Description: TODO
 * @Version 1.0
 */
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    //根据用户id查询所有订单
    @Select("SELECT * FROM t_order WHERE id = #{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.mpdemo.mapper.UserMapper.selectById")
            ),
    })
    List<Order> SelectAllOrdersAndUser();
}

package com.example.mpdemo.mapper;

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

import java.util.List;

/**
 * @Author Fxdll
 * @Date 2024/5/4 22:54
 * @PackageName:com.example.mpdemo.mapper
 * @ClassName: UserMapper
 * @Description: TODO
 * @Version 1.0
 */
//用于操作用户表，Mybatis会根据mapper注解，动态实现UserMapper接口(实现类），动态代理技术
    //SPring会自动创建usermapper接口实现类对应的实例
@Mapper
public interface UserMapper{
//查询所有用户
    @Select("select * from user")
    public List<User> find();
//20240506
    //查询用户，根据用户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 = "id", property = "orders", javaType = List.class,
                        //通过OrderMapper的selectByUid方法查询订单 拷贝全路径
                        many = @Many(select = "com.example.mpdemo.mapper.OrderMapper.selectByUid")
                ),
        })
        List<User> SelectAllUserAndOrders();



//历史202405
//    @Select("select * from user where id = #{id}")

    @Update("update user set username = #{username},password = #{password},birthday = #{birthday} where id = #{id}")
    public int update(User user);

    @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
    public int insert(User user);

    @Delete("delete from user where id = #{id}")
    public int delete(Integer id);

    @Select("select * from user where id = #{id}")
    public User findById(Integer id);




}

package com.example.mpdemo.controller;

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.*;

import java.util.List;

/**
 * @Author Fxdll
 * @Date 2024/5/4 22:52
 * @PackageName:com.example.mpdemo.controller
 * @ClassName: UserController
 * @Description: TODO
 * @Version 1.0
 */
@RestController
public class UserController {
    @Autowired
    private UserMapper userMapper;
//0506更新--------------------------------------
    @GetMapping("/user/findAll")
    public List<User> query() {
//        List<User> list = userMapper.find();
//        System.out.println(list);
//        return list;
        return userMapper.SelectAllUserAndOrders();
//        return userMapper.find();
    }
    //待更新------------------------------------------
    //条件查询

    //分页查询

//    0504更新-------------------------------------------
    @PostMapping("/user")
    public String save(User user) {
        int i = userMapper.insert(user);
        if (i > 0) {
            return "插入成功";
        } else {
            return "插入失败";
        }
    }

    @PutMapping("/user")
    public String update(User user) {
        int i = userMapper.update(user);
        if (i > 0) {
            return "更新成功";
        } else {
            return "更新失败";
        }
    }

    @DeleteMapping("/user/{id}")
    public String delete(@PathVariable("id") int id) {
        int i = userMapper.delete(id);
        if (i > 0) {
            return "删除成功";
        } else {
            return "删除失败";
        }
    }
}

分页查询

创建Mybatis配置类

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

直接在Controller中使用Mapper的selectPage方法即可

@GetMapping("/m2")
public IPage m2(){
    Page<User> page = new Page<>(0, 2);  // 0表示页数，2表示每页多少个
    IPage ipage = userMapper.selectPage(page, null);
    System.out.println(ipage);
    return ipage;
}