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

2. 创建OrderMapper

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

3. 手动书写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();
}

4. 最后调用方法即可。

分页查询

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