mybatis-plus多表查询，返回封装形式的json

接口返回的类型有时需要返回一组一组的json格式，例如

如果按照普通的mybatis联表查询，则会返回一个一个的属性，不会被封装起来。我们需要在实体类中添加其他表的实体类，具体操作如下：

1.创建三张表

activity表

activity_user表

activity_project表

2.创建对应的实体类

Activity实体类中添加了ActivityUser实体类和ActivityProject实体类

Activity 实体类

@Data
public class Activity {
    private Integer id;
    private Integer userId;
    private Integer projectId;
    private Date time;
    //    //user中的字段
    private ActivityUser user;
    //    //project中的字段
    private ActivityProject project;
}复制

ActivityUser实体类 

@Data
@TableName("activity_user")
public class ActivityUser {
    @TableField("user_id")
    private Integer userId;
    private String nickname;
    private String avatar;
}复制

ActivityProject实体类 

@Data
@TableName("activity_project")
public class ActivityProject {
    @TableField("project_id")
    private Integer projectId;
    private String name;
    private String action;
    private String event;
}复制

3.编写对应的Mapper做查询操作

我使用的注解式查询方法

ActivityMapper

@Mapper
public interface ActivityMapper extends BaseMapper<Activity> {
    
    @Select("select a.id,au.nickname,au.avatar from activity a,activity_user au where a.user=au.user_id")
    List<ActivityUser> selectUser();
    
    @Select("select a.id,ap.action,ap.event,ap.name from activity a,activity_project ap where a.project=ap.project_id")
    List<ActivityProject> selectProject();
 
    @Select("select id,time from activity,activity_user where activity.user = activity_user.user_id")
    List<Activity> selectActivity();
}复制

1. selectUser方法查询的信息如下

   

1. selectProject方法查询的信息如下

   

1. selectActivity方法查询的信息如下

   

此时，Activity中还没有ActivityUser（user）和ActivityProject（project）的信息。

4.编写对应的Service和Impl实现方法

ActivityService

public interface ActivityService extends IService<Activity> {
    List<ActivityUser> selectUser();
    List<ActivityProject> selectProject();
    List<Activity> selectActivity();
}复制

ActivityServiceImpl

@Service
public class ActivityServiceImpl extends ServiceImpl<ActivityMapper, Activity> implements ActivityService {
    @Autowired
    private ActivityMapper activityMapper;
    @Override
    public List<ActivityUser> selectUser() {
        List<ActivityUser> users = activityMapper.selectUser();
        return users;
    }
 
    @Override
    public List<ActivityProject> selectProject() {
        List<ActivityProject> projects = activityMapper.selectProject();
        return projects;
    }
 
    @Override
    public List<Activity> selectActivity() {
        List<Activity> activities = activityMapper.selectActivity();
        return activities;
    }
}复制

5.编写对应的Controller

AnalysisController

@RestController
@RequestMapping("/api")
public class AnalysisController {
    @Autowired
    private ActivityService activityUserService;
    @SneakyThrows
    @GetMapping("/workplace/activity")
    public List activity(){
        List<ActivityUser> users = activityUserService.selectUser();
        List<ActivityProject> projects = activityUserService.selectProject();
        List<Activity> activities = activityUserService.selectActivity();
        for (int i = 0; i < activities.size(); i++) {
            activities.get(i).setUser(users.get(i));
            activities.get(i).setProject(projects.get(i));
        }
        return activities;
    }
}复制

使用for循环，分别将从activity_user表和activity_project表查询到的信息传给Activity实体类中的user和project属性。

6.测试

此时查询到有很多我们不需要显示的null空字段，我们需要在application.yml文件中添加

spring:
 jackson:
  default-property-inclusion: non_null复制

这样就可以不显示不用的null字段了，再次测试