多表查询与分页查询

多表查询

对于你要查询的东西，相应的接收数据类应该也要做出合适的修改。

MyBatis Plus不能简化完成多表查询。

下面是User类和Order类的声明：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


@TableName("t_memberinfo")
public class User {
    private String MemberID;
    private String Name;
    private String Gender;
    private int Age;
    private String WorkUnit;
    private String Contact;
    private int LevelID;
    private int Points;

    @TableField(exist = false)
    private List<Order> Orders;
    ...
}

查询的时候，MyBatis Plus代理实现的接口Select语句如果从一张表中查不出来这个属性，需要多表连接才能实现的时候，应当加上@TableField(exist = false)注解。

1
2
3
4
5
6
7
8
9


public class Order {
    private String MemberID;
    private String ItemID;
    private String ItemName;
    private String Category;
    private int Quantity;
    private double Price;
    ...
}

对于多表查询，需要实现查找类的Mapper以及其多表连接类的Mapper，写法可以如下：

MemberInfoMapper

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


@Mapper
public interface MemberInfoMapper extends BaseMapper<User> {

    @Select("select * from t_memberinfo where MemberID = #{userID}")
    List<User> getMemberInfo(String userID);

    @Select("select * from t_memberinfo")
    @Results({
            @Result(column = "MemberID",property = "MemberID"),
            @Result(column = "Name",property = "Name"),
            @Result(column = "Gender",property = "Gender"),
            @Result(column = "Age",property = "Age"),
            @Result(column = "WorkUnit",property = "WorkUnit"),
            @Result(column = "Contact",property = "Contact"),
            @Result(column = "LevelID",property = "LevelID"),
            @Result(column = "Points",property = "Points"),
            @Result(column = "MemberID",property = "Orders",javaType = List.class,
                    many = @Many(select = "dev.mczs.springbootstu.mapper.OrderMapper.getOrders")
            )
    })
    List<User> selectAllUserAndOrder();
}

OrderMapper

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


@Mapper
public interface OrderMapper {

    @Select("select * from t_shoppingrecord where MemberID = #{userID}")
    List<Order> getOrders(String userID);

    @Select("select * from t_shoppingrecord")
    @Results({
            @Result(column = "ShoppingID", property = "ShoppingID"),
            @Result(column = "MemberID", property = "MemberID"),
            @Result(column = "ItemID", property = "ItemID"),
            @Result(column = "ItemName",property = "ItemName"),
            @Result(column = "Category",property = "Category"),
            @Result(column = "Quantity",property = "Quantity"),
            @Result(column = "Price",property = "Price"),
            @Result(column = "MemberID",property = "user",javaType = User.class,
                    one = @One(select = "dev.mczs.springbootstu.mapper.MemberInfoMapper.getMemberInfo")
            )
    })
    List<Order> getOrdersByUserID();
}

这里的@Results注解可以使得查询结果和Java变量一一对应。一旦加了该注解，即使变量名一致也得写@Result注解。

@Result(column = "MemberID",property = "Orders",javaType = List.class,many = @Many(select = "dev.mczs.springbootstu.mapper.OrderMapper.getOrders")) 这里的javaType表示property变量的类型，@Many注解表示一对多的数据，相当于SQL中的子查询。

@One 注解则表示一对一的关系。

最后其中一个查询的结果如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42


[
  {
    "Age": 28,
    "Contact": "13800138001",
    "Gender": "男",
    "LevelID": 2,
    "MemberID": "M00001",
    "Name": "张三",
    "Orders": [
      {
        "Category": "食品",
        "ItemID": "G00001",
        "ItemName": "进口牛奶",
        "MemberID": "M00001",
        "Price": 68.5,
        "Quantity": 2
      }
    ],
    "Points": 1200,
    "WorkUnit": "XX科技有限公司"
  },
  {
    "Age": 35,
    "Contact": "13900139002",
    "Gender": "女",
    "LevelID": 1,
    "MemberID": "M00002",
    "Name": "李四",
    "Orders": [
      {
        "Category": "食品",
        "ItemID": "G00004",
        "ItemName": "面包",
        "MemberID": "M00002",
        "Price": 8.5,
        "Quantity": 4
      }
    ],
    "Points": 800,
    "WorkUnit": "XX教育机构"
  }
]

条件查询、分页查询等

MyBatis-Plus 提供了常见SQL语句的封装类，通过其提供的条件构造器能使用C Like的方式实现查询功能，详细请见MyBatis-Plus | Wrapper。

下面看两个常用的查询方式

条件查询

示例：

1
2
3
4
5
6
7


//条件查询
@GetMapping("user/find")
public List<User> findUser(@RequestParam String userID) {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("MemberID",userID);
    return memberInfoMapper.selectList(queryWrapper);
}

分页查询

为了提高查询的效率，一般在只查询去部分信息，这也符合大量数据的时候在前端一般会分页来显示，通过每次查询少量信息，这是前后端中常见的情况，这等价于SQL中的limit语句。

于 v3.5.9 起，PaginationInnerInterceptor 已分离出来。如需使用，则需单独引入 mybatis-plus-jsqlparser 依赖，具体请查看安装一章。
1
implementation 'com.baomidou:mybatis-plus-jsqlparser:3.5.16'

为了使用分页查询，需要先实现一个配置类，参考如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


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

分页查询的实际使用如下：

1
2
3
4
5


@GetMapping("/user/fingByPage")
public IPage fingByPage(@RequestParam int curPage) {
    Page<User> page = new Page<>(curPage, 10);
    return memberInfoMapper.selectPage(page, null);
}

这里返回的是IPage对象，其帮助我们实现了返回当前页数、总页数等信息。